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

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

    XMLWordPrintable

    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

            Activity

              People

              Assignee:
              igor Igor Babaev
              Reporter:
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration