Parser throws uninitialized offsets warning when "REPLACE" MySQL function used in SELECT clause

[witchi]

From [email protected] on December 22, 2014 16:20:09

Hello,

Using the replace function in MySQL (the str_replace of MySQL) in a SELECT clause will cause the parser to throw PHP notices & warnings.

Example: Query is : SELECT test_ID, test_name, REPLACE( test_name , 'aa', 'bb') AS 'test_name_modified' FROM test
ORDER BY test_name ASC;

Notice: Undefined index: alias in PHP-SQL-Parser\src\processors\OrderByProcessor.php on line 69 Notice: Undefined index: INTO in PHP-SQL-Parser\src\processors\InsertProcessor.php on line 50 Warning: Invalid argument supplied for foreach() in PHP-SQL-Parser\src\processors\InsertProcessor.php on line 51 Notice: Uninitialized string offset: 0 in PHP-SQL-Parser\src\processors\AbstractProcessor.php on line 66
Notice: Uninitialized string offset: 0 in PHP-SQL-Parser\src\processors\AbstractProcessor.php on line 71 Notice: Uninitialized string offset: 0 in PHP-SQL-Parser\src\processors\AbstractProcessor.php on line 76

I am using php-sql-parser version SVN: $Id: PHPSQLParser.php 757 2013-12-16 09:54:05Z [email protected] $ with PHP 5.4.6

Is there a way around this? It "works" but I like to have a clean output, without warnings & notices.

Thank you!

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

[tbjornli]

I'm experience the exact same issue with the following SQL statement

SELECT accountNo FROM bankAccount
WHERE REPLACE(REPLACE(accountNo, '.', ''), ' ', '') = REPLACE(REPLACE('1234 56 78910', '.', ''), ' ', '')

Looking forward to a fix for this.

[smgallo]

The REPLACE() function is interpreted as a REPLACE DML in the parser. Parsing of the SQL SELECT actually stops when it sees the REPLACE() function and any other columns in the SELECT clause are skipped and a [REPLACE] key is added to the parser output. For example:

REPLACE(req.project_title, '\n', ' ') AS project_title,

will generate:

[REPLACE] => Array ( [0] => Array ( [expr_type] => table [table] => [no_quotes] => Array ( [delim] => . [parts] => Array ( ) ) [alias] => [base_expr] => ) )

If the parsed object gets passed to PHPSQLCreator it will fail:

PHP Notice: Undefined index: expr_type in /data/www/smgallo/vendor/greenlion/php-sql-parser/src/PHPSQLParser/builders/ColumnReferenceBuilder.php on line 66

[smgallo]

I've made a patch that addresses this issue and correctly handles REPLACE in CREATE TABLE, REPLACE INTO, and the string function REPLACE() in a SELECT clause. I will put in a pull request as well.

https://github.com/smgallo/PHP-SQL-Parser/commit/8c8e8163fb008146ecdb406dfa1a8fde20ea73d4