ANNOUNCE: Rel version 3.013 now available!
Quote from Hugh on February 13, 2019, 2:36 pmI know Chris Date was enthusiastic about these additions, but I regret to say that I didn't alert him to the response (I think it was Brian Selzer's) pointing out that by returning a tuple rather than an SQL-style pseudo relation you fail to allow an outer join expression to be an operand to another outer join.
Actually, the response was yours! ?
It was here: https://forum.thethirdmanifesto.com/forum/topic/support-for-join-alternatives/?part=2 post #19.
As I noted at the time, returning a tuple was intended to avoid missing FROM TUPLE FROM (p LEFTJOIN q) verbosity. Returning a tuple means obtaining a useful result is typically missing FROM (p LEFTJOIN q)), and as noted it's trivial to wrap the subexpression in REL { ... } if desired.
Quote from Dave Voorhis on February 13, 2019, 6:49 pmQuote from Hugh on February 13, 2019, 2:36 pmI know Chris Date was enthusiastic about these additions, but I regret to say that I didn't alert him to the response (I think it was Brian Selzer's) pointing out that by returning a tuple rather than an SQL-style pseudo relation you fail to allow an outer join expression to be an operand to another outer join.
Actually, the response was yours!
It was here: https://forum.thethirdmanifesto.com/forum/topic/support-for-join-alternatives/?part=2 post #19.
As I noted at the time, returning a tuple was intended to avoid missing FROM TUPLE FROM (p LEFTJOIN q) verbosity. Returning a tuple means obtaining a useful result is typically missing FROM (p LEFTJOIN q)), and as noted it's trivial to wrap the subexpression in REL { ... } if desired.
Yes, I know all that, but somebody else, and I'm sure it was Brian, pointed out that you can't do things that you can do in SQL like this:
SELECT <stuff> FROM (A LEFT NATURAL JOIN B) LEFT NATURAL JOIN C AS X
I'm not sure I saw your answer to that. Sorry if I haven't been paying enough attention.
Hugh
Quote from Hugh on February 14, 2019, 11:45 am
Quote from Dave Voorhis on February 13, 2019, 6:49 pmQuote from Hugh on February 13, 2019, 2:36 pmI know Chris Date was enthusiastic about these additions, but I regret to say that I didn't alert him to the response (I think it was Brian Selzer's) pointing out that by returning a tuple rather than an SQL-style pseudo relation you fail to allow an outer join expression to be an operand to another outer join.
Actually, the response was yours!
It was here: https://forum.thethirdmanifesto.com/forum/topic/support-for-join-alternatives/?part=2 post #19.
As I noted at the time, returning a tuple was intended to avoid missing FROM TUPLE FROM (p LEFTJOIN q) verbosity. Returning a tuple means obtaining a useful result is typically missing FROM (p LEFTJOIN q)), and as noted it's trivial to wrap the subexpression in REL { ... } if desired.
Yes, I know all that, but somebody else, and I'm sure it was Brian, pointed out that you can't do things that you can do in SQL like this:
SELECT <stuff> FROM (A LEFT NATURAL JOIN B) LEFT NATURAL JOIN C AS X
I'm not sure I saw your answer to that. Sorry if I haven't been paying enough attention.
Hugh
I don't recall what my answer was there, but my answer here is that the approach that SQL takes is fine for SQL. Outer joins in Tutorial D are rarely needed; note that we've gone for over a decade in Rel without them, and no sense that they're particularly lacking at all, let alone that they need to be chained.
Quote from Dave Voorhis on February 14, 2019, 1:41 pmQuote from Hugh on February 14, 2019, 11:45 am
Quote from Dave Voorhis on February 13, 2019, 6:49 pmQuote from Hugh on February 13, 2019, 2:36 pmI know Chris Date was enthusiastic about these additions, but I regret to say that I didn't alert him to the response (I think it was Brian Selzer's) pointing out that by returning a tuple rather than an SQL-style pseudo relation you fail to allow an outer join expression to be an operand to another outer join.
Actually, the response was yours!
It was here: https://forum.thethirdmanifesto.com/forum/topic/support-for-join-alternatives/?part=2 post #19.
As I noted at the time, returning a tuple was intended to avoid missing FROM TUPLE FROM (p LEFTJOIN q) verbosity. Returning a tuple means obtaining a useful result is typically missing FROM (p LEFTJOIN q)), and as noted it's trivial to wrap the subexpression in REL { ... } if desired.
Yes, I know all that, but somebody else, and I'm sure it was Brian, pointed out that you can't do things that you can do in SQL like this:
SELECT <stuff> FROM (A LEFT NATURAL JOIN B) LEFT NATURAL JOIN C AS X
I'm not sure I saw your answer to that. Sorry if I haven't been paying enough attention.
Hugh
I don't recall what my answer was there, but my answer here is that the approach that SQL takes is fine for SQL. Outer joins in Tutorial D are rarely needed; note that we've gone for over a decade in Rel without them, and no sense that they're particularly lacking at all, let alone that they need to be chained.
Yes, you did write something like that. Actually, when I thought about the matter some more I came to much the same conclusion. Btw, in Rel command line mode I'd probably wrap in rel{...} just to get column headings as in tabular displays of relations.
Hugh
Quote from Hugh on February 14, 2019, 11:45 am... you can't do things [with the Rel outer join result] that you can do in SQL like this:
SELECT <stuff> FROM (A LEFT NATURAL JOIN B) LEFT NATURAL JOIN C AS X
Thanks Hugh. I think what you mean is that you can do that kind of thing in Rel , but only with 'excessive circumlocution'. Firstly as you say
I'd probably wrap in
rel{...}
Then you could manipulate the result. I think it would be more ergonomic to start from A JOIN B and B NOT MATCHING A directly, rather than using the Rel LEFT JOIN form.
Another symptom of the poor compositionality is that the Rel form is not associative. That is, these two do not return the same result (without further manipulation):
blah1( REL{A LEFT JOIN B} ) LEFT JOIN C
A LEFT JOIN blah2( REL{B LEFT JOIN C} )
In which blah1( ), blah2( ) are manipulations to get the nested result into a suitable state to be an operand.
The form I suggested is associative. I would have thought that would be a 'selling point' for Rel/Tutorial D/TTM as against SQL.
Because: the SQL form you give above is not associative (in general). It probably is associative in most practical/industrial situations. It takes quite a bit of reasoning over the schema and nullability of in-common columns and cardinalities and bagness of nested results to understand in which situations it is associative.
Wouldn't that be a teachable moment for a Tutorial language?
Quote from Dave Voorhis on February 14, 2019, 1:41 pm... my answer here is that the approach that SQL takes is fine for SQL. Outer joins in Tutorial D are rarely needed; ...
That seems inconsistent with your earlier
it was introduced mainly to solve a practical use case ...
(More on that use case below.)
... note that we've gone for over a decade in Rel without them, and no sense that they're particularly lacking at all,
SIRA_PRISE has a form of outer join (whose details you got wrong). I believe that has been in place for more than a decade. BS12 had a form (very similar to SIRA_PRISE), as Hugh pointed out in the earlier thread.
I'd be interested to hear from Erwin if anybody's using that form.
How would you [Dave] know they're "lacking"? The Rel form is just a shorthand for something that's relatively easy to write longhand. The form I suggested could be written longhand. (If you didn't have a IMAGE_IN( ), you could use an EXTEND/GROUP in even longer hand; or asulling's code would work, albeit rather long-winded.) So potentially people have been writing that form for a decade. The RVA format of result from what I suggested is at least blessed enough to appear in DBE (Chapter 26).
Would you know if people had been writing that sort of code longhand? It seems to me lack of evidence is not evidence of lack. I suppose your claims are not inconsistent with there being nobody using Rel beyond the classroom. Perhaps the absence of a shorthand corresponding to outer join has turned potential 'customers' back to SQL?
To consider your earlier message:
a practical use case for which it serves well.
I see no use case other than the request from asulling(?) Then what Rel has delivered is not what was asked for. (Neither does it correspond to what's in SIRA_PRISE, nor was in BS12, nor what several commenters suggested.) asulling's suggested result format was
VAR leftjoin REAL RELATION {name CHARACTER, phones RELATION {number INTEGER}} KEY {name};
I note this is a relation (not a tuple, so is composable); with cardinality same as the original person relation (source of attribute name, which is key to both); and catering for the cardinality of person: phone being 1: 0-or-many (so a notable improvement over SQL's outer join); also potentially catering for the phone relation having more attributes than {owner, number} – perhaps an indicator home/work/mobile.
Now asulling's o.p. was rather confused and confusing, as you'd expect from somebody asking a question. So no need to follow the suggestion slavishly. But I see no response in that thread explaining there's anything actually wrong with the idea. (The responses were more along the lines: I'd rather do it this other way.) And DBE Ch 26 gives no strong reason against. (The context of that Chapter is considering base relations in that format. I'd be disinclined to go so far. And anyway nobody's suggesting that here: the outer join is at a 'presentation layer' end of a query.)
I'd laud asulling's suggestion as being easily chainable to " all ["cohesive"] information about the entities in it [the database]", if I might cherry-pick from the 'practical use case' in the o.p.
Whereas with what's delivered in Rel, such chaining would be rather non-cohesive: from chaining person to phone to email, you'd get three missing-type RVAs: persons with neither phone nor email; persons with phone but no email; persons with email but no phone. (If there's a business need for those combinations, that's ergonomically provided with a restriction over the 'flat' relation result I would prefer, treating the RVAs as ZOM attributes = 'Zero-Or-More'.)
Quote from AntC on February 15, 2019, 3:34 amQuote from Dave Voorhis on February 14, 2019, 1:41 pm... my answer here is that the approach that SQL takes is fine for SQL. Outer joins in Tutorial D are rarely needed; ...
That seems inconsistent with your earlier
it was introduced mainly to solve a practical use case ...
(More on that use case below.
Actually, no. It was introduced to solve my use case involving a rather tedious process of assigning personal tutors to students, needlessly complicated by the muddle of historical data I was given. At the same time, asulling fortuitously and coincidentally wanted approximately the same thing. So I built it and it worked and it did what I needed. I don't know whether it met asulling's needs or not. I hadn't needed it before -- various longhand equivalents are fine, just longer -- nor have I needed it since, but it was handy at the time.
Quote from AntC on February 15, 2019, 3:34 am
SIRA_PRISE has a form of outer join (whose details you got wrong). I believe that has been in place for more than a decade. BS12 had a form (very similar to SIRA_PRISE), as Hugh pointed out in the earlier thread.
I'd be interested to hear from Erwin if anybody's using that form.
The birdwatcher demo application and the database administration client use it, and surprisingly more at that than I expected myself.
E.g. listing keys and their attribute sets requires it for otherwise it's going to miss the empty keys.
I have no idea whether and/or how and/or for what purposes anyone is actively using SIRA_PRISE.
Quote from Erwin on February 19, 2019, 1:56 pmQuote from AntC on February 15, 2019, 3:34 am
SIRA_PRISE has a form of outer join (whose details you got wrong). I believe that has been in place for more than a decade. BS12 had a form (very similar to SIRA_PRISE), as Hugh pointed out in the earlier thread.
I'd be interested to hear from Erwin if anybody's using that form.
The birdwatcher demo application and the database administration client use it, and surprisingly more at that than I expected myself.
E.g. listing keys and their attribute sets requires it for otherwise it's going to miss the empty keys.
I have no idea whether and/or how and/or for what purposes anyone is actively using SIRA_PRISE.
As for "surprisingly more than I expected" : in fact it shouldn't be surprising at all given that there might be a use case whenever :
(a) two entity types from the conceptual model are being queried together, and
(b) those two entity types have a 1:n relationship between them, and
(c) the minimum cardinality on the many side is zero, not one.
That scenario cannot be called "rare".
Quote from Erwin on February 19, 2019, 1:56 pm
As for "surprisingly more than I expected"
The first example of an outer join I came across was before Outer Joins were invented. A part-time student had to write an SQL query which returned his company's component part descriptions along with the component's supplier descriptions. Where the company made the part in their factory, the query had to return the corresponding description of the factory instead of the supplier. The query comprised an inner join unioned with something that returned the factory-made parts + descriptions.
When the first publication about Outer Joins came out 2 years later, I realised immediately that the student's query would have been very considerably easier to write if SQL had had an outer join.
Except that it wouldn't; because SQL would not have let us put in the factory descriptions that the student's manager wanted in his query result. They'd have been replaced by NULLs. So we'd have had to write a long-winded SQL query anyway !
Does anyone know why the bizarre design decision to ENFORCE NULLs in SQL outer joins was taken ? I can appreciate that a language that incorporates NULLs should allow the possibility of NULLs in outer join results. But to enforce them to the exclusion of a real value ??!!!! Perhaps this is the origin of some people having concerns about outer joins if they think (understandably) that NULLs are an integral part of them, when they're not.