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

Wrong result with in_to_exists, IN / NOT IN and HAVING

    XMLWordPrintable

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL)
    • 10.5, 10.6
    • Optimizer

    Description

      CREATE TABLE t (a DATE NOT NULL, b INT) ENGINE=MyISAM;
      INSERT INTO t VALUES ('1980-01-23',1);
       
      SELECT * FROM t WHERE (a, b)     IN ( SELECT a, b FROM t HAVING b = 1 );
      SELECT * FROM t WHERE (a, b) NOT IN ( SELECT a, b FROM t HAVING b = 1 );
       
      DROP TABLE t;
      

      With the default optimizer switch (all of in_to_exists, semijoin and matherialization ON), IN query correctly returns the row, while NOT IN query incorrectly returns the same row:

      10.3 be99d0dd

      SELECT * FROM t WHERE (a, b)     IN ( SELECT a, b FROM t HAVING b = 1 );
      a	b
      1980-01-23	1
      SELECT * FROM t WHERE (a, b) NOT IN ( SELECT a, b FROM t HAVING b = 1 );
      a	b
      1980-01-23	1
      

      Plans with the default optimizer switch:

      EXPLAIN EXTENDED SELECT * FROM t WHERE (a, b)     IN ( SELECT a, b FROM t HAVING b = 1 );
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	PRIMARY	t	system	NULL	NULL	NULL	NULL	1	100.00	
      1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	7	const,const	1	100.00	
      2	MATERIALIZED	t	system	NULL	NULL	NULL	NULL	1	100.00	
      Warnings:
      Note	1003	/* select#1 */ select '1980-01-23' AS `a`,1 AS `b` from  <materialize> (/* select#2 */ select '1980-01-23',1 from dual having 1) where `<subquery2>`.`a` = '1980-01-23' and `<subquery2>`.`b` = 1
      EXPLAIN EXTENDED SELECT * FROM t WHERE (a, b) NOT IN ( SELECT a, b FROM t HAVING b = 1 );
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	PRIMARY	t	system	NULL	NULL	NULL	NULL	1	100.00	
      2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING noticed after reading const tables
      Warnings:
      Note	1003	/* select#1 */ select '1980-01-23' AS `a`,1 AS `b` from dual where !<expr_cache><'1980-01-23',1>(<in_optimizer>(('1980-01-23',1),<exists>(/* select#2 */ select '1980-01-23',1 from dual having 0)))
      

      Without semijoin or materialization the result is even worse, then the IN query incorrectly returns an empty result set, while NOT IN query incorrectly returns a row:

      SET optimizer_switch='semijoin=off';
      SELECT * FROM t WHERE (a, b)     IN ( SELECT a, b FROM t HAVING b = 1 );
      a	b
      SELECT * FROM t WHERE (a, b) NOT IN ( SELECT a, b FROM t HAVING b = 1 );
      a	b
      1980-01-23	1
      

      With in_to_exists=off the result is correct:

      SET optimizer_switch='in_to_exists=off';
      SELECT * FROM t WHERE (a, b)     IN ( SELECT a, b FROM t HAVING b = 1 );
      a	b
      1980-01-23	1
      SELECT * FROM t WHERE (a, b) NOT IN ( SELECT a, b FROM t HAVING b = 1 );
      a	b
      DROP TABLE t;
      

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.