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

Reply via email to