Possible bug with table generating functions
Is there a restriction on the use of the same table generating function in the same select statement? This relates to the following possible bug: -- reimplement generate_series for MonetDBLite 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; --- This seems to work quite nicely: select * from my_generate_series(1,10); -- and it's fast: select count(*) from my_generate_series(1,100000); -- however, I get odd and non-deterministic counts for the following: select count(*) from my_generate_series(1,100) as t1, my_generate_series(1,100) as t2; This is using MonetDB v11.23.3 (Jun2016) that was compiled with embedded R, but not run with --set embedded_r=true. I got similar errors with MonetDBLite. Kindly, Mark.
Hi Mark, this works fine for me using the latest Jul2017-SP1 release; see log below. Could you possibly upgrade to Jul2017-SP1? Best, Stefan Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2017-SP1) Database: MonetDB v11.27.5 (Jul2017-SP1), 'demo' Type \q to quit, \? for a list of available commands auto commit mode: on sql>CREATE FUNCTION my_generate_series(start INT, finish INT) more>RETURNS TABLE (value INT) more>BEGIN more> DECLARE TABLE tmp_generate_series(value INT); more> DECLARE i INT; more> SET i = start; more> WHILE (i <= finish) DO more> INSERT INTO tmp_generate_series VALUES(i); more> SET i = i + 1; more> END WHILE; more> RETURN tmp_generate_series; more>END; operation successful (15.340ms) sql>--- This seems to work quite nicely: sql>select * from my_generate_series(1,10); +-------+ | value | +=======+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | +-------+ 10 tuples (12.868ms) sql>-- and it's fast: sql>select count(*) from my_generate_series(1,100000); +--------+ | L6 | +========+ | 100000 | +--------+ 1 tuple (566.682ms) sql>-- however, I get odd and non-deterministic counts for the following: sql>select count(*) from my_generate_series(1,100) as t1, more>my_generate_series(1,100) as t2; +-------+ | L7 | +=======+ | 10000 | +-------+ 1 tuple (8.668ms) sql>select count(*) from my_generate_series(1,100) as t1,my_generate_series(1,100) as t2; +-------+ | L7 | +=======+ | 10000 | +-------+ 1 tuple (14.357ms) sql>select count(*) from my_generate_series(1,100) as t1,my_generate_series(1,100) as t2; +-------+ | L7 | +=======+ | 10000 | +-------+ 1 tuple (16.024ms) sql>select count(*) from my_generate_series(1,100) as t1,my_generate_series(1,100) as t2; +-------+ | L7 | +=======+ | 10000 | +-------+ 1 tuple (7.647ms) ----- On Aug 29, 2017, at 10:44 AM, Mark Clements mark.clements@ki.se wrote:
Is there a restriction on the use of the same table generating function in the same select statement? This relates to the following possible bug:
-- reimplement generate_series for MonetDBLite 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; --- This seems to work quite nicely: select * from my_generate_series(1,10); -- and it's fast: select count(*) from my_generate_series(1,100000); -- however, I get odd and non-deterministic counts for the following: select count(*) from my_generate_series(1,100) as t1, my_generate_series(1,100) as t2;
This is using MonetDB v11.23.3 (Jun2016) that was compiled with embedded R, but not run with --set embedded_r=true. I got similar errors with MonetDBLite.
Kindly, Mark. _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
Hi, apparently, I did not properly understand that the problem does not occur if the two function calls are identical as initially reported; see also https://www.monetdb.org/bugzilla/show_bug.cgi?id=6397 Stefan ----- On Aug 29, 2017, at 11:11 AM, Stefan Manegold Stefan.Manegold@cwi.nl wrote:
Hi Mark,
this works fine for me using the latest Jul2017-SP1 release; see log below.
Could you possibly upgrade to Jul2017-SP1?
Best, Stefan
Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2017-SP1) Database: MonetDB v11.27.5 (Jul2017-SP1), 'demo' Type \q to quit, \? for a list of available commands auto commit mode: on sql>CREATE FUNCTION my_generate_series(start INT, finish INT) more>RETURNS TABLE (value INT) more>BEGIN more> DECLARE TABLE tmp_generate_series(value INT); more> DECLARE i INT; more> SET i = start; more> WHILE (i <= finish) DO more> INSERT INTO tmp_generate_series VALUES(i); more> SET i = i + 1; more> END WHILE; more> RETURN tmp_generate_series; more>END; operation successful (15.340ms) sql>--- This seems to work quite nicely: sql>select * from my_generate_series(1,10); +-------+ | value | +=======+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | +-------+ 10 tuples (12.868ms) sql>-- and it's fast: sql>select count(*) from my_generate_series(1,100000); +--------+ | L6 | +========+ | 100000 | +--------+ 1 tuple (566.682ms) sql>-- however, I get odd and non-deterministic counts for the following: sql>select count(*) from my_generate_series(1,100) as t1, more>my_generate_series(1,100) as t2; +-------+ | L7 | +=======+ | 10000 | +-------+ 1 tuple (8.668ms) sql>select count(*) from my_generate_series(1,100) as t1,my_generate_series(1,100) as t2; +-------+ | L7 | +=======+ | 10000 | +-------+ 1 tuple (14.357ms) sql>select count(*) from my_generate_series(1,100) as t1,my_generate_series(1,100) as t2; +-------+ | L7 | +=======+ | 10000 | +-------+ 1 tuple (16.024ms) sql>select count(*) from my_generate_series(1,100) as t1,my_generate_series(1,100) as t2; +-------+ | L7 | +=======+ | 10000 | +-------+ 1 tuple (7.647ms)
----- On Aug 29, 2017, at 10:44 AM, Mark Clements mark.clements@ki.se wrote:
Is there a restriction on the use of the same table generating function in the same select statement? This relates to the following possible bug:
-- reimplement generate_series for MonetDBLite 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; --- This seems to work quite nicely: select * from my_generate_series(1,10); -- and it's fast: select count(*) from my_generate_series(1,100000); -- however, I get odd and non-deterministic counts for the following: select count(*) from my_generate_series(1,100) as t1, my_generate_series(1,100) as t2;
This is using MonetDB v11.23.3 (Jun2016) that was compiled with embedded R, but not run with --set embedded_r=true. I got similar errors with MonetDBLite.
Kindly, Mark. _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
participants (2)
-
Mark Clements -
Stefan Manegold