> You say that > … > isn't valid SQL. What's wrong with it? It looks valid and correct to > me.
Right, this was phrased poorly. I assume this is valid ANSI SQL, but it is not for the versions of Oracle and SQL Server I’m currently working with. To this point I haven’t run into any issues with the generated SQL that I haven’t been able to readily work around. It’s unclear to me what the approach is to handling this sort of situation. Do I simply remove that rule when targeting these specific data sources? Can the rule be configured in some manner to produce the SQL valid for those targets? Thanks On 2024/05/21 18:32:42 Julian Hyde wrote: > 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 <je...@gmail.com> 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.) >
