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

Exists2In: ORDER BY doesn't work with exists_to_in=ON on a query with EXISTS subquery and OR condition

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.0.1
    • Component/s: None
    • Labels:
      None

      Description

      The following test case

      SET optimizer_switch = 'in_to_exists=on,exists_to_in=on';
       
      CREATE TABLE t1 (a INT, b VARCHAR(1)) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (4,'j'),(6,'v'),(3,'c');
       
      CREATE TABLE t2 (c VARCHAR(1)) ENGINE=MyISAM;
      INSERT INTO t2 VALUES ('b'),('y');
       
      SELECT a FROM t1
      WHERE EXISTS ( 
      	SELECT 1 FROM t2 WHERE c = b 
      ) OR b NOT IN ('U')  
      ORDER BY a;

      returns an unsorted result set, which is obviously wrong:

      bzr
      a
      ---
      4
      6
      3

      Minimal optimizer_switch: in_to_exists=on,exists_to_in=on or materialization=on,exists_to_in=on
      Also reproducible with the default optimizer_switch + exists_to_in=on.
      Not reproducible without exists_to_in.

      Reproducible with MyISAM, Aria, InnoDB.

      EXPLAIN (with the default optimizer_switch + exists_to_in=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	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
      Warnings:
      Note	1276	Field or reference 'test.t1.b' of SELECT #2 was resolved in SELECT #1
      Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (<expr_cache><`test`.`t1`.`b`>(<in_optimizer>(`test`.`t1`.`b`,`test`.`t1`.`b` in ( <materialize> (select `test`.`t2`.`c` from `test`.`t2` where 1 ), <primary_index_lookup>(`test`.`t1`.`b` in <temporary table> on distinct_key where ((`test`.`t1`.`b` = `<subquery2>`.`c`)))))) or (`test`.`t1`.`b` <> 'U')) order by `test`.`t1`.`a`
       

      bzr version-info (lp:~maria-captains/maria/5.5-exists2in)

      revision-id: sanja@montyprogram.com-20121004114534-4vclmqpxvhxkd7ok
      date: 2012-10-04 14:45:34 +0300
      build-date: 2012-10-05 23:05:07 +0400
      revno: 3545

        Attachments

          Activity

            People

            Assignee:
            sanja Oleksandr Byelkin
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: