diff options
author | Michael Widenius <monty@mariadb.org> | 2018-03-09 14:05:35 +0200 |
---|---|---|
committer | Monty <monty@mariadb.org> | 2018-03-29 13:59:44 +0300 |
commit | a7abddeffa6a760ce948c2dfb007cdf3f1a369d5 (patch) | |
tree | 70eb743fa965a17380bbc0ac88ae79ca1075b896 /mysql-test/t/subselect_innodb.test | |
parent | ab1941266c59a19703a74b5593cf3f508a5752d7 (diff) | |
download | mariadb-git-a7abddeffa6a760ce948c2dfb007cdf3f1a369d5.tar.gz |
Create 'main' test directory and move 't' and 'r' there
Diffstat (limited to 'mysql-test/t/subselect_innodb.test')
-rw-r--r-- | mysql-test/t/subselect_innodb.test | 613 |
1 files changed, 0 insertions, 613 deletions
diff --git a/mysql-test/t/subselect_innodb.test b/mysql-test/t/subselect_innodb.test deleted file mode 100644 index 544bcd994ed..00000000000 --- a/mysql-test/t/subselect_innodb.test +++ /dev/null @@ -1,613 +0,0 @@ --- source include/have_innodb.inc - -# Note: the tests uses only non-semijoin subqueries so semi-join switch -# settings are not relevant. -set @subselect_innodb_tmp=@@optimizer_switch; -set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; ---disable_warnings -drop table if exists t1,t2,t3; ---enable_warnings - -# -# key field overflow test -# -CREATE TABLE t1 -( -FOLDERID VARCHAR(32)BINARY NOT NULL -, FOLDERNAME VARCHAR(255)BINARY NOT NULL -, CREATOR VARCHAR(255)BINARY -, CREATED TIMESTAMP NOT NULL -, DESCRIPTION VARCHAR(255)BINARY -, FOLDERTYPE INTEGER NOT NULL -, MODIFIED TIMESTAMP -, MODIFIER VARCHAR(255)BINARY -, FOLDERSIZE INTEGER NOT NULL -, PARENTID VARCHAR(32)BINARY -, REPID VARCHAR(32)BINARY -, ORIGINATOR INTEGER - -, PRIMARY KEY ( FOLDERID ) -) ENGINE=InnoDB; -CREATE INDEX FFOLDERID_IDX ON t1 (FOLDERID); -CREATE INDEX CMFLDRPARNT_IDX ON t1 (PARENTID); -INSERT INTO t1 VALUES("0c9aab05b15048c59bc35c8461507deb", "System", "System", "2003-06-05 16:30:00", "The system content repository folder.", "3", "2003-06-05 16:30:00", "System", "0", NULL, "9c9aab05b15048c59bc35c8461507deb", "1"); -INSERT INTO t1 VALUES("2f6161e879db43c1a5b82c21ddc49089", "Default", "System", "2003-06-09 10:52:02", "The default content repository folder.", "3", "2003-06-05 16:30:00", "System", "0", NULL, "03eea05112b845949f3fd03278b5fe43", "1"); -INSERT INTO t1 VALUES("c373e9f5ad0791724315444553544200", "AddDocumentTest", "admin", "2003-06-09 10:51:25", "Movie Reviews", "0", "2003-06-09 10:51:25", "admin", "0", "2f6161e879db43c1a5b82c21ddc49089", "03eea05112b845949f3fd03278b5fe43", NULL); -SELECT 'c373e9f5ad0791a0dab5444553544200' IN(SELECT t1.FOLDERID FROM t1 WHERE t1.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t1.FOLDERNAME = 'Level1'); -drop table t1; - -# -# UNION unlocking test -# -create table t1 (a int) engine=innodb; -create table t2 (a int) engine=innodb; -create table t3 (a int) engine=innodb; -insert into t1 values (1),(2),(3),(4); -insert into t2 values (10),(20),(30),(40); -insert into t3 values (1),(2),(10),(50); -select a from t3 where t3.a in (select a from t1 where a <= 3 union select * from t2 where a <= 30); -drop table t1,t2,t3; - - -CREATE TABLE t1 ( - processor_id INTEGER NOT NULL, - PRIMARY KEY (processor_id) -) ENGINE=InnoDB; -CREATE TABLE t3 ( - yod_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, - login_processor INTEGER UNSIGNED , - PRIMARY KEY (yod_id) -) ENGINE=InnoDB; -CREATE TABLE t2 ( - processor_id INTEGER NOT NULL, - yod_id BIGINT UNSIGNED NOT NULL, - PRIMARY KEY (processor_id, yod_id), - INDEX (processor_id), - INDEX (yod_id), - FOREIGN KEY (processor_id) REFERENCES t1(processor_id), - FOREIGN KEY (yod_id) REFERENCES t3(yod_id) -) ENGINE=InnoDB; -INSERT INTO t1 VALUES (1),(2),(3); -INSERT INTO t3 VALUES (1,1),(2,2),(3,3); -INSERT INTO t2 VALUES (1,1),(2,2),(3,3); -SELECT distinct p1.processor_id, (SELECT y.yod_id FROM t1 p2, t2 y WHERE p2.processor_id = p1.processor_id and p2.processor_id = y.processor_id) FROM t1 p1; -drop table t2,t1,t3; - -# -# innodb locking -# -CREATE TABLE t1 ( - id int(11) NOT NULL default '0', - b int(11) default NULL, - c char(3) default NULL, - PRIMARY KEY (id), - KEY t2i1 (b) -) ENGINE=innodb DEFAULT CHARSET=latin1; -INSERT INTO t1 VALUES (0,0,'GPL'),(1,0,'GPL'),(2,1,'GPL'),(3,2,'GPL'); -CREATE TABLE t2 ( - id int(11) NOT NULL default '0', - b int(11) default NULL, - c char(3) default NULL, - PRIMARY KEY (id), - KEY t2i (b) -) ENGINE=innodb DEFAULT CHARSET=latin1; -INSERT INTO t2 VALUES (0,0,'GPL'),(1,0,'GPL'),(2,1,'GPL'),(3,2,'GPL'); -select (select max(id) from t2 where b=1 group by b) as x,b from t1 where b=1; -drop table t1,t2; - -# -# reiniting innodb tables -# -create table t1 (id int not null, value char(255), primary key(id)) engine=innodb; -create table t2 (id int not null, value char(255)) engine=innodb; -insert into t1 values (1,'a'),(2,'b'); -insert into t2 values (1,'z'),(2,'x'); -select t2.id,t2.value,(select t1.value from t1 where t1.id=t2.id) from t2; -select t2.id,t2.value,(select t1.value from t1 where t1.id=t2.id) from t2; -drop table t1,t2; - -# -# unlocking tables with subqueries in HAVING -# -create table t1 (a int, b int) engine=innodb; -insert into t1 values (1,2), (1,3), (2,3), (2,4), (2,5), (3,4), (4,5), (4,100); -create table t2 (a int) engine=innodb; -insert into t2 values (1),(2),(3),(4); -select a, sum(b) as b from t1 group by a having b > (select max(a) from t2); -drop table t1, t2; - -# -# bug #5220 test suite -# -CREATE TABLE `t1` ( `unit` varchar(50) NOT NULL default '', `ingredient` varchar(50) NOT NULL default '') ENGINE=InnoDB DEFAULT CHARSET=latin1; - -CREATE TABLE `t2` ( `ingredient` varchar(50) NOT NULL default '', `unit` varchar(50) NOT NULL default '', PRIMARY KEY (ingredient, unit)) ENGINE=InnoDB DEFAULT CHARSET=latin1; - -INSERT INTO `t1` VALUES ('xx','yy'); -INSERT INTO `t2` VALUES ('yy','xx'); - -SELECT R.unit, R.ingredient FROM t1 R WHERE R.ingredient IN (SELECT N.ingredient FROM t2 N WHERE N.unit = R.unit); - -drop table t1, t2; - -# -# possible early unlock -# -CREATE TABLE t1 ( - id INT NOT NULL auto_increment, - date1 DATE, coworkerid INT, - description VARCHAR(255), - sum_used DOUBLE, - sum_remaining DOUBLE, - comments VARCHAR(255), - PRIMARY KEY(id) -) engine=innodb; -insert into t1 values (NULL, '1999-01-01', 1,'test', 22, 33, 'comment'), (NULL, '1999-01-01', 1,'test', 22, 33, 'comment'), (NULL, '1999-01-01', 1,'test', 22, 33, 'comment'), (NULL, '1998-01-01', 1,'test', 22, 33, 'comment'), (NULL, '1998-01-01', 1,'test', 22, 33, 'comment'), (NULL, '2004-01-01', 1,'test', 22, 33, 'comment'), (NULL, '2004-01-01', 1,'test', 22, 33, 'comment'); -SELECT DISTINCT - (SELECT sum(sum_used) FROM t1 WHERE sum_used > 0 AND year(date1) <= '2004') as somallontvangsten, - (SELECT sum(sum_used) FROM t1 WHERE sum_used < 0 AND year(date1) <= '2004') as somalluitgaven - FROM t1; -select * from t1; -drop table t1; - -# -# cleaning up of results of subselects (BUG#8125) -# -CREATE TABLE `t1` ( `a` char(3) NOT NULL default '', `b` char(3) NOT NULL default '', `c` char(3) NOT NULL default '', PRIMARY KEY (`a`,`b`,`c`)) ENGINE=InnoDB; -CREATE TABLE t2 LIKE t1; -INSERT INTO t1 VALUES (1,1,1); -INSERT INTO t2 VALUES (1,1,1); -PREPARE my_stmt FROM "SELECT t1.b, count(*) FROM t1 group by t1.b having -count(*) > ALL (SELECT COUNT(*) FROM t2 WHERE t2.a=1 GROUP By t2.b)"; -EXECUTE my_stmt; -EXECUTE my_stmt; -deallocate prepare my_stmt; -drop table t1,t2; - -# End of 4.1 tests - -CREATE TABLE t1 ( - school_name varchar(45) NOT NULL, - country varchar(45) NOT NULL, - funds_requested float NOT NULL, - schooltype varchar(45) NOT NULL -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -insert into t1 values ("the school", "USA", 1200, "Human"); - -select count(country) as countrycount, sum(funds_requested) as smcnt, - country, (select sum(funds_requested) from t1) as total_funds -from t1 -group by country; - -select count(country) as countrycount, sum(funds_requested) as smcnt, - country, (select sum(funds_requested) from t1) as total_funds -from t1 -group by country; - -drop table t1; - -# -# BUG#14342: wrong placement of subquery internals in complex queries -# -CREATE TABLE `t1` ( - `t3_id` int NOT NULL, - `t1_id` int NOT NULL, - PRIMARY KEY (`t1_id`) -); -CREATE TABLE `t2` ( - `t2_id` int NOT NULL, - `t1_id` int NOT NULL, - `b` int NOT NULL, - PRIMARY KEY (`t2_id`), - UNIQUE KEY `idx_t2_t1_b` (`t1_id`,`b`) -) ENGINE=InnoDB; -CREATE TABLE `t3` ( - `t3_id` int NOT NULL -); -INSERT INTO `t3` VALUES (3); -select - (SELECT rs.t2_id - FROM t2 rs - WHERE rs.t1_id= - (SELECT lt.t1_id - FROM t1 lt - WHERE lt.t3_id=a.t3_id) - ORDER BY b DESC LIMIT 1) -from t3 AS a; -# repeat above query in SP ---disable_warnings -DROP PROCEDURE IF EXISTS p1; ---enable_warnings -delimiter //; -create procedure p1() -begin - declare done int default 3; - repeat - select - (SELECT rs.t2_id - FROM t2 rs - WHERE rs.t1_id= - (SELECT lt.t1_id - FROM t1 lt - WHERE lt.t3_id=a.t3_id) - ORDER BY b DESC LIMIT 1) as x - from t3 AS a; - set done= done-1; - until done <= 0 end repeat; -end// -delimiter ;// -call p1(); -call p1(); -call p1(); -drop procedure p1; -drop tables t1,t2,t3; - ---echo # ---echo # Bug #58756 ---echo # Crash in heap_rrnd on query with HAVING ... IN (subquery) + LIMIT ---echo # - -CREATE TABLE t1 ( - col_time_key time DEFAULT NULL, - col_datetime_key datetime DEFAULT NULL, - col_varchar_nokey varchar(1) DEFAULT NULL, - KEY col_time_key (col_time_key), - KEY col_datetime_key (col_datetime_key) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; - -INSERT INTO t1 VALUES ('17:53:30','2005-11-10 12:40:29','h'); -INSERT INTO t1 VALUES ('11:35:49','2009-04-25 00:00:00','b'); -INSERT INTO t1 VALUES (NULL,'2002-11-27 00:00:00','s'); -INSERT INTO t1 VALUES ('06:01:40','2004-01-26 20:32:32','e'); -INSERT INTO t1 VALUES ('05:45:11','2007-10-26 11:41:40','j'); -INSERT INTO t1 VALUES ('00:00:00','2005-10-07 00:00:00','e'); -INSERT INTO t1 VALUES ('00:00:00','2000-07-15 05:00:34','f'); -INSERT INTO t1 VALUES ('06:11:01','2000-04-03 16:33:32','v'); -INSERT INTO t1 VALUES ('13:02:46',NULL,'x'); -INSERT INTO t1 VALUES ('21:44:25','2001-04-25 01:26:12','m'); -INSERT INTO t1 VALUES ('22:43:58','2000-12-27 00:00:00','c'); - -CREATE TABLE t2 ( - col_time_key time DEFAULT NULL, - col_datetime_key datetime DEFAULT NULL, - col_varchar_nokey varchar(1) DEFAULT NULL, - KEY col_time_key (col_time_key), - KEY col_datetime_key (col_datetime_key) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; - -INSERT INTO t2 VALUES ('11:28:45','2004-10-11 18:13:16','w'); - -SELECT col_time_key, col_datetime_key -FROM -( SELECT * FROM t1 ) AS table1 -HAVING ( 'r' , 'e' ) IN - ( SELECT col_varchar_nokey , col_varchar_nokey FROM t2 ) -ORDER BY col_datetime_key -LIMIT 10; - -DROP TABLE t1; -DROP TABLE t2; - ---echo # End of Bug #58756 - ---echo # ---echo # Bug#60085 crash in Item::save_in_field() with time data type ---echo # - -CREATE TABLE t1(a date, b int, unique(b), unique(a), key(b)) engine=innodb; -INSERT INTO t1 VALUES ('2011-05-13', 0); -SELECT * FROM t1 WHERE b < (SELECT CAST(a as date) FROM t1 GROUP BY a); -DROP TABLE t1; - ---echo # ---echo # Bug #11766300 59387: FAILING ASSERTION: CURSOR->POS_STATE == 1997660512 (BTR_PCUR_IS_POSITIONE ---echo # - -CREATE TABLE t1 (a INT) ENGINE=INNODB; -INSERT INTO t1 VALUES (0); -CREATE TABLE t2 (d BINARY(2), PRIMARY KEY (d(1)), UNIQUE KEY (d)) ENGINE=INNODB; - -SELECT 1 FROM t1 WHERE NOT EXISTS -(SELECT 1 FROM t2 WHERE d = (SELECT d FROM t2 WHERE a >= 1) ORDER BY d); - -EXPLAIN SELECT 1 FROM t1 WHERE NOT EXISTS -(SELECT 1 FROM t2 WHERE d = (SELECT d FROM t2 WHERE a >= 1) ORDER BY d); - -DROP TABLE t2; - -CREATE TABLE t2 (b INT, c INT, UNIQUE KEY (b), UNIQUE KEY (b, c )) ENGINE=INNODB; -INSERT INTO t2 VALUES (1, 1); - -SELECT 1 FROM t1 -WHERE a != (SELECT 1 FROM t2 WHERE a <=> b OR a > '' AND 6 = 7 ORDER BY b, c); - -DROP TABLE t1, t2; - ---echo # ---echo # Bug #13639204 64111: CRASH ON SELECT SUBQUERY WITH NON UNIQUE ---echo # INDEX ---echo # -CREATE TABLE t1 ( -id int -) ENGINE=InnoDB; -INSERT INTO t1 (id) VALUES (11); - -CREATE TABLE t2 ( -t1_id int, -position int, -KEY t1_id (t1_id), -KEY t1_id_position (t1_id,position) -) ENGINE=InnoDB; - -let $query=SELECT -(SELECT position FROM t2 -WHERE t2.t1_id = t1.id -ORDER BY t2.t1_id , t2.position -LIMIT 10,1 -) AS maxkey -FROM t1 -LIMIT 1; - -eval EXPLAIN $query; -eval $query; - -DROP TABLE t1,t2; - ---echo End of 5.1 tests - ---echo # ---echo # lp:827416 Crash in select_describe() on EXPLAIN with DISTINCT in nested subqueries ---echo # - -CREATE TABLE t3 ( b int) ENGINE=InnoDB; -CREATE TABLE t2 ( c int) ENGINE=InnoDB; -CREATE TABLE t1 ( a int NOT NULL , PRIMARY KEY (a)) ENGINE=InnoDB; - -EXPLAIN SELECT * -FROM t1 -WHERE t1.a = ( - SELECT SUM( c ) - FROM t2 - WHERE (SELECT DISTINCT b FROM t3) > 0); -SELECT * -FROM t1 -WHERE t1.a = ( - SELECT SUM( c ) - FROM t2 - WHERE (SELECT DISTINCT b FROM t3) > 0); - -DROP TABLE t1, t2, t3; - - ---echo # ---echo # lp:858148 Fourth crash in select_describe() with nested subqueries ---echo # - -CREATE TABLE t1 ( f1 int(11)) ENGINE=InnoDB; -CREATE TABLE t2 ( f1 int(11), f2 int(11), PRIMARY KEY (f1)) ; -CREATE TABLE t3 ( f3 int(11)) ENGINE=InnoDB; - -EXPLAIN -SELECT MAX( f1 ) FROM t2 -WHERE f2 >= ( - SELECT SUM( f1 ) - FROM t1 - WHERE EXISTS ( - SELECT f3 - FROM t3 - GROUP BY 1 - ) -); - -SELECT MAX( f1 ) FROM t2 -WHERE f2 >= ( - SELECT SUM( f1 ) - FROM t1 - WHERE EXISTS ( - SELECT f3 - FROM t3 - GROUP BY 1 - ) -); - -drop table t1, t2, t3; - ---echo # ---echo # LP BUG#1006231 crash in select_describe ---echo # - -create table t1(a1 int) ENGINE=InnoDB; -insert into t1 values (1); -explain -select 1 from t1 where 1 like (select 1 from t1 where 1 <=> (select 1 from t1 group by a1)); -select 1 from t1 where 1 like (select 1 from t1 where 1 <=> (select 1 from t1 group by a1)); -drop table t1; - - ---echo # ---echo # MDEV-3988 crash in create_tmp_table ---echo # - -drop table if exists `t1`,`t2`; -create table `t1`(`a` char(1) character set utf8)engine=innodb; -create table `t2`(`b` char(1) character set utf8)engine=memory; -select distinct (select 1 from `t2` where `a`) `d2` from `t1`; -select distinct (select 1 from `t2` where `a`) `d2`, a from `t1`; -select distinct a, (select 1 from `t2` where `a`) `d2` from `t1`; -select distinct (1 + (select 1 from `t2` where `a`)) `d2` from `t1`; - -drop table t1,t2; - ---echo # ---echo # MDEV-4042: Assertion `table->key_read == 0' fails in close_thread_table on EXPLAIN with GROUP BY and HAVING in EXISTS SQ, ---echo # MDEV-4536: ...sql/sql_base.cc:1598: bool close_thread_table(THD*, TABLE**): Assertion `table->key_read == 0' failed. -CREATE TABLE t1 (a INT) ENGINE=InnoDB; -CREATE TABLE t2 (b INT PRIMARY KEY, c INT) ENGINE=InnoDB; -CREATE TABLE t3 (d INT) ENGINE=InnoDB; - -EXPLAIN -SELECT * FROM t1 WHERE EXISTS ( SELECT b FROM t2, t3 GROUP BY b HAVING b != 3 ); -SELECT * FROM t1 WHERE EXISTS ( SELECT b FROM t2, t3 GROUP BY b HAVING b != 3 ); - -DROP TABLE t1,t2,t3; - -CREATE TABLE t1 ( - pk int auto_increment primary key, - col_int_key int(11), - key col_int_key (col_int_key),col_varchar_key varchar(128), - key (col_varchar_key) -) engine=innodb; - -EXPLAIN -SELECT 1 FROM t1 AS alias1 -WHERE EXISTS ( SELECT SQ2_alias1 . col_int_key AS SQ2_field1 - FROM ( t1 AS SQ2_alias1 RIGHT OUTER JOIN - t1 AS SQ2_alias2 ON (SQ2_alias2 . col_int_key = SQ2_alias1 . col_int_key ) - ) - GROUP BY SQ2_field1 - HAVING SQ2_alias1 . col_int_key >= 7 - ); - -SELECT 1 FROM t1 AS alias1 -WHERE EXISTS ( SELECT SQ2_alias1 . col_int_key AS SQ2_field1 - FROM ( t1 AS SQ2_alias1 RIGHT OUTER JOIN - t1 AS SQ2_alias2 ON (SQ2_alias2 . col_int_key = SQ2_alias1 . col_int_key ) - ) - GROUP BY SQ2_field1 - HAVING SQ2_alias1 . col_int_key >= 7 - ); -drop table t1; - - -set optimizer_switch=@subselect_innodb_tmp; - ---echo # ---echo # MDEV-9635:Server crashes in part_of_refkey or assertion ---echo # `!created && key_to_save < (int)s->keys' failed in ---echo # TABLE::use_index(int) or with join_cache_level>2 ---echo # - -SET join_cache_level=3; - -CREATE TABLE t1 (f1 VARCHAR(1024)) ENGINE=InnoDB; -CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; - -CREATE TABLE t2 (f2 VARCHAR(4)) ENGINE=InnoDB; -INSERT INTO t2 VALUES ('foo'),('bar'); - -SELECT * FROM v1, t2 WHERE ( f1, f2 ) IN ( SELECT f1, f1 FROM t1 ); - -set join_cache_level = default; -drop view v1; -drop table t1,t2; - ---echo # ---echo # MDEV-10693: cost-based choice between materialization and in-to-exists ---echo # for a subquery from the expression used in ref access ---echo # - ---source include/have_innodb.inc - -CREATE TABLE t1 (i1 INT PRIMARY KEY) ENGINE=InnoDB; -INSERT INTO t1 VALUES (1),(2); - -CREATE TABLE t2 (i2 INT) ENGINE=InnoDB; - -CREATE TABLE t3 (i3 INT PRIMARY KEY) ENGINE=InnoDB; -INSERT INTO t3 VALUES (3); - -SELECT * FROM t1 - WHERE NULL IN ( SELECT i2 FROM t2 - WHERE i1 IN ( i2 IN ( SELECT i3 FROM t3 ) ) AND i2 = 2 ); - -DROP TABLE t1,t2,t3; - ---echo # ---echo # MDEV-6041: ORDER BY+subqueries: subquery_table.key=outer_table.col is not recongized as binding ---echo # -create table t1(a int) engine=innodb; -insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); - -create table t2( - id int primary key, - key1 int, - col1 int, - key(key1) -) engine=innodb; - -insert into t2 - select - A.a + B.a*10 + C.a*100 + D.a* 1000, - A.a + 10*B.a, - 123456 -from t1 A, t1 B, t1 C, t1 D; - ---echo # Table tsubq: ---echo # - must use 'ref' (not 'index'), and must not use 'Using filesort' ---echo # - shows a bad estimate for 'rows' (but I'm not sure if one can do better w/o histograms) -explain select - (SELECT - concat(id, '-', key1, '-', col1) - FROM t2 - WHERE t2.key1 = t1.a - ORDER BY t2.id ASC LIMIT 1) -from - t1; - ---echo # ---echo # MDEV-6081: ORDER BY+ref(const): selectivity is very incorrect (MySQL Bug#14338686) ---echo # - -alter table t2 add key2 int; -update t2 set key2=key1; -alter table t2 add key(key2); -analyze table t2; -flush tables; ---echo # Table tsubq must use 'ref' + Using filesort (not 'index' w/o filesort) ---replace_column 9 # -explain select - (SELECT - concat(id, '-', key1, '-', col1) - FROM t2 - WHERE t2.key1 = t1.a - ORDER BY t2.key2 ASC LIMIT 1) -from - t1; - -drop table t1,t2; - ---echo # ---echo # mdev-12931: semi-join in ON expression of STRAIGHT_JOIN ---echo # joining a base table and a mergeable derived table ---echo # - -CREATE TABLE t1 (f1 int) ENGINE=InnoDB; -INSERT INTO t1 VALUES (3),(2); - -CREATE TABLE t2 (f2 int) ENGINE=InnoDB; -INSERT INTO t2 VALUES (1),(4); - -CREATE TABLE t3 (f3 int) ENGINE=InnoDB; -INSERT INTO t3 VALUES (5),(6); - -CREATE TABLE t4 (f4 int) ENGINE=InnoDB; -INSERT INTO t4 VALUES (1),(8); - -SELECT * -FROM t1 - INNER JOIN - ( t2 STRAIGHT_JOIN ( SELECT * FROM t3 ) AS sq - ON ( 1 IN ( SELECT f4 FROM t4 ) ) ) - ON ( f1 >= f2 ); - -EXPLAIN EXTENDED -SELECT * -FROM t1 - INNER JOIN - ( t2 STRAIGHT_JOIN ( SELECT * FROM t3 ) AS sq - ON ( 1 IN ( SELECT f4 FROM t4 ) ) ) - ON ( f1 >= f2 ); - -DROP TABLE t1,t2,t3,t4; - |