missing ref_clause in joins / wrong interpretation

[GoogleCodeExporter]

What steps will reproduce the problem?
1. parse the query

What is the expected output? What do you see instead?
query:
SELECT a.field1, b.field1, c.field1
  FROM tablea a 
  LEFT OUTER JOIN tableb b ON b.ida = a.id
  LEFT OUTER JOIN tablec c ON c.idb = b.id

result:
[SELECT] => Array
    (
        [0] => Array
            (
                [expr_type] => colref
                [alias] => `a.field1`
                [base_expr] => a.field1
                [sub_tree] => 
            )

        [1] => Array
            (
                [expr_type] => colref
                [alias] => `b.field1`
                [base_expr] => b.field1
                [sub_tree] => 
            )

        [2] => Array
            (
                [expr_type] => colref
                [alias] => `c.field1`
                [base_expr] => c.field1
                [sub_tree] => 
            )

    )

[FROM] => Array
    (
        [0] => Array
            (
                [table] => tablea
                [alias] => a
                [join_type] => JOIN
                [ref_type] => 
                [ref_clause] => 
                [base_expr] => 
                [sub_tree] => 
            )

        [1] => Array
            (
                [table] => tableb
                [alias] => b
                [join_type] => LEFT LEFT JOIN
                [ref_type] => ON
                [ref_clause] =>  OUTER 
                [base_expr] => 
                [sub_tree] => 
            )

        [2] => Array
            (
                [table] => tablec
                [alias] => c
                [join_type] => JOIN
                [ref_type] => ON
                [ref_clause] =>  c.idb = b.id
                [base_expr] => 
                [sub_tree] => 
            )

    )

i am missing the reference b.ida = a.id (and the last join should be left outer)

What version of the product are you using? On what operating system?
latest (29.04.2011) on zend server 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:19

[GoogleCodeExporter]

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

• Changed state: Accepted
• Added labels: Priority-Critical
• Removed labels: Priority-Medium

[GoogleCodeExporter]

$sql = 'SELECT a.field1, b.field1, c.field1
  FROM tablea a 
  LEFT OUTER JOIN tableb b ON b.ida = a.id
  RIGHT JOIN tablec c ON c.idb = b.id
  JOIN tabled d USING (d_id)
  right outer join e on e.id = a.e_id;
  left join e e2 using (e_id)
  join e e3 on (e3.e_id = e2.e_id)';
echo $sql . "\n";

$parser->parse($sql);
$p = $parser->parsed;
print_r($p);

-- output --

Array
(
    [SELECT] => Array
        (
            [0] => Array
                (
                    [expr_type] => colref
                    [alias] => `a.field1`
                    [base_expr] => a.field1
                    [sub_tree] => 
                )

            [1] => Array
                (
                    [expr_type] => colref
                    [alias] => `b.field1`
                    [base_expr] => b.field1
                    [sub_tree] => 
                )

            [2] => Array
                (
                    [expr_type] => colref
                    [alias] => `c.field1`
                    [base_expr] => c.field1
                    [sub_tree] => 
                )

        )

    [FROM] => Array
        (
            [0] => Array
                (
                    [table] => tablea
                    [alias] => a
                    [join_type] => JOIN
                    [ref_type] => 
                    [ref_clause] => 
                    [base_expr] => 
                    [sub_tree] => 
                )

            [1] => Array
                (
                    [table] => tableb
                    [alias] => b
                    [join_type] => LEFT 
                    [ref_type] => ON
                    [ref_clause] => b.ida = a.id
                    [base_expr] => 
                    [sub_tree] => 
                )

            [2] => Array
                (
                    [table] => tablec
                    [alias] => c
                    [join_type] => RIGHT 
                    [ref_type] => ON
                    [ref_clause] => c.idb = b.id
                    [base_expr] => 
                    [sub_tree] => 
                )

            [3] => Array
                (
                    [table] => tabled
                    [alias] => d
                    [join_type] => JOIN
                    [ref_type] => USING
                    [ref_clause] => d_id
                    [base_expr] => 
                    [sub_tree] => 
                )

            [4] => Array
                (
                    [table] => e
                    [alias] => e
                    [join_type] => RIGHT 
                    [ref_type] => ON
                    [ref_clause] => e.id = a.e_id
                    [base_expr] => 
                    [sub_tree] => 
                )

            [5] => Array
                (
                    [table] => e
                    [alias] => e2
                    [join_type] => LEFT 
                    [ref_type] => USING
                    [ref_clause] => e_id
                    [base_expr] => 
                    [sub_tree] => 
                )

            [6] => Array
                (
                    [table] => e
                    [alias] => e3
                    [join_type] => JOIN
                    [ref_type] => ON
                    [ref_clause] => e3.e_id = e2.e_id
                    [base_expr] => 
                    [sub_tree] => 
                )

        )

)

Original comment by greenlion@gmail.com on 2 May 2011 at 4:30

• Changed state: Verified

[GoogleCodeExporter]

Verified fixed in R41.

Original comment by greenlion@gmail.com on 2 May 2011 at 4:34