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

Wrong result upon query with aggregate function returning NULL in subquery

    XMLWordPrintable

    Details

      Description

      create table t1 (a date);
      insert into t1 values ('2021-12-12'),('2012-12-21');
      create table t2 (b int);
      insert into t2 values (null),(null); # Optional, fails either way
       
      # returns NULL as expected
      select max(b) from t2;
      # returns empty result as expected
      select a from t1 where a not in (null);
      # returns a result set, unexpectedly
      select a from t1 where a not in (select max(b) from t2);
       
      drop table t1, t2;
      

      10.2 85defc47

      select max(b) from t2;
      max(b)
      NULL
      select a from t1 where a not in (null);
      a
      select a from t1 where a not in (select max(b) from t2);
      a
      2021-12-12
      2012-12-21
      

      plan

      explain extended select a from t1 where a not in (select max(b) from t2);
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
      2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
      Warnings:
      Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select max(`test`.`t2`.`b`) from `test`.`t2` having trigcond(<cache>(`test`.`t1`.`a`) = <ref_null_helper>(max(`test`.`t2`.`b`))))))
      

      Was also reproducible on 10.1, but not on 10.0.
      Not reproducible on MySQL 5.7.

        Attachments

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.