error occurred during a query: "'CASE WHEN" sentence

[monetdb-team]

Date: 2013-11-08 06:12:15 +0100
From: movingon
To: SQL devs <>
Version: 11.15.17 (Feb2013-SP5)
CC: @mlkersten, @njnes

Last updated: 2013-12-03 13:59:33 +0100

Comment 19326

Date: 2013-11-08 06:12:15 +0100
From: movingon

User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.110 Safari/537.36 CoolNovo/2.0.9.20
Build Identifier:

SQL error when performing sample queries to perform.

Reproducible: Always

Steps to Reproduce:

1.sample table create

create table aaa (a varchar(100), b varchar(100), c int);

2. data insert

---

insert into aaa values ('a1aaaaaaaaaaaaaaaaa', 'b1bbbbbbbbbbbbbbbbbb', 100);
insert into aaa values ('a2aaaaaaaaaaaaaaaaa', 'b2bbbbbbbbbbbbbbbbbb', 100);
insert into aaa values ('a3aaaaaaaaaaaaaaaaa', 'b3bbbbbbbbbbbbbbbbbb', 100);

3. execute SQL

---

SELECT a, CASE WHEN c>=100.000000
AND c<=200.000000 THEN 'IntValue1' WHEN c>=200.000000
AND c<=300.000000 THEN 'IntValue2' ELSE 'Out of range' END AS CaseSentence
FROM aaa
WHERE
(
a = 'aaa'
and
(
(
CASE WHEN c>=100.000000
AND c<=200.000000 THEN 'IntValue1' WHEN c>=200.000000
AND c<=300.000000 THEN 'IntValue2' ELSE 'Out of range' END
) = 'IntValue1'
)
)
or
(
a = 'bbb'
and
(
(
CASE WHEN c>=100.000000
AND c<=200.000000 THEN 'IntValue1' WHEN c>=200.000000
AND c<=300.000000 THEN 'IntValue2' ELSE 'Out of range' END
) = 'IntValue2'
)
);

Actual Results:

TypeException:user.s8_1[35]:'calc.ifthenelse' undefined in: _228:any := calc.ifthenelse(_222:bit, _54:bit, _224:bit, _226:oid)
TypeException:user.s8_1[37]:'bat.insert' undefined in: _231:any := bat.insert(_217:bat[:oid,:bit], _229:oid, _228:any)
TypeException:user.s8_1[52]:'calc.ifthenelse' undefined in: _246:any := calc.ifthenelse(_240:bit, _71:bit, _242:bit, _244:oid)
TypeException:user.s8_1[54]:'bat.insert' undefined in: _249:any := bat.insert(_237:bat[:oid,:bit], _247:oid, _246:any)
TypeException:user.s8_1[62]:'calc.ifthenelse' undefined in: _264:any := calc.ifthenelse(_260:bit, A13:str, A14:str, _262:oid)
TypeException:user.s8_1[64]:'bat.insert' undefined in: _267:any := bat.insert(_255:bat[:oid,:str], _265:oid, _264:any)
TypeException:user.s8_1[73]:'calc.ifthenelse' undefined in: _282:any := calc.ifthenelse(_276:bit, A10:str, _278:str, _280:oid)
TypeException:user.s8_1[75]:'bat.insert' undefined in: _285:any := bat.insert(_273:bat[:oid,:str], _283:oid, _282:any)
TypeException:user.s8_1[98]:'calc.ifthenelse' undefined in: _302:any := calc.ifthenelse(_296:bit, _88:bit, _298:bit, _300:oid)
TypeException:user.s8_1[100]:'bat.insert' undefined in: _305:any := bat.insert(_291:bat[:oid,:bit], _303:oid, _302:any)
TypeException:user.s8_1[124]:'calc.ifthenelse' undefined in: _320:any := calc.ifthenelse(_314:bit, _88:bit, _316:bit, _318:oid)
TypeException:user.s8_1[126]:'bat.insert' undefined in: _323:any := bat.insert(_311:bat[:oid,:bit], _321:oid, _320:any)
TypeException:user.s8_1[134]:'calc.ifthenelse' undefined in: _338:any := calc.ifthenelse(_334:bit, A22:str, A23:str, _336:oid)
TypeException:user.s8_1[136]:'bat.insert' undefined in: _341:any := bat.insert(_329:bat[:oid,:str], _339:oid, _338:any)
TypeException:user.s8_1[145]:'calc.ifthenelse' undefined in: _356:any := calc.ifthenelse(_350:bit, A19:str, _352:str, _354:oid)
TypeException:user.s8_1[147]:'bat.insert' undefined in: _359:any := bat.insert(_347:bat[:oid,:str], _357:oid, _356:any)
program contains errors

Expected Results:

SQL is performed successfully and no result data.

Comment 19327

Date: 2013-11-08 12:15:27 +0100
From: @mlkersten

Added the test script. Explain shows a lot of iterators.

Comment 19342

Date: 2013-11-16 13:57:01 +0100
From: MonetDB Mercurial Repository <>

Changeset db8112175827 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=db8112175827

Changeset description:

fixed bug #3395 (only calc functions which 'require' a candidate list (div), should be called with an extra argument, ie not ifthenelse).

also fixed the test itself (removed extra table, and bogus cast statements).

Comment 19343

Date: 2013-11-16 13:57:03 +0100
From: MonetDB Mercurial Repository <>

Changeset d2ecf368fd11 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=d2ecf368fd11

Changeset description:

added test output for bug #3395

Comment 19344

Date: 2013-11-16 13:58:33 +0100
From: @njnes

Fixed, ie exclude candidate list in call to ifthenelse

Comment 19377

Date: 2013-12-03 13:59:33 +0100
From: @sjoerdmullender

Feb2013-SP6 has been released.