Date: 2017-12-08 15:46:05 +0100
From: @PedroTadim
To: SQL devs <>
Version: -- development
CC: @njnes

Last updated: 2018-02-12 16:12:10 +0100

Comment 25972

Date: 2017-12-08 15:46:05 +0100
From: @PedroTadim

User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.84 Safari/537.36
Build Identifier:

The server outputs a wrong result set for a complex select query in one of the SQLitelogictests.

Reproducible: Always

Steps to Reproduce:

1. CREATE TABLE tab0(pk INTEGER PRIMARY KEY, col0 INTEGER, col1 FLOAT, col2 TEXT, col3 INTEGER, col4 FLOAT, col5 TEXT);
2. INSERT INTO tab0 VALUES(0,12,15.70,'jrjqj',55,64.9,'mcxlo');
3. INSERT INTO tab0 VALUES(1,30,85.9,'qevld',61,28.45,'ptqcc');
4. INSERT INTO tab0 VALUES(2,59,33.53,'zcima',67,27.7,'tlkbd');
5. INSERT INTO tab0 VALUES(3,43,43.70,'xhixm',20,57.7,'bgrne');
6. INSERT INTO tab0 VALUES(4,26,22.50,'iqtqt',59,29.40,'fndas');
7. INSERT INTO tab0 VALUES(5,46,69.20,'wmnog',35,16.96,'caknv');
8. INSERT INTO tab0 VALUES(6,70,25.58,'unfoi',21,31.68,'kylow');
9. INSERT INTO tab0 VALUES(7,83,53.2,'lasra',26,63.67,'hfklb');
10. INSERT INTO tab0 VALUES(8,21,87.65,'zbwej',8,82.36,'cmega');
11. INSERT INTO tab0 VALUES(9,65,98.48,'ylwvi',83,60.61,'shwdq');
12. SELECT pk FROM tab0 WHERE ((col0 IS NULL OR (col0 < 19) OR (((col0 > 86))) OR col4 < 33.55 OR (col1 <= 97.79) AND col0 > 23 AND col0 IN (33,7,60,65,32)) AND (col1 IS NULL)) OR (((((col4 IS NULL)))) AND col0 >= 37 OR ((col0 >= 16 OR col0 > 84 AND (col1 >= 7.8)))) AND (((col1 IN (SELECT col4 FROM tab0 WHERE col1 >= 29.15)) OR col1 > 78.13));

Actual Results:

A result set with the integers 2 and 9

Expected Results:

A result set with the integers 1, 8 and 9

I am also debugging it :)

Comment 25973

Date: 2017-12-08 16:11:42 +0100
From: @PedroTadim

Sorry! Wrong query! This is the one failing:

SELECT pk FROM tab0 WHERE ((col4 <= 58.66) AND ((col0 >= 55 OR col0 <= 94 AND (col0 BETWEEN 44 AND 80) OR (((col0 > 49 OR col1 >= 40.18 OR col3 > 40 AND col4 > 46.95 OR col3 IN (42,45,3,49,42,64) AND col0 <= 92 AND (col3 > 56) AND col0 > 43) OR col4 IS NULL) OR col0 IN (86) OR (col4 > 47.25) OR col3 IS NULL AND (col1 < 50.52) AND (col4 > 10.91) OR (col0 IS NULL) AND (col1 < 99.56 AND (col0 IS NULL) AND col3 > 95 OR (((col0 IS NULL)))) AND (col0 < 82) AND ((col0 > 17)) AND (col0 = 84 OR col4 = 38.74) OR ((col3 = 47) AND col0 IN (6,40,51,32,45,87) OR (col1 <= 55.60) AND (col4 = 73.84) OR ((col0 < 52 AND (col1 > 88.42) AND (((col3 > 66) OR col3 > 73)) OR (((((((col3 > 97)) AND (col3 >= 53)) OR col1 < 70.80))) AND col0 > 35) AND ((col3 = 42) AND (col3 <= 87) OR (col3 < 54) AND (((col0 > 21 AND col4 = 51.35) AND (col3 = 53)) OR col1 >= 30.64)) AND (col1 IN (SELECT col4 FROM tab0 WHERE col0 >= 9)) AND (col0 >= 78)))) AND col4 IN (SELECT col1 FROM tab0 WHERE (col1 < 44.79))) AND ((((col0 < 14)) OR col0 IN (SELECT col3 FROM tab0 WHERE col1 > 62.61) AND ((((col0 > 6)))))))) OR (col1 < 82.4 AND (((col3 <= 65 OR col0 < 44 AND col3 <= 79 OR (((col1 = 61.10 AND (col3 > 7))) OR col0 = 78)) AND col0 = 30))) OR col0 > 83) OR (col0 > 52) OR col4 BETWEEN 59.77 AND 33.71 AND col0 >= 11

Actual Results:

A result set with the integers: 2 3 6 7 9

Expected Results:

A result set with the integers: 2 5 6 7 9

Maybe is related to floating-point comparisons.

Comment 25978

Date: 2017-12-10 17:52:29 +0100
From: @njnes

seems to work just fine