SQL executor has problem with (position of) a subquery in a SELECT-list. Inconsistent behavior.

[monetdb-team]

Date: 2015-07-09 18:33:29 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.19.15 (Oct2014-SP4)
CC: @njnes

Last updated: 2015-08-28 13:42:56 +0200

Comment 20984

Date: 2015-07-09 18:33:29 +0200
From: Martin van Dinther <<martin.van.dinther>>

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

Query:
SELECT NULL AS table_catalog, (SELECT s.name FROM sys.schemas s WHERE t.schema_id = s.id) AS table_schema FROM sys.tables t;
fails with a error: zero_or_one: cardinality violation (46>1)

while the similar (only columns in select list are swapped) query:
SELECT (SELECT s.name FROM sys.schemas s WHERE t.schema_id = s.id) AS table_schema, NULL AS table_catalog FROM sys.tables t;
is accepted and processed normally.

Reproducible: Always

Steps to Reproduce:

1. start mserver5 (MonetDB v11.19.15 (Oct2014-SP4))
2. start mclient
3. run SQL commands:
   SELECT NULL AS table_catalog, (SELECT s.name FROM sys.schemas s WHERE t.schema_id = s.id) AS table_schema FROM sys.tables t;
   SELECT (SELECT s.name FROM sys.schemas s WHERE t.schema_id = s.id) AS table_schema, NULL AS table_catalog FROM sys.tables t;

On

Actual Results:

bash-4.2$ mclient -p 49000
Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.19.15 (Oct2014-SP4), 'demo'
Type \q to quit, ? for a list of available commands
auto commit mode: on

sql>SELECT NULL AS table_catalog, (SELECT s.name FROM sys.schemas s WHERE t.schema_id = s.id) AS table_schema FROM sys.tables t;
zero_or_one: cardinality violation (46>1)

sql>SELECT (SELECT s.name FROM sys.schemas s WHERE t.schema_id = s.id) AS table_schema, NULL AS table_catalog FROM sys.tables t;
+--------------+---------------+
| table_schema | table_catalog |
+==============+===============+
| sys | null |
| sys | null |
| sys | null |
| sys | null |
| sys | null |
| sys | null |
| sys | null |
| sys | null |
| sys | null |
| sys | null |
| sys | null |
| sys | null |
| sys | null |
| tmp | null |
| tmp | null |
| tmp | null |
| tmp | null |
| tmp | null |
| tmp | null |
| sys | null |
| sys | null |
| sys | null |
| sys | null |
| sys | null |
| sys | null |
| sys | null |
| sys | null |
| sys | null |
| sys | null |
| sys | null |
| sys | null |
| sys | null |
| sys | null |
| sys | null |
| sys | null |
| sys | null |
| sys | null |
| sys | null |
| sys | null |
| bam | null |
| bam | null |
| bam | null |
| bam | null |
| bam | null |
| sys | null |
| sys | null |
+--------------+---------------+
46 tuples (4.029ms)
sql>

Expected Results:

No execution error for the 1st SELECT query: SELECT NULL AS table_catalog, (SELECT s.name FROM sys.schemas s WHERE t.schema_id = s.id) AS table_schema FROM sys.tables t;

It also happens for MonetDB 5 server v11.22.0 (default branch) and Jul2015 branch.

Additional SQL tests (work only for Jul2015 and default branches):

SELECT NULL AS table_catalog, (SELECT table_type_name FROM sys.table_types WHERE table_type_id = type) AS table_type_name FROM sys.tables t;
-- this fails with Error: zero_or_one: cardinality violation (61>1)

SELECT (SELECT table_type_name FROM sys.table_types WHERE table_type_id = type) AS table_type_name, NULL AS table_catalog FROM sys.tables t;
-- this works fine

SELECT NULL AS table_catalog, (SELECT s.name FROM sys.schemas s WHERE t.schema_id = s.id) AS table_schema, t.name as table_name, (SELECT table_type_name FROM sys.table_types WHERE table_type_id = type) AS table_type_name FROM sys.tables t;
-- this fails with Error: zero_or_one: cardinality violation (61>1)

SELECT (SELECT s.name FROM sys.schemas s WHERE t.schema_id = s.id) AS table_schema, t.name as table_name, (SELECT table_type_name FROM sys.table_types WHERE table_type_id = type) AS table_type_name, NULL AS table_catalog FROM sys.tables t;
-- this works fine

I need this SQL syntax (with "NULL AS table_catalog," as the first column in the SELECT-list) to work properly in order to continue with the creation of the view: information_schema.tables.

Comment 21077

Date: 2015-08-01 15:18:11 +0200
From: MonetDB Mercurial Repository <>

Changeset 30d12a4105a0 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=30d12a4105a0

Changeset description:

Bug fixes

Bug #3759 fixed types of returned bats (ie use timestamps instead of lng) in modules/mal/clients.c
Bug #3769 fixed crash (triggered assert) by properly looking for referenced groupby columns in push aggr down
optimizer in rel_optimizer.c
Bug #3760 fixed correlated subquery handling in rel_select.c, ie merge only distinct expressions.
Bug #3761 fixed by properly adding the zero_or_one aggregation only when needed (ie when the cardinality of
the outer query is a column/table, ie when we expect one value per row for the subquery).
Bug #3364 fixed in sql_privileges.c, ie allow set role when role_id == auth_id (ie default role).
Bug #3365 fixed in sql_privileges.c, the sql_grant_table_privs always added 'all' privileges in one go
	       in rel_schema.c properly pass the login_id or role_id based on the 'from current_{role/user}'
		part of the grant statement
Bug #3476 was already fixed by previous revoke / schema fixes.

in gdk/gdk_select.c fixed problem with anti select and nils (stop before first nil instead of last)

Comment 21085

Date: 2015-08-01 16:22:42 +0200
From: MonetDB Mercurial Repository <>

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

Changeset description:

added tests for bugs 3760 and 3761

Comment 21087

Date: 2015-08-01 16:23:40 +0200
From: @njnes

only call zero or one when needed

Comment 21223

Date: 2015-08-28 13:42:56 +0200
From: @sjoerdmullender

Jul2015 has been released.