A query fails to return correct results silently

Description

The following query fails silently to return a correct result select v1.username, v2.username from user_ety v1, user_ety v2 where v1.username = '1' and v2.username = '2'. It returns one row with "1,1" instead of one row with "1,2".
The query works with pre JPA CMP EJBs (ver 2.1) but fails when moved to JPA based persistence.
This failure is very dangerous since it is silent and without manually checking data it is impossible to determine if a correct set is returned.

The workaround is to use aliases and modify the query so it looks like that:

select v1.username as username1, v2.username as username2 from user_ety v1, user_ety v2 where v1.username = '1' and v2.username = '2'.

Environment

JBoss EAP 5.1

Activity

Show:
Tom Ross
March 7, 2011, 3:28 PM

This is the test case for the bug.

Tom Ross
March 7, 2011, 3:31 PM

To use the test case unzip it to a directory.
Then cd TestCase-hsql and update the build.properties file to update the value of as.home that points to JBoss EAP home directory. Then run ant to build the test case and ant test to run the junit test. The test will fail.

Strong Liu
August 20, 2011, 3:18 AM
Steve Ebersole
November 29, 2011, 7:31 PM

I have identified this condition. This is something Hibernate cannot support. The problem is that through the JDBC ResultSetMetaData contract these 2 columns look like the same; JDBC does not provide access to enough contextual information here.

I will make the change to throw an exception when this condition is encountered.

The workaround/solution is simple, apply an alias to the column(s) in the sql query:
code
select v1.username as v1_username, v2.username as v2_username ...
code

Assignee

Steve Ebersole

Reporter

Tom Ross

Fix versions

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure