Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-1636

JDBC adapter generates wrong SQL for self join with sub-query

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.11.0
    • Fix Version/s: 1.12.0
    • Component/s: jdbc-adapter
    • Labels:
      None

      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.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                ransom Zhiqiang He
                Reporter:
                ransom Zhiqiang He
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: