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

"Full join" generates an incorrect execution plan under mysql

VotersWatch issueWatchersLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    Description

      The current jdbcJoinRules can match any join type, but not all jdbc databases can support full join, such as mysql and H2.
      Calcite will generate the following plan for

      select count(*) as c from \"foodmart\".\"store\" as p1 full join \"foodmart\".\"store\" as p2 using (\"store_id\")
      

      .

      JdbcToEnumerableConverter
        JdbcAggregate(group=[{}], C=[COUNT()])
          JdbcJoin(condition=[=($0, $1)], joinType=[full])
            JdbcProject(store_id=[$0])
              JdbcTableScan(table=[[foodmart, store]])
            JdbcProject(store_id=[$0])
              JdbcTableScan(table=[[foodmart, store]])

      When the plan is executed in Mysql, the execution will fail due to the existence of a full join.

       

       

      I think it is necessary to add supportsFullJoin method in SqlDialect, and add JdbcJoinRule matching rules to solve this problem

       

      Attachments

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            Unassigned Unassigned
            angelzouxin JIasen Sheng
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0h
                0h
                Logged:
                Time Spent - 1h 10m
                1h 10m

                Slack

                  Issue deployment