(markdown preview removes underline in table names so saved as plain text)
Windows 10, SQLite version 3.31.1
# The following 2 queries should (as far as I can see) return the same data but they don't
>query 1 returns no rows while query 2 returns the expected results;<BR>
I encountered the initial problem in a production db and thought it might be corrupted indexes or data but couldn't pin it down.
I then tried reproducing it in a db with 1 record per table but couldn't - everything worked as expected.
I then created a fresh db with just the data for the 3 tables from the production db but no indexing and found the problem had resurfaced.
## query 1
<code>
select *
from
market_selection_state ss
join market_selection s on (s.sysid = ss.market_selection_sysid )
join market_selection_status ssr on (ssr.sysid = ss.market_selection_status_sysid and ssr.value = "REMOVED")
where
ss.market_selection_sysid = 10 ;
</code>
## query 2
<code>
with removeStatus as
(
select *
from
market_selection_state ss
join market_selection_status ssr on (ssr.sysid = ss.market_selection_status_sysid and ssr.value = "REMOVED")
where
ss.market_selection_sysid = 10
)
select r.* ,s.*
from
removeStatus r
join market_selection s on (s.sysid = r.market_selection_sysid ) ;
</code>
## ddl<BR>
DB Size : ~223MB
<code>
-- 5 rows
CREATE TABLE market_selection_status (
sysid INT,
value TEXT
);
-- 615,934 rows
CREATE TABLE market_selection (
sysid [UNSIGNED INTEGER],
market_sysid [UNSIGNED INTEGER],
participant_sysid [UNSIGNED INTEGER],
id [UNSIGNED INTEGER],
removed SMALLINT
);
-- 6,719,068 rows
CREATE TABLE market_selection_state (
sysid [UNSIGNED INTEGER],
mcmpt NUMERIC (13, 0),
market_selection_sysid [UNSIGNED INTEGER],
adjust_factor [UNSIGNED INTEGER],
market_selection_status_sysid [UNSIGNED INTEGER]
);
</code>
(data csv files,ddl and setup/execute commands are 50K zipped)