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

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.