SQLite Forum

'LEFT OUTER JOIN' has been broken with 3.31
Login

'LEFT OUTER JOIN' has been broken with 3.31

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

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] [source]

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 [source]

Thanks for sending the database!

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

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

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,