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

Trivial correlation detection/removal for IN subqueries

    XMLWordPrintable

Details

    Description

      EXISTS-to-IN optimization performs trivial correlation detection and removal.

      MySQL 8 has got it too, but in addition to EXISTS subqueries, they perform de-correlation also for IN subqueries. This allows them to use Materialization strategy for trivially-correlated IN- subqueries:

      create table ten(a int primary key);
      		 
      insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
      		 
      create table t1 (a int, b int, c int);
      		 
      insert into t1 select a,a,a from ten;
      		 
      create table t2 select * from t1;
      		 
      explain select * from t1 where a in (select a from t2 where t1.b=t2.b);


      id     select_type     table   partitions      type    possible_keys   key     key_len ref     rows    filtered        Extra
      		 
      1      SIMPLE  t1      NULL    ALL     NULL    NULL    NULL    NULL    10      100.00  Using where
      		 
      1      SIMPLE  <subquery2>     NULL    eq_ref  <auto_distinct_key>     <auto_distinct_key>     10      test.t1.a,test.t1.b     1       100.00  NULL
      		 
      2      MATERIALIZED    t2      NULL    ALL     NULL    NULL    NULL    NULL    10      100.00  NULL

      Attachments

        Issue Links

          blocks

          Task - A task that needs to be done. MDEV-31510 Allow exists2in and decorrelate-in to work for IN/EXISTS subqueries in arbitrary context

          • Major - Major loss of function.
          • Open
          is blocked by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30073 Wrong result on 2nd execution of PS for query with NOT EXISTS

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

          Technical task - A technical task. MDEV-3881 Endless loop for query with EXISTS predicate using outer reference to view

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-31229 Extend check_equality_for_exist2in() to cover inner table expressions

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31408 Second SELECT from VIEW based on information_schema.optimizer_trace gives NULL result

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31647 Stack looping and SIGSEGV in Item_args::walk_args on UPDATE

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

          Activity

            People

              psergei Sergei Petrunia
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              9 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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