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

Single table UPDATE/DELETE doesn't use non-semijoin Materialization

Details

    Description

      Stumbled on this in MDEV-22377.

      Single-table UPDATE/DELETEs do not support Materialization (I mean non-semi-join one, for the issue with semi-joins, see MDEV-7487).

      The reason is in JOIN::choose_subquery_plan():

        #0  JOIN::choose_subquery_plan (this=0x7ffc40017368, join_tables=3) at /home/psergey/dev-git/10.4-rel/sql/opt_subselect.cc:6467
      		 
        #1  0x0000555555db8ad8 in make_join_statistics (join=0x7ffc40017368, tables_list=..., keyuse_array=0x7ffc40017658) at /home/psergey/dev-git/10.4-rel/sql/sql\_select.cc:5590
      		 
        #2  0x0000555555dac671 in JOIN::optimize_inner (this=0x7ffc40017368) at /home/psergey/dev-git/10.4-rel/sql/sql_select.cc:2254
      		 
        #3  0x0000555555da9fb6 in JOIN::optimize (this=0x7ffc40017368) at /home/psergey/dev-git/10.4-rel/sql/sql\_select.cc:1601
      		 
        #4  0x0000555555d301ab in st_select_lex::optimize_unflattened_subqueries (this=0x7ffc40005470, const_only=false) at /home/psergey/dev-git/10.4-rel/sql/sql_lex.cc:4188
      		 
        #5  0x0000555555e701d2 in mysql_update (thd=0x7ffc40000d50, table_list=0x7ffc40012450, fields=..., values=..., conds=0x7ffc40016e80, order_num=0, order=0x0, limit=18446744073709551615, ignore=false, found_return=0x7fffdcbb0e70, updated_return=0x7fffdcbb0f30) at /home/psergey/dev-git/10.4-rel/sql/sql_update.cc:516


          {
      		 
            /*
      		 
              TODO: outer_join can be NULL for DELETE statements.
      		 
              How to compute its cost?
      		 
            */
      		 
            outer_lookup_keys= 1;
      		 
          }

      Here, we don't know how many times the subquery will be evaluated (if we assume it's evaluated once, Materialization doesn't make sense).

      Can we figure out how many rows single-table UPDATE/DELETE is going to examine?
      Not yet: mysql_update() calls optimize_unflattened_subqueries:

        if (select_lex->optimize_unflattened_subqueries(false))
      		 
          DBUG_RETURN(TRUE);

      before it does partition pruning or range analysis.

      Attachments

        Issue Links

          is duplicated by

          New Feature - A new feature of the product, which has yet to be developed. MDEV-25008 Single-table UPDATE/DELETE: make cost-based choice between subquery strategies

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-7487 Semi-join optimization for single-table update/delete statements

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22248 Optimizer chooses wrong strategy on delete

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22377 Subquery in an UPDATE query uses full scan instead of range

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. MDEV-25008 Single-table UPDATE/DELETE: make cost-based choice between subquery strategies

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            People

              ycp Yuchen Pei
              psergei Sergei Petrunia
              Votes:
              1 Vote for this issue
              Watchers:
              8 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.