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.)
