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.

        Issue Links

          Activity

          Hide
          ransom Zhiqiang He added a comment -
          Show
          ransom Zhiqiang He added a comment - https://github.com/apache/calcite/pull/373 Please review it. thanks.
          Hide
          jbalint@gmail.com Jess Balint added a comment -

          This is not specific to DB2. The problem is with references to fields from subqueries. I have an alternative approach to this. I will check if they're consistent and add my tests.

          Show
          jbalint@gmail.com Jess Balint added a comment - This is not specific to DB2. The problem is with references to fields from subqueries. I have an alternative approach to this. I will check if they're consistent and add my tests.
          Hide
          julianhyde Julian Hyde added a comment -

          I agree that it doesn't seem DB2-specific. I changed the abstract.

          Jess Balint, Can you review and commit this? You will need to change the comment to include the JIRA case number, the contributor's name in parentheses, and a 'Close apache/calcite#xxx' comment to cause the pull request to be closed. See https://github.com/apache/calcite/commit/2d7e1f1a6649c085c88f8d81410363800a5b91ae as an example. If you want to add tests, or change the fix, that's your call. Just make sure that the contributor retains credit for the work they did.

          Show
          julianhyde Julian Hyde added a comment - I agree that it doesn't seem DB2-specific. I changed the abstract. Jess Balint , Can you review and commit this? You will need to change the comment to include the JIRA case number, the contributor's name in parentheses, and a 'Close apache/calcite#xxx' comment to cause the pull request to be closed. See https://github.com/apache/calcite/commit/2d7e1f1a6649c085c88f8d81410363800a5b91ae as an example. If you want to add tests, or change the fix, that's your call. Just make sure that the contributor retains credit for the work they did.
          Show
          jbalint@gmail.com Jess Balint added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/f7d29063b3a323b21968125f5aea16a0d33a39e6 . Thanks for the PR, Zhiqiang He!
          Show
          julianhyde Julian Hyde added a comment - Updated commit comment in http://git-wip-us.apache.org/repos/asf/calcite/commit/bbb7eaa93f47bf58ab2e44219b26ee0c09058f50 .
          Hide
          julianhyde Julian Hyde added a comment -

          Resolved in release 1.12.0 (2017-03-24).

          Show
          julianhyde Julian Hyde added a comment - Resolved in release 1.12.0 (2017-03-24).

            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:

                Development