SQLite Forum

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