Date: 2010-11-29 13:18:54 +0100
From: Alexander Ulrich <>
To: SQL devs <>
Version: 2.40.1 (Oct2010) [obsolete]
CC: @njnes

Last updated: 2011-03-28 17:31:24 +0200

Comment 15235

Date: 2010-11-29 13:18:54 +0100
From: Alexander Ulrich <>

User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10_6_5; en-US) AppleWebKit/534.7 (KHTML, like Gecko) Chrome/7.0.517.44 Safari/534.7
Build Identifier:

Executing the following SQL query leads to multiple TypeExceptions. The query works fine on PostgreSQL 9.

$ cat foo.sql
WITH
foo (row, col, val) AS
(SELECT row, col, val
FROM (VALUES (1,1,1),
(1,2,2)) AS foo(row, col, val)),

-- binding due to rownum operator
t0000 (item1_int, item2_int, item3_int, iter4_nat) AS
(SELECT a0001.col AS item1_int, a0001.row AS item2_int,
a0001.val AS item3_int,
ROW_NUMBER () OVER (ORDER BY a0001.row ASC, a0001.col ASC) AS
iter4_nat
FROM foo AS a0001),

-- binding due to set operation
t0001 (iter7_nat, pos8_nat) AS
((SELECT a0002.iter4_nat AS iter7_nat, 1 AS pos8_nat
FROM t0000 AS a0002)
UNION ALL
(SELECT a0003.iter4_nat AS iter7_nat, 2 AS pos8_nat
FROM t0000 AS a0003)),

-- binding due to rank operator
t0002 (pos19_nat, iter7_nat, pos8_nat, item1_int, item2_int,
item3_int, iter4_nat, iter32_nat) AS
(SELECT a0000.pos19_nat, a0004.iter7_nat, a0004.pos8_nat, a0005.item1_int,
a0005.item2_int, a0005.item3_int, a0005.iter4_nat,
DENSE_RANK () OVER
(ORDER BY a0004.iter7_nat ASC, a0004.pos8_nat ASC, a0000.pos19_nat ASC)
AS iter32_nat
FROM (VALUES (1),
(2)) AS a0000(pos19_nat),
t0001 AS a0004,
t0000 AS a0005
WHERE a0004.iter7_nat = a0005.iter4_nat),

-- binding due to rank operator
t0003 (item13_int, iter38_nat, pos39_nat, pos40_nat, iter31_nat) AS
(SELECT a0007.item13_int, a0008.iter7_nat AS iter38_nat,
a0008.pos8_nat AS pos39_nat, a0007.pos14_nat AS pos40_nat,
DENSE_RANK () OVER
(ORDER BY a0008.iter7_nat ASC, a0008.pos8_nat ASC, a0007.pos14_nat ASC)
AS iter31_nat
FROM (VALUES (100, 1),
(200, 2)) AS a0007(item13_int,
pos14_nat),
t0001 AS a0008,
t0000 AS a0009
WHERE a0008.iter7_nat = a0009.iter4_nat)

SELECT 1 AS iter29_nat,
((a0006.item3_int + 42) + a0010.item13_int) AS item28_int
FROM t0002 AS a0006,
t0003 AS a0010
WHERE a0006.iter32_nat = a0010.iter31_nat
ORDER BY a0006.iter32_nat ASC;

$ mclient -l sql -d test foo.sql
TypeException:user.s0_1[78]:'bat.append' undefined in: _116:any := bat.append(_115:bat[:oid,:bte], _110:sht, _89:bit)
TypeException:user.s0_1[79]:'bat.reverse' undefined in: _117:any := bat.reverse(_116:any)
TypeException:user.s0_1[80]:'bat.reverse' undefined in: _118:any := bat.reverse(_117:any)
TypeException:user.s0_1[82]:'algebra.project' undefined in: _121:any := algebra.project(_118:any, _119:int)
TypeException:user.s0_1[83]:'bat.reverse' undefined in: _122:any := bat.reverse(_121:any)
TypeException:user.s0_1[85]:'algebra.join' undefined in: _124:any := algebra.join(_123:bat[:oid,:int], _122:any)
TypeException:user.s0_1[87]:'algebra.markT' undefined in: _126:any := algebra.markT(_124:any, _125:oid)
TypeException:user.s0_1[88]:'bat.reverse' undefined in: _127:any := bat.reverse(_126:any)
TypeException:user.s0_1[89]:'algebra.leftjoin' undefined in: _128:any := algebra.leftjoin(_127:any, _109:bat[:oid,:int])
TypeException:user.s0_1[90]:'algebra.sortTail' undefined in: _129:any := algebra.sortTail(_128:any)
TypeException:user.s0_1[92]:'algebra.leftjoin' undefined in: _131:any := algebra.leftjoin(_127:any, _130:bat[:oid,:bte])
TypeException:user.s0_1[93]:'group.refine' undefined in: _132:any := group.refine(_129:any, _131:any)
TypeException:user.s0_1[94]:'bat.reverse' undefined in: _133:any := bat.reverse(_124:any)
TypeException:user.s0_1[96]:'algebra.markT' undefined in: _136:any := algebra.markT(_133:any, _134:oid)
TypeException:user.s0_1[97]:'bat.reverse' undefined in: _137:any := bat.reverse(_136:any)
TypeException:user.s0_1[104]:'algebra.leftjoin' undefined in: _146:any := algebra.leftjoin(_137:any, _145:bat[:oid,:bte])
TypeException:user.s0_1[105]:'group.refine' undefined in: _147:any := group.refine(_132:any, _146:any)
TypeException:user.s0_1[144]:'algebra.leftjoin' undefined in: _197:any := algebra.leftjoin(_137:any, _116:any)
TypeException:user.s0_1[145]:'algebra.leftjoin' undefined in: _198:any := algebra.leftjoin(_196:bat[:oid,:oid], _197:any)
SQLException:SQLengine:Program contains errors

Reproducible: Always

Steps to Reproduce:

1. Installed MonetDB from changeset 38323:decb8b46e1ec of the Oct2010 branch
2. executed SQL query

Actual Results:

(see error messages above)

Expected Results:

$ psql90 test < foo.sql
iter29_nat | item28_int
------------+------------
1 | 143
1 | 243
1 | 143
1 | 243
1 | 144
1 | 244
1 | 144
1 | 244
(8 rows)

$ mserver5 --version
MonetDB server v5.22.2 (64-bit), based on kernel v1.40.2 (64-bit oids)
Not released
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2010 MonetDB B.V., all rights reserved
Visit http://monetdb.cwi.nl/ for further information
Found 5.5GiB available memory, 2 available cpu cores
Configured for prefix: /opt/monetdb
Libraries:
libpcre: 8.10 2010-06-25 (compiled with 8.10)
openssl: OpenSSL 1.0.0a 1 Jun 2010 (compiled with OpenSSL 1.0.0a 1 Jun 2010)
libxml2: 2.7.7 (compiled with 2.7.7)
Compiled by: ulricha@u-081-c089.eap.uni-tuebingen.de (x86_64-apple-darwin10.5.0)
Compilation: gcc -Wall -Wextra -g -Werror-implicit-function-declaration -Werror -Wpointer-arith -Wdeclaration-after-statement -Wundef -Wp,-D_FORTIFY_SOURCE=2
Linking : /usr/libexec/gcc/i686-apple-darwin10/4.2.1/ld

Comment 15254

Date: 2010-12-03 20:38:52 +0100
From: @njnes

this problem is cause by the values(100)/(200). There are type casts missing. Test values_type_cast added. Fixed by a new implementation of rel_values. This also solves problems with too deep recursion of value lists.

Comment 15255

Date: 2010-12-03 20:56:15 +0100
From: @njnes

Changeset 3d33e0899e64 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=3d33e0899e64

Changeset description:

re-implemented 'values'. Solves bug #2735  (and bugs related to large
value lists, which crashed because of too deep recursion).

Comment 15271

Date: 2010-12-04 09:56:50 +0100
From: @grobian

fixed on default, not Oct2010

Comment 15273

Date: 2010-12-04 11:56:18 +0100
From: @grobian

backporting not trivial

Comment 15616

Date: 2011-03-28 17:31:24 +0200
From: @sjoerdmullender

The Mar2011 version has been released.