GROUP BY subquey with different aliases gives wrong result

[GoogleCodeExporter]

Consider a simple database:

CREATE TABLE A (
          id NUMERIC(15) NOT NULL,
          b NUMERIC(15) NOT NULL,
          PRIMARY KEY  (id)
);
CREATE TABLE B (
          id NUMERIC(15) NOT NULL,
          name varchar(255) default NULL,
          PRIMARY KEY  (id)
        );

Some data:
INSERT INTO A(id, b) VALUES (5,5);
INSERT INTO A(id, b) VALUES (1,1);
INSERT INTO A(id, b) VALUES (2,2);
INSERT INTO B(id, name) VALUES (5,"five");

And a query like this:
SELECT COUNT(*) c, CASE WHEN a.b in (SELECT b1.id FROM B b1) THEN 1 ELSE 0 END 
FROM A
GROUP BY CASE WHEN a.b in (SELECT b2.id FROM B b2) THEN 1 ELSE 0 END 

The result of the query is:
1 0
2 0

But should be:
1 1
2 0

Notice that the result is correct for a slightly different query:
SELECT COUNT(*) c, CASE WHEN a.b in (SELECT b1.id FROM B b1) THEN 1 ELSE 0 END 
FROM A
GROUP BY CASE WHEN a.b in (SELECT b1.id FROM B b1) THEN 1 ELSE 0 END 

The only difference between the queries is the name of the B-table alias in
the subquery. In the failing query different aliases are used, in the
correct query the same alias is used.

The problem for us is that it's Hibernate which generates the SQL queries,
so we don't have the control over table aliases.

I used H2 1.2.128, Java 1.6.0_17 from Sun, Windows XP SP2 Pro.

Original issue reported on code.google.com by grzegorz...@gmail.com on 1 Apr 2010 at 10:34

[GoogleCodeExporter]

The behavior is exactly the same when using 1.2.132 (the newest version 
available).

Original comment by grzegorz...@gmail.com on 1 Apr 2010 at 10:38

[GoogleCodeExporter]

Hi,

Please don't post a bug and then comment on it on the group. Either do one or 
the
other. Otherwise we need to reply in both places.

All databases I tested except MySQL reject the query. Currently, I don't plan to
change the current behavior:

drop table a;
drop table b;
CREATE TABLE A (id NUMERIC(15) PRIMARY KEY, b NUMERIC(15) NOT NULL);
CREATE TABLE B (id NUMERIC(15) PRIMARY KEY, name varchar(255));
INSERT INTO A(id, b) VALUES (5,5);
INSERT INTO A(id, b) VALUES (1,1);
INSERT INTO A(id, b) VALUES (2,2);
INSERT INTO B(id, name) VALUES (5,'five');
SELECT COUNT(*) AS c, CASE WHEN a.b in (SELECT b1.id FROM B b1) THEN 1 ELSE 0 
END
FROM A GROUP BY CASE WHEN a.b in (SELECT b2.id FROM B b2) THEN 1 ELSE 0 END;

-- H2: 1, 0; 2, 0
-- MySQL: 1, 1; 2, 1
-- 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.
-- HSQLDB: expression not in aggregate or GROUP BY columns: 
CASEWHEN(((PUBLIC.A.B)=()),1,0)
-- PostgreSQL: ERROR: column "a.b" must appear in the GROUP BY clause or be 
used in
an aggregate function

SELECT COUNT(*) AS c, CASE WHEN a.b in (SELECT b1.id FROM B b1) THEN 1 ELSE 0 
END
FROM A GROUP BY CASE WHEN a.b in (SELECT b1.id FROM B b1) THEN 1 ELSE 0 END;

-- Derby, HSQLDB, PostgreSQL, H2, MySQL, 2, 0; 1, 1

Therefore, I will resolve the issue as "won't fix".

Regards,
Thomas

Original comment by thomas.t...@gmail.com on 4 Apr 2010 at 3:47

• Changed state: WontFix

[GoogleCodeExporter]

Sorry, I see you didn't send a mail to the group - my mistake.

Original comment by thomas.t...@gmail.com on 4 Apr 2010 at 3:50

[GoogleCodeExporter]

OK. I see. No worries about the group.
I only compared it with MySQL. That's why I thought it could be supported.

Thanks a lot for your effort!

Original comment by grzegorz...@gmail.com on 4 Apr 2010 at 5:21