Erroneous "Duplicate column name"

[GoogleCodeExporter]

This query works:

SELECT movie.id,movie.name,genre.name
FROM omdb.movie
INNER JOIN omdb.genre
ON (movie.genreid=genre.id);

But, just adding a count(*) wrapper fails:
SELECT count(*) FROM (
    SELECT movie.id,movie.name,genre.name
    FROM omdb.movie
    INNER JOIN omdb.genre
    ON (movie.genreid=genre.id)
);

with the following error:
org.h2.jdbc.JdbcSQLException: Duplicate column name "NAME"; 

Note that the queries are not hand written. They are being generated 
automatically by my project, hence this is not a one-off problem; there are 
many other cases where this problem appears.

Thanks,
Harshad

Original issue reported on code.google.com by harshad...@gmail.com on 30 May 2012 at 7:50

[GoogleCodeExporter]

This doesn't have high priority for me as MySQL also throws an exception.

If you could provide a patch I will have a look at it, but otherwise I'm afraid 
I will not have time to work on it.

Test case:
DROP TABLE IF EXISTS TEST;
CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255));
INSERT INTO TEST VALUES(1, 'Hello');
SELECT count(*) FROM (SELECT a.id, b.id FROM test a INNER JOIN test b ON a.id = 
b.id) as x

PostgreSQL, HSQLDB, Derby, SQLite: works
MySQL 5.1.65: Duplicate column name 'id' 42S21/1060

Original comment by thomas.t...@gmail.com on 30 Nov 2012 at 7:04

• Changed state: WontFix

[GoogleCodeExporter]

Thanks for looking into this problem.

I don't know the code enough to make a patch myself. But if you keep the issue 
open, perhaps someone will come along and make a patch.

Original comment by harshad...@gmail.com on 30 Nov 2012 at 7:34

[GoogleCodeExporter]

Unfortunately, not many developers look at the issues list. If you are looking 
for a volunteer, the best place it to ask at the Google Group.

Original comment by thomas.t...@gmail.com on 30 Nov 2012 at 8:04