greenlion/php-sql-parser

A pure PHP SQL (non validating) parser w/ focus on MySQL dialect of SQL

php-sql-parser's Issues

Select parser problem with quote

From [email protected] on January 13, 2011 13:40:03

What steps will reproduce the problem? 1. use the parser to parse a query like Select table.field from table 2. 3. What is the expected output? What do you see instead? if you look at the 'Select' statement generated you will get

Array
(
[0] => Array
(
[expr_type] => colref
[alias] => prj_seq_key
[base_expr] => aje.
[sub_tree] =>
)
)

where you sould get

Array
(
[0] => Array
(
[expr_type] => colref
[alias] => aje.prj_seq_key
[base_expr] => aje.prj_seq_key`
[sub_tree] =>
) What version of the product are you using? On what operating system? Please provide any additional information below.

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=4

Does not support CREATE TABLE

Looking for a solution that supports CREATE TABLE statements

fake aliases for table names

From [email protected] on January 20, 2012 09:12:19

Hi,

I try to use your parser within a small project. I need the table names and their aliases within a SELECT statement to check, which columns are used within the ORDER BY clause. The first test produces a problem:

SELECT * FROM answers ORDER BY qid

The parser generates an alias ANSWERS, but the table doesn't have an alias. So it is not possible to find column names within the ORDER clause with the alias (the alias can or cannot be a valid alias).

Please remove the alias from the resulting array, if the table doesn't have an alias within the SQL statement.

Thanks a lot
Andre

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=18

Parser fails when it finds a newline after SELECT statement

From [email protected] on December 14, 2010 17:06:53

If I try :

$parser = new PHPSQLParser('SELECT
name, 1, test;');

PHP is giving me error (see method process_select_expr):

• Warning: E_WARNING array_pop() expects parameter 1 to be array, string given
• Fatal error: Cannot use string offset as an array

A newline after between SELECT and field list makes the parser to fail.

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=1

alias of a subquery

From [email protected] on January 23, 2012 15:49:36

If you have a subquery which has an alias, the alias is part of the "base_expr". It is complicated to parse it (there can be additional aliases within the subquery or the alias is very short like "s", which can be part of the subquery string too). So it would be nice to remove the alias from the "base_expr" like in the "table" part.

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=22

Feature request: parse values in VALUES (...)

From [email protected] on December 01, 2011 16:39:44

What steps will reproduce the problem? INSERT INTO test (test2,test3) VALUES ('10','coucou')

What is the expected output?
...
["VALUES"]=>
array(2) {
[0]=>
string(4) "'10'"
[1]=>
string(8) "'coucou'"
}
...

What do you see instead?
...
["VALUES"]=>
array(1) {
[0]=>
string(15) "('10','coucou')"
}
... What version of the product are you using? On what operating system? trunk Please provide any additional information below.

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=16

Creater would create wrong SQL when use t multil column index.

for example: index (c1, c2) will get index (c1 c2), lost a comma.

Usage of DEC as reserved keyword in MySQL

Hi Justin,

in the user guide on http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-7.html I have found the DEC as reserved keyword. Do you know a statement, which uses it? I have an open issue 150 on GoogleCode (http://code.google.com/p/php-sql-parser/issues/detail?id=150), where I have to differ between the reserved keyword and a column name.

Thanks
André

PHPUnit

I see that composer.json pulls in phpunit, but I don't see the tests utilizing PHPUnit functionality.

Would you consider switching over to PHPUnit for the unit tests? This would provide a great framework for code coverage among other things.

Undefined offset error

From [email protected] on January 02, 2011 03:50:12

What steps will reproduce the problem? 1. Try an parse the following query:
2. UPDATE table1 SET field1='foo' WHERE field2='bar' AND id=(SELECT if FROM test1 t where t.field1=(SELECT id from test2 t2 where t2.field = 'foo'))

Granted, its my own typo ("if" should be "id" in the first subselect) but I dont think php-sql-parser should be throwing an undefined offset exception. What is the expected output? What do you see instead? Expected: Blank screen.
See instead: Notice: Undefined offset: 1 in /var/www/php-sql-parser.php on line 1102

Can be fixed by changing line 1102 of php-sql-parser.php from:
$sub_expr = $tokens[$key+1];
to:
if(!empty($tokens[$key+1])) $sub_expr = $tokens[$key+1];

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=2

Negative integers in IN() get broken

Hello,
we have a query like this: SELECT * FROM table WHERE id IN(0,-1,-2,-3). The SQLParser does not handle this properly, creating the result again with SQLCreator breaks the query: SELECT * FROM table WHERE id IN(0,-,1,-,2,-,3). As far as I could investigate, reason is the "-" splitter in LexerSplitter.php.

if(count($processed) == 1)

From [email protected] on March 02, 2011 14:28:49

if(count($processed) == 1) {
...
}

produces errors on some sql-statments.
i added:

if(is_array($processed)) {
if(count($processed) == 1) {
...
}
}

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=6

missing ref_clause in joins / wrong interpretation

From [email protected] on April 29, 2011 23:19:26

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: http://code.google.com/p/php-sql-parser/issues/detail?id=8

WHERE clause with strings with parentheses is parsed incorrectly

From [email protected] on February 29, 2012 10:58:00

1. Provide WHERE clause with string constants with inner parentheses: "RIGHT(REPLACE(foo.bar,'(0',''),7) = 'a'"
2. Parsing result is incorrect

Expected:
Array
(
[SELECT] => Array
(
[0] => Array
(
[expr_type] => colref
[alias] => dummy
[base_expr] => dummy
[sub_tree] =>
)

    )

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

    )

[WHERE] => Array
    (
        [0] => Array
            (
                [expr_type] => function
                [base_expr] => RIGHT
                [sub_tree] =>
            )

        [1] => Array
            (
                [expr_type] => expression
                [base_expr] => (REPLACE(foo.bar,'(0',''),7)
                [sub_tree] => Array
                    (
                        [0] => Array
                            (
                                [expr_type] => function
                                [base_expr] => REPLACE
                                [sub_tree] =>
                            )

                        [1] => Array
                            (
                                [expr_type] => expression
                                [base_expr] => (foo.bar,'(0','')
                                [sub_tree] => Array
                                    (
                                        [0] => Array
                                            (
                                                [expr_type] => colref
                                                [base_expr] => foo.bar
                                                [sub_tree] =>
                                            )

                                        [1] => Array
                                            (
                                                [expr_type] => colref
                                                [base_expr] => ,
                                                [sub_tree] =>
                                            )

                                        [2] => Array
                                            (
                                                [expr_type] => const
                                                [base_expr] => '(0'
                                                [sub_tree] =>
                                            )

                                        [3] => Array
                                            (
                                                [expr_type] => colref
                                                [base_expr] => ,
                                                [sub_tree] =>
                                            )

                                        [4] => Array
                                            (
                                                [expr_type] => const
                                                [base_expr] => ''
                                                [sub_tree] =>
                                            )

                                    )

                            )

                        [2] => Array
                            (
                                [expr_type] => colref
                                [base_expr] => ,
                                [sub_tree] =>
                            )

                        [3] => Array
                            (
                                [expr_type] => const
                                [base_expr] => 7
                                [sub_tree] =>
                            )

                    )

            )

        [2] => Array
            (
                [expr_type] => operator
                [base_expr] => =
                [sub_tree] =>
            )
        [3] => Array
            (
                [expr_type] => const
                [base_expr] => 'a'
                [sub_tree] =>
            )

    )

)

Actual:
Array
(
[SELECT] => Array
(
[0] => Array
(
[expr_type] => colref
[alias] => dummy
[base_expr] => dummy
[sub_tree] =>
)

    )

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

    )

[WHERE] => Array
    (
        [0] => Array
            (
                [expr_type] => function
                [base_expr] => RIGHT
                [sub_tree] => 
            )

        [1] => Array
            (
                [expr_type] => colref
                [base_expr] => (REPLACE(foo.bar,'(0',''),7) = 'a'
                [sub_tree] => 
            )

    )

)

Fix for this issue: change count_paren method:
from
private function count_paren($token,$chars=array('(',')')) {
$len = strlen($token);
$open=array();
$close=array();
for($i=0;$i<$len;++$i){
if($token[$i] == $chars[0]) {
$open[] = $i;
} elseif($token[$i] == $chars[1]) {
$close[] = $i;
}

        }
        return array('open' => $open, 'close' => $close, 'balanced' =>( count($close) - count($open)));
    }

to
private function count_paren($token,$chars=array('(',')')) {
$len = strlen($token);
$open=array();
$close=array();

        $quotes = '';

        for($i=0;$i<$len;++$i){
            if (in_array($token[$i], array('"', "'"))) {
                if ($quotes == $token[$i]) {
                    $quotes = '';
                } elseif (!strlen($quotes)) {
                    $quotes = $token[$i];
                }
            } elseif (!strlen($quotes)) {
                if($token[$i] == $chars[0]) {
                    $open[...

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=30

select * is treated as an operator

From [email protected] on October 20, 2011 06:53:58

What steps will reproduce the problem? 1. Try to parse sql like "SELECT * FROM TableName". What is the expected output? What do you see instead? It should treat * as a reference to all columns without an alias. Instead, it comes out as * is an "operator" with an alias of *. What version of the product are you using? On what operating system? Downloaded as of 10-20-2011. Please provide any additional information below.

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=14

+v

From [email protected] on February 17, 2012 17:25:46

What steps will reproduce the problem? 1. 2. 3. What is the expected output? What do you see instead? What version of the product are you using? On what operating system? Please provide any additional information below.

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=27

INSERT issue in VALUES clause

[this bug was invalid]

in-list conatins too many commas

From [email protected] on February 22, 2012 16:13:45

SELECT q.qid, question, gid FROM questions as q WHERE (select count(*) from answers as a where a.qid=q.qid and scale_id=0)=0 and sid=11929 AND type IN ('F', 'H', 'W', 'Z', '1') and q.parent_qid=0

goes wrong, the in-list contains commas, so we will get from the creator:

SELECT q.qid,question,gid FROM questions q WHERE (SELECT count(*) FROM answers a WHERE a.qid = q.qid and scale_id = 0) = 0 and sid = 11929 AND type IN ('F',, ,'H',, ,'W',, ,'Z',, ,'1') and q.parent_qid = 0

Version: https://www.phosco.info/publicsvn/php-sql-parser/tags/20120217

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=29

Make reserved works non-static

@witchi - In Shard-Query I've made the parser list of reserved words into a regular array, instead of a static array. The reason for this is that Shard-Query has custom aggregate functions, and they have to be dynamically added to the list of reserved words.

Do you have any objection with me making the list of reserved words non-static? I understand it likely will slow the creation of the parser object by a small amount.

Positions are wrong

From [email protected] on February 07, 2012 09:21:07

The third test case within t/positions.php goes wrong, because of the regular expression within lookForBaseExpression(). It awaits a delimiter between the parts of the statement. The part u.uid=a.owner_id has no delimiter between colref, operator and colref. What version of the product are you using? On what operating system? Version on http://www.phosco.info/php-sql-parser_current.zip

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=24

Create does not support column COMMENT

it would throw Exception : unknown [expr_type] = comment in "CREATE TABLE column-type subtree"

Some table names are keywords.

From [email protected] on March 02, 2012 07:49:13

What steps will reproduce the problem? 1. Use a query that uses "cache" as the table name.
2. Other keywords can cause the same issue. What is the expected output? What do you see instead? The parse array uses "CACHE" keyword and does not parse the correct table part of the query. Completely changes the parse tree. What version of the product are you using? On what operating system? PHP 5.3 on MAMP 2.0 install.

Is this something that can be fixed?

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=34

Parentesis Urgent Fix

From [email protected] on November 23, 2011 18:23:40

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

...

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=15

Eclipse PDT

Does anyone use Eclipse PDT as IDE? I have problems to configure the autoload feature there. At the moment I cannot change the parser code, because without the require_once statements the class dependencies are broken.

Parser does not produce UPDATE section if table name is reserved word

From [email protected] on March 01, 2012 23:29:10

What steps will reproduce the problem? Parse the following query:
UPDATE user SET lastlogin = 7, x = 3; What is the expected output? What do you see instead? I expected the 'UPDATE' section to be present in the parsed member.
The 'UPDATE' section is wholly missing, only the 'SET' section exists.
Changing table name to 'users' works correctly, so it seems to be an issue with the table name being a reserved word. The query runs fine on MySQL 5.x What version of the product are you using? On what operating system? Downloaded 2/29/2012. PHP 5.3.5 on 64-bit ubuntu Linux. Please provide any additional information below.

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=32

Escaped values in $parser->parsed['VALUES'] become '' instead of \'

From [email protected] on March 05, 2012 11:32:06

What steps will reproduce the problem? <?php

// require("php-sql-parser.php");
require("PHPSQLParser.php");

$sql = "INSERT INTO test (name) VALUES ('ben's test containing an escaped quote')";

$parser = new PHPSQLParser($sql);

echo "

";

print_r( $parser->parsed );

echo "

"; What is the expected output? What do you see instead? EXPECTED:
[VALUES] => Array
(
[0] => ('ben's test containing an escaped quote')
)

OBSERVED:
[VALUES] => Array
(
[0] => ('ben''s test containing an escaped quote')
) What version of the product are you using? On what operating system? Both of the following have the issue: http://www.phosco.info/php-sql-parser_current.zip http://php-sql-parser.googlecode.com/svn/trunk/php-sql-parser.php

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=35

How difficult would it be to add window functions?

They take syntax:
function(expr) OVER (PARTITION BY alias ORDER BY alias)

like:
select some_col, sum(col2) OVER () from some_table;
select some_col, sum(col2) OVER (ORDER BY some_col) from some_table;
select some_col as s, rank() as r OVER (PARTITION BY s) from some_table;

There are some function names, like rank() which would need to be added too.

ASC statements

From [email protected] on February 20, 2012 09:18:32

There is a problem with explicit ASC statements within ORDER-BY. It is not filtered from the base_expr. So the creator class generates it twice.

Version: https://www.phosco.info/publicsvn/php-sql-parser/tags/20120217

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=28

Option Update error

From [email protected] on December 07, 2011 14:51:01

What steps will reproduce the problem? 1. Execute SELECT pl_namespace,pl_title FROM pagelinks WHERE pl_from = '1' FOR UPDATE What is the expected output? What do you see instead? I saw:
[WHERE] => Array
(
[0] => Array
(
[expr_type] => colref
[base_expr] => pl_from
[sub_tree] =>
)

        [1] => Array
            (
                [expr_type] => operator
                [base_expr] => =
                [sub_tree] => 
            )

        [2] => Array
            (
                [expr_type] => const
                [base_expr] => '1'
                [sub_tree] => 
            )

        [3] => Array
            (
                [expr_type] => reserved
                [base_expr] => UPDATE
                [sub_tree] => 
            )
    )

But i was expecting:
[WHERE] => Array
(
[0] => Array
(
[expr_type] => colref
[base_expr] => pl_from
[sub_tree] =>
)

        [1] => Array
            (
                [expr_type] => operator
                [base_expr] => =
                [sub_tree] => 
            )

        [2] => Array
            (
                [expr_type] => const
                [base_expr] => '1'
                [sub_tree] => 
            )
    ) What version of the product are you using? On what operating system? I am running Ubuntu 11.10. And using the version in the site in the Dec 7 2010 Please provide any additional information below.

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=17

NOW() function makes parsing to fail

From [email protected] on January 05, 2011 22:40:03

SELECT NOW();

or

SELECT test1
FROM table1
WHERE test_date < NOW();

I get a blank page showing this error :

Fatal error: Cannot use string offset as an array in H:\Project\Openflyers30\classes\PHPSQLParser.php on line 1145

For any SQL queries where I've "NOW()", the parser always fails.

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=3

Proper use of PHP namespaces and PSR-0/PSR-4 through Composer

I guess i could split this up into two. But since composer.json is there why not start using it's auto-loader? This would simplify the code quite a bit.

This would cleanup the namespace stuff a bit and no more "require"/"require_once" lines.

P.S. I could whip up a Pull Request with the changes for your review.

Small bug at line 965 (missing '$this->')

From [email protected] on February 07, 2012 01:02:11

What steps will reproduce the problem? 1. subquery within the from clause What is the expected output? What do you see instead? Will not parse, instead throws error at line 965. It's missing '$this->' before 'removeParenthesisFromStart($token)' What version of the product are you using? On what operating system? Latest Version ( issue #22 ) from http://www.phosco.info/php-sql-parser_current.zip

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=23

Version number

In the past I used the current date as version number. I would change this to a more semantic version (http://semver.org/). Which version I should use as the first one? Maybe 2.0.0? Do you prefer a specific number?

Best regards
André

Support nested join operations in the FROM clause.

From [email protected] on April 29, 2011 23:22:10

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: http://code.google.com/p/php-sql-parser/issues/detail?id=9

"WHERE id>1" not parsed correctly

From [email protected] on April 04, 2011 02:52:01

What steps will reproduce the problem? 1. parse a query like "SELECT * FROM test WHERE id>1" What is the expected output? What do you see instead? Expected output is:

[WHERE] => Array (
[0] => Array ( [expr_type] => colref [base_expr] => id [sub_tree] => )
[1] => Array ( [expr_type] => operator [base_expr] => > [sub_tree] => )
[2] => Array ( [expr_type] => const [base_expr] => 1 [sub_tree] => )
)

Instead we get:

[WHERE] => Array (
[0] => Array ( [expr_type] => colref [base_expr] => id [sub_tree] => )
[1] => Array ( [expr_type] => colref [base_expr] => >1 [sub_tree] => )
) What version of the product are you using? On what operating system? I tested this with revision 36 on linux. Works ok with "WHERE id > 1"

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=7

Support for parsing the CREATE TABLE SQL statement

From [email protected] on March 02, 2012 07:45:12

I would like to see the parser support the CREATE TABLE statement.

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=33

1. please turn off debug print_r() 2. probbaly, some bug in parser triggers it

From [email protected] on June 19, 2011 15:31:59

WHAT STEPS WILL REPRODUCE THE PROBLEM?

1. $sql = "SELECT SQL_CALC_FOUND_ROWS SmTable.*, MATCH (SmTable.fulltextsearch_keyword) AGAINST ('google googles') AS keyword_score FROM SmTable WHERE SmTable.status = 'A' AND (SmTable.country_id = 1 AND SmTable.state_id = 10) AND MATCH (SmTable.fulltextsearch_keyword) AGAINST ('google googles') ORDER BY SmTable.level DESC, keyword_score DESC LIMIT 0,10"
2. $parser = new PHPSQLParser($sql);

WHAT IS THE EXPECTED OUTPUT? WHAT DO YOU SEE INSTEAD?

No output expected.

Instead, it hits line 1242 and prints smth:
if(!is_array($processed)) {
print_r($processed); // 1242
$processed = false;
} What version of the product are you using? On what operating system? http://php-sql-parser.googlecode.com/svn/trunk/php-sql-parser.php uname -a
Linux ********* #1 SMP Tue Sep 1 10:25:30 EDT 2009 x86_64 GNU/Linux

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=12

Infinite loop when table alias is empty

Hi,
the following invalid SQL leads to an infinite loop:

$sql = 'select column from table as ';
$parser = new PHPSQLParser\PHPSQLParser($sql, false);

Best regards,
Johannes

Parser breaks on nexted subqueries ending in ))

From [email protected] on February 15, 2012 02:13:50

If I try to parse a complex query like this:

SELECT * FROM contacts WHERE contacts.id IN (SELECT email_addr_bean_rel.bean_id FROM email_addr_bean_rel, email_addresses WHERE email_addresses.id = email_addr_bean_rel.email_address_id AND email_addr_bean_rel.deleted = 0 AND email_addr_bean_rel.bean_module = 'Contacts' AND email_addresses.email_address IN ("[email protected]"))

then the query parser does not parse the internal subquery properly. The reason for that is that trim functions which are used on encompassing subquery do trim($sql, " ()") and thus when trimming they remove both enclosing parentheses for the subquery and the ones that belong to inner IN, which they should not be doing. I would propose to use something like this:

private function trimSubquery($sq)
{
$sq = trim($sq);
if(empty($sq)) return '';
while($sq[0] == '(' && substr($sq, -1) == ')') {
$sq = substr($sq, 1, -1);
$sq = trim($sq);
}
return $sq;
}

to keep trims balanced and use this functions instead of regular trim with ' ()'.

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=25

[REQUEST] Start and stop positions of the statement components within the SQL string

From [email protected] on January 20, 2012 09:23:54

Is it possible to return the positions of the statement components within the original SQL string? I have to replace some column names, but I don't know, where the column name is used within the statement. A simple string replace doesn't work in all cases.

Thanks
Andre

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=19

Parsing issue on INSERT query (missing space between "VALUES" and opening parenthesis

From [email protected] on May 09, 2011 15:57:22

Well, this query is well parsed :

$parser = new PHPSQLParser();
$query = "INSERT INTO test (test2,test3) VALUES ('10','coucou')";
$result = $parser->parse($query);

But for this one :

$parser = new PHPSQLParser();
$query = "INSERT INTO test (test2,test3) VALUES('10','coucou')";
$result = $parser->parse($query);

There is no data "RESULT" into the array. The part "('10','coucou')" is not parsed so I cannot know which values were affected to columns of table test.

That's just because there is not a space between "VALUES" and "(".

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=10

unparsed ref_clause on join

From [email protected] on January 20, 2012 14:00:05

SELECT * FROM SURVEYS inner join SURVEYS_LANGUAGESETTINGS on (surveyls_survey_id=sid and surveyls_language=language) WHERE sid=74191

returns in the FROM clause:

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

        [1] => Array
            (
                [table] => SURVEYS_LANGUAGESETTINGS
                [alias] => SURVEYS_LANGUAGESETTINGS
                [join_type] => JOIN
                [ref_type] => ON
                [ref_clause] => surveyls_survey_id=sid and surveyls_language=language
                [base_expr] => 
                [sub_tree] => 
            )

    )

Can you parse the ref_clause, that I will have colrefs?

Thanks a lot
Andre

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=20

Update packaging to point here for sources and releases

Would be nice to point packagist here for sources and releases:
https://packagist.org/packages/greenlion/php-sql-parser

Composer handles github.com using API and is able to download the zip files for releases as well.

<> in split_sql() wont split

From [email protected] on March 02, 2011 14:20:02

What steps will reproduce the problem? select * from test where value<>"brainstorm"

Suggestion
i removed the "|([^ ,]+)" from the preg_split(),
fixed this problem for me, but don't know if it is breaking somthing.

What is the expected output?
[WHERE] => Array
(
[0] => Array
(
[expr_type] => colref
[base_expr] => value
[sub_tree] =>
)

        [1] => Array
            (
                [expr_type] => operator
                [base_expr] => \<>
                [sub_tree] => 
            )

        [2] => Array
            (
                [expr_type] => const
                [base_expr] => "brainstorm"
                [sub_tree] => 
            )

    )

What do you see instead?
[WHERE] => Array
(
[0] => Array
(
[expr_type] => colref
[base_expr] => value
[sub_tree] =>
)

        [1] => Array
            (
                [expr_type] => colref
                [base_expr] => \<>"brainstorm"
                [sub_tree] => 
            )

    )

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=5

Parser produces errors on query parse

From [email protected] on March 01, 2012 23:04:36

What steps will reproduce the problem? Parse the following query:
SELECT sp.level,
CASE sp.level
WHEN 'bronze' THEN 0
WHEN 'silver' THEN 1
WHEN 'gold' THEN 2
ELSE -1
END AS levelnum,
sp.alt_en,
sp.alt_pl,
DATE_FORMAT(sp.vu_start,'%Y-%m-%d %T') AS vu_start,
DATE_FORMAT(sp.vu_stop,'%Y-%m-%d %T') AS vu_stop,
ABS(TO_DAYS(now()) - TO_DAYS(sp.vu_start)) AS frdays,
ABS(TO_DAYS(now()) - TO_DAYS(sp.vu_stop)) AS todays,
IF(ISNULL(TO_DAYS(sp.vu_start)) OR ISNULL(TO_DAYS(sp.vu_stop))
, 1
, IF(TO_DAYS(now()) < TO_DAYS(sp.vu_start)
, TO_DAYS(now()) - TO_DAYS(sp.vu_start)
, IF(TO_DAYS(now()) > TO_DAYS(sp.vu_stop)
, TO_DAYS(now()) - TO_DAYS(sp.vu_stop)
, 0))) AS status,
st.id,
SUM(IF(st.type='view',1,0)) AS view,
SUM(IF(st.type='click',1,0)) AS click
FROM stats AS st,
sponsor AS sp
WHERE st.id=sp.id
GROUP BY st.id
ORDER BY sp.alt_en asc, sp.alt_pl asc What is the expected output? What do you see instead? Query should parse... (it runs on MySQL 5.x)
I get this:
Notice: Uninitialized string offset: 0 in /media/sf_swjozef2/vendor/sqlparser/php-sql-parser.php on line 677 Notice: Undefined variable: processed in /media/sf_swjozef2/vendor/sqlparser/php-sql-parser.php on line 1241 Notice: Undefined variable: processed in /media/sf_swjozef2/vendor/sqlparser/php-sql-parser.php on line 1243 Notice: Undefined variable: expr_type in /media/sf_swjozef2/vendor/sqlparser/php-sql-parser.php on line 1247 Warning: array_pop() expects parameter 1 to be array, string given in /media/sf_swjozef2/vendor/sqlparser/php-sql-parser.php on line 717 Notice: Uninitialized string offset: 0 in /media/sf_swjozef2/vendor/sqlparser/php-sql-parser.php on line 744 Notice: Undefined variable: processed in /media/sf_swjozef2/vendor/sqlparser/php-sql-parser.php on line 1241 Notice: Undefined variable: processed in /media/sf_swjozef2/vendor/sqlparser/php-sql-parser.php on line 1243 Notice: Undefined variable: expr_type in /media/sf_swjozef2/vendor/sqlparser/php-sql-parser.php on line 1247 Notice: Uninitialized string offset: 0 in /media/sf_swjozef2/vendor/sqlparser/php-sql-parser.php on line 762 Fatal error: Cannot use string offset as an array in /media/sf_swjozef2/vendor/sqlparser/php-sql-parser.php on line 762 What version of the product are you using? On what operating system? Downloaded on 2/29/2012, running on ubuntu Linux 64-bit, PHP 5.3.5 Please provide any additional information below.

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=31

Repatriate issues from Google Code to GitHub

Hello,

Some open issues are still on the Google Code page. Because Google Code will soon be turned off, it may be interesting to repatriate them on GitHub.

Here they are:

https://code.google.com/p/php-sql-parser/issues/list?can=2

Also, at least 2 of them seem to contain fixes:

https://code.google.com/p/php-sql-parser/issues/detail?id=155
https://code.google.com/p/php-sql-parser/issues/detail?id=154

Regards,

Tristan

functions within INSERT

From [email protected] on February 16, 2012 10:08:05

The parser doesn't recognize functions within INSERT statements. You will get the function name as column reference and the function parameters as expression.

Used version: https://www.phosco.info/publicsvn/php-sql-parser/tags/20120215

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=26

Tab character causes a wrong parsing.

From [email protected] on January 23, 2012 12:07:44

Here an example query :
SELECT SUM( 10 ) FROM account

There is a tab character before the SUM function. I've a class which use php-sql-parser to perform internal extra validation.

The example query causes my parser to fail.

But with this query where I've replaced tab character by a space :
SELECT SUM( 10 ) FROM account
My parser doesn't fail.

Parser result with tab character :
[SELECT] => Array
(
[0] => Array
(
[expr_type] => expression
[alias] => SUM( 10 as test
[base_expr] => SUM( 10 as test
[sub_tree] => Array
(
[0] => Array
(
[expr_type] => aggregate_function
[base_expr] => SUM
[sub_tree] =>
)

                        [1] => Array
                            (
                                [expr_type] => colref
                                [base_expr] => ( 10   as test
                                [sub_tree] => 
                            )

                    )

            )

    )

Parser result with space character :
[SELECT] => Array
(
[0] => Array
(
[expr_type] => expression
[alias] => test
[base_expr] => SUM( 10 )
[sub_tree] => Array
(
[0] => Array
(
[expr_type] => aggregate_function
[base_expr] => SUM
[sub_tree] =>
)

                        [1] => Array
                            (
                                [expr_type] => const
                                [base_expr] => ( 10 )
                                [sub_tree] => 
                            )

                    )

            )

    )

Should this bug be fixed onto php-sql-parser or my own class ?

The fix would be to do a : str_replace("\t", ' ', $query);

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=21

[Request] Compile back result array to sql statement

From [email protected] on August 26, 2011 06:31:34

After some modification on the array.

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=13

Very long query cannot be parsed

From [email protected] on May 20, 2011 19:33:46

Test code :

$test = str_repeat('0', 18000);
$query = "UPDATE club SET logo='$test' WHERE id=1";

$parser = new PHPSQLParser();
$result = $parser->parse($query);

Result : query cannot be parsed and cause the current PHP process to crash/timeout.

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=11