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 pedro.ferreira@monetdbsolutions.com 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 niels@cwi.nl 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.