From 1b7a2e4ca2a774da23eac0a8b839f2e273cf5cd2 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Thu, 17 Aug 2017 17:04:31 +0200 Subject: MDEV-13510 main.delete_use_source fails in buildbot and outside with wrong plan --- mysql-test/r/delete_use_source.result | 27 +++++++++++++++------------ mysql-test/t/delete_use_source.test | 11 ++++++----- 2 files changed, 21 insertions(+), 17 deletions(-) diff --git a/mysql-test/r/delete_use_source.result b/mysql-test/r/delete_use_source.result index 7886caf6ba4..08da8901528 100644 --- a/mysql-test/r/delete_use_source.result +++ b/mysql-test/r/delete_use_source.result @@ -1,9 +1,12 @@ create table t1(c1 integer not null,c2 integer not null, key (c1)) engine=InnoDb; create view v1 as select * from t1 where c1 in (0,1); insert t1 select 0,seq from seq_1_to_500; -insert t1 select 1,seq from seq_1_to_50; -insert t1 select 2,seq from seq_1_to_20; +insert t1 select 1,seq from seq_1_to_100; +insert t1 select 2,seq from seq_1_to_50; insert t1 select 3,seq from seq_1_to_20; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK # # Delete with limit (quick select - range acces) # @@ -31,7 +34,7 @@ select count(*) from v1 where c1=2; count(*) 0 delete from t1 where c1=2 and exists(select 'x' from t1 b where b.c2<10); -affected rows: 20 +affected rows: 50 delete from t1 where c1=2 and exists(select 'x' from t1 b where b.c2<10); affected rows: 0 select count(*) from v1 where c1=2; @@ -39,13 +42,13 @@ count(*) 0 rollback; # -# Delete throw a view with limit (range access) +# Delete through a view with limit (range access) # start transaction; explain delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 limit 1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range c1 c1 4 NULL 550 Using where -2 DEPENDENT SUBQUERY b ref c1 c1 4 test.t1.c1 82 Using index +1 PRIMARY t1 ALL c1 NULL NULL NULL 502 Using where +2 DEPENDENT SUBQUERY b ref c1 c1 4 test.t1.c1 58 Using index delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 limit 1; affected rows: 1 delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 limit 1; @@ -55,13 +58,13 @@ count(*) 499 rollback; # -# Delete throw a view (ALL access) +# Delete through a view (ALL access) # start transaction; explain delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL c1 NULL NULL NULL 717 Using where -2 DEPENDENT SUBQUERY b ref c1 c1 4 test.t1.c1 82 Using index +1 PRIMARY t1 ALL c1 NULL NULL NULL 502 Using where +2 DEPENDENT SUBQUERY b ref c1 c1 4 test.t1.c1 58 Using index delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 ; affected rows: 500 select count(*) from v1 where c1=0; @@ -92,13 +95,13 @@ ERROR 45000: Unhandled user-defined exception condition select c1,count(*) from t1 group by c1; c1 count(*) 0 500 -1 50 -2 20 +1 100 +2 50 3 20 rollback; drop trigger trg; # -# Delete throw a view with returning +# Delete through a view with returning # start transaction; delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) = 500 order by c2 asc limit 10 returning c1,c2; diff --git a/mysql-test/t/delete_use_source.test b/mysql-test/t/delete_use_source.test index a54d12c3d65..93065c6fe23 100644 --- a/mysql-test/t/delete_use_source.test +++ b/mysql-test/t/delete_use_source.test @@ -4,9 +4,10 @@ create table t1(c1 integer not null,c2 integer not null, key (c1)) engine=InnoDb create view v1 as select * from t1 where c1 in (0,1); insert t1 select 0,seq from seq_1_to_500; -insert t1 select 1,seq from seq_1_to_50; -insert t1 select 2,seq from seq_1_to_20; +insert t1 select 1,seq from seq_1_to_100; +insert t1 select 2,seq from seq_1_to_50; insert t1 select 3,seq from seq_1_to_20; +analyze table t1; --echo # --echo # Delete with limit (quick select - range acces) @@ -43,7 +44,7 @@ select count(*) from v1 where c1=2; rollback; --echo # ---echo # Delete throw a view with limit (range access) +--echo # Delete through a view with limit (range access) --echo # start transaction; @@ -56,7 +57,7 @@ select count(*) from v1 where c1=0; rollback; --echo # ---echo # Delete throw a view (ALL access) +--echo # Delete through a view (ALL access) --echo # start transaction; @@ -97,7 +98,7 @@ rollback; drop trigger trg; --echo # ---echo # Delete throw a view with returning +--echo # Delete through a view with returning --echo # start transaction; -- cgit v1.2.1