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

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

    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

              • Assignee:
                igor Igor Babaev
                Reporter:
                ccalender Chris Calender
              • Votes:
                1 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: