From a2a2e32c02643a0cec111511220227703fda1cd5 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Mon, 1 Jul 2024 20:15:00 +0200 Subject: Merging upstream version 1:11.4.2. Signed-off-by: Daniel Baumann --- mysql-test/main/subselect4.test | 37 +++++++------------------------------ 1 file changed, 7 insertions(+), 30 deletions(-) (limited to 'mysql-test/main/subselect4.test') diff --git a/mysql-test/main/subselect4.test b/mysql-test/main/subselect4.test index 854b3736..49484c4e 100644 --- a/mysql-test/main/subselect4.test +++ b/mysql-test/main/subselect4.test @@ -290,33 +290,6 @@ EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) AS RESULT FROM t1 GROUP B DROP TABLE t1,t2; ---echo # ---echo # BUG#45928 "Differing query results depending on MRR and ---echo # engine_condition_pushdown settings" ---echo # - -CREATE TABLE `t1` ( - `pk` int(11) NOT NULL AUTO_INCREMENT, - `time_nokey` time NOT NULL, - `varchar_key` varchar(1) NOT NULL, - `varchar_nokey` varchar(1) NOT NULL, - PRIMARY KEY (`pk`), - KEY `varchar_key` (`varchar_key`) -) AUTO_INCREMENT=12 DEFAULT CHARSET=latin1; -INSERT INTO `t1` VALUES (10,'00:00:00','i','i'),(11,'00:00:00','',''); - -set @old_optimizer_switch = @@session.optimizer_switch; - -SET SESSION OPTIMIZER_SWITCH = 'materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=on,engine_condition_pushdown=on'; - - SELECT `time_nokey` G1 FROM t1 WHERE ( `varchar_nokey` , `varchar_key` ) IN ( -SELECT `varchar_nokey` , `varchar_nokey` ) AND `varchar_key` >= 'c' HAVING G1 ORDER -BY `pk` ; - -set @@session.optimizer_switch = @old_optimizer_switch; - -DROP TABLE t1; - --echo # --echo # During work with BUG#45863 I had problems with a query that was --echo # optimized differently in regular and prepared mode. @@ -481,7 +454,7 @@ INSERT INTO t2 VALUES (9,1); --echo # Enable Index condition pushdown --replace_column 1 # set @old_icp=@@optimizer_switch; -SET SESSION optimizer_switch="engine_condition_pushdown=on"; +SET SESSION optimizer_switch="index_condition_pushdown=on"; --echo SELECT pk @@ -944,7 +917,7 @@ drop table t1, t2; CREATE TABLE t1 (c1 varchar(1) DEFAULT NULL); CREATE TABLE t2 (c1 varchar(1) DEFAULT NULL); -INSERT INTO t2 VALUES ('k'), ('d'); +INSERT INTO t2 VALUES ('k'), ('d'),('x'); CREATE TABLE t3 (c1 varchar(1) DEFAULT NULL); INSERT INTO t3 VALUES ('a'), ('b'), ('c'); CREATE TABLE t4 (c1 varchar(1) primary key); @@ -2284,9 +2257,13 @@ INSERT INTO t4 VALUES SET @save_join_cache_level=@@join_cache_level; SET join_cache_level=0; +explain select 1 +from t2 join t1 on +('i','w') not in (select t1.v1,t4.v2 from t4,t1,t3 where t3.v2 = t1.v1) LIMIT ROWS EXAMINED 10; + select 1 from t2 join t1 on -('i','w') not in (select t1.v1,t4.v2 from t4,t1,t3 where t3.v2 = t1.v1) LIMIT ROWS EXAMINED 500; +('i','w') not in (select t1.v1,t4.v2 from t4,t1,t3 where t3.v2 = t1.v1) LIMIT ROWS EXAMINED 10; SET join_cache_level= @save_join_cache_level; DROP TABLE t1,t2,t3,t4; -- cgit v1.2.3