Stamatis Zampetakis created IMPALA-15366:
--------------------------------------------
Summary: Deactivated simplifications prevent CTE detection
Key: IMPALA-15366
URL: https://issues.apache.org/jira/browse/IMPALA-15366
Project: IMPALA
Issue Type: Sub-task
Components: Frontend
Reporter: Stamatis Zampetakis
Consider the following TPC-H query and its respective CALCITE plan.
{code:sql}
WITH segment_sales AS (
SELECT
c.c_mktsegment AS segment,
c.c_nationkey AS nationkey,
SUM(o.o_totalprice) AS total_sales
FROM orders o
JOIN customer c
ON c.c_custkey = o.o_custkey
GROUP BY
c.c_mktsegment,
c.c_nationkey
)
SELECT
n1.n_name,
ss1.segment,
ss1.total_sales,
n2.n_name,
ss2.segment,
ss2.total_sales
FROM segment_sales ss1
JOIN nation n1
ON ss1.nationkey = n1.n_nationkey
JOIN segment_sales ss2
ON ss1.segment = ss2.segment
JOIN nation n2
ON ss2.nationkey = n2.n_nationkey
{code}
{noformat}
ImpalaProjectRel(N_NAME=[$4], SEGMENT=[$0], TOTAL_SALES=[$2], N_NAME0=[$9],
SEGMENT0=[$5], TOTAL_SALES0=[$7])
ImpalaJoinRel(condition=[=($6, $8)], joinType=[inner])
ImpalaJoinRel(condition=[=($0, $5)], joinType=[inner])
ImpalaJoinRel(condition=[=($1, $3)], joinType=[inner])
ImpalaAggRel(group=[{0, 1}], TOTAL_SALES=[SUM($2)])
ImpalaProjectRel(SEGMENT=[$4], NATIONKEY=[$3], o_totalprice=[$1])
ImpalaJoinRel(condition=[=($2, $0)], joinType=[inner])
ImpalaFilterRel(condition=[IS NOT NULL($0)])
ImpalaProjectRel(o_custkey=[$1], o_totalprice=[$3])
ImpalaHdfsScanRel(table=[[tpch, orders]])
ImpalaFilterRel(condition=[AND(IS NOT NULL($0), IS NOT NULL($1),
IS NOT NULL($2))])
ImpalaProjectRel(c_custkey=[$0], c_nationkey=[$3],
c_mktsegment=[$6])
ImpalaHdfsScanRel(table=[[tpch, customer]])
ImpalaFilterRel(condition=[IS NOT NULL($0)])
ImpalaProjectRel(n_nationkey=[$0], n_name=[$1])
ImpalaHdfsScanRel(table=[[tpch, nation]])
ImpalaAggRel(group=[{0, 1}], TOTAL_SALES=[SUM($2)])
ImpalaProjectRel(SEGMENT=[$4], NATIONKEY=[$3], o_totalprice=[$1])
ImpalaJoinRel(condition=[=($2, $0)], joinType=[inner])
ImpalaFilterRel(condition=[IS NOT NULL($0)])
ImpalaProjectRel(o_custkey=[$1], o_totalprice=[$3])
ImpalaHdfsScanRel(table=[[tpch, orders]])
ImpalaFilterRel(condition=[AND(IS NOT NULL($0), IS NOT NULL($2), IS
NOT NULL($1))])
ImpalaProjectRel(c_custkey=[$0], c_nationkey=[$3],
c_mktsegment=[$6])
ImpalaHdfsScanRel(table=[[tpch, customer]])
ImpalaFilterRel(condition=[IS NOT NULL($0)])
ImpalaProjectRel(n_nationkey=[$0], n_name=[$1])
ImpalaHdfsScanRel(table=[[tpch, nation]])
{noformat}
Naturally we would expect that "segment_sales" CTE defined in the WITH clause
to be examined by the optimizer for potential reuse. The same repetition is
also visible in the query plan where the following snippet appears "almost"
twice.
{noformat}
ImpalaAggRel(group=[{0, 1}], TOTAL_SALES=[SUM($2)])
ImpalaProjectRel(SEGMENT=[$4], NATIONKEY=[$3], o_totalprice=[$1])
ImpalaJoinRel(condition=[=($2, $0)], joinType=[inner])
ImpalaFilterRel(condition=[IS NOT NULL($0)])
ImpalaProjectRel(o_custkey=[$1], o_totalprice=[$3])
ImpalaHdfsScanRel(table=[[tpch, orders]])
ImpalaFilterRel(condition=[AND(IS NOT NULL($0), IS NOT NULL($1),
IS NOT NULL($2))])
ImpalaProjectRel(c_custkey=[$0], c_nationkey=[$3],
c_mktsegment=[$6])
ImpalaHdfsScanRel(table=[[tpch, customer]])
{noformat}
"Almost" because the two sub-trees rooted at ImpalaAggRel are not identical.
There is a subtle but important difference that prevents the CTE detection
which relies on identical expressions.
By comparing the filter expressions above the customer can we can observe that
predicate order is different.
{noformat}
ImpalaFilterRel(condition=[AND(IS NOT NULL($0), IS NOT NULL($2), IS NOT
NULL($1))])
ImpalaFilterRel(condition=[AND(IS NOT NULL($0), IS NOT NULL($1), IS NOT
NULL($2))])
{noformat}
Semantically the expressions are equivalent but structurally they are not the
same.
The [simplifications are disabled for many
rules|https://github.com/apache/impala/blob/ac16a39210df9999f8dd1941e3fa513594f9aab2/java/calcite-planner/src/main/java/org/apache/impala/calcite/rules/ImpalaCoreRules.java#L66]
which in this case are causing this discrepancy in the plan. In this case, its
a mere normalization that is not performed but as shown it has impact on the
detection of the CTE.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
