wrong count values (1 instead of 0) for correlated aggregation queries

[monetdb-team]

Date: 2017-04-06 16:30:21 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.25.15 (Dec2016-SP3)
CC: @njnes

Last updated: 2017-05-01 13:33:02 +0200

Comment 25201

Date: 2017-04-06 16:30:21 +0200
From: Martin van Dinther <<martin.van.dinther>>

User-Agent: Mozilla/5.0 (X11; Fedora; Linux x86_64; rv:52.0) Gecko/20100101 Firefox/52.0
Build Identifier:

Query:

SELECT s.name, /* s.id AS schema_id, / s.authorization, s.owner, s.system
, (SELECT CAST(COUNT() as int) FROM sys.tables t WHERE t.schema_id = s.id) AS " tables/views"
, (SELECT CAST(COUNT() as int) FROM sys.tables t WHERE t.schema_id = s.id AND t.system AND t.query is NULL) AS " system tables"
, (SELECT CAST(COUNT() as int) FROM sys.tables t WHERE t.schema_id = s.id AND NOT t.system AND t.query is NULL) AS " user tables"
, (SELECT CAST(COUNT() as int) FROM sys.tables t WHERE t.schema_id = s.id AND t.system AND t.query is NOT NULL) AS " system views"
, (SELECT CAST(COUNT() as int) FROM sys.tables t WHERE t.schema_id = s.id AND NOT t.system AND t.query is NOT NULL) AS " user views"
-- SELECT *
FROM sys.schemas s
WHERE s.name IN ('tmp','json','profiler');

Produces wrong number of tables/views (1) where it should be 0.

Reproducible: Always

Steps to Reproduce:

CREATE VIEW sys.schema_stats AS
SELECT s.name, /* s.id AS schema_id, / s.authorization, s.owner, s.system
, (SELECT CAST(COUNT() as int) FROM sys.tables t WHERE t.schema_id = s.id) AS " tables/views"
, (SELECT CAST(COUNT() as int) FROM sys.tables t WHERE t.schema_id = s.id AND t.system AND t.query is NULL) AS " system tables"
, (SELECT CAST(COUNT() as int) FROM sys.tables t WHERE t.schema_id = s.id AND NOT t.system AND t.query is NULL) AS " user tables"
, (SELECT CAST(COUNT() as int) FROM sys.tables t WHERE t.schema_id = s.id AND t.system AND t.query is NOT NULL) AS " system views"
, (SELECT CAST(COUNT() as int) FROM sys.tables t WHERE t.schema_id = s.id AND NOT t.system AND t.query is NOT NULL) AS " user views"
-- SELECT *
FROM sys.schemas s
WHERE s.name IN ('tmp','json','profiler')
; --ORDER BY s.name;

SELECT * FROM sys.schema_stats;

--SELECT count() as " sys tables/views" FROM sys.tables WHERE schema_id IN (SELECT id FROM sys.schemas WHERE name = 'sys');
SELECT count() as " tmp tables/views" FROM sys.tables WHERE schema_id IN (SELECT id FROM sys.schemas WHERE name = 'tmp');
SELECT count() as " json tables/views" FROM sys.tables WHERE schema_id IN (SELECT id FROM sys.schemas WHERE name = 'json');
SELECT count() as " profiler tables/views" FROM sys.tables WHERE schema_id IN (SELECT id FROM sys.schemas WHERE name = 'profiler');

--SELECT count() as " sys system tables" FROM sys.tables WHERE schema_id IN (SELECT id FROM sys.schemas WHERE name = 'sys') AND system AND query is NULL;
SELECT count() as " tmp system tables" FROM sys.tables WHERE schema_id IN (SELECT id FROM sys.schemas WHERE name = 'tmp') AND system AND query is NULL;
SELECT count() as " json system tables" FROM sys.tables WHERE schema_id IN (SELECT id FROM sys.schemas WHERE name = 'json') AND system AND query is NULL;
SELECT count() as " profiler system tables" FROM sys.tables WHERE schema_id IN (SELECT id FROM sys.schemas WHERE name = 'profiler') AND system AND query is NULL;

--SELECT count() as " sys user tables" FROM sys.tables WHERE schema_id IN (SELECT id FROM sys.schemas WHERE name = 'sys') AND NOT system AND query is NULL;
SELECT count() as " tmp user tables" FROM sys.tables WHERE schema_id IN (SELECT id FROM sys.schemas WHERE name = 'tmp') AND NOT system AND query is NULL;
SELECT count() as " json user tables" FROM sys.tables WHERE schema_id IN (SELECT id FROM sys.schemas WHERE name = 'json') AND NOT system AND query is NULL;
SELECT count() as " profiler user tables" FROM sys.tables WHERE schema_id IN (SELECT id FROM sys.schemas WHERE name = 'profiler') AND NOT system AND query is NULL;

--SELECT count() as " sys system views" FROM sys.tables WHERE schema_id IN (SELECT id FROM sys.schemas WHERE name = 'sys') AND system AND query is NOT NULL;
SELECT count() as " tmp system views" FROM sys.tables WHERE schema_id IN (SELECT id FROM sys.schemas WHERE name = 'tmp') AND system AND query is NOT NULL;
SELECT count() as " json system views" FROM sys.tables WHERE schema_id IN (SELECT id FROM sys.schemas WHERE name = 'json') AND system AND query is NOT NULL;
SELECT count() as " profiler system views" FROM sys.tables WHERE schema_id IN (SELECT id FROM sys.schemas WHERE name = 'profiler') AND system AND query is NOT NULL;

--SELECT count() as " sys user views" FROM sys.tables WHERE schema_id IN (SELECT id FROM sys.schemas WHERE name = 'sys') AND NOT system AND query is NOT NULL;
SELECT count() as " tmp user views" FROM sys.tables WHERE schema_id IN (SELECT id FROM sys.schemas WHERE name = 'tmp') AND NOT system AND query is NOT NULL;
SELECT count() as " json user views" FROM sys.tables WHERE schema_id IN (SELECT id FROM sys.schemas WHERE name = 'json') AND NOT system AND query is NOT NULL;
SELECT count() as " profiler user views" FROM sys.tables WHERE schema_id IN (SELECT id FROM sys.schemas WHERE name = 'profiler') AND NOT system AND query is NOT NULL;

DROP VIEW sys.schema_stats;

Actual Results:

name authorization owner system tables/views system tables user tables system views user views
tmp 2 3 true 6 6 1 1 1
json 3 3 true 1 1 1 1 1
profiler 3 3 true 1 1 1 1 1

Expected Results:

name authorization owner system tables/views system tables user tables system views user views
tmp 2 3 true 6 6 0 0 0
json 3 3 true 0 0 0 0 0
profiler 3 3 true 0 0 0 0 0

Comment 25202

Date: 2017-04-06 16:41:03 +0200
From: MonetDB Mercurial Repository <>

Changeset 2082c0b4ada4 made by Martin van Dinther martin.van.dinther@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=2082c0b4ada4

Changeset description:

Add test for Bug #6257

Comment 25211

Date: 2017-04-10 22:36:00 +0200
From: MonetDB Mercurial Repository <>

Changeset ed98f4df49d1 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=ed98f4df49d1

Changeset description:

fixed bug #6257, ie pas proper column when rewriting the LOJ apply down a groupby with
a count(*).
fixed crash caused by a wrong function lookup.

Comment 25212

Date: 2017-04-10 22:37:23 +0200
From: @njnes

fixed. Now we pass a unique column (identity) of the inner table to the count aggregator (count(*) rewrite).