Sqlitelogictest: count aggregation with not in operator

[monetdb-team]

Date: 2018-01-19 16:41:01 +0100
From: @PedroTadim
To: SQL devs <>
Version: -- development
CC: @njnes

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

Comment 26104

Date: 2018-01-19 16:41:01 +0100
From: @PedroTadim

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

The query bellow gets wrong results.

Reproducible: Always

Steps to Reproduce:

1. CREATE TABLE tab2(col0 INTEGER, col1 INTEGER, col2 INTEGER);
2. INSERT INTO tab2 VALUES (64,77,40), (75,67,58), (46,51,23);
3. SELECT DISTINCT - 15 * - 19 + - COUNT ( * ) * + 70 FROM tab2 WHERE ( col2 ) NOT IN ( + 12 + col0 );

Actual Results:

145

Expected Results:

75

Comment 26106

Date: 2018-01-19 17:00:03 +0100
From: MonetDB Mercurial Repository <>

Changeset 63944bc2d4a4 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=63944bc2d4a4

Changeset description:

Added tests for bugs 6518 and 6519 (enough bugs for some time)

Comment 26111

Date: 2018-01-24 18:28:44 +0100
From: MonetDB Mercurial Repository <>

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

Changeset description:

fixes for bugs 6518 and 6519
added type checks in (not) in correlated case (only when needed)
in case of correlated (not) in we should add the identity column (and join on that)

Comment 26115

Date: 2018-01-24 18:46:50 +0100
From: @njnes

fixed by adding an identity join for correlated subqueries (not in)