Guys, Just a clarification to my previous post: I'm not looking for a solution to my example problem (but thanx anyway for the suggestions). What I'm really trying to understand is if the behaviour of the LEFT JOIN operator is correct. I think SQlLite produces the wrong result, but please correct me if I'm wrong.
My statement: 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; Should be interpreted as 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; The "C LEFT JOIN A" part is to be evaluated first; produces output for all value in C (only value 5). Each of these rows of C is completed either with all suitable value of A, or NULL if such value does not exist. Sqlite should find 2 candidates from A (values 3 and 4). The a values 1, 2, and 5 are not suitable, since they can never satisfy the ON clause. (Note that for finding these suitable values in A SqLite also needs to look into table B, but that is, I think, an implementtion detail) So the result of "C LEFT JOIN A" is c a - - 5 3 5 4 This intermediate table is LEFT JOINed with B. Again, this means that if a suitable b can be found it will complete the row with this b, and otherwise with NULL. Since both rows can be completed with suitable values of B the end result would be: c a b - - - 5 3 4 5 4 3 But SqLite produces additional 3 rows c a b - - - 5 1 <null> -- additional row 5 2 <null> -- additional row 5 3 5 4 5 5 <null> -- additional row It looks like SqLite interprets the query as (warning pseudo sql) 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); So for each value in A it tries to find a suitable value in B resulting in a b - - 1 <null> 2 <null> 3 4 4 3 5 <null> and then JOINs C with this intermediate table. leading to: c a b - - - 5 1 <null> 5 2 <null> 5 3 4 5 4 3 5 5 <null> So my question if Sqlite produces the correct result remains. I think it doesn't. Regards, Rob The join is valid and the results are perfectly ok. You are using LEFT JOIN, which produces a row even if there is NO MATCH on the RHS, returning NULL for fields selected from there. SELECT * FROM C JOIN A ON A.a*A.a + B.b*B.b = C.c*c.c JOIN B ON A.a*A.a + B.b*B.b = C.c*c.c; c a b ---------- ---------- ---------- 5 3 4 5 4 3 -----Urspr?ngliche Nachricht----- Von: Rob Golsteijn [mailto:rob.golste...@mapscape.eu] Gesendet: Donnerstag, 16. J?nner 2014 11:21 An: sqlite-users@sqlite.org Betreff: [sqlite] Mutally dependent JOIN clauses 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
