[hibernate-issues] [Hibernate-JIRA] Issue Comment Edited: (HHH-5352) Misleading replacement of commas with cross joins

[Patras Vlad (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-5352?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=39680#action_39680 ] 

Patras Vlad edited comment on HHH-5352 at 1/18/11 7:21 AM:
-----------------------------------------------------------

When HHH-1480 was implemented it is obvious that {{org.hibernate.test.hql.HQLTest}} would have failed.
So the developer added {{public static boolean REGRESSION_STYLE_CROSS_JOINS = false;}} to {{org.hibernate.hql.ast.SqlGenerator}}. 
This is set to {{true}} in {{prepareTest}} and to {{false}} in {{cleanupTest}}.
If you want {{HQLTest}} to fail just set {{REGRESSION_STYLE_CROSS_JOINS}} to {{false}} again, it's only purpose is to make {{HQLTest}} pass.

Even the test case considers "cross join" and comma to be equivalent by ignoring the change (it tells the query generator to use commas again just for testing purposes). There is no test that actually checks if the generated sql returns the same thing after the change.

For this purpose I modified {{org.hibernate.test.orphan.OrphanTest}} (it has suitable entities).
The important bit is:
{code}
SqlGenerator.REGRESSION_STYLE_CROSS_JOINS = true;
		
session = openSession();
t = session.beginTransaction();
log.info("Running with comma");
		
//when REGRESSION_STYLE_CROSS_JOINS is true, commas in hql are translated to commas is SQl and this will work as expected.
//the condition "where 1=1" is added so the queries are different, otherwise the next one will be taken from cache.
		
assertEquals(2, session.createQuery("from Product, Mail m right join m.user where 1=1").list().size());						
		
t.commit();
session.close();
		
SqlGenerator.REGRESSION_STYLE_CROSS_JOINS = false;
		
session = openSession();
t = session.beginTransaction();
		
//when REGRESSION_STYLE_CROSS_JOINS is true, commas in hql are translated a dialect specific string, usually "cross join"
//this fails on H2 with "unsupported outer join condition" - it seems H2 doesn't even support this
//on MySQL and MS Sql the assert will fail because the query only returns one result
		
log.info("Running with cross join");			
assertEquals(2, session.createQuery("from Product, Mail m right join m.user where 2=2").list().size());						
		
t.commit();
session.close();
{code}

It is obvious from the test that when creating sql the old way ({{REGRESSION_STYLE_CROSS_JOINS}} is {{true}}) everything works as expected. But when replacing commas with "cross join" ({{REGRESSION_STYLE_CROSS_JOINS}} is {{false}}), the query dosn't return the expected results and even crashes on HyperDB 2.

Note that a patch with the changes is attached.

      was (Author: senoctar):
    When HHH-1480 was implemented it is obvious that {{org.hibernate.test.hql.HQLTest}} would have failed.
So the developer added {{public static boolean REGRESSION_STYLE_CROSS_JOINS = false;}} to {{org.hibernate.hql.ast.SqlGenerator}}. 
This is set to {{true}} in {{prepareTest}} and to {{false}} in {{cleanupTest}}.
If you want {{HQLTest}} to fail just set {{REGRESSION_STYLE_CROSS_JOINS}} to {{false}} again, it's only purpose is to make {{HQLTest}} pass.

Even the test case considers "cross join" and comma to be equivalent by ignoring the change (it tells the query generator to use commas again just for testing purposes). There is no test that actually checks if the generated sql returns the same thing after the change.

For this purpose I modified {{org.hibernate.test.orphan.OrphanTest}} (it has suitable entities).
The important bit is:
{code}
SqlGenerator.REGRESSION_STYLE_CROSS_JOINS = true;
		
session = openSession();
t = session.beginTransaction();
log.info("Running with comma");
		
//when REGRESSION_STYLE_CROSS_JOINS is true, commas in hql are translated to commas is SQl and this will work as expected.
//the condition "where 1=1" is added so the queries are different, otherwise the next one will be taken from cache.
		
assertEquals(2, session.createQuery("from Product, Mail m right join m.user where 1=1").list().size());						
		
t.commit();
session.close();
		
SqlGenerator.REGRESSION_STYLE_CROSS_JOINS = false;
		
session = openSession();
t = session.beginTransaction();
		
//when REGRESSION_STYLE_CROSS_JOINS is true, commas in hql are translated a dialect specific string, usually "cross join"
//this fails on H2 with "unsupported outer join condition" - it seems H2 doesn't even support this
//on MySQL and MS Sql the assert will fail because the query only returns one result
		
log.info("Running with cross join");			
assertEquals(2, session.createQuery("from Product, Mail m right join m.user where 2=2").list().size());						
		
t.commit();
session.close();
{code}

It is obvious from the test that when creating sql the old way ({{REGRESSION_STYLE_CROSS_JOINS}} is {{true}}) everything works as expected. But when replacing commas with "cross join" ({{REGRESSION_STYLE_CROSS_JOINS}} is {{false}}), the query dosn't return the expected results and even crashes on HyperDB 2.
  
> Misleading replacement of commas with cross joins
> -------------------------------------------------
>
>                 Key: HHH-5352
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5352
>             Project: Hibernate Core
>          Issue Type: Bug
>    Affects Versions: 3.5.0-Beta-2, 3.5.0-Beta-3, 3.5.0-Beta-4, 3.5.0-CR-1, 3.5.0-CR-2, 3.5.0-Final, 3.5.1, 3.5.2, 3.5.3, 3.6.0
>         Environment: Hibernate 3.5.3
> Ms SQL Server 2005
>            Reporter: Patras Vlad
>             Fix For: 3.6.next, 4.0.0.Alpha1
>
>         Attachments: commaAgainstCrossJoin.patch
>
>
>   There was a new feature implemented in 3.5.0-Beta-2: HHH-1480, as a result, for many dialects, the comma operator "," was replaced with "cross join". As it's already obvious from the feature's description, this changes join precedence. If the query contains outer joins (left or right) this replacement changes the results (because outer join precedence is important).
>   This might not be interpreted as a bug if you know what SQL operator will be generated when you place a comma in HQL, but it's misleading if you don't. A HQL query like:
> {noformat}select a from A a, B left join B.c c{noformat} 
> is not equivalent with the SQL query:
> {noformat}select a.* from A.a, B b left join C c on b.cid = c.id{noformat} 
> because the actual SQL query is:
> {noformat}select a.* from A.a cross join B b left join C c on b.cid = c.id{noformat}
> The first SQL query would make a left join on B and C and then a cross join with the result and A (and this is what we should expect). The second SQL query makes a cross join on A and B and then a left join with the result and C (this is not expected since we had a comma in HQL).
> I understand the issues presented in HHH-1480, but they should be expected, i.e. a developer SHOULD expect a faliure from a query like 
> {noformat}select a from A a, B b join a.c C{noformat}
> because "a" is not visible in the join. Hibernate should not use a cross join behind the scenes to allow such a query at the expense of providing misleading results for others.
> Maybe turn this into a feature/improvement or add another to allow explicit comma or croos join in HQL, or support parenthesis in HQL.
> The only workaround I can see for now is to re-order the query like this:
> {noformat}select a from B left join B.c c, A a{noformat} 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira