Bug 327411

Description andis 2010-10-10 20:36:30 EDT

Build Identifier: Eclipselink 1.1.3 installed by default under Eclipse for Java EE build 20090621-0832

Eclipselink 1.1.3 generates invalid SQL that doesn't use set table aliases.

Creating JPQL query:

select c from Client c, Order ord WHERE ord.client=c and c.id=400

Ends with invalid SQL :

SELECT clients.clt_id, clients.clt_name FROM orders t0, clients t1 
WHERE ((t0.ord_cli_id = t1.clt_id) AND (t0.ord_id = ?))

Using PostgreSQL 8.1 it generates following error:

Internal Exception: org.postgresql.util.PSQLException: ERROR: invalid reference to FROM-clause entry for table 

"clients"
  Wskazówka: Perhaps you meant to reference the table alias "t1".
  Pozycja: 8
Error Code: 0
Call: SELECT clients.clt_id, clients.clt_name FROM orders t0, clients t1 WHERE ((t0.ord_cli_id = t1.clt_id) AND 

(t0.ord_id = ?))
	bind => [400]


EclipseLink should use clients alias "t1" while referencing clients table, not "clients" table name. 



This is an invalid use of SQL. PostgreSQL 8.1 is serious and strict about this rule 

(http://www.network-theory.co.uk/docs/postgresql/vol1/TableandColumnAliases.html):

"The alias becomes the new name of the table reference for the current query--it is no longer possible to refer to the 

table by the original name."






Reproducible: Always

Steps to Reproduce:
Entities
Client (maps to table name: clients)
id (maps to column name: cli_id)
name (column name: cli_name)

Order (table name: orders)
id (column name: ord_id)
date (column name: ord_date)
client (join column: ord_cli_id, refColumnName: cli_id)

Execute following query:

select c from Client c, Order ord WHERE ord.client=c and c.id=400