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

LP:1008773 - Wrong result (NULL instead of a value) with no matching rows, subquery in FROM and HAVING

    XMLWordPrintable

Details

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

    Description

      The following query

      SELECT MAX(a), ( SELECT 1 FROM t2 ) AS bb FROM t1

      with 0 rows in t1 and 1 row in t2 produces NULL, NULL on maria/5.3 and maria/5.5. Same happens if t1 is not empty, but the outer SELECT has an impossible WHERE condition. t2 can have more rows too, in which case a subquery should use an aggregate function, e.g. SUM.

      The same query without t2 produces NULL, 1;
      SELECT MAX(a), ( SELECT 1 ) AS bb FROM t1

      maria-5.1, maria-5.2, mysql-5.1, mysql-5.5, mysql-trunk, postgres-8.4.7 return NULL, 1 for both queries.

      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 required to execute the query).

      EXPLAIN (with in_to_exists=on, everything else OFF):

      id select_type table type possible_keys key key_len ref rows filtered Extra
      1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found
      2 SUBQUERY t2 system NULL NULL NULL NULL 1 100.00
      Warnings:
      Note 1003 select max(NULL) AS `MAX(a)`,(select 1 from dual) AS `bb` from `test`.`t1`

      1. 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);

      1. Either t1 is empty, or SELECT has an impossible condition.
      2. 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;

      1. End of test case
      1. Expected result:
        #
      2. MAX(a) bb
      3. NULL 1
      1. Actual result:
        #
      2. MAX(a) bb
      3. NULL NULL

      Attachments

        Activity

          People

            timour Timour Katchaounov (Inactive)
            elenst Elena Stepanova
            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.