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

Wrong result with NOT IN subquery from Spider table

    XMLWordPrintable

Details

    Description

      # This may not work, e.g. for in-source builds, fix the path
      --source plugin/spider/spider/include/init_spider.inc
      #--source ../storage/spider/mysql-test/spider/include/init_spider.inc
       
      SET spider_same_server_link= on;
      --eval create server s foreign data wrapper mysql options (host "127.0.0.1", database "test", user "root", port $MASTER_MYPORT);
       
      create table t (a int);
      insert into t values (2),(2),(1),(3);
      create table t_spider (a int) engine=Spider comment='wrapper "mysql", srv "s", table "t"';
       
      SELECT * FROM t WHERE a NOT IN ( SELECT a FROM t_spider );
       
      # Cleanup
      drop table t_spider, t;
      drop server s;
       
      # This may not work, e.g. for in-source builds, fix the path
      --source plugin/spider/spider/include/deinit_spider.inc
      #--source ../storage/spider/mysql-test/spider/include/deinit_spider.inc
      

      10.4 40adf52d

      SELECT * FROM t WHERE a NOT IN ( SELECT a FROM t_spider );
      a
      1
      3
      

      The expected result is obviously an empty result set.
      Plan:

      explain extended SELECT * FROM t WHERE a NOT IN ( SELECT a FROM t_spider );
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	PRIMARY	t	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
      2	MATERIALIZED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Storage engine handles GROUP BY
      Warnings:
      Note	1003	/* select#1 */ select `test`.`t`.`a` AS `a` from `test`.`t` where !<expr_cache><`test`.`t`.`a`>(<in_optimizer>(`test`.`t`.`a`,`test`.`t`.`a` in ( <materialize> (/* select#2 */ select `test`.`t_spider`.`a` from `test`.`t_spider` ), <primary_index_lookup>(`test`.`t`.`a` in <temporary table> on distinct_key where `test`.`t`.`a` = `<subquery2>`.`a`))))
      

      Attachments

        Activity

          People

            ycp Yuchen Pei
            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.