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

Different results with and without subquery_cache on a query with a constant NOT IN condition

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 5.5.25, 5.3.7
    • 5.5.27, 5.3.8
    • None
    • None

    Description

      The following query

      SELECT * FROM t1 
      WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL )
      OR a > 100 

      returns an empty result set with the default optimizer_switch (where subquery_cache=ON), and some rows with subquery_cache=OFF. I believe that the empty result set is correct, because all `a` values are less than 100, and the other part of the WHERE condition evaluates as NULL.

      bzr version-info

      revision-id: igor@askmonty.org-20120623220005-f4323jdj5mw7y2o5
      date: 2012-06-23 15:00:05 -0700
      build-date: 2012-06-26 00:01:40 +0400
      revno: 3550

      mysql-trunk with the default optimizer_switch also returns rows.
      maria/5.2 does not.

      EXPLAIN with the default optimizer_switch (subquery_cache=on):

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
      2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
      Warnings:
      Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((not(<expr_cache><3>(<in_optimizer>((3,3),<exists>(select NULL,NULL having (((3 = NULL) or isnull(NULL)) and ((3 = NULL) or isnull(NULL)) and <is_not_null_test>(NULL) and <is_not_null_test>(NULL))))))) or (`test`.`t1`.`a` > 100))

      EXPLAIN with subquery_cache=off:

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
      2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
      Warnings:
      Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((not(<in_optimizer>((3,3),<exists>(select NULL,NULL having (((3 = NULL) or isnull(NULL)) and ((3 = NULL) or isnull(NULL)) and <is_not_null_test>(NULL) and <is_not_null_test>(NULL)))))) or (`test`.`t1`.`a` > 100))

      Test case:

       
      CREATE TABLE t1 (a INT) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (1),(2),(3);
       
      SELECT * FROM t1
      WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL )
      OR a > 100;
       
      SET optimizer_switch = 'subquery_cache=off';
       
      SELECT * FROM t1
      WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL )
      OR a > 100;
       
      DROP TABLE t1;
       

      Result:

      CREATE TABLE t1 (a INT) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (1),(2),(3);
      SELECT * FROM t1 
      WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL )
      OR a > 100;
      a
      SET optimizer_switch = 'subquery_cache=off';
      SELECT * FROM t1
      WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL )
      OR a > 100;
      a
      2
      3
      DROP TABLE t1;

      Attachments

        Activity

          People

            sanja Oleksandr Byelkin
            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.