'Column "Foo" must be in the GROUP BY list' appear with version .144

[Steffen Bach]

Hi list.

I recently upgraded my H2.jar from .138 to .144 and started receiving
the error message in $subject. After some investigation I thought, I
had it narrowed down to when I was using a database created by .138
and connecting with the new .144, but it seems that I get the error
intermittently even on databases created with .144. In short, I get
the error consistently when querying a database created with .138
using .144, and intermittently when querying a database created with .
144 using .144.

The causing stack trace says:
-----------
Caused by: org.h2.jdbc.JdbcSQLException: Column "ME.M_TYPE_ID" must be
in the GROUP BY list; SQL statement: [..SNIP.. (see below)]
[90016-144]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:
327)
at org.h2.message.DbException.get(DbException.java:167)
at org.h2.message.DbException.get(DbException.java:144)
at org.h2.expression.ExpressionColumn.getValue(ExpressionColumn.java:
169)
at org.h2.index.IndexCondition.getCurrentValue(IndexCondition.java:
120)
at org.h2.index.IndexCursor.find(IndexCursor.java:99)
at org.h2.table.TableFilter.next(TableFilter.java:310)
at org.h2.command.dml.Select.queryFlat(Select.java:498)
at org.h2.command.dml.Select.queryWithoutCache(Select.java:597)
at org.h2.command.dml.Query.query(Query.java:257)
at org.h2.command.dml.Query.query(Query.java:227)
at org.h2.expression.Subquery.getValue(Subquery.java:36)
at org.h2.index.IndexCondition.getCurrentValue(IndexCondition.java:
120)
at org.h2.index.IndexCursor.find(IndexCursor.java:99)
at org.h2.table.TableFilter.next(TableFilter.java:310)
at org.h2.command.dml.Update.update(Update.java:86)
at org.h2.command.CommandContainer.update(CommandContainer.java:69)
at org.h2.command.Command.executeUpdate(Command.java:201)
at
org.h2.jdbc.JdbcPreparedStatement.execute(JdbcPreparedStatement.java:
181)
at sun.reflect.GeneratedMethodAccessor5.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:
25)
at java.lang.reflect.Method.invoke(Method.java:597)
at
org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:
45)
at $Proxy17.execute(Unknown Source)
at
org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:
22)
at
org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:
51)
at
org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:
29)
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:
73)
at
org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:
43)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:
102)
... 12 more
-----------


The query I'm trying to execute is:
-----------
UPDATE measurements SET
value = value + ?
WHERE metric_id = (
SELECT me.id
FROM metrics me
INNER JOIN measurement_types mt ON mt.id = me.m_type_id
WHERE me.name = ?
AND mt.name = ?)
AND time_stamp = ?
-----------

Where measurement_types is:
- id (PK)
- Name

and metrics is:
- id (PK)
- m_type_id (FK)
- name

and measurements is:
- metric_id (FK)
- value
- time_stamp


I don't understand this error as the executing query doesn't contain a
group by clause and it worked when using .138 to query a database
created by .138.

Looking through the changelog; it doesn't seem like there has been any
changes to GROUP between .138 and .144. Does anybody have an idea as
to what is happening here?

- Steffen

[Thomas Mueller]

Hi,

Try "IN" instead of "=":

WHERE metric_id IN ( SELECT me.id ...)

Regards,
Thomas

[Steffen Bach]

On Nov 3, 7:49 am, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

> Try "IN" instead of "=":
>
> WHERE metric_id IN ( SELECT me.id ...)

Using IN still produces the error. The nested SELECT only returns 1
row anyway.

Any other suggestions as to what is causing this?

- Steffen

[Stefan]

Hi list,

I'm getting the same error on version 145. I haven't tested 144.
But for me it only shows up when I'm using MULTI_THREADED=1

In my case the error and query look like this:

org.h2.jdbc.JdbcSQLException: Column "RNG.RANGE" must be in the GROUP
BY list; SQL statement:
INSERT INTO propertyRange_d0 (property, range, sourceId1,
sourceTable1, sourceId2, sourceTable2)
SELECT rng.property, sc.super, MIN(rng.id) AS sourceId1,
'propertyRange' AS sourceTable1, MIN(sc.id) AS sourceId2, 'subClass'
AS sourceTable2
FROM propertyRange AS rng
INNER JOIN subClass_d2 AS sc ON sc.sub = rng.range
WHERE NOT EXISTS (
SELECT property, range
FROM propertyRange_d0 AS bottom
WHERE bottom.property = rng.property AND bottom.range = sc.super
)
GROUP BY rng.property, sc.super

[Steffen Bach]

On Nov 8, 3:38 pm, Stefan <stefan.langenma...@gmail.com> wrote:
> I'm getting the same error on version 145. I haven't tested 144.

I'm getting the error on version .145 also.

- Steffen

[Steffen Bach]

Hi.

The error disappears for me if I downgrade to version 143.

@Stefan:
Could I get you to check if the error disappears for you if you
downgrade to version 143?

@Thomas:
Looking through the change log between .143 and .144, I can't see any
thing that changes nested selects (which seems to be the common link
between mine and Stefans error). Do you have any tips as to how I can
narrow down the part of the code that changed between 143 and 144 and
lead to this error?

- Steffen

[Thomas Mueller]

Hi,

Could you provide a reproducible test case? I would need the CREATE
TABLE statements, plus some example data. The easiest solution is to
send the database script (SCRIPT TO 'script.sql'), or the database
file. That would save me from having to re-create the database based
on your description, and invent data.

Regards,
Thomas

[Stefan]

Hi Steffen,

I went from 143 straight to 145. With 143 this error never happened to
me.

Kind regards
Stefan

On Nov 9, 2:24 pm, Steffen Bach <zpej...@gmail.com> wrote:
> Hi.
>
> The error disappears for me if I downgrade to version 143.
>
> @Stefan:
> Could I get you to check if the error disappears for you if you
> downgrade to version 143?
>
> @Thomas:
> Looking through the change log between .143 and .144, I can't see any
> thing that changes nested selects (which seems to be the common link
> between mine and Stefans error). Do you have any tips as to how I can
> narrow down the part of the code that changed between 143 and 144 and
> lead to this error?
>
> - Steffen
>

[Steffen Bach]

Test case sent in separate email.

- Steffen

On Nov 9, 8:43 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

[Thomas Mueller]

Hi,

I don't understand yet what the problem is, but thanks to the test
case, I can now reproduce the problem. It seems to be some kind of
timing problem, but that's all I know so far. I had to change the
original test case a bit to get the exception (all threads now use the
same connection, and I increased the loop and thread count).

Regards,
Thomas

> --
> You received this message because you are subscribed to the Google Groups "H2 Database" group.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to h2-database...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
>
>

[Thomas Mueller]

Hi,

I found and fixed the problem now. It's quite a serious bug, but it
only occurred very very seldom. The problem is that parsing and
preparing statements was not synchronized, and this could result in
problems, specially when a lot of statements were prepared
concurrently (as in this case). Usually this is not a problem, and in
the future I will make sure concurrently preparing statements works,
but currently it is not supported.

Anyway, the problem will be fixed in the next release. Thanks a lot
for your help!

Regards,
Thomas

[Steffen Bach]

Hi.

On Nov 13, 9:32 am, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

> I found and fixed the problem now.

Good to hear.

> Anyway, the problem will be fixed in the next release.

Any ETA for when it will be released?

> Thanks a lot for your help!

No problem; and thank you for H2.

- Steffen

[Thomas Mueller]

Hi,

>> Anyway, the problem will be fixed in the next release.
> Any ETA for when it will be released?

See http://h2database.com/html/faq.html#known_bugs

Regards,
Thomas

[Vadim Kirilchuk]

Hi there,

I checked known bugs, open issues link there and also github issues page but failed to find any evidence on the issue.

Could anyone point me it? When(in which version) it was fixed? 

Looks like we are facing the similar issue on 1.4.179.

Thanks in advance