On Fri, Sep 18, 2009 at 02:33:13PM +0200, mkurtulus wrote:
Hello,
in our attempt to use monetdb as a backend for Mondrian, we are running into trouble with queries that Mondrian generates where functions are used in group by.
eg.
select substring('string',1,2) from mytable group by substring('string',1,2);
Monet complains about a syntax error...
On the contrary, using this syntax works:
select substring('string',1,2) as col from mytable group by col;
We have found a closed bug (http://sourceforge.net/tracker/index.php?func=detail&aid=946935&group_id=56967&atid=482468) in sourceforge that says it is not a bug, as using functions in group by is not supported in standard sql'99. Indeed this is still the case.
<group by clause> ::= GROUP BY [ <set quantifier> ] <grouping element list> <grouping element list> ::= <grouping element> [ { <comma> <grouping element> }... ] <grouping element> ::= <ordinary grouping set> | <rollup list> | <cube list> | <grouping sets specification> | <empty grouping set> <ordinary grouping set> ::= <grouping column reference> | <left paren> <grouping column reference list> <right paren> <grouping column reference> ::= <column reference> [ <collate clause> ] A column reference is basicaly an chain of (at most 3) identifiers, such a schema.table.column. There is no room for expressions (ie functions) in there.
We have thought of modifying Mondrian query generator to use the supported syntax... but the supported syntax is _not_ supported by Oracle ! So not supporting Oracle in favor of Monet is not (yet :) an option... Also notice that MS Sqlserver does not support aliases in group by either. Some even state that it is not standard sql... Reading the BNF was not of great help to me :-)
Would you think supporting functions in group by could be done ?
Niels
Mehmet -- Open WebMail Project (http://openwebmail.org)
------------------------------------------------------------------------------ Come build with us! The BlackBerry® Developer Conference in SF, CA is the only developer event you need to attend this year. Jumpstart your developing skills, take BlackBerry mobile applications to market and stay ahead of the curve. Join us from November 9-12, 2009. Register now! http://p.sf.net/sfu/devconf _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Niels Nes, Centre for Mathematics and Computer Science (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room C0.02/M3.46, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl