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

Wrong result upon LEFT JOIN and NULL comparison with not_null_range_scan

    XMLWordPrintable

    Details

      Description

      Possibly related to MDEV-21781

      CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM;
      INSERT INTO t1 VALUES  (10,1),(null,2);
       
      CREATE TABLE t2 (pk INT PRIMARY KEY) ENGINE=MyISAM;
      INSERT INTO t2 VALUES (1),(2);
       
      SELECT t1.b FROM t1 LEFT JOIN t2 ON t1.a = t2.pk WHERE t1.a IS NULL ORDER BY t1.b;
      SET optimizer_switch= 'not_null_range_scan=on';
      SELECT t1.b FROM t1 LEFT JOIN t2 ON t1.a = t2.pk WHERE t1.a IS NULL ORDER BY t1.b;
       
      DROP TABLE t1, t2;
      

      Without not_null_range_scan the query returns a result set (the expected result). With not_null_range_scan it returns an empty set (wrong).

      10.5 a9d0bb12

      SELECT t1.b FROM t1 LEFT JOIN t2 ON t1.a = t2.pk WHERE t1.a IS NULL ORDER BY t1.b;
      b
      2
      SET optimizer_switch= 'not_null_range_scan=on';
      SELECT t1.b FROM t1 LEFT JOIN t2 ON t1.a = t2.pk WHERE t1.a IS NULL ORDER BY t1.b;
      b
      DROP TABLE t1, t2;
      

      Same with the plans:

      explain extended SELECT t1.b FROM t1 LEFT JOIN t2 ON t1.a = t2.pk WHERE t1.a IS NULL ORDER BY t1.b;
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using filesort
      Warnings:
      Note	1003	select `test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` is null order by `test`.`t1`.`b`
      SELECT t1.b FROM t1 LEFT JOIN t2 ON t1.a = t2.pk WHERE t1.a IS NULL ORDER BY t1.b;
      b
      2
      SET optimizer_switch= 'not_null_range_scan=on';
      SELECT t1.b FROM t1 LEFT JOIN t2 ON t1.a = t2.pk WHERE t1.a IS NULL ORDER BY t1.b;
      b
      explain extended SELECT t1.b FROM t1 LEFT JOIN t2 ON t1.a = t2.pk WHERE t1.a IS NULL ORDER BY t1.b;
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
      Warnings:
      Note	1003	select `test`.`t1`.`b` AS `b` from `test`.`t1` where 0 order by `test`.`t1`.`b`
      

        Attachments

          Activity

            People

            Assignee:
            monty Michael Widenius
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.