[DRILL-2301] Query fails when multiple table aliases are provided for CTEs

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 0.8.0
• Fix Version/s: 1.0.0
• Component/s: Query Planning & Optimization
• Assignee: Jinfeng Ni
• Reporter: Abhishek Girish
• Created: 25/Feb/15 00:45
• Updated: 28/Apr/15 20:20
• Resolved: 03/Apr/15 22:20

Description

When a query contains a WITH clause which defines Common Table Expressions (CTEs), a second alias for the same the temporary table cannot be provided.

The following query fails to validate:
WITH C AS
(
SELECT c_customer_id cid
FROM customer
GROUP BY c_customer_id )
SELECT c2.cid
FROM C c1,
C c2
WHERE c2.cid = c1.cid
ORDER BY c2.cid limit 10;

Query failed: SqlValidatorException: Table 'c2' not found

The above query validates and executes successfully on Postgres.

When the WITH clause is removed, the query succeeds.

The following query executes fine:
SELECT c2.c_customer_id
FROM customer c1,
customer c2
WHERE c2.c_customer_id = c1.c_customer_id
ORDER BY c2.c_customer_id limit 10;

Logs attached.

Issue Links

• is duplicated by: DRILL-2318 Query fails when an ORDER BY clause is used with WITH-CLAUSE