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

Wrong output from the EXPLAIN command of the test case for lp bug #714999

    XMLWordPrintable

Details

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

    Description

      In MariaDB 5.3 the output from the EXPLAIN of the test case for lp bug #714999 is

      MariaDB [test]> EXPLAIN SELECT f2 FROM t3 WHERE (
      -> SELECT MAX( pk ) FROM t1
      -> WHERE EXISTS (
      -> SELECT max(f1)
      -> FROM t2 GROUP BY f1
      -> )
      -> ) IS NULL ;
      -------------------------------------------------------------------------------------------------------------+

      id select_type table type possible_keys key key_len ref rows Extra

      -------------------------------------------------------------------------------------------------------------+

      1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
      2 SUBQUERY t1 system NULL NULL NULL NULL 1  
      3 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort

      -------------------------------------------------------------------------------------------------------------+

      So the expected number of examined rows from t2 is 2 and that is correct.

      Yet in MariaDB 5.5 the EXPLAIN output for the same test case is
      MariaDB [test]> EXPLAIN SELECT f2 FROM t3 WHERE (
      -> SELECT MAX( pk ) FROM t1
      -> WHERE EXISTS (
      -> SELECT max(f1)
      -> FROM t2 GROUP BY f1
      -> )
      -> ) IS NULL ;
      ----------------------------------------------------------------------------------------------------------------------------+

      id select_type table type possible_keys key key_len ref rows Extra

      ----------------------------------------------------------------------------------------------------------------------------+

      1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
      2 SUBQUERY t1 system NULL NULL NULL NULL 1  
      3 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort

      ----------------------------------------------------------------------------------------------------------------------------+

      Here the name of the table in the third line is 'internal_tmp_table' and the expected number of the examined rows is 1.

      Apparently this is not correct. This line of the EXPLAIN output is for the plan of the uncorrelated sub-query 'SELECT max(f1)FROM t2 GROUP BY f1'.

      This change in the plan appeared after the patch that fixes lp bug #944706.

      I consider this as a serious regression bug.

      Attachments

        Activity

          People

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