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

LP:664594 - Wrong subquery result with join_cache_level=6, even in .result files

    XMLWordPrintable

Details

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

    Description

      mysql-test/r/subselect3.result shows:

      explain select t21.* from t21,t22 where t21.a = t22.a and
      t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
      id select_type table type possible_keys key key_len ref rows Extra
      1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 8 Using temporary; Using filesort
      1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer
      1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; Using join buffer
      2 SUBQUERY t11 ALL NULL NULL NULL NULL 8 Using where
      2 SUBQUERY t12 ALL NULL NULL NULL NULL 8 Using where; Using join buffer
      select t21.* from t21,t22 where t21.a = t22.a and
      t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
      a b c
      256 67 NULL

      while
      mysql-test/r/subselect3_jcl6.result shows:

      explain select t21.* from t21,t22 where t21.a = t22.a and
      t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
      id select_type table type possible_keys key key_len ref rows Extra
      1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 8 Using temporary; Using filesort
      1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer
      1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; Using join buffer
      2 SUBQUERY t11 ALL NULL NULL NULL NULL 8 Using where
      2 SUBQUERY t12 ALL NULL NULL NULL NULL 8 Using where; Using join buffer
      select t21.* from t21,t22 where t21.a = t22.a and
      t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
      a b c
      256 67 NULL
      256 67 NULL

      That is, the _jcl6 variant produces two records instead of one.

      Attachments

        Activity

          People

            igor Igor Babaev (Inactive)
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            0 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.