You say that
SELECT code, CASE WHEN code = 'test_val' THEN ABS(val) ELSE NULL END AS absval
FROM TEST
WHERE CASE WHEN code = 'test_val' THEN ABS(val) > 0 ELSE FALSE END
isn't valid SQL. What's wrong with it? It looks valid and correct to
me. (I acknowledge that it's neither pretty nor optimal.)
To make the query more pretty and optimal, it would be nice if Calcite
did a couple of simplifications:
1. Simplify CASE to AND. "CASE WHEN code = 'test_val' THEN ABS(val) >
0 ELSE FALSE END" --> "code = 'test_val' AND ABS(val) > 0". This
rewrite is valid only if the expressions are pure, can't throw, and
NULL values don't matter, all true in this case.
2. Reduce constants. "CASE WHEN code = 'test_val' THEN ABS(val) ELSE
NULL END AS absval" becomes "ABS(val) AS absval" because the WHERE
clause has ensured that "code = 'test_val'" is always true.
I believe there's a rule for 2 but not for 1.
Julian
On Tue, May 21, 2024 at 11:18 AM <[email protected]> wrote:
>
> I'm having an issue with CoreFilters.FILTER_REDUCE_EXPRESSIONS. I'm
> wondering if this is a problem with the rule, or with how I've set up my
> logical tree.
>
> Conceptually I am trying to use the equivalent of a computed column in a
> WHERE. Since this isn't legal:
>
> SELECT code, CASE WHEN code = 'test_val' THEN ABS(val) ELSE NULL END AS
> absval
> FROM TEST
> WHERE absval > 0
>
> I need to move that same CASE logic into the WHERE clause, so:
>
> SELECT code, CASE WHEN code = 'test_val' THEN ABS(val) ELSE NULL END AS
> absval
> FROM TEST
> WHERE CASE WHEN code = 'test_val' THEN ABS(val) ELSE NULL END > 0
>
> I'm able to set up my logical RelNode tree and generate exactly this SQL
> directly from the logical tree. The builder logic has:
>
> RelNode relNode = builder
> .projectPlus(builder.alias(caseNode, "ABSVAL"))
> .filter(builder.greaterThan(caseNode, builder.literal(0)))
> .build();
>
> However, when I try to generate a physical tree from this, I can see the
> FILTER_REDUCE_EXPRESSIONS rule coerses the CASE statement in the filter into
> a BOOLEAN expression, and it produces:
>
> SELECT code, CASE WHEN code = 'test_val' THEN ABS(val) ELSE NULL END AS
> absval
> FROM TEST
> WHERE CASE WHEN code = 'test_val' THEN ABS(val) > 0 ELSE FALSE END
>
> Which isn't even valid SQL. If I remove that rule from the planner
> altogether:
>
> planner.removeRule(FILTER_REDUCE_EXPRESSIONS)
>
> The SQL looks correct (effectively the same as that translated directly from
> the logical tree above.)
