>-----Ursprüngliche Nachricht----- >Von: Rob Golsteijn [mailto:rob.golste...@mapscape.eu] >Gesendet: Freitag, 17. Jänner 2014 11:38 >An: sqlite-users@sqlite.org >Betreff: Re: [sqlite] Mutally dependent JOIN clauses > >... >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) >
I don't think it is an implementation detail. Looking at the left left join you get (C left join A on A.a*A.a + B.b*B.b = C.c*c.c) with an expression that evaluates to "don't know (yet)". As a DB Engine you have two options a) return an error code (i.e. "there is no table B to read from here") b) postpone the evaluation until there is a table B (which gives the Cartesian Product as a result set of the join) As the ON clause is supposed to be evaluated BEFORE the join, option a would be the safe bet -------------------------------------------------------------------------- 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