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

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5.25
    • Fix Version/s: 5.5.27
    • Component/s: None
    • Labels:
      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

            • Assignee:
              timour Timour Katchaounov (Inactive)
              Reporter:
              igor Igor Babaev
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: