Isolation of generating functions not correct

[monetdb-team]

Date: 2017-08-29 11:17:33 +0200
From: @mlkersten
To: SQL devs <>
Version: -- development
CC: @njnes

Last updated: 2017-10-26 14:01:41 +0200

Comment 25598

Date: 2017-08-29 11:17:33 +0200
From: @mlkersten

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.12; rv:55.0) Gecko/20100101 Firefox/55.0
Build Identifier:

Functions that declare and return local tables are not isolated properly.
The DECLARE local table behaves as being a global DDL, which means that
the actual state becomes unpredictable.

A test case is added.

Reproducible: Always

Steps to Reproduce:

CREATE FUNCTION my_generate_series(start INT, finish INT)
RETURNS TABLE (value INT)
BEGIN
DECLARE TABLE tmp_generate_series(value INT);
DECLARE i INT;
SET i = start;
WHILE (i <= finish) DO
INSERT INTO tmp_generate_series VALUES(i);
SET i = i + 1;
END WHILE;
RETURN tmp_generate_series;
END;

explain select count(*) from my_generate_series(1,5) as t1,
my_generate_series(1,100) as t2;

select count() from my_generate_series(1,5) as t1,
my_generate_series(1,100) as t2;
select count() from my_generate_series(1,5) as t1,
my_generate_series(1,100) as t2;
select count(*) from my_generate_series(1,5) as t1,
my_generate_series(1,100) as t2;

Comment 25599

Date: 2017-08-29 11:22:51 +0200
From: MonetDB Mercurial Repository <>

Changeset dc63791e4dc6 made by Martin Kersten mk@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

Added bug #6397

Comment 25638

Date: 2017-09-17 09:45:27 +0200
From: @njnes

reimplementated declared tables. Each call now uses the mal call stack, solving the isolation problem.

Comment 25640

Date: 2017-09-17 18:01:00 +0200
From: MonetDB Mercurial Repository <>

Changeset 9ccae5c8d830 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

re-implemented declared tables, to solve the side-effect bug #6397.

also lots of fixes for the sqllitelogistest_case bug #6335 (doesn't
crash (or run out of memory) anymore).