Related subqueries in HAVING clause

[GoogleCodeExporter]

The following case is not supported by H2:

DROP TABLE VV;
CREATE TABLE VV (V1 INT, V2 VARCHAR(1));
INSERT INTO VV VALUES (1, 'A');
INSERT INTO VV VALUES (1, 'A');
INSERT INTO VV VALUES (1, 'B');
INSERT INTO VV VALUES (1, 'B');
INSERT INTO VV VALUES (2, 'A');
INSERT INTO VV VALUES (2, 'A');
INSERT INTO VV VALUES (2, 'B');

SELECT V1, V2 FROM VV A GROUP BY V1, V2 HAVING COUNT(*) =
 (SELECT MAX(k) FROM
  (SELECT V1, V2, COUNT(*) AS K FROM VV B WHERE B.V1 = A.V1 GROUP BY
V1, V2) S);

SQLite and PostgreSQL: It works.

MySQL: Unknown column 'A.V1' in 'where clause'

HSQLDB: user lacks privilege or object not found: A.V1

Apache Derby: Column 'A.V1' is either not in any table in the FROM
list or appears within a join specification and is outside the scope
of the join specification or appears in a HAVING clause and is not in
the GROUP BY list.

Original issue reported on code.google.com by thomas.t...@gmail.com on 20 Mar 2010 at 3:38

[GoogleCodeExporter]

Original comment by thomas.t...@gmail.com on 21 Mar 2010 at 11:31

[GoogleCodeExporter]

Original comment by thomas.t...@gmail.com on 21 Mar 2010 at 11:31

• Changed state: Accepted

[GoogleCodeExporter]

Hi, I'm new in this thing (open source project). Right now, I only know basic 
Java and a little about database, could I contribute to this project? If I 
could, then how to do it? Thanks in advance :).

Original comment by izharishaksa on 30 Dec 2010 at 4:29

[GoogleCodeExporter]

Hi,

There are various way to help develop an open source project like H2. The first 
step could be to translate the error messages and the GUI to your native 
language:

http://h2database.com/html/build.html#translating

Then, you could provide patches:

http://h2database.com/html/build.html#providing_patches

I would start with small patches, specially if you have never contributed to 
open source software before. It could be adding a test case to improve code 
coverage, which is listed here:

http://h2database.com/html/build.html#automated

This will get you started with the codebase. You will have to install an IDE 
(maybe Eclipse) and work with the code. For how to do that, see:

http://h2database.com/html/build.html

Once you are familiar with the code, you could implement missing features, the 
list of feature requests is here:

http://www.h2database.com/html/roadmap.html

I suggest to start with very small features that are easy to implement. Always 
keep in mind to provide test cases as well (the target code coverage is 90% for 
H2).

Regards,
Thomas

Original comment by thomas.t...@gmail.com on 3 Jan 2011 at 9:42

[GoogleCodeExporter]

This issue is in the roadmap at http://www.h2database.com/html/roadmap.html

Original comment by thomas.t...@gmail.com on 28 Jan 2011 at 7:25

• Changed state: InRoadmap