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

Wrong result with EXISTS subquery over view on 2nd execution of PS

    XMLWordPrintable

Details

    Description

      CREATE TABLE t1 (a varchar(1));
      INSERT INTO t1 VALUES ('m'),('v');
       
      CREATE TABLE t2 (b int, c varchar(1));
      INSERT INTO t2 VALUES (2,'m'),(1,'x');
       
      CREATE VIEW v2 AS SELECT * FROM t2;
       
      PREPARE stmt FROM "SELECT STRAIGHT_JOIN * FROM t1 WHERE EXISTS ( SELECT b FROM v2 WHERE c = t1.a )";
      EXECUTE stmt;
      EXECUTE stmt;
       
      # Cleanup
      DROP VIEW v2;
      DROP TABLE t1, t2;
      

      10.3 0ca3aaa7

      EXECUTE stmt;
      a
      m
      EXECUTE stmt;
      a
      

      The query is expected to return the row.

      If we change the query to EXPLAIN EXTENDED, we'll see

      EXECUTE stmt;
      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	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
      Warnings:
      Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
      Note	1003	/* select#1 */ select straight_join `test`.`t1`.`a` AS `a` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`c` from `test`.`t2` where 1 ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`c`))))
      EXECUTE stmt;
      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	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
      Warnings:
      Note	1003	/* select#1 */ select straight_join `test`.`t1`.`a` AS `a` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`b` from `test`.`t2` where 1 ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`b`))))
      

      Note `c` changing to `b`.

      Attachments

        Issue Links

          Activity

            People

              igor Igor Babaev
              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.