Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Sqlitelogictest - Wrong result set of complex conditional query #6489

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

Sqlitelogictest - Wrong result set of complex conditional query #6489

monetdb-team opened this issue Nov 30, 2020 · 0 comments

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant