Martin/Niels,
The following error may be avoided by converting the query into an
in-line view as follows:
SELECT pc1
FROM (
SELECT CASE WHEN col1=1 THEN 'ONE' END AS "pc1"
FROM test1 ) sq
GROUP BY pc1;
I have done many such syntax changes for our use. I think it's the
CASE/NULL combination that's causing this issue. There are few more
such issues and I will let you know if I come across more.
-Venks
On Thu, Aug 28, 2008 at 2:06 AM, Martin Kersten
Rick Spickelmier wrote:
Hi!
Dear Rick,
thank you very much for submission of this bug report. It is indeed an error, also in the current head. We will look into the problem a.s.a.p.
regards, Martin
I am running into this error:
batcalc.ifThenElse:requires bats of identical size
My server (running on Windows XP 64):
# MonetDB server v5.6.0, based on kernel v1.24.0
# Serving database 'demo'
# Compiled for i686-pc-win32/64bit with 32bit OIDs dynamically linked
The following recreates the problem:
CREATE TABLE test1 (col1 INTEGER);
INSERT INTO test1 VALUES (null);
INSERT INTO test1 VALUES (null);
SELECT
CASE WHEN col1=1 THEN 'ONE' END AS "pc1"
FROM
test1
GROUP BY "pc1";
Any ideas? Obviously the above isn't too interesting, but it a simplified test case from a more complex query on a much larger dataset. Seems to be related to GROUP BY on a CASE...
Thanks!
Rick
+-----------------------------------------------------------------------------+
| mal |
+=============================================================================+
| function user.s0_3():void; |
| _1:bat[:oid,:int]{rows=1:lng} := sql.bind("voc","test2","col1",0); |
| _6:bat[:oid,:int]{rows=1:lng} := sql.bind("voc","test2","col1",1); |
| _8{rows=2:lng} := algebra.kunion(_1,_6); |
| _1:bat[:oid,:int]{rows=1:lng} := nil; |
| _6:bat[:oid,:int]{rows=1:lng} := nil; |
| _9:bat[:oid,:int]{rows=1:lng} := sql.bind("voc","test2","col1",2); |
| _11{rows=2:lng} := algebra.kdifference(_8,_9); |
| _8{rows=2:lng} := nil; |
| _12{rows=3:lng} := algebra.kunion(_11,_9); |
| _11{rows=2:lng} := nil; |
| _9:bat[:oid,:int]{rows=1:lng} := nil; |
| _13:bat[:oid,:oid]{rows=1:lng} := sql.bind_dbat("voc","test2",1); |
| _14{rows=1:lng} := bat.reverse(_13); |
| _13:bat[:oid,:oid]{rows=1:lng} := nil; |
| _15{rows=3:lng} := algebra.kdifference(_12,_14); |
| _14{rows=1:lng} := nil; |
| _17{rows=3:lng,tlb>=0@0:oid,tub<1@0:oid} := algebra.markT(_15,0@0); |
| _15{rows=3:lng} := nil; |
| _18{rows=3:lng,hlb>=0@0:oid,hub<1@0:oid} := bat.reverse(_17); |
| _17{rows=3:lng,tlb>=0@0:oid,tub<1@0:oid} := nil; |
| _19{rows=3:lng,hlb>=0@0:oid,hub<1@0:oid} := algebra.join(_18,_12); |
| _12{rows=3:lng} := nil; |
| _20:bat[:oid,:bit]{rows=3:lng,hlb>=0@0:oid,hub<1@0:oid} := |
: batcalc.==(_19,1); |
| _23{rows=3:lng,hlb>=0@0:oid,hub<1@0:oid} := algebra.project(_18,nil:str); |
| _24:bat[:oid,:str]{rows=3:lng,hlb>=0@0:oid,hub<1@0:oid} := |
: batcalc.ifthenelse(_20,"ONE",_23); |
| _20:bat[:oid,:bit]{rows=3:lng,hlb>=0@0:oid,hub<1@0:oid} := nil; |
| _23{rows=3:lng,hlb>=0@0:oid,hub<1@0:oid} := nil; |
| (ext36,grp34):= group.new(_24); |
| _24:bat[:oid,:str]{rows=3:lng,hlb>=0@0:oid,hub<1@0:oid} := nil; |
| _27 := bat.mirror(ext36); |
| ext36 := nil; |
| _28 := algebra.join(_27,_19); |
| _27 := nil; |
| _19{rows=3:lng,hlb>=0@0:oid,hub<1@0:oid} := nil; |
| _29:bat[:oid,:bit] := batcalc.==(_28,1); |
| _28 := nil; |
| _30{rows=3:lng,hlb>=0@0:oid,hub<1@0:oid} := algebra.project(_18,nil:str); |
| _18{rows=3:lng,hlb>=0@0:oid,hub<1@0:oid} := nil; |
| _31:bat[:oid,:str] := batcalc.ifthenelse(_29,"ONE",_30); |
| _29:bat[:oid,:bit] := nil; |
| _30{rows=3:lng,hlb>=0@0:oid,hub<1@0:oid} := nil; |
| _32 := sql.resultSet(1,1,_31); |
| sql.rsColumn(_32,"voc.","pc1","char",0,0,_31); |
| _31:bat[:oid,:str] := nil; |
| _37 := io.stdout(); |
| sql.exportResult(_37,_32,""); |
| end s0_3; |
+-----------------------------------------------------------------------------+
Rick Spickelmier | CTO | Success Metrics, Inc. | w (415) 738-2078 | ricks@successmetricsinc.com mailto:ricks@successmetricsinc.com
------------------------------------------------------------------------
------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/ ------------------------------------------------------------------------
_______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users