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

Wrong result with joins in WHERE IN subquery

    XMLWordPrintable

Details

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

    Description

      Possibly the test case can be reduced further, I kept the structures close to the original dbt3, just removed unnecessary columns and rows.

      CREATE DATABASE dbt3;
      USE dbt3;
       
      CREATE TABLE `customer` (
        `c_nationkey` int(11) DEFAULT NULL,
        KEY `i_c_nationkey` (`c_nationkey`)
      ) ENGINE=MyISAM;
       
      INSERT INTO `customer` VALUES (18),(17),(8);
       
      CREATE TABLE `lineitem` (
        `l_orderkey` int(11) NOT NULL DEFAULT 0,
        `l_partkey` int(11) DEFAULT NULL,
        `l_linenumber` int(11) NOT NULL DEFAULT 0,
        PRIMARY KEY (`l_orderkey`,`l_linenumber`)
      ) ENGINE=MyISAM;
       
      INSERT INTO `lineitem` VALUES (1,1,1),(1,1,4),(3,1,1);
       
      CREATE TABLE `nation` (
        `n_nationkey` int(11) NOT NULL,
        `n_regionkey` int(11) DEFAULT NULL,
        PRIMARY KEY (`n_nationkey`),
        KEY `i_n_regionkey` (`n_regionkey`)
      ) ENGINE=MyISAM;
       
      INSERT INTO `nation` VALUES (17,1),(24,1);
       
      CREATE TABLE `orders` (
        `o_orderkey` int(11) NOT NULL,
        `o_totalprice` double DEFAULT NULL,
        PRIMARY KEY (`o_orderkey`)
      ) ENGINE=MyISAM;
       
      INSERT INTO `orders` VALUES
      (1,125405.67),(2,36349.29),(3,150931.2),(4,28568.05),(5,80601.73);
       
      CREATE TABLE `partsupp` (
        `ps_partkey` int(11) NOT NULL DEFAULT 0,
        `ps_suppkey` int(11) NOT NULL DEFAULT 0,
        PRIMARY KEY (`ps_partkey`,`ps_suppkey`)
      ) ENGINE=MyISAM;
       
      INSERT INTO `partsupp` VALUES (1,1),(17,1);
       
      CREATE TABLE `region` (
        `r_regionkey` int(11) NOT NULL,
        PRIMARY KEY (`r_regionkey`)
      ) ENGINE=MyISAM;
       
      INSERT INTO `region` VALUES (0),(1),(2),(3),(4);
       
      CREATE TABLE `supplier` (
        `s_suppkey` int(11) NOT NULL,
        `s_nationkey` int(11) DEFAULT NULL,
        `s_acctbal` double DEFAULT NULL,
        PRIMARY KEY (`s_suppkey`)
      ) ENGINE=MyISAM;
       
      INSERT INTO `supplier` VALUES (1,17,5755.94);
       
      SELECT n_nationkey FROM nation LEFT JOIN region ON ( r_regionkey = n_regionkey ) JOIN supplier ON ( s_nationkey = n_nationkey ) JOIN partsupp ON ( s_suppkey = ps_suppkey ) JOIN lineitem ON ( ps_partkey = l_partkey ) JOIN orders ON ( l_orderkey = o_orderkey ) JOIN customer WHERE o_totalprice = 151 OR c_nationkey = s_nationkey AND s_acctbal > 187;
       
      CREATE TABLE t (a INT);
      INSERT INTO t VALUES (1),(17),(2),(17);
       
      SELECT * FROM t WHERE a IN (
        SELECT n_nationkey FROM nation LEFT JOIN region ON ( r_regionkey = n_regionkey ) JOIN supplier ON ( s_nationkey = n_nationkey ) JOIN partsupp ON ( s_suppkey = ps_suppkey ) JOIN lineitem ON ( ps_partkey = l_partkey ) JOIN orders ON ( l_orderkey = o_orderkey ) JOIN customer WHERE o_totalprice = 151 OR c_nationkey = s_nationkey AND s_acctbal > 187
      );
       
      # Cleanup
      DROP DATABASE dbt3;
      

      SELECT n_nationkey FROM nation LEFT JOIN region ON ( r_regionkey = n_regionkey ) JOIN supplier ON ( s_nationkey = n_nationkey ) JOIN partsupp ON ( s_suppkey = ps_suppkey ) JOIN lineitem ON ( ps_partkey = l_partkey ) JOIN orders ON ( l_orderkey = o_orderkey ) JOIN customer WHERE o_totalprice = 151 OR c_nationkey = s_nationkey AND s_acctbal > 187;
      n_nationkey
      17
      17
      17
      CREATE TABLE t (a INT);
      INSERT INTO t VALUES (1),(17),(2),(17);
      SELECT * FROM t WHERE a IN (
      SELECT n_nationkey FROM nation LEFT JOIN region ON ( r_regionkey = n_regionkey ) JOIN supplier ON ( s_nationkey = n_nationkey ) JOIN partsupp ON ( s_suppkey = ps_suppkey ) JOIN lineitem ON ( ps_partkey = l_partkey ) JOIN orders ON ( l_orderkey = o_orderkey ) JOIN customer WHERE o_totalprice = 151 OR c_nationkey = s_nationkey AND s_acctbal > 187
      );
      a
      

      So, the first SELECT above returns rows with value 17.
      The table t contains values 17.
      However, when the same SELECT is used as IN subquery, the resulting query doesn't return anything.

      Plan:

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	PRIMARY	supplier	system	PRIMARY	NULL	NULL	NULL	1	100.00	
      1	PRIMARY	nation	const	PRIMARY	PRIMARY	4	const	1	100.00	
      1	PRIMARY	region	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
      1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	1	100.00	Using where
      1	PRIMARY	t	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where; Using join buffer (flat, BNL join)
      2	MATERIALIZED	lineitem	ALL	PRIMARY	NULL	NULL	NULL	3	100.00	Using where
      2	MATERIALIZED	orders	eq_ref	PRIMARY	PRIMARY	4	dbt3.lineitem.l_orderkey	1	100.00	
      2	MATERIALIZED	customer	index	i_c_nationkey	i_c_nationkey	5	NULL	3	100.00	Using where; Using index; Using join buffer (flat, BNL join)
      2	MATERIALIZED	partsupp	eq_ref	PRIMARY	PRIMARY	8	dbt3.lineitem.l_partkey,const	1	100.00	Using index
      Warnings:
      Note	1003	select `dbt3`.`t`.`a` AS `a` from `dbt3`.`t` semi join (`dbt3`.`nation` left join `dbt3`.`region` on(1 = 1) join `dbt3`.`partsupp` join `dbt3`.`lineitem` join `dbt3`.`orders` join `dbt3`.`customer`) where `dbt3`.`t`.`a` = 17 and `dbt3`.`orders`.`o_orderkey` = `dbt3`.`lineitem`.`l_orderkey` and `dbt3`.`partsupp`.`ps_partkey` = `dbt3`.`lineitem`.`l_partkey` and `dbt3`.`partsupp`.`ps_suppkey` = 1 and (`dbt3`.`orders`.`o_totalprice` = 151 or `dbt3`.`customer`.`c_nationkey` = 17 and `dbt3`.`t`.`a` = 17)
      

      Attachments

        Activity

          People

            igor Igor Babaev
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            5 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.