Ok, now I'm seeing the 25 you're talking about.
So we're saying that if you do
with A as (something 1),
B as (something 2)
select foo from A, B
you get something different than
create temp table A as (something 1);
create temp table B as (something 2);
select foo from A, B
That does indeed seem fishy.
sqlite> create temp table 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
...> ;
QUERY PLAN
|--SCAN TABLE Customer AS c USING INDEX IPK_Customer
|--SEARCH TABLE Invoice AS i USING INDEX IFK_InvoiceCustomerId (CustomerId=?)
`--SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?)
Run Time: real 0.000 user 0.000000 sys 0.000000
sqlite> create temp table MbC as SELECT Country, MAX(spent) spent
...> FROM GbC GROUP BY 1
...> ;
QUERY PLAN
|--SCAN TABLE GbC
|--USE TEMP B-TREE FOR GROUP BY
`--SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?)
Run Time: real 0.000 user 0.000000 sys 0.000000
sqlite> SELECT outerr.*
...> FROM GbC outerr
...> WHERE outerr.spent =
...> (SELECT spent
...> FROM MbC WHERE Country = outerr.Country)
...> ORDER BY 1, 3 DESC
...> ;
QUERY PLAN
|--SCAN TABLE GbC AS outerr
|--CORRELATED SCALAR SUBQUERY
| `--SCAN TABLE MbC
`--USE TEMP B-TREE FOR ORDER BY
Country|CustomerId|spent
Argentina|56|37.62
Australia|55|37.62
Austria|7|42.62
Belgium|8|37.62
Brazil|1|39.62
Canada|3|39.62
Chile|57|46.62
Czech Republic|6|49.62
Denmark|9|37.62
Finland|44|41.62
France|43|40.62
Germany|37|43.62
Hungary|45|45.62
India|58|38.62
Ireland|46|45.62
Italy|47|37.62
Netherlands|48|40.62
Norway|4|39.62
Poland|49|37.62
Portugal|34|39.62
Spain|50|37.62
Sweden|51|38.62
USA|26|47.62
United Kingdom|52|37.62
United Kingdom|53|37.62
Run Time: real 0.016 user 0.015600 sys 0.000000
sqlite> drop table MbC;
QUERY PLAN
|--SCAN TABLE sqlite_master
`--SCAN TABLE sqlite_master
Run Time: real 0.000 user 0.000000 sys 0.000000
sqlite> drop table GbC;
QUERY PLAN
|--SCAN TABLE sqlite_master
`--SCAN TABLE sqlite_master
Run Time: real 0.000 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 0x5452C8
| |--SCAN TABLE Customer AS c USING INDEX IPK_Customer
| `--SEARCH TABLE Invoice AS i USING INDEX IFK_InvoiceCustomerId (CustomerId=?)
|--SCAN SUBQUERY 0x5452C8 AS outerr
|--CORRELATED SCALAR SUBQUERY
| |--CO-ROUTINE 0x53B368
| | |--CO-ROUTINE 0x55DB08
| | | |--SCAN TABLE Customer AS c USING INDEX IPK_Customer
| | | `--SEARCH TABLE Invoice AS i USING AUTOMATIC COVERING INDEX
(CustomerId=?)
| | |--SCAN SUBQUERY 0x55DB08
| | `--USE TEMP B-TREE FOR GROUP BY
| `--SEARCH SUBQUERY 0x53B368 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.015 user 0.000000 sys 0.015600
sqlite>
-----Original Message-----
From: David Raymond
Sent: Wednesday, September 05, 2018 10:10 AM
To: SQLite mailing list
Subject: RE: [sqlite] Incorrect result when using WHERE with with correlated
sub-select query
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
