[MonetDB-users] functions in group by
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. 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 ? Mehmet -- Open WebMail Project (http://openwebmail.org)
On Fri, Sep 18, 2009 at 02:33:13PM +0200, mkurtulus wrote:
<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.
Would you think supporting functions in group by could be done ?
Niels
-- 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
Hello, In addition to the remark below, the syntax of Oracle/SQL does not comply with either SQL'03 nor SQL'08. They are system specific extensions which technically creates a challenge for those systems to avoid doing double work. select f(x) from y group by f(x) calls for expression subsumption analysis, which in general is hard. Limitation to simple function calls, a language design glitch. Conceptually this query is intentionally equivalent to select t.z from (select f(x) as z from y) as t group by t.z We are interested in your progress in combination of Mondrian/MonetDB. Please keep us informed. regards, Martin Niels Nes wrote:
Hi, I am working with Mehmet on Monetdb / Mondrian tests. So, to sum up: 1) functions in group by are not supported in ansi sql standard (I take your words on this :-) 2) on the contrary, column aliases in group by are not supported by some major rdbms, eg. Oracle and MS Sqlserver (while functions in group by are) 3) some rdbms support both (eg. Postgresql) 4) Mondrian, as a ROLAP tool, generate sql queries and make great use of aggregates. Those queries contain functions in group by (not standard, but works on Oracle, Sqlserver, Postgres, etc.) So if I understand you, supporting functions in group by in Monetdb would not be a trivial task... Do you confirm we should not count on it (unless we propose a patch, I guess) ? This might well be a showstopper for Mondrian / Monet. A solution might be to change the Mondrian query generator specifically for Monetdb (using a special dialect setting), but it might not be that trivial either (sql generation is widespreaded in mondrian code :-(. Does this sum up the situation ? Franck
I concur with the observation. All but a few out of the 2000 sql test queries for Mondrian worked using its functionality to accommodate different sql dialects. What version of Mondrian are you using? Which tests in Mondrian's test suite fail in your case? Can you provide the complete test suite coverage report? regards, Martin Stefan de Konink wrote:
Well... we are using latest Mondrian, but until now we sticked to 'generic' sql dialect setting. We have found an Infobright dialect in Mondrian that might be more suited to Monetdb. We must test this and will keep you informed. Or is there any unpublished Monetdb dialect for Mondrian laying around somewhere ? Franck Le lundi 21 septembre 2009 à 12:34 +0200, Martin Kersten a écrit :
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA512 Franck Routier schreef:
Or is there any unpublished Monetdb dialect for Mondrian laying around somewhere ?
...there is. Stefan -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.11 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEAREKAAYFAkq4nfwACgkQYH1+F2Rqwn36rwCeKrqLQ8W8vhtgp6hAGb3DZLvQ ZJUAn3iM4reBDXsIN71nPBxE4Eoz6T72 =8+H4 -----END PGP SIGNATURE----- /* // This software is subject to the terms of the Common Public License // Agreement, available at the following URL: // http://www.opensource.org/licenses/cpl.html. // Copyright (C) 2008-2009 Julian Hyde // All Rights Reserved. // You must accept the terms of that agreement to use this software. */ package mondrian.spi.impl; import java.sql.Connection; import java.sql.SQLException; /** * Implementation of {@link mondrian.spi.Dialect} for the MonetDB database. * * @author Niels nes * @version $Id: //open/mondrian/src/main/mondrian/spi/impl/MonetDBDialect.java#2 $ * @since April 16, 2009 */ public class MonetDBDialect extends JdbcDialectImpl { public static final JdbcDialectFactory FACTORY = new JdbcDialectFactory( MonetDBDialect.class, DatabaseProduct.MONETDB); /** * Creates a MonetDBDialect. * * @param connection Connection */ public MonetDBDialect(Connection connection) throws SQLException { super(connection); } public boolean requiresAliasForFromQuery() { return true; } // not sure needs testing public boolean isNullsCollateLast() { return true; } // we do support this public boolean supportsGroupByExpressions() { return true; } } // End MonetDBDialect.java
participants (5)
-
Franck Routier -
Martin Kersten -
mkurtulus -
Niels Nes -
Stefan de Konink