Type checking in table functions

[monetdb-team]

Date: 2013-06-02 17:37:49 +0200
From: @mlkersten
To: SQL devs <>
Version: 11.15.7 (Feb2013-SP2)
CC: @njnes

Last updated: 2013-07-03 08:48:01 +0200

Comment 18770

Date: 2013-06-02 17:37:49 +0200
From: @mlkersten

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

The test table_functions.sql has been added. When ran, it produces two type errors and misses the insertion of a tuple in the temporary table.

Reproducible: Always

Actual Results:

sql>CREATE FUNCTION f1(i int) RETURNS TABLE (j int) BEGIN RETURN TABLE(SELECT i); END;
operation successful (20.375ms)
sql>CREATE FUNCTION f2(i int) RETURNS TABLE (j int) BEGIN RETURN TABLE(SELECT i UNION SELECT 2 * i); END;
operation successful (13.811ms)
sql>CREATE FUNCTION f3(i bigint) RETURNS TABLE (j int) BEGIN RETURN TABLE(SELECT i UNION SELECT 2 * i); END;
operation successful (13.805ms)
sql>CREATE FUNCTION f4(i int) RETURNS TABLE (j int) BEGIN RETURN TABLE(SELECT i UNION SELECT i); END;
operation successful (14.160ms)
sql>
sql>SELECT * FROM f1(1);
+------+
| j |
+======+
| 1 |
+------+
1 tuple (1.077ms)
sql>SELECT * FROM f2(3);
TypeException:user.f2[27]:RETURN type mismatch at type 'bat[:oid,:lng]'
TypeException:user.s2_1[3]:'user.f2' undefined in: _5:bat[:oid,:int] := user.f2(_4:int)
program contains errors
sql>SELECT * FROM f3(5);
TypeException:user.f3[27]:RETURN type mismatch at type 'bat[:oid,:lng]'
TypeException:user.s3_1[3]:'user.f3' undefined in: _5:bat[:oid,:int] := user.f3(_4:lng)
program contains errors
sql>SELECT * FROM f4(7);
+------+
| j |
+======+
| 7 |
+------+
1 tuple (1.543ms)

Comment 18771

Date: 2013-06-02 19:30:54 +0200
From: @njnes

Indeed a type check is missing. The f4 should however return a single value, unless you change UNION into union ALL.

Comment 18783

Date: 2013-06-06 19:26:19 +0200
From: MonetDB Mercurial Repository <>

Changeset 52ed955b70c9 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=52ed955b70c9

Changeset description:

fixed bug #3279 and 3278. We properly look up the order by columns.
And keep table names with all sub-relations.
Fixed bug #3296, ie fixed type checking on table returning functions.