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

            links to

            Web Link GitHub Pull Request #737

              Activity

              Ascending order - Click to sort in descending order
              Hide
              Permalink
              ransom Zhiqiang He added a comment -

              https://github.com/apache/calcite/pull/373
              Please review it. thanks.

              Show
              ransom Zhiqiang He added a comment - https://github.com/apache/calcite/pull/373 Please review it. thanks.
              Hide
              Permalink
              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
              Permalink
              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.
              Hide
              Permalink
              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
              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!
              Hide
              Permalink
              julianhyde Julian Hyde added a comment -

              Updated commit comment in http://git-wip-us.apache.org/repos/asf/calcite/commit/bbb7eaa93f47bf58ab2e44219b26ee0c09058f50.

              Show
              julianhyde Julian Hyde added a comment - Updated commit comment in http://git-wip-us.apache.org/repos/asf/calcite/commit/bbb7eaa93f47bf58ab2e44219b26ee0c09058f50 .
              Hide
              Permalink
              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