UnableToCalculatePositionException when JOIN clause contains SQL functions

hi,

With the following code:

$query = 'SELECT start_date FROM users INNER JOIN vacation ON DATE(start_date) <= DATE(end_date)';
$parser = new \PHPSQLParser\PHPSQLParser($query, true);

an exception is thrown:

# Error        PHPSQLParser\exceptions\UnableToCalculatePositionException
# File      External/greenlion/php-sql-parser/src/PHPSQLParser/positions/PositionCalculator.php
# Line 243
# Trace
# File       Function        Line
# External/greenlion/php-sql-parser/src/PHPSQLParser/positions/PositionCalculator.php   lookForBaseExpression   259
# External/greenlion/php-sql-parser/src/PHPSQLParser/positions/PositionCalculator.php   lookForBaseExpression   259
# External/greenlion/php-sql-parser/src/PHPSQLParser/positions/PositionCalculator.php   lookForBaseExpression   125
# External/greenlion/php-sql-parser/src/PHPSQLParser/PHPSQLParser.php   setPositionsWithinSQL   100
# External/greenlion/php-sql-parser/src/PHPSQLParser/PHPSQLParser.php   parse   75

# [2020-04-01 07:02:15] - Exception     cannot calculate position of vacation ON DATE(start_date) <= DATE(end_date) within )

I tried to understand the code in PositionCalculator#lookForBaseExpression, but did not manage to.

The problem seems to be with the $backtracking array.

When the traversal of the $parsed array reaches the ref_clause for the join clause, it adds an offset and two false to the $backtracking array.

But then when finding the expressions in the date functions, the $backtracking array is popped (and nothing is added to it in the preceding sub_tree).

so it seems too many items are popped off the $backtracking array (or not enough are added),

and so when we try to find the base_expr of the second table (vacation), charPos is already at the end of the SQL query and the expression cannot be found and so an exception is thrown.

Hopefully someone will understand how to adapt the $backtracking array handling.

thanks

greenlion / PHP-SQL-Parser

UnableToCalculatePositionException when JOIN clause contains SQL functions #319