You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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
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:
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:
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).
The text was updated successfully, but these errors were encountered: