Re: [eclipselink-dev] t0 not in FROM clause in generated SQL for JUnitJP

[Tom Ware <tom.ware@xxxxxxxxxx>]

Hi Dies,

  The error occurs when we run the query that searches for the expected result. 
 It might be possible to alter that query.

  I would like to focus on the next query and whether it is possible to write 
reasonable SQL that works on Symfoware for that query:

"SELECT DISTINCT emp.id FROM Employee emp WHERE EXISTS ( SELECT managedEmp.id 
FROM Employee managedEmp WHERE managedEmp = emp.manager)"

  Can that JPQL execute successfully? (comment out: List expectedResult = 
(List)getServerSession().executeQuery(mainQuery);)

  If so, we can likely fix the test.

  The key here is the WHERE clause (WHERE managedEmp = emp.manager).  It refers 
to the outer query from the subselect.  Is there a way to write Symfoware-legal 
SQL that allows that WHERE clause to work?

  This test suite contains examples that come directly from the JPA 
specification, so we cannot change the JPQL in the example.   If we cannot 
figure out a way to write the SQL for this JPQL, it looks like we will just have 
to specify that subselects that refer to outer queries are not supported when 
using Symfoware and disable this test for Symfoware.

  BTW: I ran these tests in MySQL and subselects that refer to the outer query 
appear to be supported (Updates and Deletes with those kinds of subselects are not)

-Tom

Dies Koper wrote:
> Hi Tom, Andrei,
>
> > I think this is the issue we have discussed before related to Symfoware
> > being unable to use a reference from the outer query in a subquery. The
> > only other database I know of with this issue is MySQL - we generally
> > use temp tables to get around this for Updates and Deletes - if this is
> > not an update or delete, I am not sure how MySQL is behaving, but could
> > check if you can let me know what test is causing the issue.
>
> It's in the Subject: JUnitJPQLExamplesTestSuite's testExistsExpression.
> It also occurs in JUnitJPQLComplexAggregateTestSuite's 
> complexCountOnJoinedVariableCompositePK.
>
> I thought I was seeing a different issue here and I wasn't sure if and 
> how they were related:
>
> 1. The issue described in this e-mail (outer reference not allowed).
>
> 2. Same table used in FROM clauses of main and subquery. This is the 
> issue I brought up back in September where Andrei suggested I try the 
> global temporary tables. E.g. 'CMP3_ORDER' below:
>
>    DELETE FROM CMP3_ORDER WHERE EXISTS(SELECT t1.ORDER_ID FROM 
> CMP3_CUSTOMER t0, CMP3_ORDER t1 WHERE ((t0.NAME = 'Karen McDonald') AND 
> (t0.CUST_ID = t1.CUSTOMER_CUST_ID)) AND t1.ORDER_ID = CMP3_ORDER.ORDER_ID)
>
> Issue 1. only happens in these two tests, not in any of the other JPA 
> and Core tests. I suppose it would work if it did an 'EXISTS (SELECT 1 
> FROM ...)' instead?
>
> Issue 2. happens in many tests in the JPA test set and six of the Core 
> tests for both update and delete queries. At the time I came up with an 
> alternative statement, but that only works well when linking tables with 
> single-column primary keys.
>
> Cheers,
> Dies
>
>
> > Dies Koper wrote:
> > > Hi,
> > >
> > > Another issue I found while running the JPA test set on Symfoware:
> > >
> > > The test simply does a WHERE EXISTS with a subquery. The expected result
> > > should be the same as the following query:
> > >
> > > SELECT DISTINCT emp.id FROM Employee emp WHERE EXISTS ( SELECT
> > > managedEmp.id FROM Employee managedEmp WHERE managedEmp = emp.manager)
> > >
> > > However, the generated SQL statement (see below), has "t0.EMP_ID" in the
> > > select list of the subquery, while t0 is not in its FROM clause.
> > >
> > > SELECT t0.EMP_ID FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE (EXISTS
> > > (SELECT t0.EMP_ID FROM CMP3_SALARY t5, CMP3_EMPLOYEE t4, CMP3_SALARY t3,
> > > CMP3_EMPLOYEE t2 WHERE (((t2.EMP_ID = t4.EMP_ID) AND (t1.EMP_ID =
> > > t0.EMP_ID)) AND (((t2.EMP_ID = t0.MANAGER_EMP_ID) AND (t3.EMP_ID =
> > > t2.EMP_ID)) AND (t5.EMP_ID = t4.EMP_ID)))) AND (t1.EMP_ID = t0.EMP_ID))
> > >
> > > Is this intended?
> > >
> > > This query fails on Symfoware because of that reason.
> > > Maybe other databases are more forgiving?
> > >
> > > Thanks,
> > > Dies
>
> _______________________________________________
> eclipselink-dev mailing list
> eclipselink-dev@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-dev