unexpected org.h2.jdbc.JdbcSQLException with GROUP BY

[GoogleCodeExporter]

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
----
CREATE TABLE TEST ( PK DECIMAL(5) NOT NULL, FK DECIMAL(5) NOT NULL );

insert into TEST (PK, FK) values (1, 101);
insert into TEST (PK, FK) values (2, 102);
insert into TEST (PK, FK) values (3, 102);

select PK, FK from TEST group by FK; 

-- DROP TABLE TEST;

What is the expected output?
----
PK  FK 
--  ---
1   101
2   102

What do you see instead?
----
[Error Code: 90016, SQL State: 90016]
Column PK must be in the GROUP BY list; SQL statement:
select PK, FK from TEST group by FK [90016-111]

What version of the product are you using? On what operating system, file
system, and virtual machine?
----
h2-2009-04-10 h2-1.1.111.jar
WinXP Pro SP3, NTFS

Do you know a workaround?
----
no (not yet)

How important/urgent is the problem for you?
----
high

In your view, is this a defect or a feature request?
----
defect

Please provide any additional information below.
----
same sql works fine on MySQL 5.1
I have tested the same with h2-1.1.109, h2-1.1.110, h2-1.1.111 with no avail.
I also tested the same with adding primary-/foreign-key and indices with no
difference.

following a sql snipped I like to run in my code.
I have no references and no inices as it's only temp data.

select PK, FK, PERCENT, sum( PERCENT ) as S
from TEST group by FK having  S <> 100; 

Original issue reported on code.google.com by beat.wir...@gmail.com on 21 Apr 2009 at 6:01

[GoogleCodeExporter]

just found a workaround:
Using MIN() or MAX() on the PK solves the problem.
MySQL seems to automatically convert 'select PK, FK from TEST group by FK;' to
'select min(PK), FK from TEST group by FK;' to avoid SQLException.

drop table if exists TEST;
crate table TEST ( PK decimal(5) not nullL, FK decimal(5) not null );
insert into TEST (PK, FK) values (1, 101);
insert into TEST (PK, FK) values (2, 102);
insert into TEST (PK, FK) values (3, 102);
select max(PK), FK from TEST group by FK; 

Original comment by beat.wir...@gmail.com on 21 Apr 2009 at 6:25

[GoogleCodeExporter]

select PK, FK from TEST group by FK; 

It works in MySQL, but it is not supposed to work in standard SQL databases.

PostgreSQL: ERROR: column "test.pk" must appear in the GROUP BY clause or be 
used in
an aggregate function 42803/0

Derby: The SELECT list of a grouped query contains at least one invalid 
expression.
If a SELECT list has a GROUP BY, the list may only contain valid grouping 
expressions
and valid aggregate expressions.

Original comment by thomas.t...@gmail.com on 21 Apr 2009 at 7:14

• Changed state: WontFix