Bug
(1) By bob (bob001) on 2020-04-30 21:09:48 [link] [source]
(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)
(2) By Richard Hipp (drh) on 2020-04-30 21:18:55 in reply to 1 [link] [source]
(data csv files,ddl and setup/execute commands are 50K zipped)
Please email to drh@sqlite.org
(3) By bob (bob001) on 2020-04-30 21:33:15 in reply to 2 [link] [source]
doh ! 50MB zipped not 50K ... email won't take it .. I'll look for another way.
(4) By bob (bob001) on 2020-04-30 21:40:37 in reply to 2 [source]
you should be able to get them here: https://drive.google.com/file/d/1fqvUiIdnSJH9VRJxqCbopBFMx8O-x9co/view?usp=sharing