search

rickywu-posh / php-sql-parser

Automatically exported from code.google.com/p/php-sql-parser
BSD 3-Clause "New" or "Revised" License
0 stars 0 forks source link

Parentesis Urgent Fix #15

Closed GoogleCodeExporter closed 8 years ago

GoogleCodeExporter commented 8 years ago 
What steps will reproduce the problem?
1.

When parsing the following MySQL Query:

select usr_id, usr_login, case id_tipousuario when 1 then 'Usuario CVE' when 2 
then concat('Usuario Vendedor -', codigovendedor, '-') when 3 then 
concat('Usuario Vendedor Meson -', codigovendedor, '-') end tipousuario, 
CONCAT( usr_nombres, ' ', usr_apellidos ) as nom_com, cod_local from usuarios 
where usr_estado <> 2 order by 3, 1, 4

What is the expected output? What do you see instead?

and print_r the parsed thing you get:

    SELECT =>

        0 =>

            expr_type => colref

            alias => `usr_id`

            base_expr => usr_id

            sub_tree => 

        1 =>

            expr_type => colref

            alias => `usr_login`

            base_expr => usr_login

            sub_tree => 

        2 =>

            expr_type => expression

            alias => `tipousuario`

            base_expr => case id_tipousuario when 1 then 'Usuario CVE' when 2 then concat('Usuario Vendedor -', codigovendedor, '-') when 3 then concat('Usuario Vendedor Meson -', codigovendedor, '-') end

            sub_tree =>

                0 =>

                    expr_type => operator

                    base_expr => case

                    sub_tree => 

                1 =>

                    expr_type => colref

                    base_expr => id_tipousuario

                    sub_tree => 

                2 =>

                    expr_type => operator

                    base_expr => when

                    sub_tree => 

                3 =>

                    expr_type => const

                    base_expr => 1

                    sub_tree => 

                4 =>

                    expr_type => reserved

                    base_expr => THEN

                    sub_tree => 

                5 =>

                    expr_type => const

                    base_expr => 'Usuario CVE'

                    sub_tree => 

                6 =>

                    expr_type => operator

                    base_expr => when

                    sub_tree => 

                7 =>

                    expr_type => const

                    base_expr => 2

                    sub_tree => 

                8 =>

                    expr_type => reserved

                    base_expr => THEN

                    sub_tree => 

                9 =>

                    expr_type => function

                    base_expr => CONCAT

                    sub_tree => 

                10 =>

                    expr_type => expression

                    base_expr => ('Usuario Vendedor -', codigovendedor, '-')

                    sub_tree =>

                        0 =>

                            expr_type => const

                            base_expr => 'Usuario Vendedor -'

                            sub_tree => 

                        1 =>

                            expr_type => colref

                            base_expr => ,

                            sub_tree => 

                        2 =>

                            expr_type => colref

                            base_expr => codigovendedor

                            sub_tree => 

                        3 =>

                            expr_type => colref

                            base_expr => ,

                            sub_tree => 

                        4 =>

                            expr_type => const

                            base_expr => '-'

                            sub_tree => 

                11 =>

                    expr_type => operator

                    base_expr => when

                    sub_tree => 

                12 =>

                    expr_type => const

                    base_expr => 3

                    sub_tree => 

                13 =>

                    expr_type => reserved

                    base_expr => THEN

                    sub_tree => 

                14 =>

                    expr_type => function

                    base_expr => CONCAT

                    sub_tree => 

                15 =>

                    expr_type => expression

                    base_expr => ('Usuario Vendedor Meson -', codigovendedor, '-')

                    sub_tree =>

                        0 =>

                            expr_type => const

                            base_expr => 'Usuario Vendedor Meson -'

                            sub_tree => 

                        1 =>

                            expr_type => colref

                            base_expr => ,

                            sub_tree => 

                        2 =>

                            expr_type => colref

                            base_expr => codigovendedor

                            sub_tree => 

                        3 =>

                            expr_type => colref

                            base_expr => ,

                            sub_tree => 

                        4 =>

                            expr_type => const

                            base_expr => '-'

                            sub_tree => 

                16 =>

                    expr_type => operator

                    base_expr => end

                    sub_tree => 

        3 =>

            expr_type => expression

            alias => `nom_com`

            base_expr => CONCAT( usr_nombres, ' ', usr_apellidos )

            sub_tree =>

                0 =>

                    expr_type => function

                    base_expr => CONCAT

                    sub_tree => 

                1 =>

                    expr_type => expression

                    base_expr => ( usr_nombres, ' ', usr_apellidos )

                    sub_tree =>

                        0 =>

                            expr_type => colref

                            base_expr => usr_nombres

                            sub_tree => 

                        1 =>

                            expr_type => colref

                            base_expr => ,

                            sub_tree => 

                        2 =>

                            expr_type => const

                            base_expr => ' '

                            sub_tree => 

                        3 =>

                            expr_type => colref

                            base_expr => ,

                            sub_tree => 

                        4 =>

                            expr_type => colref

                            base_expr => usr_apellidos

                            sub_tree => 

        4 =>

            expr_type => colref

            alias => `cod_local`

            base_expr => cod_local

            sub_tree => 

    FROM =>

        0 =>

            table => usuarios

            alias => usuarios

            join_type => JOIN

            ref_type =>

            ref_clause =>

            base_expr =>

            sub_tree => 

    WHERE =>

        0 =>

            expr_type => colref

            base_expr => usr_estado

            sub_tree => 

        1 =>

            expr_type => operator

            base_expr => <>

            sub_tree => 

        2 =>

            expr_type => const

            base_expr => 2

            sub_tree => 

    ORDER =>

        0 =>

            type => pos

            base_expr => 3

            direction => ASC

        1 =>

            type => pos

            base_expr => 1

            direction => ASC

        2 =>

            type => pos

            base_expr => 4

            direction => ASC

That is correct however if you input the following MySQL query instead ( 
changes in red )

select usr_id, usr_login, case id_tipousuario when 1 then 'Usuario CVE' when 2 
then concat('Usuario Vendedor -', codigovendedor, '-') when 3 then 
concat('Usuario Vendedor Meson (', codigovendedor, ')') end tipousuario, 
CONCAT( usr_nombres, ' ', usr_apellidos ) as nom_com, cod_local from usuarios 
where usr_estado <> 2 order by 3, 1, 4

You get via print_r ( errors on red section at the bottom ) :

SELECT => 
0 => 
expr_type => colref 
alias => `usr_id` 
base_expr => usr_id 
sub_tree => 
1 => 
expr_type => colref 
alias => `usr_login` 
base_expr => usr_login 
sub_tree => 
2 => 
expr_type => expression 
alias => `case id_tipousuario when 1 then 'Usuario CVE' when 2 then 
concat('Usuario Vendedor -', codigovendedor, '-') when 3 then concat('Usuario 
Vendedor Meson (', codigovendedor, ')')end tipousuario, CONCAT( usr_nombres, ' 
'` 
base_expr => case id_tipousuario when 1 then 'Usuario CVE' when 2 then 
concat('Usuario Vendedor -', codigovendedor, '-') when 3 then concat('Usuario 
Vendedor Meson (', codigovendedor, ')')end tipousuario, CONCAT( usr_nombres, ' 
' 
sub_tree => 
0 => 
expr_type => operator 
base_expr => case 
sub_tree => 
1 => 
expr_type => colref 
base_expr => id_tipousuario 
sub_tree => 
2 => 
expr_type => operator 
base_expr => when 
sub_tree => 
3 => 
expr_type => const 
base_expr => 1 
sub_tree => 
4 => 
expr_type => reserved 
base_expr => THEN 
sub_tree => 
5 => 
expr_type => const 
base_expr => 'Usuario CVE' 
sub_tree => 
6 => 
expr_type => operator 
base_expr => when 
sub_tree => 
7 => 
expr_type => const 
base_expr => 2 
sub_tree => 
8 => 
expr_type => reserved 
base_expr => THEN 
sub_tree => 
9 => 
expr_type => function 
base_expr => CONCAT 
sub_tree => 
10 => 
expr_type => expression 
base_expr => ('Usuario Vendedor -', codigovendedor, '-') 
sub_tree => 
0 => 
expr_type => const 
base_expr => 'Usuario Vendedor -' 
sub_tree => 
1 => 
expr_type => colref 
base_expr => , 
sub_tree => 
2 => 
expr_type => colref 
base_expr => codigovendedor 
sub_tree => 
3 => 
expr_type => colref 
base_expr => , 
sub_tree => 
4 => 
expr_type => const 
base_expr => '-' 
sub_tree => 
11 => 
expr_type => operator 
base_expr => when 
sub_tree => 
12 => 
expr_type => const 
base_expr => 3 
sub_tree => 
13 => 
expr_type => reserved 
base_expr => THEN 
sub_tree => 
14 => 
expr_type => function 
base_expr => CONCAT 
sub_tree => 
15 => 
expr_type => expression 
base_expr => ('Usuario Vendedor Meson (', codigovendedor, ')') 
sub_tree => 
0 => 
expr_type => const 
base_expr => 'Usuario Vendedor Meson (' 
sub_tree => 
1 => 
expr_type => colref 
base_expr => , 
sub_tree => 
2 => 
expr_type => colref 
base_expr => codigovendedor 
sub_tree => 
3 => 
expr_type => colref 
base_expr => , 
sub_tree => 
4 => 
expr_type => const 
base_expr => ')' 
sub_tree => 
16 => 
expr_type => colref 
base_expr => end tipousuario, CONCAT( usr_nombres, ' 
sub_tree => 
17 => 
expr_type => const 
base_expr => ' 
sub_tree => 
3 => 
expr_type => colref 
alias => `nom_com` 
base_expr => usr_apellidos 
sub_tree => 
4 => 
expr_type => colref 
alias => `cod_local` 
base_expr => cod_local 
sub_tree => 
FROM => 
0 => 
table => usuarios 
alias => usuarios 
join_type => JOIN 
ref_type => 
ref_clause => 
base_expr => 
sub_tree => 
WHERE => 
0 => 
expr_type => colref 
base_expr => usr_estado 
sub_tree => 
1 => 
expr_type => operator 
base_expr => <> 
sub_tree => 
2 => 
expr_type => const 
base_expr => 2 
sub_tree => 
ORDER => 
0 => 
type => pos 
base_expr => 3 
direction => ASC 
1 => 
type => pos 
base_expr => 1 
direction => ASC 
2 => 
type => pos 
base_expr => 4 
direction => ASC 
parse time simplest query:0.0099928379058838

RED IS :

expr_type => expression 
base_expr => ('Usuario Vendedor Meson (', codigovendedor, ')') 
sub_tree => 
0 => 
expr_type => const 
base_expr => 'Usuario Vendedor Meson (' 
sub_tree => 
1 => 
expr_type => colref 
base_expr => , 
sub_tree => 
2 => 
expr_type => colref 
base_expr => codigovendedor 
sub_tree => 
3 => 
expr_type => colref 
base_expr => , 
sub_tree => 
4 => 
expr_type => const 
base_expr => ')' 
sub_tree => 
16 => 
expr_type => colref 
base_expr => end tipousuario, CONCAT( usr_nombres, ' 
sub_tree => 
17 => 
expr_type => const 
base_expr => ' 
sub_tree => 
3 => 
expr_type => colref 
alias => `nom_com` 
base_expr => usr_apellidos 
sub_tree => 
4 => 
expr_type => colref 
alias => `cod_local` 
base_expr => cod_local 
sub_tree => 

What version of the product are you using? On what operating system?

svn trunk head revision windows and linux

Please provide any additional information below.

Original issue reported on code.google.com by maximili...@cencosud.com.ar on 23 Nov 2011 at 5:23

GoogleCodeExporter commented 8 years ago 
It seems to be a problem of the regex within split_sql(). I'll see, what I can 
do.

Original comment by pho...@gmx.de on 16 Feb 2012 at 9:18

GoogleCodeExporter commented 8 years ago 
I think, I have solved the problem.
Checkout the bleeding edge on 
https://www.phosco.info/publicsvn/php-sql-parser/trunk/

Original comment by pho...@gmx.de on 17 Feb 2012 at 1:01

GoogleCodeExporter commented 8 years ago 
Accepted code contribution.

Original comment by greenlion@gmail.com on 12 Mar 2012 at 10:03