Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-12838

Wrong results with materialization=on (with subquery and joins)

    XMLWordPrintable

Details

    Description

      With optimizer_switch materialization=on, the following test case fails with wrong results in MariaDB (tested 10.0.30, 10.1.23, and 10.2.5). This works with MySQL 5.6 and 5.7, and also with MariaDB if you set materialization=off.

      Here is sample output from the sql file (attached) when piped into MariaDB 10.2.5:

      >mysql -uroot -pxxx -P3317 -e"select @@version\Gselect @@optimizer_switch\G"
      *************************** 1. row ***************************
      @@version: 10.2.5-MariaDB
      *************************** 1. row ***************************
      @@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,
      firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=
      on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on
      

      C:\>mysql -uroot -pxxx -P3317 < test_case_obfuscated.sql
      assignment_group
      ebb4620037332000158bbfc8bcbe5d89
      

      C:\>mysql -uroot -pxxx -P3317 -e"set @@global.optimizer_switch='materialization=off'"
      

      C:\>mysql -uroot -pxxx -P3317 -e"select @@version\Gselect @@optimizer_switch\G"
      *************************** 1. row ***************************
      @@version: 10.2.5-MariaDB
      *************************** 1. row ***************************
      @@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,
      firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=
      on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on
      

      C:\>mysql -uroot -pxxx -P3317 < test_case_obfuscated.sql
      assignment_group
      ebb4620037332000158bbfc8bcbe5d89
      ec70316637232000158bbfc8bcbe5d60
      3120fd2637232000158bbfc8bcbe5d42
      e08fad2637232000158bbfc8bcbe5d39
      

      Attachments

        Issue Links

          Activity

            People

              igor Igor Babaev
              ccalender Chris Calender (Inactive)
              Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.