From b17a4350696c646e4bc6154a27eeddf363cd39c7 Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Tue, 9 Feb 2016 02:31:47 +0300 Subject: MDEV-6859: scalar subqueries in a comparison produced unexpected result When one evaluates row-based comparison like (X, Y) = (A,B), one should first call bring_value() for the Item that returns row value. If you don't do that and just attempt to read values of X and Y, you get stale values. Semi-join/Materialization can take a row-based comparison apart and make ref access from it. In that case, we need to call bring_value() to get the index lookup components. --- mysql-test/t/subselect_sj.test | 44 ++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 44 insertions(+) (limited to 'mysql-test/t/subselect_sj.test') diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index 7b09cdc5f1c..51bed53be17 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -2724,6 +2724,50 @@ SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) ); drop table t1,t2,t3,t4; set optimizer_switch= @tmp_mdev7823; +--echo # +--echo # MDEV-6859: scalar subqueries in a comparison produced unexpected result +--echo # +set @tmp_mdev6859=@@optimizer_switch; +set optimizer_switch=default; +CREATE TABLE t1 ( + project_number varchar(50) NOT NULL, + PRIMARY KEY (project_number) +) ENGINE=MyISAM; + +INSERT INTO t1 (project_number) VALUES ('aaa'),('bbb'); + +CREATE TABLE t2 ( + id int(10) unsigned NOT NULL AUTO_INCREMENT, + project_number varchar(50) NOT NULL, + history_date date NOT NULL, + country varchar(50) NOT NULL, + PRIMARY KEY (id) +) ENGINE=MyISAM; + +INSERT INTO t2 (id, project_number, history_date, country) VALUES +(1, 'aaa', '2014-08-09', 'france'),(2, 'aaa', '2014-09-09', 'singapore'); + +CREATE TABLE t3 ( + region varchar(50) NOT NULL, + country varchar(50) NOT NULL +) ENGINE=MyISAM; + +INSERT INTO t3 (region, country) VALUES ('apac', 'singapore'),('eame', 'france'); + +SELECT SQL_NO_CACHE a.project_number +FROM t1 a +WHERE ( SELECT z.country + FROM t2 z + WHERE z.project_number = a.project_number AND z.history_date <= '2014-09-01' + ORDER BY z.id DESC LIMIT 1 + ) IN ( + SELECT r.country + FROM t3 r + WHERE r.region = 'eame' + ); + +drop table t1, t2, t3; +set optimizer_switch= @tmp_mdev6859; # The following command must be the last one the file set optimizer_switch=@subselect_sj_tmp; -- cgit v1.2.1