On Thu, Jan 16, 2014 at 5:21 AM, Rob Golsteijn <rob.golste...@mapscape.eu>wrote:
> Dear List, > > I came across a query with 2 LEFT JOINs of which the join clauses were > mutually dependent. > They did not produce the result I expected, but now I wonder if this is > legal SQL in the first place. > PostgreSQL gives an error for the SQL below. 'missing FROM-clause entry for table "b"'. I think you should not refer to table B in the ON close of C LEFT JOIN A since B is to the right of that join. > I created a small example which illustrates the problem. > The example tries to find Pythagorean Triples (i.e. integers a, b, and c > for which holds a^2 + b^2 = c^2) for given set of possible values for a, b, > and c. > Note: that in my query the JOIN-clause of A refers to table B that is LEFT > JOINed later, and the JOIN clause of B refers back to table A. > > > .headers on > > .null <null> > CREATE TABLE A (a INTEGER); > CREATE TABLE B (b INTEGER); > CREATE TABLE C (c INTEGER); > > INSERT INTO C VALUES(5); > > INSERT INTO A VALUES(1); > INSERT INTO A VALUES(2); > INSERT INTO A VALUES(3); > INSERT INTO A VALUES(4); > INSERT INTO A VALUES(5); > > INSERT INTO B VALUES(1); > INSERT INTO B VALUES(2); > INSERT INTO B VALUES(3); > INSERT INTO B VALUES(4); > INSERT INTO B VALUES(5); > > SELECT * FROM C > LEFT JOIN A ON A.a*A.a + B.b*B.b = C.c*c.c > LEFT JOIN B ON A.a*A.a + B.b*B.b = C.c*c.c; > c|a|b > 5|1|<null> > 5|2|<null> > 5|3|4 > 5|4|3 > 5|5|<null> > > When I look at the first result row and substitute that in the JOIN clause > of A I get > 1*1 + NULL*NULL= 5*5 > which is definitely not true since the lhs of the expression is NULL and > the rhs 25. > This made me wonder which value Sqlite uses for B.b when it is LEFT > JOINing table A. > It is appearantly not the value that is actually used when LEFT JOINing > table B. > > I don't know exactly what to expect from Sqlite. Either > (1) an error indicating that it is illegal to refer in the JOIN clause of > a LEFT JOIN to a table that is LEFT JOINed later; or > (2) only the result rows > c|a|b > 5|3|4 > 5|4|3 > but neither of these options is the case. Is this valid SQL that Sqlite > cannot handle or is this just invalid SQL? > In the latter case it would be nice if SqLite complained about it). > > > > Rob Golsteijn > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
