On Fri, 17 Jan 2014 11:37:59 +0100
Rob Golsteijn <[email protected]> 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;
>
> 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)
Most of what you say happens or should happen is, if I may say so,
wishful thinking. Parentheses in SQL are semantic, not imperative, so
what's "evaluated" first is up to the evaluator.
Why do you suppose
A.a*A.a + B.b*B.b = C.c*c.c
can be evaluated without examing B? Is there no *possible* value of B
such that B = 25 - 1? Isn't 24 a good candidate? True, no value of
B.b is 24, but that can't be ascertained without examining B, right?
I think your query is malformed, pure and simple. ON is used to refer
to the JOIN operands; WHERE refers to the JOINed set. Since there's no
way to express the outer join criteria in terms of two tables, it's a
cross join, more or less what I showed in my earlier reply.
HTH.
--jkl
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
