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. 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
