Date: 2020-10-29 17:29:07 +0100
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.39.5 (Oct2020)
CC: @PedroTadim

Last updated: 2020-11-24 11:37:23 +0100

Comment 28239

Date: 2020-10-29 17:29:07 +0100
From: Martin van Dinther <<martin.van.dinther>>

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

Dropping a STREAM TABLE does not remove the associated column info from sys._columns creating invalid table_id FK references

Reproducible: Always

Steps to Reproduce:

CREATE STREAM TABLE strt (id int primary key, nm varchar(123) NOT NULL);
\d
SELECT table_id, * FROM sys._columns WHERE (table_id) NOT IN (SELECT id FROM sys._tables);
-- no rows

DROP TABLE strt;
\d
SELECT table_id, * FROM sys._columns WHERE (table_id) NOT IN (SELECT id FROM sys._tables);
-- shows 2 columns which reference a table_id which does not exist in sys._tables

Actual Results:

Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.39.6 (hg id: 4d973ec7aa13), 'demo'
FOLLOW US on https://twitter.com/MonetDB or https://github.com/MonetDB/MonetDB
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>CREATE STREAM TABLE strt (id int primary key, nm varchar(123) NOT NULL);
operation successful
sql>\d
STREAM TABLE sys.strt
sql>SELECT table_id, * FROM sys._columns WHERE (table_id) NOT IN (SELECT id FROM sys._tables);
+----------+----+------+------+-------------+------------+----------+---------+------+--------+---------+
| table_id | id | name | type | type_digits | type_scale | table_id | default | null | number | storage |
+==========+====+======+======+=============+============+==========+=========+======+========+=========+
+----------+----+------+------+-------------+------------+----------+---------+------+--------+---------+
0 tuples
sql>
sql>DROP TABLE strt;
operation successful
sql>\d
sql>SELECT table_id, * FROM sys._columns WHERE (table_id) NOT IN (SELECT id FROM sys._tables);
+----------+------+------+---------+-------------+------------+----------+---------+-------+--------+---------+
| table_id | id | name | type | type_digits | type_scale | table_id | default | null | number | storage |
+==========+======+======+=========+=============+============+==========+=========+=======+========+=========+
| 8181 | 8177 | id | int | 32 | 0 | 8181 | null | false | 0 | null |
| 8181 | 8180 | nm | varchar | 123 | 0 | 8181 | null | false | 1 | null |
+----------+------+------+---------+-------------+------------+----------+---------+-------+--------+---------+
2 tuples
sql>

Expected Results:

Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.39.6 (hg id: 4d973ec7aa13), 'demo'
FOLLOW US on https://twitter.com/MonetDB or https://github.com/MonetDB/MonetDB
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>CREATE STREAM TABLE strt (id int primary key, nm varchar(123) NOT NULL);
operation successful
sql>\d
STREAM TABLE sys.strt
sql>SELECT table_id, * FROM sys._columns WHERE (table_id) NOT IN (SELECT id FROM sys._tables);
+----------+----+------+------+-------------+------------+----------+---------+------+--------+---------+
| table_id | id | name | type | type_digits | type_scale | table_id | default | null | number | storage |
+==========+====+======+======+=============+============+==========+=========+======+========+=========+
+----------+----+------+------+-------------+------------+----------+---------+------+--------+---------+
0 tuples
sql>
sql>DROP TABLE strt;
operation successful
sql>\d
sql>SELECT table_id, * FROM sys._columns WHERE (table_id) NOT IN (SELECT id FROM sys._tables);
+----------+----+------+------+-------------+------------+----------+---------+------+--------+---------+
| table_id | id | name | type | type_digits | type_scale | table_id | default | null | number | storage |
+==========+====+======+======+=============+============+==========+=========+======+========+=========+
+----------+----+------+------+-------------+------------+----------+---------+------+--------+---------+
0 tuples
sql>

Issue found using new \vsci command in JdbcClient

Comment 28241

Date: 2020-10-29 20:14:22 +0100
From: MonetDB Mercurial Repository <>

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

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

Changeset description:

Add test for bug #7005

Comment 28243

Date: 2020-10-30 14:09:16 +0100
From: MonetDB Mercurial Repository <>

Changeset 140964a47623 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=140964a47623

Changeset description:

Fix for bug #7005, ie don't exclude stream tables from dropping columns