[CALCITE-1636] JDBC adapter generates wrong SQL for self join with sub-query

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.11.0
• Fix Version/s: 1.12.0
• Component/s: jdbc-adapter
• Assignee: Zhiqiang He
• Reporter: Zhiqiang He
• Created: 15/Feb/17 12:19
• Updated: 24/Mar/17 03:20
• Resolved: 21/Feb/17 09:01

Description

JDBC adapter generates wrong SQL when selft join with subquery when DB2 dialect used .

input sql

select t1."customer_id", t2."customer_id" 
  from (select "customer_id" from "sales_fact_1997") as t1 
  inner join (select "customer_id" from "sales_fact_1997") t2 
  on t1."customer_id" = t2."customer_id"
 

output sql

SELECT *
FROM (SELECT sales_fact_1997.customer_id
FROM foodmart.sales_fact_1997 AS sales_fact_1997) AS t
INNER JOIN (SELECT sales_fact_19970.customer_id
FROM foodmart.sales_fact_1997 AS sales_fact_19970) AS t0 ON sales_fact_1997.product_id = sales_fact_19970.product_id
 

sales_fact_1997.product_id = sales_fact_19970.product_id is wrong, can not found tables sales_fact_1997 and sales_fact_19970 in ON condition.

Issue Links

• links to: GitHub Pull Request #737