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

Wrong result on second PS execution with early constant subquery optimization (MDEV-83)

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • None
    • None
    • None
    • None

    Description

      The following test case extracted from subselect_sj.test fails with MDEV-83 due to early subquery optimization.

      drop table if exists t0, t1, t2, t10;

      set @@storage_engine=myisam;

      create table t0 (a int);
      insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
      create table t1(a int, b int);
      insert into t1 values (0,0),(1,1),(2,2);
      create table t10 (pk int, a int, primary key(pk));
      insert into t10 select a,a from t0;
      create table t2 as select * from t1;

      – Force a very simple query plan
      set join_cache_level=0;
      set optimizer_switch='semijoin=off';

      select *
      from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
      where t1.a < 5;

      prepare s1 from
      ' select * from
      t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
      where t1.a < 5';
      execute s1;
      execute s1;

      Attachments

        Activity

          People

            timour Timour Katchaounov (Inactive)
            timour Timour Katchaounov (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            1 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.