[hibernate-issues] [Hibernate-JIRA] Updated: (HHH-1480) JOIN precendence rules per SQL-99

Steve Ebersole (JIRA) noreply at atlassian.com
Fri Oct 31 00:24:04 EDT 2008


     [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-1480?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steve Ebersole updated HHH-1480:
--------------------------------

    Description: 
In SQL-92 joins performed in the where clause (comma operator in from clause) and joins performed in the from clause (join keyword) had the same precedence.  SQL-99 clarified this such that the from clause joins had higher precedence.

Hibernate currently treats these as having the same precedence.

A good explanation comes from the MySQL docs ( http://dev.mysql.com/doc/refman/5.0/en/join.html ) :

#

Previously, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was interpreted as ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is interpreted as (t1, (t2 JOIN t3)). This change affects statements that use an ON clause, because that clause can refer only to columns in the operands of the join, and the change in precedence changes interpretation of what those operands are.

Example:

CREATE TABLE t1 (i1 INT, j1 INT);
CREATE TABLE t2 (i2 INT, j2 INT);
CREATE TABLE t3 (i3 INT, j3 INT);
INSERT INTO t1 VALUES(1,1);
INSERT INTO t2 VALUES(1,1);
INSERT INTO t3 VALUES(1,1);
SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);

Previously, the SELECT was legal due to the implicit grouping of t1,t2 as (t1,t2). Now the JOIN takes precedence, so the operands for the ON clause are t2 and t3. Because t1.i1 is not a column in either of the operands, the result is an Unknown column 't1.i1' in 'on clause' error. To allow the join to be processed, group the first two tables explicitly with parentheses so that the operands for the ON clause are (t1,t2) and t3:

SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);

Alternatively, avoid the use of the comma operator and use JOIN instead:

SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);

This change also applies to statements that mix the comma operator with INNER JOIN, CROSS JOIN, LEFT JOIN, and RIGHT JOIN, all of which now have higher precedence than the comma operator.


  was:
http://dev.mysql.com/doc/refman/5.0/en/join.html

"Beginning with MySQL 5.0.12, natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard. These changes make MySQL more compliant with standard SQL. However, they can result in different output columns for some joins. Also, some queries that appeared to work correctly in older versions must be rewritten to comply with the standard. The following list provides more detail about several effects of the 5.0.12 change in join processing. The term "previously" means "prior to MySQL 5.0.12."

Using 5.0.18 and hibernate 3.1.2, hibernate is not producing the correct join syntax for the latest versions of mysql. 

For example:

  FROM tableA AS A, tableB AS B INNER JOIN tableC AS C ON A.field1 =
C.field2

The above fails, because the B JOIN C is evaluated before the A alias has
been created.

The solution is to use parentheses to force an order of evaluation:

  FROM (tableA AS A, tableB AS B) INNER JOIN tableC AS C ON A.field1 =
C.field2 

        Summary: JOIN precendence rules per SQL-99  (was: Support new MySQL 5.0.12 join syntax)

> JOIN precendence rules per SQL-99
> ---------------------------------
>
>                 Key: HHH-1480
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-1480
>             Project: Hibernate Core
>          Issue Type: New Feature
>          Components: query-hql
>    Affects Versions: 3.1.2
>            Reporter: trebor iksrazal
>            Assignee: Steve Ebersole
>             Fix For: 3.2.x, 3.3.x, 3.4
>
>
> In SQL-92 joins performed in the where clause (comma operator in from clause) and joins performed in the from clause (join keyword) had the same precedence.  SQL-99 clarified this such that the from clause joins had higher precedence.
> Hibernate currently treats these as having the same precedence.
> A good explanation comes from the MySQL docs ( http://dev.mysql.com/doc/refman/5.0/en/join.html ) :
> #
> Previously, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was interpreted as ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is interpreted as (t1, (t2 JOIN t3)). This change affects statements that use an ON clause, because that clause can refer only to columns in the operands of the join, and the change in precedence changes interpretation of what those operands are.
> Example:
> CREATE TABLE t1 (i1 INT, j1 INT);
> CREATE TABLE t2 (i2 INT, j2 INT);
> CREATE TABLE t3 (i3 INT, j3 INT);
> INSERT INTO t1 VALUES(1,1);
> INSERT INTO t2 VALUES(1,1);
> INSERT INTO t3 VALUES(1,1);
> SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);
> Previously, the SELECT was legal due to the implicit grouping of t1,t2 as (t1,t2). Now the JOIN takes precedence, so the operands for the ON clause are t2 and t3. Because t1.i1 is not a column in either of the operands, the result is an Unknown column 't1.i1' in 'on clause' error. To allow the join to be processed, group the first two tables explicitly with parentheses so that the operands for the ON clause are (t1,t2) and t3:
> SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);
> Alternatively, avoid the use of the comma operator and use JOIN instead:
> SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);
> This change also applies to statements that mix the comma operator with INNER JOIN, CROSS JOIN, LEFT JOIN, and RIGHT JOIN, all of which now have higher precedence than the comma operator.

-- 
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

        



More information about the hibernate-issues mailing list