[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&grou...) 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:
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&grou...) 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
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:
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&grou...) 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
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
We had the Mondrian testsuite running with a dialect. Do you know what test fails?
Stefan
Op 21 sep 2009 om 09:51 heeft Franck Routier franck.routier@axege.com het volgende geschreven:\
Hi,
I am working with Mehmet on Monetdb / Mondrian tests.
So, to sum up:
- functions in group by are not supported in ansi sql standard (I
take your words on this :-)
- 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)
some rdbms support both (eg. Postgresql)
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
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
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:
We had the Mondrian testsuite running with a dialect. Do you know what test fails?
Stefan
Op 21 sep 2009 om 09:51 heeft Franck Routier franck.routier@axege.com het volgende geschreven:\
Hi,
I am working with Mehmet on Monetdb / Mondrian tests.
So, to sum up:
- functions in group by are not supported in ansi sql standard (I
take your words on this :-)
- 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)
some rdbms support both (eg. Postgresql)
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
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
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
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 :
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:
We had the Mondrian testsuite running with a dialect. Do you know what test fails?
Stefan
Op 21 sep 2009 om 09:51 heeft Franck Routier franck.routier@axege.com het volgende geschreven:\
Hi,
I am working with Mehmet on Monetdb / Mondrian tests.
So, to sum up:
- functions in group by are not supported in ansi sql standard (I
take your words on this :-)
- 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)
some rdbms support both (eg. Postgresql)
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
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
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
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
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA512
Franck Routier schreef:
Or is there any unpublished Monetdb dialect for Mondrian laying around somewhere ?
...there is.
Stefan
/* // 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