On 12/1/16, Mark Brand <mabr...@mabrand.nl> wrote: > Hi, > > Using SQLite version 3.15.2, the following SQL returns 0 rows, whereas > I believe it should return 1 row. Any of the commented out alternatives > produces the expected 1 row. > > Mark > > CREATE VIEW W AS > SELECT 0 show_a; > > CREATE VIEW X AS > SELECT 'A' a, 1 v > UNION SELECT 'B', 1; > > CREATE VIEW Y AS > SELECT * FROM W JOIN X; > > SELECT CASE WHEN group_by_a THEN a END a, SUM(v) v > FROM Y > LEFT JOIN (SELECT NULL group_by_a UNION SELECT 1) x2 > ON show_a AND a IS NOT NULL > --ON show_a = 1 AND a IS NOT NULL > -- ON IFNULL(show_a, 0) AND a IS NOT NULL > --ON NOT(NOT(show_a)) AND a IS NOT NULL
or: ON +show_a AND a IS NOT NULL So there are a lot of ways to work around this problem. The problem has been in SQLite for over 10 years, completely unnoticed, because it only comes up when you use a bare column from a subquery as a constraint in a LEFT JOIN, which is apparently something that not many people ever do. It is fixed now on trunk. Thanks again for the bug report. > GROUP BY CASE WHEN group_by_a THEN a END; > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- 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
