Issue with some functions using "unit"

[dmaechler]

I had a problem with the parsing of my SQL query, containing a TIMESTAMPDIFF() function. In fact, the unit "MONTH" is parsed as a "colref" while it's clearly a UNIT (i don't know if "reserved" is appropriate since it is also a function name). Here is a sample of my query :

SELECT TIMESTAMPDIFF(MONTH, '2016-01-01', NOW()) FROM users

I got it like that after parsing process :

SELECT TIMESTAMPDIFF(MONTH, '2016-01-01', NOW()) FROM users

You can check my var_export($parsed) array :

array ( 0 => array ( 'expr_type' => 'function', 'alias' => false, 'base_expr' => 'TIMESTAMPDIFF', 'sub_tree' => array ( 0 => array ( 'expr_type' => 'colref', 'base_expr' => 'MONTH', 'no_quotes' => array ( 'delim' => false, 'parts' => array ( 0 => 'MONTH', ), ), 'sub_tree' => false, ), 1 => array ( 'expr_type' => 'const', 'base_expr' => ''2016-01-01'', 'sub_tree' => false, ), 2 => array ( 'expr_type' => 'function', 'base_expr' => 'NOW', 'sub_tree' => false, ), ), 'delim' => false, ), )

Let me know if any update, thank you in advance.

Regards, David MAECHLER

[homersimpsons]

I confirm this issue still exists, as an alternative we can use SQL_TSI_SECOND over SECOND which avoid the ambiguity.

$parser = new PHPSQLParser();

$parsed = $parser->parse('SELECT TIMESTAMPDIFF(SQL_TSI_SECOND, NOW(), NOW()) FROM dual');
assert($parsed['SELECT'][0]['sub_tree'][0]['expr_type'] === 'reserved'); // Works

$parsed = $parser->parse('SELECT TIMESTAMPDIFF(SECOND, NOW(), NOW()) FROM dual');
assert($parsed['SELECT'][0]['sub_tree'][0]['expr_type'] === 'reserved'); // Fails, here it is 'colref'

It does return 'colref' because it enters in the elseif which is because SECOND is a function:

https://github.com/greenlion/PHP-SQL-Parser/blob/e38d6f0f500d4d86bee7722e2e89262eeaab7e59/src/PHPSQLParser/processors/ExpressionListProcessor.php#L399-L405