Sqlitelogictest: Aggregation query with distinct clause produces duplicated rows

[monetdb-team]

Date: 2018-05-12 15:20:56 +0200
From: @PedroTadim
To: SQL devs <>
Version: 11.29.3 (Mar2018)
CC: @njnes

Last updated: 2018-06-20 11:02:16 +0200

Comment 26445

Date: 2018-05-12 15:20:56 +0200
From: @PedroTadim

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

The aggregation query bellow produces two identical rows in the result set despite the use of DISTINCT qualifier. The having clause has no effect in the result set.

Reproducible: Always

Steps to Reproduce:

1. CREATE TABLE tab0(col0 INTEGER, col1 INTEGER, col2 INTEGER);
2. INSERT INTO tab0 VALUES(83,0,38),(26,0,79),(43,81,24);
3. SELECT DISTINCT + + col1, col1 FROM tab0 AS cor0 GROUP BY col0, col1 HAVING NOT NULL IS NOT NULL;

Actual Results:

+------+------+
| col1 | col1 |
+======+======+
| 0 | 0 |
| 0 | 0 |
| 81 | 81 |
+------+------+

Expected Results:

+------+------+
| col1 | col1 |
+======+======+
| 0 | 0 |
| 81 | 81 |
+------+------+

Comment 26446

Date: 2018-05-12 15:24:29 +0200
From: MonetDB Mercurial Repository <>

Changeset ce9b9c9ea18b 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=ce9b9c9ea18b

Changeset description:

Added test for bug #6603

Comment 26447

Date: 2018-05-12 17:19:58 +0200
From: MonetDB Mercurial Repository <>

Changeset e8375b6f4bce 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=e8375b6f4bce

Changeset description:

fixed bug #6603 (ony reduce groupby/distinct if all groupby cols
are in the distinct list)