Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

complex SQL query leads to multiple TypeExceptions #2735

monetdb-team opened this issue Nov 30, 2020 · 0 comments

complex SQL query leads to multiple TypeExceptions #2735

monetdb-team opened this issue Nov 30, 2020 · 0 comments


Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

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
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
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)
(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,
(ORDER BY a0004.iter7_nat ASC, a0004.pos8_nat ASC, a0000.pos19_nat ASC)
AS iter32_nat
(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,
(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,
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 for further information
Found 5.5GiB available memory, 2 available cpu cores
Configured for prefix: /opt/monetdb
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: (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 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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant