[Commits] 0a3a810adae: Test result updates

revision-id: 0a3a810adaec017a3f02080880b0deeb1dae83b2 (mariadb-10.6.1-358-g0a3a810adae) parent(s): 330121d4ea8164d88a095bcd65df86c0d928133d author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-03-15 15:13:13 +0300 message: Test result updates --- .../suite/encryption/r/tempfiles_encrypted.result | 88 ++++++------- .../suite/engines/funcs/r/rpl_get_lock.result | 4 +- .../engines/iuds/r/strings_update_delete.result | 4 +- .../federated/federatedx_create_handlers.result | 56 ++++----- mysql-test/suite/federated/optimizer.result | 4 +- mysql-test/suite/innodb/r/innodb.result | 140 ++++++++++----------- mysql-test/suite/innodb/t/innodb.test | 30 ++--- mysql-test/suite/rpl/r/rpl_get_lock.result | 4 +- mysql-test/suite/rpl/r/rpl_master_pos_wait.result | 4 +- .../suite/versioning/r/delete_history.result | 4 +- mysql-test/suite/versioning/r/select.result | 36 +++--- 11 files changed, 187 insertions(+), 187 deletions(-) diff --git a/mysql-test/suite/encryption/r/tempfiles_encrypted.result b/mysql-test/suite/encryption/r/tempfiles_encrypted.result index 36dbbcbf58b..261f7383b56 100644 --- a/mysql-test/suite/encryption/r/tempfiles_encrypted.result +++ b/mysql-test/suite/encryption/r/tempfiles_encrypted.result @@ -3136,16 +3136,16 @@ sum(max(id)) over (order by max(id)) 4 explain select sum(max(id)) over (order by max(id)) from t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 Using temporary create index idx on t1(id); select sum(max(id)) over (order by max(id)) from t1; sum(max(id)) over (order by max(id)) 4 explain select sum(max(id)) over (order by max(id)) from t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used select sum(max(id)) over (order by max(id)) from t1 where id < 3; sum(max(id)) over (order by max(id)) 2 @@ -3468,8 +3468,8 @@ drop table t1; create table t1 (a int); explain select distinct 1, row_number() over (order by 1) from t1 where a=0 group by a with rollup; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables select distinct 1, row_number() over (order by 1) from t1 where a=0 group by a with rollup; 1 row_number() over (order by 1) drop table t1; @@ -3477,8 +3477,8 @@ explain SELECT DISTINCT BIT_OR(100) OVER () FROM dual GROUP BY LEFT('2018-08-24', 100) WITH ROLLUP HAVING @A := 'qwerty'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible HAVING +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING SELECT DISTINCT BIT_OR(100) OVER () FROM dual GROUP BY LEFT('2018-08-24', 100) WITH ROLLUP HAVING @A := 'qwerty'; @@ -3487,8 +3487,8 @@ explain SELECT DISTINCT BIT_OR(100) OVER () FROM dual GROUP BY LEFT('2018-08-24', 100) HAVING @A := 'qwerty'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible HAVING +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING SELECT DISTINCT BIT_OR(100) OVER () FROM dual GROUP BY LEFT('2018-08-24', 100) HAVING @A := 'qwerty'; @@ -3497,8 +3497,8 @@ create table t1 (a int); explain SELECT DISTINCT BIT_OR(100) OVER () FROM t1 GROUP BY LEFT('2018-08-24', 100) having 1=1 limit 0; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Zero limit +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Zero limit drop table t1; # # MDEV-13170: Database service (MySQL) stops after update with trigger @@ -3541,8 +3541,8 @@ CREATE TABLE t1 (b1 text NOT NULL); INSERT INTO t1 VALUES ('2'),('1'); EXPLAIN SELECT DISTINCT MIN(b1) OVER () FROM t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 Using temporary SELECT DISTINCT MIN(b1) OVER () FROM t1; MIN(b1) OVER () 1 @@ -3554,9 +3554,9 @@ create table t1 (id int, n1 int); insert into t1 values (1,1), (2,1), (3,2), (4,4); create view v1 as SELECT ifnull(max(n1) over (partition by n1),'aaa') FROM t1; explain select * from v1; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 -2 DERIVED t1 ALL NULL NULL NULL NULL 4 Using temporary +id select_type table partitions type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 4 +2 DERIVED t1 NULL ALL NULL NULL NULL NULL 4 Using temporary select * from v1; ifnull(max(n1) over (partition by n1),'aaa') 1 @@ -3602,9 +3602,9 @@ explain (SELECT AVG(0) OVER (), MAX('2') FROM t1) UNION ALL (SELECT AVG(0) OVER (), MAX('2') FROM t1); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 UNION NULL NULL NULL NULL NULL NULL NULL No tables used +id select_type table partitions type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used (SELECT AVG(0) OVER (), MAX('2') FROM t1) UNION ALL (SELECT AVG(0) OVER (), MAX('2') FROM t1); @@ -3619,16 +3619,16 @@ CREATE TABLE t1 (b1 int, b2 int); INSERT INTO t1 VALUES (1,1),(0,0); explain SELECT b1 from t1 order by row_number() over (ORDER BY b2) + 1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 Using temporary; Using filesort SELECT b1 from t1 order by row_number() over (ORDER BY b2) + 1; b1 0 1 explain SELECT b1 from t1 order by row_number() over (ORDER BY b2); -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 Using temporary; Using filesort SELECT b1 from t1 order by row_number() over (ORDER BY b2); b1 0 @@ -3638,8 +3638,8 @@ CREATE TABLE t1 (a int, b int, c int); INSERT INTO t1 VALUES (2,3,207), (1,21,909), (7,13,312), (8,64,248); explain SELECT * FROM t1 ORDER BY max(t1.a) over (partition by c); -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 4 Using temporary; Using filesort SELECT * FROM t1 ORDER BY max(t1.a) over (partition by c); a b c 1 21 909 @@ -3648,8 +3648,8 @@ a b c 8 64 248 explain SELECT max(t1.a) over (partition by c) as x, b, c from t1 order by max(t1.a) over (partition by c); -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 4 Using temporary; Using filesort SELECT max(t1.a) over (partition by c) as x, b, c from t1 order by max(t1.a) over (partition by c); x b c 1 21 909 @@ -3691,8 +3691,8 @@ create table t1 (id int, n1 int); insert into t1 values (1,1),(2,1),(3,2),(4,4); explain select max(n1) over (partition by 'abc') from t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 4 Using temporary select max(n1) over (partition by 'abc') from t1; max(n1) over (partition by 'abc') 4 @@ -3701,8 +3701,8 @@ max(n1) over (partition by 'abc') 4 explain select rank() over (partition by 'abc' order by 'xyz') from t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 4 Using temporary select rank() over (partition by 'abc' order by 'xyz') from t1; rank() over (partition by 'abc' order by 'xyz') 1 @@ -3739,8 +3739,8 @@ CREATE TABLE t1 (a INT); # explain SELECT row_number() over(), sum(1) FROM t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 system NULL NULL NULL NULL 0 Const row not found; Using temporary +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL system NULL NULL NULL NULL 0 Const row not found; Using temporary SELECT row_number() over(), sum(1) FROM t1; row_number() over() sum(1) 1 NULL @@ -3750,8 +3750,8 @@ insert into t1 values (2); # The expected result here is 1, NULL # EXPLAIN SELECT row_number() over(), sum(1) FROM t1 where a=1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables SELECT row_number() over(), sum(1) FROM t1 where a=1; row_number() over() sum(1) 1 NULL @@ -3760,8 +3760,8 @@ row_number() over() sum(1) # Empty result is expected # EXPLAIN SELECT row_number() over(), sum(1) FROM t1 where a=1 having 1=0; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible HAVING +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING SELECT row_number() over(), sum(1) FROM t1 where a=1 having 1=0; row_number() over() sum(1) # @@ -3769,8 +3769,8 @@ row_number() over() sum(1) # The expected result here is 1, 2 # EXPLAIN SELECT row_number() over(), sum(a) FROM t1 where a=2; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 system NULL NULL NULL NULL 1 Using temporary +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL system NULL NULL NULL NULL 1 Using temporary SELECT row_number() over(), sum(a) FROM t1 where a=2; row_number() over() sum(a) 1 2 @@ -3784,8 +3784,8 @@ insert into t1 values (1); # Expected result is NULL, 0, NULL # EXPLAIN SELECT MAX(a) OVER (), COUNT(a), abs(a) FROM t1 WHERE FALSE; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE SELECT MAX(a) OVER (), COUNT(a), abs(a) FROM t1 WHERE FALSE; MAX(a) OVER () COUNT(a) abs(a) NULL 0 NULL @@ -3794,8 +3794,8 @@ NULL 0 NULL # EXPLAIN SELECT MAX(1) OVER (), COUNT(a), abs(a) FROM t1 WHERE FALSE; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE SELECT MAX(1) OVER (), COUNT(a), abs(a) FROM t1 WHERE FALSE; MAX(1) OVER () COUNT(a) abs(a) 1 0 NULL diff --git a/mysql-test/suite/engines/funcs/r/rpl_get_lock.result b/mysql-test/suite/engines/funcs/r/rpl_get_lock.result index b852546e1bf..0b3af70dbc5 100644 --- a/mysql-test/suite/engines/funcs/r/rpl_get_lock.result +++ b/mysql-test/suite/engines/funcs/r/rpl_get_lock.result @@ -22,8 +22,8 @@ select is_free_lock("lock"), is_used_lock("lock") = connection_id(); is_free_lock("lock") is_used_lock("lock") = connection_id() 0 1 explain extended select is_free_lock("lock"), is_used_lock("lock"); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 select is_free_lock('lock') AS `is_free_lock("lock")`,is_used_lock('lock') AS `is_used_lock("lock")` select is_free_lock("lock2"); diff --git a/mysql-test/suite/engines/iuds/r/strings_update_delete.result b/mysql-test/suite/engines/iuds/r/strings_update_delete.result index 708d2811bf8..8004259128c 100644 --- a/mysql-test/suite/engines/iuds/r/strings_update_delete.result +++ b/mysql-test/suite/engines/iuds/r/strings_update_delete.result @@ -70461,8 +70461,8 @@ SELECT * FROM t18 WHERE c2=0; c1 c2 SIZE EXPLAIN SELECT * FROM t18 WHERE c2=0; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t18 ALL NULL NULL NULL NULL 2 Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t18 NULL ALL NULL NULL NULL NULL 2 Using where SET sql_mode= 'STRICT_ALL_TABLES'; INSERT INTO t18 VALUES('SIZE','SMALL2'); ERROR 01000: Data truncated for column 'c2' at row 1 diff --git a/mysql-test/suite/federated/federatedx_create_handlers.result b/mysql-test/suite/federated/federatedx_create_handlers.result index 6dcd53e77c7..233aa4d7ecb 100644 --- a/mysql-test/suite/federated/federatedx_create_handlers.result +++ b/mysql-test/suite/federated/federatedx_create_handlers.result @@ -90,12 +90,12 @@ id name 1 zzz EXPLAIN SELECT id FROM federated.t1 WHERE id < 5; -id select_type table type possible_keys key key_len ref rows Extra -1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL +id select_type table partitions type possible_keys key key_len ref rows Extra +1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL NULL EXPLAIN EXTENDED SELECT id FROM federated.t1 WHERE id < 5; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL NULL +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 select `federated`.`t1`.`id` AS `id` from `federated`.`t1` where `federated`.`t1`.`id` < 5 EXPLAIN FORMAT=JSON @@ -111,8 +111,8 @@ EXPLAIN } ANALYZE SELECT id FROM federated.t1 WHERE id < 5; -id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL +id select_type table partitions type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL ANALYZE FORMAT=JSON SELECT id FROM federated.t1 WHERE id < 5; ANALYZE @@ -145,10 +145,10 @@ EXPLAIN SELECT * FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t WHERE federated.t3.name=t.name; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 7 -1 PRIMARY <derived2> ref key0 key0 18 federated.t3.name 2 -2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL +id select_type table partitions type possible_keys key key_len ref rows Extra +1 PRIMARY t3 NULL ALL NULL NULL NULL NULL 7 +1 PRIMARY <derived2> NULL ref key0 key0 18 federated.t3.name 2 +2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL EXPLAIN FORMAT=JSON SELECT * FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t @@ -194,10 +194,10 @@ ANALYZE SELECT * FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t WHERE federated.t3.name=t.name; -id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 7 7.00 100.00 100.00 -1 PRIMARY <derived2> ref key0 key0 18 federated.t3.name 2 0.00 100.00 100.00 -2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL +id select_type table partitions type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t3 NULL ALL NULL NULL NULL NULL 7 7.00 100.00 100.00 +1 PRIMARY <derived2> NULL ref key0 key0 18 federated.t3.name 2 0.00 100.00 100.00 +2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL SELECT * FROM federated.t3, (SELECT t1.name FROM federated.t1 WHERE id IN (SELECT count(*) @@ -211,10 +211,10 @@ FROM federated.t3, (SELECT t1.name FROM federated.t1 WHERE id IN (SELECT count(*) FROM federated.t2 GROUP BY name)) t WHERE federated.t3.name=t.name; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 7 -1 PRIMARY <derived2> ref key0 key0 18 federated.t3.name 2 -2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL +id select_type table partitions type possible_keys key key_len ref rows Extra +1 PRIMARY t3 NULL ALL NULL NULL NULL NULL 7 +1 PRIMARY <derived2> NULL ref key0 key0 18 federated.t3.name 2 +2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL ANALYZE FORMAT=JSON SELECT * FROM federated.t3, (SELECT t1.name FROM federated.t1 @@ -290,10 +290,10 @@ FROM federated.t3, UNION SELECT * FROM federated.t1 WHERE id >= 5) t WHERE federated.t3.name=t.name; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 7 -1 PRIMARY <derived2> ref key0 key0 18 federated.t3.name 2 -2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL +id select_type table partitions type possible_keys key key_len ref rows Extra +1 PRIMARY t3 NULL ALL NULL NULL NULL NULL 7 +1 PRIMARY <derived2> NULL ref key0 key0 18 federated.t3.name 2 +2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL # # MDEV-21887: federatedx crashes on SELECT ... INTO query in select_handler code # @@ -320,8 +320,8 @@ select name into outfile 'tmp.txt' from federated.t1; explain select * from federated.t1 where name in (select name from federated.t2); -id select_type table type possible_keys key key_len ref rows Extra -1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL +id select_type table partitions type possible_keys key key_len ref rows Extra +1 PUSHED SELECT NULL NULL NULL NULL NULL NULL NULL NULL NULL explain format=json select * from federated.t1 where name in (select name from federated.t2); @@ -345,10 +345,10 @@ explain select * from t5, (select id from federated.t1 where name in (select name from federated.t2) or name like 'foo%') as TQ; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t5 ALL NULL NULL NULL NULL 2 -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) -2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL +id select_type table partitions type possible_keys key key_len ref rows Extra +1 PRIMARY t5 NULL ALL NULL NULL NULL NULL 2 +1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) +2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL # Must not show elements with select_id=3 explain format=json select * from t5, diff --git a/mysql-test/suite/federated/optimizer.result b/mysql-test/suite/federated/optimizer.result index 5d7072e0b35..2d89ac7a590 100644 --- a/mysql-test/suite/federated/optimizer.result +++ b/mysql-test/suite/federated/optimizer.result @@ -33,8 +33,8 @@ foo_id parent_foo_id foo_name 822857 NULL STRING explain select foo_id,parent_foo_id,foo_name from fed_t1 where parent_foo_id = 822857 or foo_name like 'STRING%'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE fed_t1 ALL foo_name,parent_foo_id NULL NULL NULL 6 Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE fed_t1 NULL ALL foo_name,parent_foo_id NULL NULL NULL 6 Using where select foo_id,parent_foo_id,foo_name from fed_t1 where parent_foo_id = 822857 or foo_name like 'STRING%'; foo_id parent_foo_id foo_name 968903 822857 STRING - 0 diff --git a/mysql-test/suite/innodb/r/innodb.result b/mysql-test/suite/innodb/r/innodb.result index d799cbb8fd9..668d804886d 100644 --- a/mysql-test/suite/innodb/r/innodb.result +++ b/mysql-test/suite/innodb/r/innodb.result @@ -146,14 +146,14 @@ id parent_id level 1010 102 2 1015 102 2 explain select level from t1 where level=1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref level level 1 const # Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref level level 1 const # Using index explain select level,id from t1 where level=1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref level level 1 const # Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref level level 1 const # Using index explain select level,id,parent_id from t1 where level=1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref level level 1 const # +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref level level 1 const # select level,id from t1 where level=1; level id 1 1002 @@ -616,8 +616,8 @@ id parent_id level 1025 102 2 1016 102 2 explain select level from t1 where level=1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref level level 1 const # Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref level level 1 const # Using index select level,id from t1 where level=1; level id 1 1004 @@ -785,8 +785,8 @@ DROP TABLE t1; create table t1 (a int primary key,b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int, a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, b1 int, b2 int, b3 int, b4 int, b5 int, b6 int) engine = innodb; insert into t1 values (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1); explain select * from t1 where a > 0 and a < 50; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL # Using index condition +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range PRIMARY PRIMARY 4 NULL # Using index condition drop table t1; create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=innodb; insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); @@ -921,29 +921,29 @@ drop table t1; create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=innodb; insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4); explain select * from t1 order by a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 4 NULL # +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL # explain select * from t1 order by b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # Using filesort explain select * from t1 order by c; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # Using filesort explain select a from t1 order by a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 4 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL # Using index explain select b from t1 order by b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL b 4 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index NULL b 4 NULL # Using index explain select a,b from t1 order by b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL b 4 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index NULL b 4 NULL # Using index explain select a,b from t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL b 4 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index NULL b 4 NULL # Using index explain select a,b,c from t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL # +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # drop table t1; create table t1 (t int not null default 1, key (t)) engine=innodb; desc t1; @@ -1230,12 +1230,12 @@ select count(*) from t1; count(*) 623 explain select * from t1 where c between 1 and 2500; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range c c 5 NULL # # +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range c c 5 NULL # # update t1 set c=a; explain select * from t1 where c between 1 and 2500; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL c NULL NULL NULL # # +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL c NULL NULL NULL # # drop table t1,t2; create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=innodb; insert into t1 (id) values (null),(null),(null),(null),(null); @@ -1641,8 +1641,8 @@ a b c d e 2 2 b 2 2 3 3 ab 3 3 explain select * from t1 order by a,b,c,d; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 3 Using filesort drop table t1; create table t1 (a char(1), b char(1), key(a, b)) engine=innodb; insert into t1 values ('8', '6'), ('4', '7'); @@ -1675,8 +1675,8 @@ select count(*) from t1 where x = -16; count(*) 0 explain select count(*) from t1 where x > -16; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 2 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index PRIMARY PRIMARY 8 NULL 2 Using where; Using index select count(*) from t1 where x > -16; count(*) 2 @@ -1838,23 +1838,23 @@ select count(*) from t1 where v like 'a %'; count(*) 9 explain select count(*) from t1 where v='a '; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v v 13 const # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref v v 13 const # Using where; Using index explain select count(*) from t1 where c='a '; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref c c 11 const # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref c c 11 const # Using where; Using index explain select count(*) from t1 where t='a '; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref t t 13 const # Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref t t 13 const # Using where explain select count(*) from t1 where v like 'a%'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range v v 13 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range v v 13 NULL # Using where; Using index explain select count(*) from t1 where v between 'a' and 'a '; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v v 13 const # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref v v 13 const # Using where; Using index explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v v 13 const # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref v v 13 const # Using where; Using index alter table t1 add unique(v); ERROR 23000: Duplicate entry '{ ' for key 'v_2' show warnings; @@ -1876,8 +1876,8 @@ qq *a *a*a * *a *a*a * explain select * from t1 where v='a'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v,v_2 # 13 const # # +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref v,v_2 # 13 const # # select v,count(*) from t1 group by v limit 10; v count(*) a
1 @@ -2040,20 +2040,20 @@ select count(*) from t1 where v like 'a %'; count(*) 9 explain select count(*) from t1 where v='a '; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v v 303 const # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref v v 303 const # Using where; Using index explain select count(*) from t1 where v like 'a%'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range v v 303 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range v v 303 NULL # Using where; Using index explain select count(*) from t1 where v between 'a' and 'a '; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v v 303 const # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref v v 303 const # Using where; Using index explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v v 303 const # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref v v 303 const # Using where; Using index explain select * from t1 where v='a'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v v 303 const # # +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref v v 303 const # # select v,count(*) from t1 group by v limit 10; v count(*) a
1 @@ -2120,20 +2120,20 @@ select count(*) from t1 where v like 'a %'; count(*) 9 explain select count(*) from t1 where v='a '; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v v 33 const # Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref v v 33 const # Using where explain select count(*) from t1 where v like 'a%'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range v v 33 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range v v 33 NULL # Using where explain select count(*) from t1 where v between 'a' and 'a '; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v v 33 const # Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref v v 33 const # Using where explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v v 33 const # Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref v v 33 const # Using where explain select * from t1 where v='a'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v v 33 const # # +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref v v 33 const # # select v,count(*) from t1 group by v limit 10; v count(*) a
1 @@ -2291,8 +2291,8 @@ drop table t1; create table t1(a int, b varchar(12), key ba(b, a)); insert into t1 values (1, 'A'), (20, NULL); explain select * from t1 where a=20 and b is null; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref ba ba 20 const,const 1 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref ba ba 20 const,const 1 Using where; Using index select * from t1 where a=20 and b is null; a b 20 NULL diff --git a/mysql-test/suite/innodb/t/innodb.test b/mysql-test/suite/innodb/t/innodb.test index 3a8c12dfbbd..a727e993fad 100644 --- a/mysql-test/suite/innodb/t/innodb.test +++ b/mysql-test/suite/innodb/t/innodb.test @@ -91,11 +91,11 @@ update ignore t1 set id=id+1; # This will change all rows select * from t1; update ignore t1 set id=1023 where id=1010; select * from t1 where parent_id=102; ---replace_column 9 # +--replace_column 10 # explain select level from t1 where level=1; ---replace_column 9 # +--replace_column 10 # explain select level,id from t1 where level=1; ---replace_column 9 # +--replace_column 10 # explain select level,id,parent_id from t1 where level=1; select level,id from t1 where level=1; select level,id,parent_id from t1 where level=1; @@ -406,7 +406,7 @@ update ignore t1 set id=id+1; # This will change all rows select * from t1; update ignore t1 set id=1023 where id=1010; select * from t1 where parent_id=102; ---replace_column 9 # +--replace_column 10 # explain select level from t1 where level=1; select level,id from t1 where level=1; select level,id,parent_id from t1 where level=1; @@ -512,7 +512,7 @@ DROP TABLE t1; create table t1 (a int primary key,b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int, a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, b1 int, b2 int, b3 int, b4 int, b5 int, b6 int) engine = innodb; insert into t1 values (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1); ---replace_column 9 # +--replace_column 10 # --replace_result "Using where" "Using index condition" explain select * from t1 where a > 0 and a < 50; drop table t1; @@ -640,21 +640,21 @@ drop table t1; create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=innodb; insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4); ---replace_column 9 # +--replace_column 10 # explain select * from t1 order by a; ---replace_column 9 # +--replace_column 10 # explain select * from t1 order by b; ---replace_column 9 # +--replace_column 10 # explain select * from t1 order by c; ---replace_column 9 # +--replace_column 10 # explain select a from t1 order by a; ---replace_column 9 # +--replace_column 10 # explain select b from t1 order by b; ---replace_column 9 # +--replace_column 10 # explain select a,b from t1 order by b; ---replace_column 9 # +--replace_column 10 # explain select a,b from t1; ---replace_column 9 # +--replace_column 10 # explain select a,b,c from t1; drop table t1; @@ -947,10 +947,10 @@ insert into t1 (a) select b from t2; insert into t2 (a) select b from t1; insert into t1 (a) select b from t2; select count(*) from t1; ---replace_column 9 # 10 # +--replace_column 10 # 11 # explain select * from t1 where c between 1 and 2500; update t1 set c=a; ---replace_column 9 # 10 # +--replace_column 10 # 11 # explain select * from t1 where c between 1 and 2500; drop table t1,t2; diff --git a/mysql-test/suite/rpl/r/rpl_get_lock.result b/mysql-test/suite/rpl/r/rpl_get_lock.result index b852546e1bf..0b3af70dbc5 100644 --- a/mysql-test/suite/rpl/r/rpl_get_lock.result +++ b/mysql-test/suite/rpl/r/rpl_get_lock.result @@ -22,8 +22,8 @@ select is_free_lock("lock"), is_used_lock("lock") = connection_id(); is_free_lock("lock") is_used_lock("lock") = connection_id() 0 1 explain extended select is_free_lock("lock"), is_used_lock("lock"); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 select is_free_lock('lock') AS `is_free_lock("lock")`,is_used_lock('lock') AS `is_used_lock("lock")` select is_free_lock("lock2"); diff --git a/mysql-test/suite/rpl/r/rpl_master_pos_wait.result b/mysql-test/suite/rpl/r/rpl_master_pos_wait.result index aae3418b546..5eae6bf0c1a 100644 --- a/mysql-test/suite/rpl/r/rpl_master_pos_wait.result +++ b/mysql-test/suite/rpl/r/rpl_master_pos_wait.result @@ -5,8 +5,8 @@ select master_pos_wait('master-bin.999999',0,2); master_pos_wait('master-bin.999999',0,2) -1 explain extended select master_pos_wait('master-bin.999999',0,2); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 select master_pos_wait('master-bin.999999',0,2) AS `master_pos_wait('master-bin.999999',0,2)` select master_pos_wait('master-bin.999999',0); diff --git a/mysql-test/suite/versioning/r/delete_history.result b/mysql-test/suite/versioning/r/delete_history.result index a5a6de19bc6..100346009cf 100644 --- a/mysql-test/suite/versioning/r/delete_history.result +++ b/mysql-test/suite/versioning/r/delete_history.result @@ -164,8 +164,8 @@ select * from t1; x 1 explain extended delete history from t1 before system_time '2039-01-01 23:00'; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 1 100.00 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where create or replace procedure p() delete history from t1 before system_time '2039-01-01 23:00'; call p; select * from t1; diff --git a/mysql-test/suite/versioning/r/select.result b/mysql-test/suite/versioning/r/select.result index 90c99d1bf0e..3ff676676d0 100644 --- a/mysql-test/suite/versioning/r/select.result +++ b/mysql-test/suite/versioning/r/select.result @@ -153,23 +153,23 @@ delete from t1; delete from t2; explain extended select * from (select t1.x as IJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 inner join t2 on t1.x = t2.x) for system_time as of timestamp @t0 as t; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 3 100.00 Using where +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`x` AS `IJ2_x1`,`test`.`t1`.`y` AS `y1`,`test`.`t2`.`x` AS `x2`,`test`.`t2`.`y` AS `y2` from `test`.`t1` FOR SYSTEM_TIME AS OF TIMESTAMP @`t0` join `test`.`t2` FOR SYSTEM_TIME AS OF TIMESTAMP @`t0` where `test`.`t1`.`x` = `test`.`t2`.`x` and `test`.`t2`.`row_end` > @`t0` and `test`.`t2`.`row_start` <= @`t0` and `test`.`t1`.`row_end` > @`t0` and `test`.`t1`.`row_start` <= @`t0` explain extended select * from (select t1.x as LJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x) for system_time as of timestamp @t0 as t; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 Using where +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`x` AS `LJ2_x1`,`test`.`t1`.`y` AS `y1`,`test`.`t2`.`x` AS `x2`,`test`.`t2`.`y` AS `y2` from `test`.`t1` FOR SYSTEM_TIME AS OF TIMESTAMP @`t0` left join `test`.`t2` FOR SYSTEM_TIME AS OF TIMESTAMP @`t0` on(`test`.`t2`.`x` = `test`.`t1`.`x` and `test`.`t2`.`row_end` > @`t0` and `test`.`t2`.`row_start` <= @`t0`) where `test`.`t1`.`row_end` > @`t0` and `test`.`t1`.`row_start` <= @`t0` explain extended select * from (select t1.x as RJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x) for system_time as of timestamp @t0 as t; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 3 100.00 Using where +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`x` AS `RJ2_x1`,`test`.`t1`.`y` AS `y1`,`test`.`t2`.`x` AS `x2`,`test`.`t2`.`y` AS `y2` from `test`.`t2` FOR SYSTEM_TIME AS OF TIMESTAMP @`t0` left join `test`.`t1` FOR SYSTEM_TIME AS OF TIMESTAMP @`t0` on(`test`.`t1`.`x` = `test`.`t2`.`x` and `test`.`t1`.`row_end` > @`t0` and `test`.`t1`.`row_start` <= @`t0`) where `test`.`t2`.`row_end` > @`t0` and `test`.`t2`.`row_start` <= @`t0` select * from (select t1.x as IJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 inner join t2 on t1.x = t2.x) @@ -326,10 +326,10 @@ insert into t1 values (2); insert into t1 values (3); explain extended select * from t1 left outer join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a` from `test`.`t1` left join (`test`.`t1` `t2` left join `test`.`t1` `t3` on(`test`.`t3`.`a` = `test`.`t2`.`a` and `test`.`t3`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999')) on(`test`.`t2`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999' and `test`.`t1`.`a` > 1) where `test`.`t1`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999' select * from t1 left outer join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1; @@ -556,11 +556,11 @@ insert t3 values (1),(2); insert t4 values (1),(2); explain extended select f1 from t1 join t2 left join t3 left join t4 on f3 = f4 on f3 = f2; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join) -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t4 NULL ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) Warnings: Note 1003 select `test`.`t1`.`f1` AS `f1` from `test`.`t1` join `test`.`t2` left join (`test`.`t3` left join `test`.`t4` on(`test`.`t4`.`f4` = `test`.`t2`.`f2` and `test`.`t4`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999')) on(`test`.`t3`.`f3` = `test`.`t2`.`f2`) where `test`.`t1`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999' drop view v1;