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

CHEAP SQ: Inconsistent behavior of subquery in SELECT and HAVING

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • None
    • 5.5.25
    • None
    • 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

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