From the downloads page linked in the original email I'm using Chinook_Sqlite.sqlite File size: 1,067,008 Customer table: 59 records Invoice table: 412 records
Confirmed I got the same results when loading a new empty database with their Chinook_Sqlite.sql script. Query below copied and pasted using 3.24.0 yields 15 records. Again, the question is: Why do you say it "should" return 26? SQLite version 3.24.0 2018-06-04 19:24:41 Enter ".help" for usage hints. sqlite> .version SQLite 3.24.0 2018-06-04 19:24:41 c7ee0833225bfd8c5ec2f9bf62b97c4e04d03bd9566366d5221ac8fb199a87ca gcc-7.3.0 sqlite> select count(*) from Customer; QUERY PLAN `--SCAN TABLE Customer USING COVERING INDEX IFK_CustomerSupportRepId count(*) 59 Run Time: real 0.003 user 0.000000 sys 0.000000 sqlite> select count(*) from Invoice; QUERY PLAN `--SCAN TABLE Invoice USING COVERING INDEX IFK_InvoiceCustomerId count(*) 412 Run Time: real 0.003 user 0.000000 sys 0.000000 sqlite> WITH GbC AS ( SELECT c.Country, c.CustomerId, SUM(i.total) spent ...> FROM Customer c ...> JOIN Invoice i ...> ON i.CustomerId = c.CustomerId ...> GROUP BY 1, 2), ...> MbC AS ( SELECT Country, MAX(spent) spent ...> FROM GbC GROUP BY 1) ...> SELECT outerr.* ...> FROM GbC outerr ...> WHERE outerr.spent = ...> (SELECT spent ...> FROM MbC WHERE Country = outerr.Country) ...> ORDER BY 1, 3 DESC ...> ; QUERY PLAN |--CO-ROUTINE 0x538DE8 | |--SCAN TABLE Customer AS c USING INDEX IPK_Customer | `--SEARCH TABLE Invoice AS i USING INDEX IFK_InvoiceCustomerId (CustomerId=?) |--SCAN SUBQUERY 0x538DE8 AS outerr |--CORRELATED SCALAR SUBQUERY | |--CO-ROUTINE 0x53B818 | | |--CO-ROUTINE 0x568FD8 | | | |--SCAN TABLE Customer AS c USING INDEX IPK_Customer | | | `--SEARCH TABLE Invoice AS i USING AUTOMATIC COVERING INDEX (CustomerId=?) | | |--SCAN SUBQUERY 0x568FD8 | | `--USE TEMP B-TREE FOR GROUP BY | `--SEARCH SUBQUERY 0x53B818 USING AUTOMATIC COVERING INDEX (Country=?) `--USE TEMP B-TREE FOR ORDER BY Country|CustomerId|spent Argentina|56|37.62 Australia|55|37.62 Czech Republic|6|49.62 Denmark|9|37.62 Finland|44|41.62 France|43|40.62 India|58|38.62 Italy|47|37.62 Netherlands|48|40.62 Poland|49|37.62 Portugal|34|39.62 Sweden|51|38.62 USA|26|47.62 United Kingdom|52|37.62 United Kingdom|53|37.62 Run Time: real 0.032 user 0.000000 sys 0.000000 sqlite> -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Edson Poderoso Sent: Monday, September 03, 2018 9:32 AM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Incorrect result when using WHERE with with correlated sub-select query Using the chinook database available at: https://github.com/lerocha/chinook-database The following query should return 26 rows, instead I returns 15. WITH GbC AS ( SELECT c.Country, c.CustomerId, SUM(i.total) spent FROM Customer c JOIN Invoice i ON i.CustomerId = c.CustomerId GROUP BY 1, 2), MbC AS ( SELECT Country, MAX(spent) spent FROM GbC GROUP BY 1) SELECT outerr.* FROM GbC outerr WHERE outerr.spent = (SELECT spent FROM MbC WHERE Country = outerr.Country) ORDER BY 1, 3 DESC -- Edson Poderoso _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
