On 10/25/18, R Smith <ryansmit...@gmail.com> wrote: > > CREATE TABLE x(a, b, c); > CREATE TABLE y(a, b); > INSERT INTO x VALUES (1, 0, 1); > INSERT INTO y VALUES (1, 2); > > SELECT x.a FROM x JOIN y ON x.c = y.a WHERE x.b = 1 AND x.b = 1;
It also starts working if you remove the redundant x.b=1 constraint: SELECT x.a FROM x JOIN y ON x.c=y.a WHERE x.b=1; This came about due to an optimization that tries to propagate constants that are defined by WHERE clause constraints. So if you have a WHERE clause constraint like "x.b=1" you can go through and replace every "x.b" expression with the constant "1". But because of the way this is implemented, the original query gets transformed into: SELECT x.a FROM x JOIN y ON x.c=y.a WHERE 1=1 AND 1=1; The first "x.b=1" terms causes the second to change into "1=1" and the second causes the first to change into "1=1". This can only happen if there are two or more "x.b=" constraints in the WHERE clause, which is apparently not one of the millions of test cases in our test suite. (It will be soon!) -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
