Crash (and assertion failure) after querying a view which uses a correlated subquery in the select-list

[monetdb-team]

Date: 2017-03-30 14:40:41 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.25.9 (Dec2016-SP2)
CC: @njnes

Last updated: 2020-02-12 09:20:13 +0100

Comment 25191

Date: 2017-03-30 14:40:41 +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:

mserver5: ../../../../dev/sql/backends/monet5/rel_bin.c:2419: rel2bin_project: Assertion `0' failed.

Reproducible: Always

Steps to Reproduce:

CREATE VIEW sys.view_stats AS
SELECT s.name AS schema_nm, s.id AS schema_id, t.name AS table_nm, t.id AS table_id, t.system AS is_system_view
, (SELECT CAST(COUNT(*) as int) FROM sys.columns c WHERE c.table_id = t.id) AS " columns"
FROM sys.tables t JOIN sys.schemas s ON t.schema_id = s.id
WHERE query IS NOT NULL
; --ORDER BY s.name, t.name;

SELECT * FROM sys.view_stats;
-- prints worrying output in console, see below.
SELECT * FROM sys.view_stats WHERE is_system_view;
-- crash
SELECT * FROM sys.view_stats WHERE NOT is_system_view;
-- crash

DROP VIEW sys.view_stats;

Actual Results:

builtin opt gdk_dbpath = /export/scratch2/dinther/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 embedded_py = true
cmdline opt mapi_port = 41000
cmdline opt gdk_debug = 10
MonetDB 5 server v11.25.14
This is an unreleased version
Serving database 'demo', using 8 threads
Compiled for x86_64-unknown-linux-gnu/64bit with 128bit integers
Found 15.589 GiB available main-memory.
Copyright (c) 1993-July 2008 CWI.
Copyright (c) August 2008-2017 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
MonetDB/SQL module loaded
MonetDB/Python module loaded
MonetDB/R module loaded

SQL catalog created, loading sql scripts once
could not find L121.id
t.type_digits
t.type_scale
t.table_id
t.default
t.null
t.number
t.storage
L120.%TID%
mserver5: ../../../../dev/sql/backends/monet5/rel_bin.c:2419: rel2bin_project: Assertion `0' failed.
./start_INSTALL_mserver5.sh: line 7: 3171 Aborted (core dumped) /export/scratch2/dinther/INSTALL/bin/mserver5 -d10 --set embedded_r=true --set embedded_py=true --set mapi_port=41000

bash-4.3$ mclient -p 41000
Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.25.14 (unreleased), 'demo'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>\d
sql>CREATE VIEW sys.view_stats AS
more>SELECT s.name AS schema_nm, s.id AS schema_id, t.name AS table_nm, t.id AS table_id, t.system AS is_system_view
more>, (SELECT CAST(COUNT(*) as int) FROM sys.columns c WHERE c.table_id = t.id) AS " columns"
more> FROM sys.tables t JOIN sys.schemas s ON t.schema_id = s.id
more>WHERE query IS NOT NULL
more>; --ORDER BY s.name, t.name;
operation successful (24.245ms)
sql>\d
VIEW sys.view_stats
sql>SELECT * FROM sys.view_stats;
sql>
bash-4.3$

Expected Results:

No assertion failure. No crash of mserver5 process.

Comment 25192

Date: 2017-03-30 14:47:31 +0200
From: MonetDB Mercurial Repository <>

Changeset 08b7ae073169 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=08b7ae073169

Changeset description:

Add test for Bug #6254

Comment 25193

Date: 2017-04-03 10:25:23 +0200
From: MonetDB Mercurial Repository <>

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

Changeset description:

fixed bug #6254, ie make sure we push left outer down if allowed.

fixed problem in apply renames, only rename on the side which
may have a conflict (ie uses the apply relation/expressions).

Comment 25194

Date: 2017-04-03 19:13:11 +0200
From: @njnes

fixed in dec2016, ie make sure we push left outer apply relational operations down selects when possible

Comment 26068

Date: 2018-01-11 16:22:57 +0100
From: MonetDB Mercurial Repository <>

Changeset 70fff99d6931 made by Joeri van Ruth joeri.van.ruth@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=70fff99d6931

Changeset description:

Approve of changed crash_correlated_subqueries_in_select.Bug-6254 output

Comment 27538

Date: 2020-02-12 09:20:13 +0100
From: MonetDB Mercurial Repository <>

Changeset 6bfc51fb1fb9 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=6bfc51fb1fb9

Changeset description:

Reworked crash_correlated_subqueries_in_select.Bug-6254 test. Don't use system tables on regular tests, because their contents change between release, thus requiring a reapproval