Date: 2016-02-11 15:24:49 +0100
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: -- development
CC: @njnes

Last updated: 2016-06-23 10:24:36 +0200

Comment 21777

Date: 2016-02-11 15:24:49 +0100
From: Martin van Dinther <<martin.van.dinther>>

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

SQL query:
SELECT DISTINCT cast(null as char(1)) AS "PROCEDURE_CAT", "schemas"."name" AS "PROCEDURE_SCHEM", "functions"."name" AS "PROCEDURE_NAME"
, (SELECT COUNT(*) FROM "sys"."args" where "args"."func_id" = "functions"."id" and "args"."number" = 0) AS "PROCEDURE_TYPE"
, CAST(CASE "functions"."language" WHEN 0 THEN "functions"."mod" || '.' || "functions"."func" ELSE "schemas"."name" || '.' || "functions"."name" END AS VARCHAR(1500)) AS "SPECIFIC_NAME"
FROM "sys"."functions", "sys"."schemas" WHERE "functions"."schema_id" = "schemas"."id"
AND "functions"."type" = 2;

results in an assertion failure: dev/sql/backends/monet5/rel_bin.c:2641: rel2bin_groupby: Assertion `0' failed.

Same query but without the DISTINCT keyword does not result in an assertion failure, but returns error: zero_or_one: cardinality violation (45>1)
This is not correct either. It should produce a normal table result.

Same query but without the DISTINCT keyword and without the part "cast(null as char(1)) AS "PROCEDURE_CAT", "
does execute and returns a table result.
So something strange is happening.
More test queries are presented in Steps to Reproduce section.

Reproducible: Always

Steps to Reproduce:

1. start mserver5 (build from default)
2. start mclient
3. run SQL queries:
   SELECT cast(null as char(1)) AS "PROCEDURE_CAT", "schemas"."name" AS "PROCEDURE_SCHEM", "functions"."name" AS "PROCEDURE_NAME"
   , (SELECT COUNT(*) FROM "sys"."args" where "args"."func_id" = "functions"."id" and "args"."number" = 0) AS "PROCEDURE_TYPE"
   , CAST(CASE "functions"."language" WHEN 0 THEN "functions"."mod" || '.' || "functions"."func" ELSE "schemas"."name" || '.' || "functions"."name" END AS VARCHAR(1500)) AS "SPECIFIC_NAME"
   FROM "sys"."functions", "sys"."schemas" WHERE "functions"."schema_id" = "schemas"."id"
   AND "functions"."type" = 2
   AND "schemas"."name" = 'sys'
   ORDER BY "PROCEDURE_SCHEM", "PROCEDURE_NAME", "SPECIFIC_NAME";

SELECT DISTINCT cast(null as char(1)) AS "PROCEDURE_CAT", "schemas"."name" AS "PROCEDURE_SCHEM", "functions"."name" AS "PROCEDURE_NAME"
, (SELECT COUNT(*) FROM "sys"."args" where "args"."func_id" = "functions"."id" and "args"."number" = 0) AS "PROCEDURE_TYPE"
, CAST(CASE "functions"."language" WHEN 0 THEN "functions"."mod" || '.' || "functions"."func" ELSE "schemas"."name" || '.' || "functions"."name" END AS VARCHAR(1500)) AS "SPECIFIC_NAME"
FROM "sys"."functions", "sys"."schemas" WHERE "functions"."schema_id" = "schemas"."id"
AND "functions"."type" = 2
AND "schemas"."name" = 'sys'
ORDER BY "PROCEDURE_SCHEM", "PROCEDURE_NAME", "SPECIFIC_NAME";

Actual Results:

bash-4.3$ mclient -p 41000
Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.22.0 (unreleased), 'demo'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>SELECT "schemas"."name" AS "PROCEDURE_SCHEM", "functions"."name" AS "PROCEDURE_NAME"
more>, CAST((SELECT COUNT() FROM "sys"."args" where "args"."func_id" = "functions"."id" and "args"."number" = 0) AS smallint) AS "PROCEDURE_TYPE"
more>, CAST(CASE "functions"."language" WHEN 0 THEN "functions"."mod" || '.' || "functions"."func" ELSE "schemas"."name" || '.' || "functions"."name" END AS VARCHAR(1500)) AS "SPECIFIC_NAME"
more>FROM "sys"."functions", "sys"."schemas" WHERE "functions"."schema_id" = "schemas"."id"
more>AND "functions"."type" = 2
more>AND "schemas"."name" = 'sys'
more>ORDER BY "PROCEDURE_SCHEM", "PROCEDURE_NAME", "SPECIFIC_NAME";
+-----------------+------------------+----------------+----------------------+
| PROCEDURE_SCHEM | PROCEDURE_NAME | PROCEDURE_TYPE | SPECIFIC_NAME |
+=================+==================+================+======================+
| sys | analyze | 1 | sys.analyze |
| sys | analyze | 1 | sys.analyze |
| sys | analyze | 1 | sys.analyze |
| sys | analyze | 1 | sys.analyze |
| sys | clearrejects | 0 | sys.clearrejects |
| sys | evalalgebra | 1 | sys.evalalgebra |
| sys | fitsattach | 1 | sys.fitsattach |
| sys | fitsload | 1 | sys.fitsload |
| sys | flush_log | 0 | sys.flush_log |
| sys | listdir | 1 | sys.listdir |
| sys | listdirpat | 1 | sys.listdirpat |
| sys | netcdf_attach | 1 | sys.netcdf_attach |
| sys | netcdf_importvar | 1 | sys.netcdf_importvar |
| sys | pause | 1 | sys.pause |
| sys | pause | 1 | sys.pause |
| sys | querylog | 1 | sys.querylog |
| sys | querylog_disable | 0 | sys.querylog_disable |
| sys | querylog_empty | 0 | sys.querylog_empty |
| sys | querylog_enable | 0 | sys.querylog_enable |
| sys | querylog_enable | 1 | sys.querylog_enable |
| sys | resume | 1 | sys.resume |
| sys | resume | 1 | sys.resume |
| sys | reuse | 1 | sys.reuse |
| sys | setsession | 1 | sys.setsession |
| sys | settimeout | 1 | sys.settimeout |
| sys | settimeout | 1 | sys.settimeout |
| sys | shrink | 1 | sys.shrink |
| sys | shutdown | 1 | sys.shutdown |
| sys | shutdown | 1 | sys.shutdown |
| sys | stop | 1 | sys.stop |
| sys | stop | 1 | sys.stop |
| sys | storagemodelinit | 0 | sys.storagemodelinit |
| sys | times | 0 | sys.times |
| sys | vacuum | 1 | sys.vacuum |
+-----------------+------------------+----------------+----------------------+
34 tuples (11.236ms)
sql>SELECT cast(NULL as char(1)) AS "PROCEDURE_CAT", "schemas"."name" AS "PROCEDURE_SCHEM", "functions"."name" AS "PROCEDURE_NAME"
more>, CAST((SELECT COUNT() FROM "sys"."args" where "args"."func_id" = "functions"."id" and "args"."number" = 0) AS smallint) AS "PROCEDURE_TYPE"
more>, CAST(CASE "functions"."language" WHEN 0 THEN "functions"."mod" || '.' || "functions"."func" ELSE "schemas"."name" || '.' || "functions"."name" END AS VARCHAR(1500)) AS "SPECIFIC_NAME"
more>FROM "sys"."functions", "sys"."schemas" WHERE "functions"."schema_id" = "schemas"."id"
more>AND "functions"."type" = 2
more>AND "schemas"."name" = 'sys'
more>ORDER BY "PROCEDURE_SCHEM", "PROCEDURE_NAME", "SPECIFIC_NAME";
zero_or_one: cardinality violation (34>1)
sql>SELECT cast('' as char(1)) AS "PROCEDURE_CAT", "schemas"."name" AS "PROCEDURE_SCHEM", "functions"."name" AS "PROCEDURE_NAME"
more>, CAST((SELECT COUNT() FROM "sys"."args" where "args"."func_id" = "functions"."id" and "args"."number" = 0) AS smallint) AS "PROCEDURE_TYPE"
more>, CAST(CASE "functions"."language" WHEN 0 THEN "functions"."mod" || '.' || "functions"."func" ELSE "schemas"."name" || '.' || "functions"."name" END AS VARCHAR(1500)) AS "SPECIFIC_NAME"
more>FROM "sys"."functions", "sys"."schemas" WHERE "functions"."schema_id" = "schemas"."id"
more>AND "functions"."type" = 2
more>AND "schemas"."name" = 'sys'
more>ORDER BY "PROCEDURE_SCHEM", "PROCEDURE_NAME", "SPECIFIC_NAME";
+---------------+-----------------+------------------+----------------+----------------------+
| PROCEDURE_CAT | PROCEDURE_SCHEM | PROCEDURE_NAME | PROCEDURE_TYPE | SPECIFIC_NAME |
+===============+=================+==================+================+======================+
| | sys | analyze | 1 | sys.analyze |
| | sys | analyze | 1 | sys.analyze |
| | sys | analyze | 1 | sys.analyze |
| | sys | analyze | 1 | sys.analyze |
| | sys | clearrejects | 0 | sys.clearrejects |
| | sys | evalalgebra | 1 | sys.evalalgebra |
| | sys | fitsattach | 1 | sys.fitsattach |
| | sys | fitsload | 1 | sys.fitsload |
| | sys | flush_log | 0 | sys.flush_log |
| | sys | listdir | 1 | sys.listdir |
| | sys | listdirpat | 1 | sys.listdirpat |
| | sys | netcdf_attach | 1 | sys.netcdf_attach |
| | sys | netcdf_importvar | 1 | sys.netcdf_importvar |
| | sys | pause | 1 | sys.pause |
| | sys | pause | 1 | sys.pause |
| | sys | querylog | 1 | sys.querylog |
| | sys | querylog_disable | 0 | sys.querylog_disable |
| | sys | querylog_empty | 0 | sys.querylog_empty |
| | sys | querylog_enable | 0 | sys.querylog_enable |
| | sys | querylog_enable | 1 | sys.querylog_enable |
| | sys | resume | 1 | sys.resume |
| | sys | resume | 1 | sys.resume |
| | sys | reuse | 1 | sys.reuse |
| | sys | setsession | 1 | sys.setsession |
| | sys | settimeout | 1 | sys.settimeout |
| | sys | settimeout | 1 | sys.settimeout |
| | sys | shrink | 1 | sys.shrink |
| | sys | shutdown | 1 | sys.shutdown |
| | sys | shutdown | 1 | sys.shutdown |
| | sys | stop | 1 | sys.stop |
| | sys | stop | 1 | sys.stop |
| | sys | storagemodelinit | 0 | sys.storagemodelinit |
| | sys | times | 0 | sys.times |
| | sys | vacuum | 1 | sys.vacuum |
+---------------+-----------------+------------------+----------------+----------------------+
34 tuples (10.825ms)
sql>SELECT DISTINCT cast('' as char(1)) AS "PROCEDURE_CAT", "schemas"."name" AS "PROCEDURE_SCHEM", "functions"."name" AS "PROCEDURE_NAME"
more>, CAST((SELECT COUNT() FROM "sys"."args" where "args"."func_id" = "functions"."id" and "args"."number" = 0) AS smallint) AS "PROCEDURE_TYPE"
more>, CAST(CASE "functions"."language" WHEN 0 THEN "functions"."mod" || '.' || "functions"."func" ELSE "schemas"."name" || '.' || "functions"."name" END AS VARCHAR(1500)) AS "SPECIFIC_NAME"
more>FROM "sys"."functions", "sys"."schemas" WHERE "functions"."schema_id" = "schemas"."id"
more>AND "functions"."type" = 2
more>AND "schemas"."name" = 'sys'
more>ORDER BY "PROCEDURE_SCHEM", "PROCEDURE_NAME", "SPECIFIC_NAME";
+---------------+-----------------+------------------+----------------+----------------------+
| PROCEDURE_CAT | PROCEDURE_SCHEM | PROCEDURE_NAME | PROCEDURE_TYPE | SPECIFIC_NAME |
+===============+=================+==================+================+======================+
| | sys | analyze | 1 | sys.analyze |
| | sys | clearrejects | 0 | sys.clearrejects |
| | sys | evalalgebra | 1 | sys.evalalgebra |
| | sys | fitsattach | 1 | sys.fitsattach |
| | sys | fitsload | 1 | sys.fitsload |
| | sys | flush_log | 0 | sys.flush_log |
| | sys | listdir | 1 | sys.listdir |
| | sys | listdirpat | 1 | sys.listdirpat |
| | sys | netcdf_attach | 1 | sys.netcdf_attach |
| | sys | netcdf_importvar | 1 | sys.netcdf_importvar |
| | sys | pause | 1 | sys.pause |
| | sys | querylog | 1 | sys.querylog |
| | sys | querylog_disable | 0 | sys.querylog_disable |
| | sys | querylog_empty | 0 | sys.querylog_empty |
| | sys | querylog_enable | 0 | sys.querylog_enable |
| | sys | querylog_enable | 1 | sys.querylog_enable |
| | sys | resume | 1 | sys.resume |
| | sys | reuse | 1 | sys.reuse |
| | sys | setsession | 1 | sys.setsession |
| | sys | settimeout | 1 | sys.settimeout |
| | sys | shrink | 1 | sys.shrink |
| | sys | shutdown | 1 | sys.shutdown |
| | sys | stop | 1 | sys.stop |
| | sys | storagemodelinit | 0 | sys.storagemodelinit |
| | sys | times | 0 | sys.times |
| | sys | vacuum | 1 | sys.vacuum |
+---------------+-----------------+------------------+----------------+----------------------+
26 tuples (9.874ms)
sql>SELECT DISTINCT cast(NULL as char(1)) AS "PROCEDURE_CAT", "schemas"."name" AS "PROCEDURE_SCHEM", "functions"."name" AS "PROCEDURE_NAME"
more>, CAST((SELECT COUNT() FROM "sys"."args" where "args"."func_id" = "functions"."id" and "args"."number" = 0) AS smallint) AS "PROCEDURE_TYPE"
more>, CAST(CASE "functions"."language" WHEN 0 THEN "functions"."mod" || '.' || "functions"."func" ELSE "schemas"."name" || '.' || "functions"."name" END AS VARCHAR(1500)) AS "SPECIFIC_NAME"
more>FROM "sys"."functions", "sys"."schemas" WHERE "functions"."schema_id" = "schemas"."id"
more>AND "functions"."type" = 2
more>AND "schemas"."name" = 'sys'
more>ORDER BY "PROCEDURE_SCHEM", "PROCEDURE_NAME", "SPECIFIC_NAME";
zero_or_one: cardinality violation (34>1)
sql>SELECT DISTINCT cast(NULL as char(1)) AS "PROCEDURE_CAT", "schemas"."name" AS "PROCEDURE_SCHEM", "functions"."name" AS "PROCEDURE_NAME"
more>, (SELECT COUNT() FROM "sys"."args" where "args"."func_id" = "functions"."id" and "args"."number" = 0) AS "PROCEDURE_TYPE"
more>, CAST(CASE "functions"."language" WHEN 0 THEN "functions"."mod" || '.' || "functions"."func" ELSE "schemas"."name" || '.' || "functions"."name" END AS VARCHAR(1500)) AS "SPECIFIC_NAME"
more>FROM "sys"."functions", "sys"."schemas" WHERE "functions"."schema_id" = "schemas"."id"
more>AND "functions"."type" = 2
more>AND "schemas"."name" = 'sys'
more>ORDER BY "PROCEDURE_SCHEM", "PROCEDURE_NAME", "SPECIFIC_NAME";
zero_or_one: cardinality violation (34>1)
sql>
sql>SELECT DISTINCT cast(null as char(1)) AS "PROCEDURE_CAT", "schemas"."name" AS "PROCEDURE_SCHEM", "functions"."name" AS "PROCEDURE_NAME"
more>, (SELECT COUNT() FROM "sys"."args" where "args"."func_id" = "functions"."id" and "args"."number" = 0) AS "PROCEDURE_TYPE"
more>, CAST(CASE "functions"."language" WHEN 0 THEN "functions"."mod" || '.' || "functions"."func" ELSE "schemas"."name" || '.' || "functions"."name" END AS VARCHAR(1500)) AS "SPECIFIC_NAME"
more>FROM "sys"."functions", "sys"."schemas" WHERE "functions"."schema_id" = "schemas"."id"
more>AND "functions"."type" = 2
more>AND "schemas"."name" = 'sys'
more>ORDER BY "PROCEDURE_SCHEM", "PROCEDURE_NAME", "SPECIFIC_NAME";
zero_or_one: cardinality violation (34>1)
sql>
sql>SELECT DISTINCT cast(null as char(1)) AS "PROCEDURE_CAT", "schemas"."name" AS "PROCEDURE_SCHEM", "functions"."name" AS "PROCEDURE_NAME"
more>, (SELECT COUNT() FROM "sys"."args" where "args"."func_id" = "functions"."id" and "args"."number" = 0) AS "PROCEDURE_TYPE"
more>, CAST(CASE "functions"."language" WHEN 0 THEN "functions"."mod" || '.' || "functions"."func" ELSE "schemas"."name" || '.' || "functions"."name" END AS VARCHAR(1500)) AS "SPECIFIC_NAME"
more>FROM "sys"."functions", "sys"."schemas" WHERE "functions"."schema_id" = "schemas"."id"
more>AND "functions"."type" = 2
more>ORDER BY "PROCEDURE_SCHEM", "PROCEDURE_NAME", "SPECIFIC_NAME";
zero_or_one: cardinality violation (45>1)
sql>
sql>SELECT DISTINCT cast(null as char(1)) AS "PROCEDURE_CAT", "schemas"."name" AS "PROCEDURE_SCHEM", "functions"."name" AS "PROCEDURE_NAME"
more>, (SELECT COUNT(*) FROM "sys"."args" where "args"."func_id" = "functions"."id" and "args"."number" = 0) AS "PROCEDURE_TYPE"
more>, CAST(CASE "functions"."language" WHEN 0 THEN "functions"."mod" || '.' || "functions"."func" ELSE "schemas"."name" || '.' || "functions"."name" END AS VARCHAR(1500)) AS "SPECIFIC_NAME"
more>FROM "sys"."functions", "sys"."schemas" WHERE "functions"."schema_id" = "schemas"."id"
more>AND "functions"."type" = 2;
sql>
bash-4.3$

bash-4.3$ ./start_INSTALL_mserver5.sh
builtin opt gdk_dbpath = /export/scratch2/dinther_scratch1/INSTALL/var/monetdb5/dbfarm/demo
builtin opt gdk_debug = 0
builtin opt gdk_vmtrim = no
builtin opt monet_prompt = >
builtin opt monet_daemon = no
builtin opt mapi_port = 50000
builtin opt mapi_open = false
builtin opt mapi_autosense = false
builtin opt sql_optimizer = default_pipe
builtin opt sql_debug = 0
cmdline opt embedded_r = true
cmdline opt mapi_port = 41000
cmdline opt gdk_debug = 10
MonetDB 5 server v11.22.0
This is an unreleased version
Serving database 'demo', using 8 threads
Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs and 128bit integers dynamically linked
Found 15.589 GiB available main-memory.
Copyright (c) 1993-July 2008 CWI.
Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved
Visit http://www.monetdb.org/ for further information
Listening for connection requests on mapi:monetdb://127.0.0.1:41000/
MonetDB/GIS module loaded
Start processing logs sql/sql_logs version 52200
Finished processing logs sql/sql_logs
MonetDB/SQL module loaded
MonetDB/R module loaded

SQL catalog created, loading sql scripts once
loading sql script: 09_like.sql
loading sql script: 10_math.sql
loading sql script: 11_times.sql
loading sql script: 12_url.sql
loading sql script: 13_date.sql
loading sql script: 14_inet.sql
loading sql script: 15_querylog.sql
loading sql script: 16_tracelog.sql
loading sql script: 17_temporal.sql
loading sql script: 20_vacuum.sql
loading sql script: 21_dependency_functions.sql
loading sql script: 22_clients.sql
loading sql script: 23_skyserver.sql
loading sql script: 24_zorder.sql
loading sql script: 25_debug.sql
loading sql script: 26_sysmon.sql
loading sql script: 27_rejects.sql
loading sql script: 39_analytics.sql
loading sql script: 39_analytics_hge.sql
loading sql script: 40_geom.sql
loading sql script: 40_json.sql
loading sql script: 40_json_hge.sql
loading sql script: 41_md5sum.sql
loading sql script: 45_uuid.sql
loading sql script: 46_gsl.sql
loading sql script: 46_profiler.sql
loading sql script: 51_sys_schema_extension.sql
loading sql script: 72_fits.sql
loading sql script: 74_netcdf.sql
loading sql script: 75_storagemodel.sql
loading sql script: 80_statistics.sql
loading sql script: 80_udf.sql
loading sql script: 80_udf_hge.sql
loading sql script: 85_bam.sql
loading sql script: 90_generator.sql
loading sql script: 90_generator_hge.sql
loading sql script: 99_system.sql

could not find L3.L3
L1.PROCEDURE_CAT
L1.PROCEDURE_SCHEM
L1.PROCEDURE_NAME
L3.PROCEDURE_TYPE
L4.SPECIFIC_NAME
L1.PROCEDURE_CAT
L1.PROCEDURE_SCHEM
L1.PROCEDURE_NAME
mserver5: ../../../../dev/sql/backends/monet5/rel_bin.c:2641: rel2bin_groupby: Assertion `0' failed.
./start_INSTALL_mserver5.sh: line 6: 1409 Aborted (core dumped) /export/scratch2/dinther_scratch1/INSTALL/bin/mserver5 -d10 --set embedded_r=true --set mapi_port=41000
bash-4.3$

Expected Results:

no assertion failure.
no "zero_or_one: cardinality violation (34>1)" or "zero_or_one: cardinality violation (45>1)" errors.

Comment 21780

Date: 2016-02-11 15:55:46 +0100
From: MonetDB Mercurial Repository <>

Changeset 16b334deaca3 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=16b334deaca3

Changeset description:

Test script and expected outputs for Bug #3920

Comment 21787

Date: 2016-02-13 13:34:20 +0100
From: MonetDB Mercurial Repository <>

Changeset 3f7b5a1465d3 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=3f7b5a1465d3

Changeset description:

fixed bug #3920 and 3921, make sure we name aggregation expressions
properly.