'LEFT OUTER JOIN' has been broken with 3.31

(1) By Hideki Sakamoto (hsakamt) on 2020-05-12 04:45:40 [link]

Hi,

I'm using sqlite3 with Ruby on Rails. When I upgrade sqlite3 to 3.31, 'LEFT OUTER JOIN' has been broken.

I have two tables:

*  "user": user information.
*  "forwarding\_addresses": mail forwarding infomation assosiated with 'user_id' column.

With 3.30, everything ok.

    # sqlite3 /tmp/production.sqlite3
    SQLite version 3.30.1 2019-10-10 20:19:45
    Enter ".help" for usage hints.
    sqlite> SELECT COUNT(*) FROM "forwarding_addresses" LEFT OUTER JOIN "users" ON "users"."id" = "forwarding_addresses"."user_id" WHERE "forwarding_addresses"."user_id" = 21;
    2
    sqlite> SELECT COUNT(*) FROM "forwarding_addresses" LEFT OUTER JOIN "users" ON "users"."id" = "forwarding_addresses"."user_id" WHERE "users"."id" = 21;
    2
    sqlite> SELECT COUNT(*) FROM "forwarding_addresses" LEFT OUTER JOIN "users" ON "users"."id" = "forwarding_addresses"."user_id" WHERE "forwarding_addresses"."user_id" = 21 AND "users"."id" = 21;
    2

However, as of 3.11, queries with both forwarding\_address.user\_id and user.id return incorrect results.

    # sqlite3 /tmp/production.sqlite3
    SQLite version 3.31.1 2020-01-27 19:55:54
    Enter ".help" for usage hints.
    sqlite> SELECT COUNT(*) FROM "forwarding_addresses" LEFT OUTER JOIN "users" ON "users"."id" = "forwarding_addresses"."user_id" WHERE "forwarding_addresses"."user_id" = 21;
    2
    sqlite> SELECT COUNT(*) FROM "forwarding_addresses" LEFT OUTER JOIN "users" ON "users"."id" = "forwarding_addresses"."user_id" WHERE "users"."id" = 21;
    2
    sqlite> SELECT COUNT(*) FROM "forwarding_addresses" LEFT OUTER JOIN "users" ON "users"."id" = "forwarding_addresses"."user_id" WHERE "forwarding_addresses"."user_id" = 21 AND "users"."id" = 21;
    19

The number '19' matches the number of items in all the data in the forwarding\_address table.

    sqlite> SELECT COUNT(*) FROM "forwarding_addresses";
    19

(2) By Richard Hipp (drh) on 2020-05-12 10:19:08 in reply to 1 [link]

Please send the /tmp/production.sqlite3 database file, so that we may
reproduce the problem.  You can email that file directly to drh@sqlite.org
if you like.

(3) By Richard Hipp (drh) on 2020-05-12 12:05:37 in reply to 2 [link]

Thanks for sending the database!

According to [bisect][1] the problem has been fixed on trunk.  Please try
again using the lastest [Prerelease Snapshot][2] and let us know if you
are still having problems.

[1]: https://sqlite.org/src/timeline?bid=yfa11230135n3bfa9cc97dy3a51919ac2yee034fe916nae6dd8d3e9n16aed5d0c6yc9a8defcefnabfb043ebbn0c683c43a6ne0c6b8bdb7
[2]: https://www.sqlite.org/download.html

(4) By Hideki Sakamoto (hsakamt) on 2020-05-12 21:51:48 in reply to 3

Thank you for checking DB and correcting the source.
I tried the latest version and was able to confirm that the problem has been fixed.


    % tar xf ../sqlite-snapshot-202005081822.tar.gz
    % cd sqlite-snapshot-202005081822
    % ./configure
    % make
     :
    % ./sqlite3 /tmp/production.sqlite3 
    SQLite version 3.32.0 2020-05-08 18:22:00
    Enter ".help" for usage hints.
    sqlite> SELECT COUNT(*) FROM "forwarding_addresses" LEFT OUTER JOIN "users" ON "users"."id" = "forwarding_addresses"."user_id" WHERE "forwarding_addresses"."user_id" = 21;
    2
    sqlite> SELECT COUNT(*) FROM "forwarding_addresses" LEFT OUTER JOIN "users" ON "users"."id" = "forwarding_addresses"."user_id" WHERE "users"."id" = 21;
    2
    sqlite> SELECT COUNT(*) FROM "forwarding_addresses" LEFT OUTER JOIN "users" ON "users"."id" = "forwarding_addresses"."user_id" WHERE "forwarding_addresses"."user_id" = 21 AND "users"."id" = 21;
    2

I was very troubled by an incident that resulted in the unintentional disclosure of information, and it was very helpful.

Thanks,