SELECT table.x AS y returns x and not y as column name.

[GoogleCodeExporter]

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1.  The following was working fine with version 2008-09-26

SELECT
    REPOS.ID AS REPOID,
    REPOS.NAME AS REPONAME,
    REPOS.URLBASE,
    CASEWHEN(REPOS.PUBLICKEY IS NULL, 0, 1) AS HASPUBLICKEY, 
    REPOS.TRUSTED,
    REPO_COLLECTIONS.ID AS COLLECTIONID,
    REPO_COLLECTIONS.DISTPATH,
    REPO_COLLECTION_SETS.ID AS COLLECTIONSETID,
    REPO_COLLECTION_SETS.NAME AS SETNAME
    FROM
    REPOS, REPO_COLLECTIONS, REPO_COLLECTION_SETS
    WHERE
    REPOS.ID=REPO_COLLECTIONS.REPOID
    AND
    REPO_COLLECTIONS.ID=REPO_COLLECTION_SETS.REPOCOLLECTIONID
    AND
    REPOS.ID>1  --Ignore the dev repo
;

What is the expected output? What do you see instead?

First column of the result should be called REPOID.  Instead it returns ID.
Notice there are a few tables here each with an ID column and each of which
I'm trying to do SELECT table.ID AS y.  In each case I'm getting ID back as
the column name - this means the result set is ambigous as it now has 3 ID
columns.

What version of the product are you using? On what operating system, file
system, and virtual machine?

H2 dated 2009-05-01
Linux Ubuntu Jaunty x64
Java 1.5.0 x64

Do you know a workaround?

No.

How important/urgent is the problem for you?

Seems like it could break a lot of things for people.

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

Defect.

Please provide any additional information below.

Original issue reported on code.google.com by peter.ro...@gmail.com on 12 May 2009 at 3:49

[GoogleCodeExporter]

OK I reverted back to Version 1.0.75 (2008-07-14) and it works again.  FYI none 
of
the 1.1.xxxx builds work.

The database storage is on the filesystem and I'm accessing it with 
org.h2.Driver and
this JDBC URL...

jdbc:h2:/home/pjr/temp/H2database

Is there a compatibility issue between 1.1.xxx and 1.0.xxx ?

Original comment by peter.ro...@gmail.com on 12 May 2009 at 4:09

[GoogleCodeExporter]

More details.  I tried connecting with the h2.sh db tool lastest 2009-05-01
connecting to the existing DB with same URL connection. Same JVM etc. In this
environment it works correctly.

Let me know what I can do to try to corner this.

Original comment by peter.ro...@gmail.com on 12 May 2009 at 4:27

[GoogleCodeExporter]

Hi,

This was changed in version version 1.1.101 (2008-10-17), see
http://www.h2database.com/html/changelog.html "For alias columns,
ResultSetMetaData.getTableName() and getColumnName() now return the real table 
and
column name in the default mode."

This is how MySQL works, and I believe it's compliant with the JDBC standard. 
See: 

http://java.sun.com/javase/6/docs/api/java/sql/ResultSetMetaData.html#getColumnL
abel(int)
http://java.sun.com/javase/6/docs/api/java/sql/ResultSetMetaData.html#getColumnN
ame(int)

You can change it using a compatibility mode (MODE=...):
http://www.h2database.com/html/features.html#compatibility
or by setting the system property h2.aliasColumnName to true:
http://www.h2database.com/javadoc/org/h2/constant/SysProperties.html#h2.aliasCol
umnName

Regards,
Thomas

Original comment by thomas.t...@gmail.com on 16 May 2009 at 9:03

[GoogleCodeExporter]

Thank you very much.  This is exactly the problem - our ResultSet handling code 
was
assuming the getColumnName would work correctly.  Funny this is working for 
Postgres
and MySQL and the earlier generation of H2.  But thanks for 'doing the right 
thing'
with a correct implementation and hopefully our code is also now spec complient.

Thanks also for the hard work on H2 - its great!

Original comment by peter.ro...@gmail.com on 16 May 2009 at 4:25

[GoogleCodeExporter]

I am now closing this issue as 'wont fix'.

Original comment by thomas.t...@gmail.com on 21 May 2009 at 4:42

• Changed state: WontFix