Simple CASE with a bracketed, function-call, cast or CASE operand runs as a searched CASE and returns wrong rows #7864

Describe the bug

A simple CASE <operand> WHEN <value> THEN ... END runs as a searched CASE WHEN <condition> THEN ... END when its operand ends in ) or END. That covers a bracketed expression, a function call, cast(... AS ...) and another CASE. The operand is dropped, and each WHEN value is evaluated as a condition.

• With a BOOLEAN operand, the query returns wrong rows and reports no error. In WHERE, it selects the wrong rows.
• With an operand of any other type, valid SQL fails with BOOLEAN expected, found <type> at the first WHEN value.

Impact: silent wrong results for valid SQL.

Reproduced at 77c6cf2. Master 6003169 has no change under core/src/main/java/io/questdb/griffin/ since then.

To reproduce

CREATE TABLE t AS (
    SELECT x::INT i, x > 1 b, ('s' || x)::SYMBOL s, CASE WHEN x = 2 THEN NULL ELSE x END::INT n
    FROM long_sequence(3)
);
-- i  b      s   n
-- 1  false  s1  1
-- 2  true   s2  null
-- 3  true   s3  3

Wrong results, no error:

EXPLAIN SELECT CASE (b) WHEN false THEN 'x' ELSE 'y' END c FROM t shows the expression folded to the constant 'y' (functions: ['y']); the plan no longer references b.

Valid SQL that fails:

Spellings that return the expected rows: CASE b WHEN ..., CASE i > 1 WHEN ..., CASE i + 0 WHEN ... and CASE i::INT WHEN .... Brackets do not change an expression's value, so CASE (b) WHEN true ... should return the same rows as CASE b WHEN true ....

Cause

ExpressionParser.java:1989 on master:

int argCount = 0;
while ((node = opStack.pop()) != null && !SqlKeywords.isCaseKeyword(node.token)) {
    argStackDepth = onNode(listener, node, argStackDepth, prevBranch);
    argCount++;
}

if (paramCount == 0) {
    if (argCount == 0) {
        // this is 'case when', we will indicate that this is regular 'case'
        // to the rewrite logic
        onNode(listener, expressionNodePool.next().of(ExpressionNode.LITERAL,
                null, Integer.MIN_VALUE, -1), argStackDepth, prevBranch);
    }
    paramCount++;
}

At the first WHEN, the parser pops the operators above case and counts them in argCount. A count of 0 makes it treat the statement as a searched CASE and emit the searched-case marker for the rewrite. An operand that ends in ) or END has already left the operator stack by the time WHEN arrives, so the count is 0 although an operand is present. The spellings that work still have a node on the operator stack when WHEN arrives: the parser parks a bare column there in case a ( follows, and i > 1 or i::INT leaves its operator there.

The check dates from 7d7bf5a (2019), which added the simple CASE.

Suggested fix

Test argStackDepth == 0 in place of argCount == 0. The CASE keyword resets argStackDepth to 0 when it opens its scope, so at the first WHEN the depth is non-zero exactly when an operand is present, whichever way it reached the output.

With that one-line change on 77c6cf2, every statement above returns the expected rows, and the EXPLAIN plan shows case([b,'y','x']). CaseFunctionFactoryTest, SwitchFunctionFactoryTest, CaseCommonTest, NullCaseFunctionTest, ExpressionParserTest and SqlParserTest pass, 1,553 of 1,554 tests. The one failure is ExpressionParserTest.testCaseWithCast, which pins the current RPN for case (cast(1 as int)) when 1 ..., searched-case marker included (1 int cast 1 'th1' ..., with an empty token after cast). Its expectation needs updating to 1 int cast 1 'th1' 2 'th2' 0 case 5 * 1 +.

Related

• A parenthesised value list in a scalar expression drops operands or throws NullPointerException #7784: a parenthesised value list such as (true, false) in a CASE branch or operator operand drops operands. It is in the same parser code but has a different cause: there the slot holds two values, here the operand is counted as none.

Where it was found

Review of PR #7602. The PR does not change this code path.