... = IFNULL(C.c*C.c,0) leads to the lemma: Triangles with sides of all NULL length are right angled (for some values of NULL).
The OP posted the query as an example of ON clauses that reference fields from tables other than those immediately to the left and right of the JOIN, including implicit circular references. These are legal and SQLite can implement them as nested loops, just like any other join. The ON expression(s) are evaluated on each tuple of rows; the LEFT key word just adds a virtual row with all NULL fields in case there was no left join hit. BTW, the parser could determine that the expression is the same in both ON clauses, but at least the code generator realizes it doesn't have to fetch the field values twice. asql> explain 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; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- ... 5 Rewind 0 28 0 00 NULL 6 Rewind 1 27 0 00 NULL 7 Rewind 2 26 0 00 NULL 8 Column 1 0 3 00 a.a R3 = a 9 Multiply 3 3 2 00 NULL R2 = a*a 10 Column 2 0 5 00 b.b R5 = b 11 Multiply 5 5 4 00 NULL R4 = b*b 12 Add 4 2 1 00 NULL R1 = LHS 13 Column 0 0 2 00 c.c R2 = c (clobbers a*a) 14 Multiply 2 2 4 00 NULL R4 = c*c (clobbers b*b) 15 Ne 4 25 1 6a NULL 16 Multiply 3 3 1 00 NULL R1 = a*a (clobbers LHS) 17 Multiply 5 5 6 00 NULL R6 = b*b 18 Add 6 1 4 00 NULL R4 = LHS 19 Multiply 2 2 6 00 NULL R6 = c*c 20 Ne 6 25 4 6a NULL 21 Column 0 0 8 00 c.c 22 Column 1 0 9 00 a.a 23 Column 2 0 10 00 b.b 24 ResultRow 8 3 0 00 NULL 25 Next 2 8 0 01 NULL 26 Next 1 7 0 01 NULL 27 Next 0 6 0 01 NULL ... -----Ursprüngliche Nachricht----- Von: John McKown [mailto:john.archie.mck...@gmail.com] Gesendet: Donnerstag, 16. Jänner 2014 16:26 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Mutally dependent JOIN clauses On Thu, Jan 16, 2014 at 8:47 AM, Igor Tandetnik <i...@tandetnik.org> wrote: > On 1/16/2014 5:21 AM, Rob Golsteijn wrote: > >> 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; >> > > I'm not sure how SQLite interprets this query. In any case, it doesn't > make much sense. I suspect you are looking for something like this: > > SELECT * FROM A, B, C > WHERE A.a*A.a + B.b*B.b = C.c*C.c; > > -- > Igor Tandetnik > org:8080/cgi-bin/mailman/listinfo/sqlite-users<http://sqlite.org:8080/ > cgi-bin/mailman/listinfo/sqlite-users> > The example did not include any rows with a NULL in them, but if that is a possibility, the OP might want: SELECT * FROM A,B,C WHERE IFNULL(A.a*A*a,0)+IFNULL(B.b*B*b,0) = C.c*C.c; So that a row such as A.a=NULL, B.b=1, and C.c=1 would be displayed. Without the IFNULL, I think that NULL+1 would equal NULL, not 1. I don't know if the OP wants this type of row or now. I would guess that he does because I think that was his reason for trying a LEFT JOIN. -- Wasn't there something about a PASCAL programmer knowing the value of everything and the Wirth of nothing? Maranatha! <>< John McKown _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -------------------------------------------------------------------------- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
