SQLite Forum

Bug
Login

Bug

(1) By bob (bob001) on 2020-04-30 21:09:48 [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 [link] [source]

you should be able to get them here: https://drive.google.com/file/d/1fqvUiIdnSJH9VRJxqCbopBFMx8O-x9co/view?usp=sharing