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 - Select interval comparisons between floating-points and NULL #6496

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


Copy link

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

Date: 2017-12-13 16:27:43 +0100
From: @PedroTadim
To: SQL devs <>
Version: -- development
CC: @njnes

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

Comment 26002

Date: 2017-12-13 16:27:43 +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:

Adding a BETWEEN clause between a floating-point and a null value produces wrong results. The same behavior happens with other comparison predicates.

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,698,169.42,'apdbu',431,316.15,'sqvis');

  3. INSERT INTO tab0 VALUES(1,538,676.36,'fuqeu',514,685.97,'bgwrq');

  4. INSERT INTO tab0 VALUES(2,90,205.26,'yrrzx',123,836.88,'kpuhc');

  5. INSERT INTO tab0 VALUES(3,620,864.8,'myrdv',877,820.98,'oxkuv');

  6. INSERT INTO tab0 VALUES(4,754,677.3,'iofrg',67,665.49,'bzqba');

  7. INSERT INTO tab0 VALUES(5,107,710.19,'lhfro',286,504.28,'kwwsg');

  8. INSERT INTO tab0 VALUES(6,904,193.16,'eozui',48,698.55,'ejyzs');

  9. INSERT INTO tab0 VALUES(7,606,650.64,'ovmce',417,962.43,'dvkbh');

  10. INSERT INTO tab0 VALUES(8,535,18.11,'ijika',630,489.63,'hpnyu');

  11. INSERT INTO tab0 VALUES(9,501,776.40,'cvygg',725,75.5,'etlyv');

  12. SELECT + - COUNT ( * ) FROM tab0 AS cor0 WHERE NOT ( - col0 ) BETWEEN - + col4 AND NULL;

  13. CREATE TABLE tab1(col0 INTEGER, col1 INTEGER, col2 INTEGER);

  14. INSERT INTO tab1 VALUES(51,14,96);

  15. INSERT INTO tab1 VALUES(85,5,59);

  16. INSERT INTO tab1 VALUES(91,47,68);

  17. SELECT ALL + COUNT ( DISTINCT 64 ) FROM tab1 AS cor0 WHERE col2 + 5 > NULL;

Actual Results:

The first query 0, the second 1

Expected Results:

The first query -5, the second 0

Comment 26003

Date: 2017-12-13 16:30:12 +0100
From: MonetDB Mercurial Repository <>

Changeset 6f3ac2786528 made by Pedro Ferreira in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=6f3ac2786528

Changeset description:

Added tests for bugs 6494, 6495 and 6496

Comment 26020

Date: 2017-12-17 11:10:23 +0100
From: @njnes

fixed bug in groupby_expr reduction. I do not agree on the -5, as the result of a
< NULL should not return anything.

Comment 26022

Date: 2017-12-17 11:12:29 +0100
From: MonetDB Mercurial Repository <>

Changeset ecafd37b7cb4 made by Niels Nes in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=ecafd37b7cb4

Changeset description:

fixed bugs 6496 and 6493, don't over reduce the number of group by expressions.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant