Hi!
Thanks for the suggestion. My original query is more complex so the reformulation was more interesting:
select case when col1=1 then 'one' end as "pc1", sum(col2) from test3 group by "pc1";
transforming:
select pc1, sum(col2) from (select case when col1=1 then 'one' end as "pc1", col2 from test3) ta group by "pc1";
Thus I need to create the view with all of my columns, but remove any aggregation from the view and put it in the final projection list since that is where the grouping is occurring. I have to think about how to do this since I machine generate these queries and it is not clear how to restructure the code to produce the difference results...
If you are interested in the kinds of changes I had to make in moving my code from SQL Server to MonetDB (not done yet... still a prototype):
+ CREATE INDEX is treated as a 'hint' in MonetDB; as a result I did not create any indexes + No UPDATE ... FROM - transformed to old style update using multiple tables + No TOP - still not sure about this one + Bulk load has no format file (COPY INTO), thus must populate all columns in the input file, need explicit 'null' string + Bulk load only supports ASCII + No RANK, no CHECKSUM - can be handled as updates after the load + Different type names for datetime, text, varchar(max) + NUMERIC(P,S) must be P+S <=18, our standard is (19,6) + $ not supported in column names (no special characters allowed) + Column aliases are double quoted in projection lists: SELECT A AS "Year" ... + Column aliases must be valid identifiers - SELECT A AS "# Threads" ... does not work + SUM(1) doesn't seem to work, need to replace with COUNT(1) + had to change SQL server date functions - DATEDIFF, GETDATE, DATEPART - to more standard SQL date functions + ORDER BY and GROUP BY on an expression fails, must be a projected column
Note that performance based upon early tests seems to be significantly better than SQL Server, except for the case of one type of query where we use non-equijoins to join to tables (i.e. table1.col < table2.col).
Rick
-----Original Message----- From: monetdb-users-bounces@lists.sourceforge.net [mailto:monetdb-users-bounces@lists.sourceforge.net] On Behalf Of Venks Sent: Thursday, August 28, 2008 5:34 AM To: Communication channel for MonetDB users Subject: Re: [MonetDB-users] batcalc.ifThenElse:requires bats of identicalsize
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 Martin.Kersten@cwi.nl wrote:
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
test1GROUP 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
------------------------------------------------------------------------
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
------------------------------------------------------------------------ - 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