Support nested join operations in the FROM clause.

[GoogleCodeExporter]

What steps will reproduce the problem?
1. use the query ;)

What is the expected output? What do you see instead?
SELECT *
    FROM (t1 LEFT JOIN t2 ON t1.a=t2.a)
         LEFT JOIN t3
         ON t2.b=t3.b OR t2.b IS NULL
(taken from 
http://dev.mysql.com/doc/refman/5.0/en/nested-join-optimization.html)

is parsed to
[SELECT] => Array
    (
        [0] => Array
            (
                [expr_type] => operator
                [alias] => `*`
                [base_expr] => *
                [sub_tree] => 
            )

    )

[FROM] => Array
    (
        [0] => Array
            (
                [table] => (t1 LEFT JOIN t2 ON t1.a=t2.a)
                [alias] => (t1 LEFT JOIN t2 ON t1.a=t2.a)
                [join_type] => JOIN
                [ref_type] => 
                [ref_clause] => 
                [base_expr] => 
                [sub_tree] => 
            )

        [1] => Array
            (
                [table] => t3
                [alias] => t3
                [join_type] => LEFT 
                [ref_type] => ON
                [ref_clause] =>  t2.b=t3.b OR t2.b IS NULL
                [base_expr] => 
                [sub_tree] => 
            )

    )

i expect the (t1... part to be parsed into two tables

What version of the product are you using? On what operating system?
latest on zend server using php 5.3

Please provide any additional information below.

Original issue reported on code.google.com by josef.br...@gmail.com on 29 Apr 2011 at 9:22

[GoogleCodeExporter]

Add support for nested table joins.  This is a MySQL extension to the ANSI join 
syntax which is omitted.  Since the goal is full MySQL syntax support, this is 
an important enhancement request.

There are plenty of examples on the listed documentation page to use as test 
cases, as well as the example included in this report.

Original comment by greenlion@gmail.com on 30 Apr 2011 at 4:46

• Changed title: Support nested join operations in the FROM clause.
• Changed state: Accepted
• Added labels: Type-Enhancement, Priority-High
• Removed labels: Type-Defect, Priority-Medium

[GoogleCodeExporter]

Nested join support is checked in at revision 42:

SELECT *
    FROM (t1 LEFT JOIN t2 ON t1.a=t2.a)
         LEFT JOIN t3
         ON t2.b=t3.b OR t2.b IS NULL
Array
(
    [SELECT] => Array
        (
            [0] => Array
                (
                    [expr_type] => operator
                    [alias] => `*`
                    [base_expr] => *
                    [sub_tree] => 
                )

        )

    [FROM] => Array
        (
            [0] => Array
                (
                    [table] => (t1 LEFT JOIN t2 ON t1.a=t2.a)
                    [alias] => 
                    [join_type] => JOIN
                    [ref_type] => 
                    [ref_clause] => 
                    [base_expr] => t1 LEFT JOIN t2 ON t1.a=t2.a
                    [sub_tree] => Array
                        (
                            [0] => Array
                                (
                                    [table] => t1
                                    [alias] => t1
                                    [join_type] => JOIN
                                    [ref_type] => 
                                    [ref_clause] => 
                                    [base_expr] => 
                                    [sub_tree] => 
                                )

                            [1] => Array
                                (
                                    [table] =>  
                                    [alias] => t2
                                    [join_type] => LEFT 
                                    [ref_type] => ON
                                    [ref_clause] => t1.a=t2.a
                                    [base_expr] => 
                                    [sub_tree] => 
                                )

                        )

                )

            [1] => Array
                (
                    [table] => t3
                    [alias] => t3
                    [join_type] => LEFT 
                    [ref_type] => ON
                    [ref_clause] => t2.b=t3.b OR t2.b IS NULL
                    [base_expr] => 
                    [sub_tree] => 
                )

        )

)

t/nested.php is added to verify operation

Original comment by greenlion@gmail.com on 2 May 2011 at 5:15

• Changed state: Verified