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

CHEAP SQ: Inconsistent behavior of subquery in SELECT and HAVING

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 5.5.25
    • Component/s: None
    • Labels:
      None

      Description

      The following type of scenarios produces lots of mismatches between the main 5.5 tree and MDEV-193 tree:

       
      CREATE TABLE t1 (a INT) ENGINE=MyISAM;
      CREATE TABLE t2 (b INT) ENGINE=MyISAM;
      INSERT INTO t2 VALUES (1);
       
      # Either t1 is empty, or SELECT has an impossible condition
      # Either t2 has 1 row, or an aggregate function, e.g. SUM, is used in the subquery
       
      SELECT MAX(a), ( SELECT 1 FROM t2 ) AS bb FROM t1
      HAVING bb IS NOT NULL;

      On 5.5 main tree, the query returns an empty result set. On MDEV-193 tree it returns ( NULL, NULL ).

      The same query, but without a HAVING clause, returns (NULL, NULL) on both branches, which is also incorrect, see https://bugs.launchpad.net/maria/+bug/1008773. However, 5.5 really considers bb to be NULL when it applies the HAVING condition, while MDEV-193 seems to "think" that bb actually contains 1, but does not show it.

      Possibly internally the behavior on MDEV-193 is even more correct, but it causes visibly inconsistent result; besides, my concern is that it might affect cases when the resultset without HAVING is actually correct, so it would be good to check the internal logic, just in case.

      Reproducible with MyISAM and Aria, but not InnoDB.
      Reproducible with the default optimizer_switch as well as with all OFF values except for in_to_exists which is required to run the query.

      Test case:

       
      SET optimizer_switch='in_to_exists=on';
       
      CREATE TABLE t1 (a INT) ENGINE=MyISAM;
      CREATE TABLE t2 (b INT) ENGINE=MyISAM;
      INSERT INTO t2 VALUES (1);
       
      # Either t1 is empty, or SELECT has an impossible condition
       
      SELECT MAX(a), ( SELECT 1 FROM t2 ) AS bb FROM t1
      HAVING bb IS NOT NULL;
       

      Result on 5.5 main:

      MAX(a)	bb

      Result on MDEV-193 tree:

      MAX(a)	bb
      NULL	NULL

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                timour Timour Katchaounov (Inactive)
                Reporter:
                elenst Elena Stepanova
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: