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

SQL query produces error with MAL statements #2908

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

SQL query produces error with MAL statements #2908

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


Copy link

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

Date: 2011-10-19 15:38:05 +0200
From: @sjoerdmullender
To: SQL devs <>
Version: 11.5.3 (Aug2011-SP1) [obsolete]

Last updated: 2011-10-26 13:22:04 +0200

Comment 16443

Date: 2011-10-19 15:38:05 +0200
From: @sjoerdmullender

The query that the ODBC driver generates for the call to SQLTables with catalog="", schema="", table name="%", table type="TABLE" results in an error with lots of MAL code in the error message.

The actual query that is used is:

select cast(null as varchar(1)) as table_cat, s."name" as table_schem, t."name" as table_name, case when t."type" = 0 and t."system" = false and t."temporary" = 0 then cast('TABLE' as varchar(20)) when t."type" = 0 and t."system" = true and t."temporary" = 0 then cast('SYSTEM TABLE' as varchar(20)) when t."type" = 1 then cast('VIEW' as varchar(20)) when t."type" = 0 and t."system" = false and t."temporary" = 1 then cast('LOCAL TEMPORARY' as varchar(20)) else cast('INTERNAL TABLE TYPE' as varchar(20)) end as table_type, cast('' as varchar(1)) as remarks from sys."schemas" s, sys."tables" t where s."id" = t."schema_id" and t."name" like '%' and (1 = 0 or (t."type" = 0 and t."system" = false and t."temporary" = 0)) order by table_type, table_schem, table_name;

The output is:

TypeException:user.s2_1[33]:'algebra.markT' undefined in: _72:any := algebra.markT(_71:bit, _67:oid)
TypeException:user.s2_1[72]:'algebra.kunion' undefined in: _125:any := algebra.kunion(_124:bat[:oid,:oid], _72:any)
TypeException:user.s2_1[73]:'algebra.markT' undefined in: _126:any := algebra.markT(_125:any, _66:oid)
TypeException:user.s2_1[74]:'bat.reverse' undefined in: _127:any := bat.reverse(_126:any)
TypeException:user.s2_1[75]:'algebra.leftjoin' undefined in: _128:any := algebra.leftjoin(_127:any, _63:bat[:oid,:int])
TypeException:user.s2_1[76]:'bat.reverse' undefined in: _129:any := bat.reverse(_128:any)
TypeException:user.s2_1[88]:'algebra.join' undefined in: _146:any := algebra.join(_145:bat[:oid,:int], _129:any)
TypeException:user.s2_1[89]:'algebra.markT' undefined in: _147:any := algebra.markT(_146:any, _57:oid)
TypeException:user.s2_1[90]:'bat.reverse' undefined in: _148:any := bat.reverse(_147:any)
TypeException:user.s2_1[91]:'algebra.leftjoin' undefined in: _149:any := algebra.leftjoin(_148:any, _56:bat[:oid,:str])
TypeException:user.s2_1[110]:'algebra.markT' undefined in: _175:any := algebra.markT(_174:bit, _170:oid)
TypeException:user.s2_1[173]:'algebra.kunion' undefined in: _258:any := algebra.kunion(_257:bat[:oid,:oid], _175:any)
TypeException:user.s2_1[174]:'algebra.markT' undefined in: _259:any := algebra.markT(_258:any, _169:oid)
TypeException:user.s2_1[175]:'bat.reverse' undefined in: _260:any := bat.reverse(_259:any)
TypeException:user.s2_1[176]:'algebra.leftjoin' undefined in: _261:any := algebra.leftjoin(_260:any, _168:bat[:oid,:int])
TypeException:user.s2_1[177]:'bat.reverse' undefined in: _262:any := bat.reverse(_261:any)
TypeException:user.s2_1[178]:'algebra.join' undefined in: _263:any := algebra.join(_145:bat[:oid,:int], _262:any)
TypeException:user.s2_1[179]:'algebra.markT' undefined in: _264:any := algebra.markT(_263:any, _155:oid)
TypeException:user.s2_1[180]:'bat.reverse' undefined in: _265:any := bat.reverse(_264:any)
TypeException:user.s2_1[181]:'algebra.leftjoin' undefined in: _266:any := algebra.leftjoin(_265:any, _56:bat[:oid,:str])
TypeException:user.s2_1[183]:'bat.append' undefined in: _270:any := bat.append(_267:bat[:oid,:str], _266:any, _271:bit)
TypeException:user.s2_1[184]:'bat.append' undefined in: _272:any := bat.append(_270:any, _149:any, _271:bit)
TypeException:user.s2_1[185]:'algebra.project' undefined in: _273:any := algebra.project(_272:any, _33:str)
TypeException:user.s2_1[201]:'algebra.leftjoin' undefined in: _294:any := algebra.leftjoin(_127:any, _95:bat[:oid,:str])
TypeException:user.s2_1[203]:'bat.reverse' undefined in: _296:any := bat.reverse(_146:any)
TypeException:user.s2_1[204]:'algebra.markT' undefined in: _297:any := algebra.markT(_296:any, _295:oid)
TypeException:user.s2_1[205]:'bat.reverse' undefined in: _298:any := bat.reverse(_297:any)
TypeException:user.s2_1[206]:'algebra.leftjoin' undefined in: _299:any := algebra.leftjoin(_298:any, _294:any)
TypeException:user.s2_1[213]:'algebra.leftjoin' undefined in: _306:any := algebra.leftjoin(_260:any, _207:bat[:oid,:str])
TypeException:user.s2_1[215]:'bat.reverse' undefined in: _309:any := bat.reverse(_263:any)
TypeException:user.s2_1[216]:'algebra.markT' undefined in: _310:any := algebra.markT(_309:any, _307:oid)
TypeException:user.s2_1[217]:'bat.reverse' undefined in: _311:any := bat.reverse(_310:any)
TypeException:user.s2_1[218]:'algebra.leftjoin' undefined in: _312:any := algebra.leftjoin(_311:any, _306:any)
TypeException:user.s2_1[220]:'bat.append' undefined in: _316:any := bat.append(_313:bat[:oid,:str], _312:any, _317:bit)
TypeException:user.s2_1[221]:'bat.append' undefined in: _318:any := bat.append(_316:any, _299:any, _317:bit)
TypeException:user.s2_1[237]:'algebra.leftjoin' undefined in: _340:any := algebra.leftjoin(_127:any, _105:bat[:oid,:sht])
TypeException:user.s2_1[238]:'algebra.leftjoin' undefined in: _341:any := algebra.leftjoin(_298:any, _340:any)
TypeException:user.s2_1[248]:'algebra.leftjoin' undefined in: _351:any := algebra.leftjoin(_260:any, _350:bat[:oid,:sht])
TypeException:user.s2_1[249]:'algebra.leftjoin' undefined in: _352:any := algebra.leftjoin(_311:any, _351:any)
TypeException:user.s2_1[251]:'bat.append' undefined in: _356:any := bat.append(_353:bat[:oid,:sht], _352:any, _357:bit)
TypeException:user.s2_1[252]:'bat.append' undefined in: _358:any := bat.append(_356:any, _341:any, _357:bit)
TypeException:user.s2_1[268]:'algebra.leftjoin' undefined in: _382:any := algebra.leftjoin(_127:any, _378:bat[:oid,:bit])
TypeException:user.s2_1[269]:'algebra.leftjoin' undefined in: _383:any := algebra.leftjoin(_298:any, _382:any)
TypeException:user.s2_1[282]:'algebra.leftjoin' undefined in: _399:any := algebra.leftjoin(_260:any, _398:bat[:oid,:bit])
TypeException:user.s2_1[283]:'algebra.leftjoin' undefined in: _400:any := algebra.leftjoin(_311:any, _399:any)
TypeException:user.s2_1[285]:'bat.append' undefined in: _404:any := bat.append(_401:bat[:oid,:bit], _400:any, _405:bit)
TypeException:user.s2_1[286]:'bat.append' undefined in: _406:any := bat.append(_404:any, _383:any, _405:bit)
TypeException:user.s2_1[302]:'algebra.leftjoin' undefined in: _428:any := algebra.leftjoin(_127:any, _119:bat[:oid,:int])
TypeException:user.s2_1[303]:'algebra.project' undefined in: _429:any := algebra.project(_428:any, _426:bte)
TypeException:user.s2_1[304]:'algebra.leftjoin' undefined in: _430:any := algebra.leftjoin(_298:any, _429:any)
TypeException:user.s2_1[312]:'algebra.leftjoin' undefined in: _439:any := algebra.leftjoin(_260:any, _252:bat[:oid,:int])
TypeException:user.s2_1[313]:'algebra.project' undefined in: _440:any := algebra.project(_439:any, _437:bte)
TypeException:user.s2_1[314]:'algebra.leftjoin' undefined in: _441:any := algebra.leftjoin(_311:any, _440:any)
TypeException:user.s2_1[316]:'bat.append' undefined in: _445:any := bat.append(_442:bat[:oid,:bte], _441:any, _446:bit)
TypeException:user.s2_1[317]:'bat.append' undefined in: _447:any := bat.append(_445:any, _430:any, _446:bit)
TypeException:user.s2_1[403]:'group.refine' undefined in: _559:any := group.refine(_558:bat[:oid,:str], _272:any)
TypeException:user.s2_1[404]:'group.refine' undefined in: _560:any := group.refine(_559:any, _318:any)
TypeException:user.s2_1[405]:'algebra.markT' undefined in: _561:any := algebra.markT(_560:any, _274:oid)
TypeException:user.s2_1[406]:'bat.reverse' undefined in: _562:any := bat.reverse(_561:any)
TypeException:user.s2_1[407]:'algebra.leftjoin' undefined in: _563:any := algebra.leftjoin(_562:any, _273:any)
TypeException:user.s2_1[410]:'algebra.leftjoin' undefined in: _568:any := algebra.leftjoin(_562:any, _272:any)
TypeException:user.s2_1[413]:'algebra.leftjoin' undefined in: _573:any := algebra.leftjoin(_562:any, _318:any)
TypeException:user.s2_1[415]:'algebra.leftjoin' undefined in: _576:any := algebra.leftjoin(_562:any, _557:bat[:oid,:str])
TypeException:user.s2_1[422]:'algebra.project' undefined in: _587:any := algebra.project(_272:any, _584:str)
TypeException:user.s2_1[423]:'algebra.leftjoin' undefined in: _588:any := algebra.leftjoin(_562:any, _587:any)
SQLException:SQLengine:Program contains errors

Comment 16444

Date: 2011-10-19 16:01:01 +0200
From: @sjoerdmullender

The problem disappears when the last conjunct is rewritten to
(t."type" = 0 and t."system" = false and t."temporary" = 0)
i.e. removing the 1 = 0 part. This was there to ease generation of the query.

Comment 16445

Date: 2011-10-19 16:06:11 +0200
From: @sjoerdmullender

Changeset 1cf953b04968 made by Sjoerd Mullender in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=1cf953b04968

Changeset description:

Remove the "1 = 0 or" part of the generated conjunct for SQLTables.
By removing this part, the SQLTables query works.
This works around the problem in bug #2908.

Comment 16448

Date: 2011-10-20 09:15:14 +0200
From: @sjoerdmullender

Changeset cd853280fc14 made by Sjoerd Mullender in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=cd853280fc14

Changeset description:

Added changelog message for bug #2908.

Comment 16453

Date: 2011-10-20 17:07:21 +0200
From: @njnes

Changeset 9cc9850043c1 made by Niels Nes in the MonetDB repo, refers to this bug.

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

Changeset description:

fixed bug #2908, handle predicate in relselect properly

Comment 16455

Date: 2011-10-20 17:27:05 +0200
From: @sjoerdmullender

Changeset 8fc79f20d6ef made by Sjoerd Mullender in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=8fc79f20d6ef

Changeset description:

Changed test for bug #2908 so that it also works in isolation.

Comment 16456

Date: 2011-10-20 17:28:36 +0200
From: @sjoerdmullender

It looks like the bug is now really fixed.

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