Issue Type: Bug Bug
Affects Versions: 4.2.0.Final, 3.6.10, 3.5.6
Assignee: Unassigned
Attachments: OrderByDistinctTest.zip, tables.sql
Components: core
Created: 04/Jun/13 10:21 AM
Description:

This issue seems identical to HHH-172 (maybe a regression?)

The following query :
select distinct prod.supplier from products prod order by prod.supplier.supplierName

for the attached database, generates an incorrect SQL query with an extra join:
[Oracle]
select
distinct supplier1_.SUPPLIER_ID as SUPPLIER1_1_,
supplier1_.SUPPLIER_NAME as SUPPLIER2_1_,
supplier1_.CONTACT_NAME as CONTACT3_1_
from
PRODUCTS products0_
inner join
SUPPLIER supplier1_
on products0_.SUPPLIER_ID=supplier1_.SUPPLIER_ID,
SUPPLIER supplier2_
where
products0_.SUPPLIER_ID=supplier2_.SUPPLIER_ID
order by
supplier2_.SUPPLIER_NAME

[H2]:

SELECT DISTINCT supplier1_.SUPPLIER_ID AS SUPPLIER1_1_,
supplier1_.SUPPLIER_NAME AS SUPPLIER2_1_,
supplier1_.CONTACT_NAME AS CONTACT3_1_
FROM PUBLIC.PRODUCTS products0_
INNER JOIN PUBLIC.SUPPLIER supplier1_
ON products0_.SUPPLIER_ID=supplier1_.SUPPLIER_ID
CROSS JOIN PUBLIC.SUPPLIER supplier2_
WHERE products0_.SUPPLIER_ID=supplier2_.SUPPLIER_ID
ORDER BY supplier2_.SUPPLIER_NAME

I attach a test case with an auto-generated h2 database (it's a runnable netbeans project ) , but this is the output using an Oracle database:

INFO: HHH000046: Connection properties:

{user=SBUREAU_D, password=****}

04-jun-2013 19:00:24 org.hibernate.dialect.Dialect <init>
INFO: HHH000400: Using dialect: org.hibernate.dialect.Oracle10gDialect
04-jun-2013 19:00:24 org.hibernate.engine.transaction.internal.TransactionFactoryInitiator initiateService
INFO: HHH000399: Using default transaction strategy (direct JDBC transactions)
04-jun-2013 19:00:24 org.hibernate.hql.internal.ast.ASTQueryTranslatorFactory <init>
INFO: HHH000397: Using ASTQueryTranslatorFactory
Hibernate:
select
distinct supplier1_.SUPPLIER_ID as SUPPLIER1_1_,
supplier1_.SUPPLIER_NAME as SUPPLIER2_1_,
supplier1_.CONTACT_NAME as CONTACT3_1_
from
PRODUCTS products0_
inner join
SUPPLIER supplier1_
on products0_.SUPPLIER_ID=supplier1_.SUPPLIER_ID,
SUPPLIER supplier2_
where
products0_.SUPPLIER_ID=supplier2_.SUPPLIER_ID
order by
supplier2_.SUPPLIER_NAME
04-jun-2013 19:00:24 org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 1791, SQLState: 42000
04-jun-2013 19:00:24 org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: ORA-01791: no es una expresión obtenida bajo SELECT

Project: Hibernate ORM
Priority: Major Major
Reporter: David Latorre
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira