PHPSQLCreator cannot rebuild JOIN conditions containing expression nodes (e.g. CASE expressions)

[adq-talbot]

Description

PHPSQLParser correctly parses queries containing expressions in JOIN ... ON conditions, but PHPSQLCreator fails to rebuild them.

Reproduction

$sql = "SELECT * FROM t1
            LEFT JOIN t2
            ON t2.id = CASE WHEN t1.flag = 1 THEN 10 ELSE 20 END";

$parser = new PHPSQLParser();
$parsed = $parser->parse($sql);

$creator = new PHPSQLCreator($parsed);
echo $creator->created;

Expected behavior

The generated SQL should preserve the CASE expression in the JOIN condition.

Actual behavior

PHPSQLCreator fails to rebuild the query and throws:

PHPSQLParser\exceptions\UnableToCreateSQLException:
unknown [expr_type] = reserved in "expression ref_clause" [2]

greenlion/php-sql-parser/src/PHPSQLParser/builders/RefClauseBuilder.php

Analysis

The parser successfully generates an AST for the query, but PHPSQLCreator appears unable to handle all valid expression node types when rebuilding JOIN reference clauses.

In the failing example above, the exception indicates that a node with:

[
    'expr_type' => 'reserved',
    ...
]

is encountered in a context that RefClauseBuilder does not support.

During investigation of a related issue in our application, I also encountered JOIN conditions containing nodes with:

[
    'expr_type' => ExpressionType::EXPRESSION,
    ...
]

For example, a query such as:

SELECT a.id
FROM table_a a
LEFT JOIN table_b b
    ON b.label =
       CASE
           WHEN a.status = 'A' THEN 'Active'
           ELSE 'Inactive'
       END

required adding support for ExpressionType::EXPRESSION in RefClauseBuilder in order to rebuild the query correctly:

protected function buildExpression($parsed)
{
    if ($parsed['expr_type'] !== ExpressionType::EXPRESSION) {
        return '';
    }

    $builder = new SubTreeBuilder();
    return $builder->build($parsed);
}

This suggests that RefClauseBuilder may currently lack support for some valid expression node types (reserved, expression, or both), depending on the AST structure produced by the parser.

Additional examples

The issue may not be limited to CASE expressions and could potentially affect other complex expressions used in JOIN conditions, such as:

ON t2.id = COALESCE(t1.value, 0)

or

ON t2.id = (t1.a + t1.b)

Possible direction

It may be worth reviewing how RefClauseBuilder handles expression-related node types and whether unsupported nodes should be delegated to SubTreeBuilder for reconstruction.