[HIVE-28208] WITH column list doesn't work with CTE materialization

• Type: Bug
• Status: Patch Available
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 4.0.0
• Component/s: Query Planning
• Labels: pull-request-available
• Assignee: Shohei Okumiya
• Reporter: Shohei Okumiya
• Created: 23/Apr/24 13:55
• Updated: 24/Sep/24 00:25

Description

I found materialized CTEs with column labels don't work as expected.

create table src (key string, value string);

-- Without materialization
with q1(srcKey, srcValue) as (select key, value from src where key= '5')
select a.srcKey
from q1 a join q1 b
on a.srcKey=b.srcKey;

+-----------+
| a.srckey  |
+-----------+
+-----------+

-- With materialization
set hive.optimize.cte.materialize.threshold=2;
set hive.optimize.cte.materialize.full.aggregate.only=false;
with q1(srcKey, srcValue) as (select key, value from src where key= '5')
select a.srcKey
from q1 a join q1 b
on a.srcKey=b.srcKey;

Error: Error while compiling statement: FAILED: SemanticException [Error 10002]: Line 4:5 Invalid column reference 'srcKey' (state=42000,code=10002) 

This is the stack trace on HiveServer2.

Caused by: org.apache.hadoop.hive.ql.parse.SemanticException: Line 4:5 Invalid column reference 'srcKey'
    at org.apache.hadoop.hive.ql.parse.CalcitePlanner.genAllRexNode(CalcitePlanner.java:5435) ~[hive-exec-4.1.0-SNAPSHOT.jar:4.1.0-SNAPSHOT]
    at org.apache.hadoop.hive.ql.parse.CalcitePlanner.access$800(CalcitePlanner.java:361) ~[hive-exec-4.1.0-SNAPSHOT.jar:4.1.0-SNAPSHOT]
    at org.apache.hadoop.hive.ql.parse.CalcitePlanner$CalcitePlannerAction.genJoinRelNode(CalcitePlanner.java:2658) ~[hive-exec-4.1.0-SNAPSHOT.jar:4.1.0-SNAPSHOT]
    at org.apache.hadoop.hive.ql.parse.CalcitePlanner$CalcitePlannerAction.genJoinLogicalPlan(CalcitePlanner.java:2892) ~[hive-exec-4.1.0-SNAPSHOT.jar:4.1.0-SNAPSHOT]
    at org.apache.hadoop.hive.ql.parse.CalcitePlanner$CalcitePlannerAction.genLogicalPlan(CalcitePlanner.java:5052) ~[hive-exec-4.1.0-SNAPSHOT.jar:4.1.0-SNAPSHOT]
    at org.apache.hadoop.hive.ql.parse.CalcitePlanner$CalcitePlannerAction.apply(CalcitePlanner.java:1629) ~[hive-exec-4.1.0-SNAPSHOT.jar:4.1.0-SNAPSHOT]
    at org.apache.hadoop.hive.ql.parse.CalcitePlanner$CalcitePlannerAction.apply(CalcitePlanner.java:1572) ~[hive-exec-4.1.0-SNAPSHOT.jar:4.1.0-SNAPSHOT]
    at org.apache.calcite.tools.Frameworks.lambda$withPlanner$0(Frameworks.java:131) ~[hive-exec-4.1.0-SNAPSHOT.jar:4.1.0-SNAPSHOT]
    at org.apache.calcite.prepare.CalcitePrepareImpl.perform(CalcitePrepareImpl.java:914) ~[hive-exec-4.1.0-SNAPSHOT.jar:4.1.0-SNAPSHOT]
    at org.apache.calcite.tools.Frameworks.withPrepare(Frameworks.java:180) ~[hive-exec-4.1.0-SNAPSHOT.jar:4.1.0-SNAPSHOT]
    at org.apache.calcite.tools.Frameworks.withPlanner(Frameworks.java:126) ~[hive-exec-4.1.0-SNAPSHOT.jar:4.1.0-SNAPSHOT]
    at org.apache.hadoop.hive.ql.parse.CalcitePlanner.logicalPlan(CalcitePlanner.java:1324) ~[hive-exec-4.1.0-SNAPSHOT.jar:4.1.0-SNAPSHOT]
    at org.apache.hadoop.hive.ql.parse.CalcitePlanner.genOPTree(CalcitePlanner.java:572) ~[hive-exec-4.1.0-SNAPSHOT.jar:4.1.0-SNAPSHOT]
    at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:13178) ~[hive-exec-4.1.0-SNAPSHOT.jar:4.1.0-SNAPSHOT] 

Issue Links

• links to: GitHub Pull Request #5232