Issue H2 Parser GROUP BY, 1.4.181 -> 1.4.184

[Nicolas Fortin (OrbisGIS)]

Hi,

There is a bug with H2 Parser when using group by with the same field name as one of select column.

With H2 1.4.181 (OK)

drop table test if exists;
create table test(id bigint, neigh bigint, val double);
insert into test values (1, 2, 15);
insert into test values (2, 1, 10);
insert into test values (2, 1 , 5);
insert into test values (3, 1, 8);

select a.id, POWER(a.val, sum(b.val)) val from test a,test b group by a.id, a.val;

ID
VAL
14.9143699204177834E44
23.637978807091713E26
32.076918743413931E34
21.0E38

(4 rows, 2 ms)

With H2 1.4.184

 Invalid use of aggregate function "SUM(B.VAL)"; SQL statement:
select a.id, POWER(a.val, sum(b.val)) val from test a,test b group by a.id, a.val [90054-184]
org.h2.jdbc.JdbcSQLException: Invalid use of aggregate function "SUM(B.VAL)"; SQL statement:
select a.id, POWER(a.val, sum(b.val)) val from test a,test b group by a.id, a.val [90054-184]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
    at org.h2.message.DbException.get(DbException.java:179)
    at org.h2.message.DbException.get(DbException.java:155)
    at org.h2.expression.Aggregate.getValue(Aggregate.java:296)
    at org.h2.expression.Function.getValueWithArgs(Function.java:1168)
    at org.h2.expression.Function.getValue(Function.java:576)
    at org.h2.expression.Alias.getValue(Alias.java:36)
    at org.h2.command.dml.Select.queryGroup(Select.java:340)
    at org.h2.command.dml.Select.queryWithoutCache(Select.java:639)
    at org.h2.command.dml.Query.query(Query.java:322)
    at org.h2.command.dml.Query.query(Query.java:290)
    at org.h2.command.dml.Query.query(Query.java:36)
    at org.h2.command.CommandContainer.query(CommandContainer.java:90)
    at org.h2.command.Command.executeQuery(Command.java:197)
    at org.h2.jdbc.JdbcStatement.executeQuery(JdbcStatement.java:79)

The explain is different:

explain select a.id, POWER(a.val, sum(b.val)) val from test a,test b group by a.id, a.val;

With H2 1.4.181

SELECT
    A.ID,
    POWER(A.VAL, SUM(B.VAL)) AS VAL
FROM PUBLIC.TEST A
    /* PUBLIC.TEST.tableScan */
INNER JOIN PUBLIC.TEST B
    /* PUBLIC.TEST.tableScan */
    ON 1=1
GROUP BY A.ID, A.VAL

With H2 1.4.184

SELECT
    A.ID,
    POWER(A.VAL, SUM(B.VAL)) AS VAL
FROM PUBLIC.TEST A
    /* PUBLIC.TEST.tableScan */
INNER JOIN PUBLIC.TEST B
    /* PUBLIC.TEST.tableScan */
    ON 1=1
GROUP BY A.ID, POWER(A.VAL, SUM(B.VAL))

I don’t find the commit that change the behavior.

regards,

Nicolas Fortin
IRSTV FR CNRS 2488

​

[Thomas Mueller]

Hi,

This was a regression introduced in version 1.4.184, to fix the following issue: 

Group by with a quoted select column name alias didn't work. Example: select 1 "a" from dual group by "a"

I have fixed this now (will commit to the trunk today or tomorrow).

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.