Error in comparison test when using "group by"

[GoogleCodeExporter]

Hi,

I am using the version H2 1.3.175.
Here are the requests to reproduce the issue:

CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255));
insert into test values (1, 'test1');
insert into test values (2, 'test2');

select * from test;

Output:
ID      NAME  
1   test1
2   test2

select id,
name IN ('test2','test3') col
from test;

Output:
ID      COL  
1   FALSE
2   TRUE

select id,
name IN ('test2','test3') col
from test
group by id;

Expected output:
ID      COL  
1   FALSE
2   TRUE

Seen output:
ID      COL  
1   TRUE
2   TRUE

Thanks and regards.

Original issue reported on code.google.com by cheve.cy...@gmail.com on 23 Jan 2015 at 11:03

[GoogleCodeExporter]

Hi,

I finally had time to look at it and fixed the problem (in the trunk).

Regards,
Thomas

Original comment by thomas.t...@gmail.com on 12 Mar 2015 at 6:51

• Changed state: Accepted

[GoogleCodeExporter]

Should be fixed with version 1.4.187

Original comment by thomas.t...@gmail.com on 10 Apr 2015 at 6:53

[GoogleCodeExporter]

Original comment by thomas.t...@gmail.com on 10 Apr 2015 at 6:53

• Changed state: Fixed

[GoogleCodeExporter]

Hello Thomas,

I think there is some regression caused by this fix, we have a request:

     SELECT 
       id, 
       COUNT(something), 
       CASE WHEN status in THEN SUM(something) ... END
     FROM ...
     GROUP BY id

And with 1.4.187, the driver throw an exception asking to put "status" in the 
"group by" (and that seems not correct). This was working fine with 1.4.186.

Best regards, 

Régis.

Original comment by rcas...@itsm-cockpit.com on 29 Jun 2015 at 10:50

[GoogleCodeExporter]

Hi,

If you have an error to report, please open a new issue (at GitHub, not here). 
And please include a _complete_, simple test case.

I think your query is not valid. I tested with PostgreSQL and it fails:

drop table test;
create table test(id int, something int, status int);
insert into test values(1, 1, 1);
insert into test values(2, 2, 2);

    SELECT 
       id, 
       COUNT(something), 
       CASE WHEN status in(1, 3) THEN SUM(something) else 0 END
     FROM test
     GROUP BY id;

Please don't reply to this. The Google Code project is closed.

Original comment by thomas.t...@gmail.com on 29 Jun 2015 at 11:45