summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/delete_use_source.result145
-rw-r--r--mysql-test/r/lowercase_view.result3
-rw-r--r--mysql-test/r/merge.result42
-rw-r--r--mysql-test/r/view.result3
-rw-r--r--mysql-test/t/delete_use_source.test168
-rw-r--r--mysql-test/t/lowercase_view.test4
-rw-r--r--mysql-test/t/merge.test60
-rw-r--r--mysql-test/t/view.test7
-rw-r--r--sql/sql_delete.cc102
-rw-r--r--sql/sql_delete.h2
-rw-r--r--sql/sql_prepare.cc4
11 files changed, 174 insertions, 366 deletions
diff --git a/mysql-test/r/delete_use_source.result b/mysql-test/r/delete_use_source.result
index c7ca43083cf..7886caf6ba4 100644
--- a/mysql-test/r/delete_use_source.result
+++ b/mysql-test/r/delete_use_source.result
@@ -1,43 +1,18 @@
-set sql_mode=oracle;
-use test;
-create or replace table tab_delete(c1 integer not null,c2 integer not null) engine=InnoDb;
-create index tab_delete_c1 on tab_delete(c1);
-create or replace view view_delete as select * from tab_delete where c1 in (0,1);
-CREATE or replace PROCEDURE gendata(a int, count int ) AS
-i INT:=0;
-BEGIN
-FOR i IN 1 .. count
-LOOP
-insert into tab_delete values (a,i);
-END LOOP;
-END;
-/
-create or replace trigger trg after delete on tab_delete for each row
-begin
-declare c int;
-begin
-if old.c1 = 1 then
-select count(*) into c from tab_delete where c1!=old.c1;
-SIGNAL SQLSTATE '45000' set table_name=c;
-end if;
-end;
-end;
-/
-set @count=500;
-call gendata(0,@count);
-call gendata(1,50);
-call gendata(2,20);
-call gendata(3,20);
-commit;
+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 3,seq from seq_1_to_20;
#
# Delete with limit (quick select - range acces)
#
start transaction;
-delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 limit 1;
+delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) = 500 limit 1;
affected rows: 1
-delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 limit 1;
+delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) = 500 limit 1;
affected rows: 0
-select count(*) from view_delete where c1=0;
+select count(*) from v1 where c1=0;
count(*)
499
rollback;
@@ -45,21 +20,21 @@ rollback;
# Delete
#
start transaction;
-delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 ;
+delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) = 500 ;
affected rows: 500
rollback;
#
# Delete with exists
#
start transaction;
-select count(*) from view_delete where c1=2;
+select count(*) from v1 where c1=2;
count(*)
0
-delete from tab_delete where c1=2 and exists(select 'x' from tab_delete b where b.c2<10);
+delete from t1 where c1=2 and exists(select 'x' from t1 b where b.c2<10);
affected rows: 20
-delete from tab_delete where c1=2 and exists(select 'x' from tab_delete b where b.c2<10);
+delete from t1 where c1=2 and exists(select 'x' from t1 b where b.c2<10);
affected rows: 0
-select count(*) from view_delete where c1=2;
+select count(*) from v1 where c1=2;
count(*)
0
rollback;
@@ -67,18 +42,15 @@ rollback;
# Delete throw a view with limit (range access)
#
start transaction;
-# Acces by range (quick_select), initied = INDEX
-# +------+--------------------+------------+-------+---------------+---------------+---------+--------------------+------+-------------+
-# | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-# +------+--------------------+------------+-------+---------------+---------------+---------+--------------------+------+-------------+
-# | 1 | PRIMARY | tab_delete | range | tab_delete_c1 | tab_delete_c1 | 4 | NULL | 550 | Using where |
-# | 2 | DEPENDENT SUBQUERY | b | ref | tab_delete_c1 | tab_delete_c1 | 4 | test.tab_delete.c1 | 73 | Using index |
-# +------+--------------------+------------+-------+---------------+---------------+---------+--------------------+------+-------------+
-delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500 limit 1;
+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
+delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 limit 1;
affected rows: 1
-delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500 limit 1;
+delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 limit 1;
affected rows: 0
-select count(*) from view_delete where c1=0;
+select count(*) from v1 where c1=0;
count(*)
499
rollback;
@@ -86,41 +58,50 @@ rollback;
# Delete throw a view (ALL access)
#
start transaction;
-# Acces by pointer, initied = RND
-# +------+--------------------+------------+------+---------------+---------------+---------+--------------------+------+-------------+
-# | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-# +------+--------------------+------------+------+---------------+---------------+---------+--------------------+------+-------------+
-# | 1 | PRIMARY | tab_delete | ALL | tab_delete_c1 | NULL | NULL | NULL | 589 | Using where |
-# | 2 | DEPENDENT SUBQUERY | b | ref | tab_delete_c1 | tab_delete_c1 | 4 | test.tab_delete.c1 | 295 | Using index |
-# +------+--------------------+------------+------+---------------+---------------+---------+--------------------+------+-------------+
-delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500 ;
+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
+delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 ;
affected rows: 500
-select count(*) from view_delete where c1=0;
+select count(*) from v1 where c1=0;
count(*)
0
rollback;
#
# Delete failed due to trigger
#
+create trigger trg after delete on t1 for each row
+begin
+declare c int;
+begin
+if old.c1 = 1 then
+select count(*) into c from t1 where c1!=old.c1;
+SIGNAL SQLSTATE '45000' set table_name=c;
+end if;
+end;
+end;
+/
start transaction;
-delete from tab_delete where c1=1 and (select count(*) from tab_delete b where b.c1=tab_delete.c1) > 0 order by c2 asc limit 10;
+delete from t1 where c1=1 and (select count(*) from t1 b where b.c1=t1.c1) > 0 order by c2 asc limit 10;
ERROR 45000: Unhandled user-defined exception condition
rollback;
start transaction;
-delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) > 0 order by c1 desc limit 100;
+delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) > 0 order by c1 desc limit 100;
ERROR 45000: Unhandled user-defined exception condition
-select c1,count(*) from tab_delete group by c1;
+select c1,count(*) from t1 group by c1;
c1 count(*)
0 500
1 50
2 20
3 20
rollback;
+drop trigger trg;
#
# Delete throw a view with returning
#
start transaction;
-delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 order by c2 asc limit 10 returning c1,c2;
+delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) = 500 order by c2 asc limit 10 returning c1,c2;
c1 c2
0 1
0 2
@@ -134,7 +115,7 @@ c1 c2
0 10
rollback;
start transaction;
-delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 order by c2 desc limit 10 returning c1,c2;
+delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) = 500 order by c2 desc limit 10 returning c1,c2;
c1 c2
0 491
0 492
@@ -147,32 +128,24 @@ c1 c2
0 499
0 500
rollback;
+drop view v1;
+drop table t1;
#
# Delete from table with more than 150000 rows
#
-insert into tab_delete select * from tab_delete;
-insert into tab_delete select * from tab_delete;
-insert into tab_delete select * from tab_delete;
-insert into tab_delete select * from tab_delete;
-insert into tab_delete select * from tab_delete;
-insert into tab_delete select * from tab_delete;
-insert into tab_delete select * from tab_delete;
-insert into tab_delete select * from tab_delete;
-select count(*) from tab_delete;
+create table t1(c1 integer not null,c2 integer not null, key (c1));
+insert t1 select 0,seq from seq_1_to_128000;
+insert t1 select 1,seq from seq_1_to_25600;
+select count(*) from t1;
count(*)
-151040
-with high memory for sort_buffer_size
-SET SESSION sort_buffer_size = 1024000;
-start transaction;
-delete from tab_delete where c1=0 and exists(select 'x' from tab_delete b where b.c1<10);
+153600
+# with a lot of memory for sort_buffer_size
+set session sort_buffer_size = 1024000;
+delete from t1 where c1=0 and exists(select 'x' from t1 b where b.c1<10);
affected rows: 128000
-rollback;
-with few memory for sort_buffer_size
-SET SESSION sort_buffer_size = 1024;
-start transaction;
-delete from tab_delete where c1=0 and exists(select 'x' from tab_delete b where b.c1<10);
+# with little memory for sort_buffer_size
+insert t1 select 0,seq from seq_1_to_128000;
+set session sort_buffer_size = 1024;
+delete from t1 where c1=0 and exists(select 'x' from t1 b where b.c1<10);
affected rows: 128000
-rollback;
-drop procedure if exists gendata;
-drop view if exists view_delete;
-drop table if exists tab_delete;
+drop table t1;
diff --git a/mysql-test/r/lowercase_view.result b/mysql-test/r/lowercase_view.result
index 3b6aeb9548b..6ccfe29b2cd 100644
--- a/mysql-test/r/lowercase_view.result
+++ b/mysql-test/r/lowercase_view.result
@@ -65,9 +65,6 @@ update v3aA set v3Aa.col1 = (select max(col1) from v2aA);
ERROR HY000: The definition of table 'v2aA' prevents operation UPDATE on table 'v3aA'
update v3aA set v3Aa.col1 = (select max(col1) from v3aA);
ERROR HY000: Table 'v3aA' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-delete from v2Aa where col1 = (select max(col1) from v1Aa);
-delete from v2aA where col1 = (select max(col1) from t1Aa);
-delete from v2Aa where col1 = (select max(col1) from v2aA);
delete v2Aa from v2aA,t2Aa where (select max(col1) from v1aA) > 0 and v2Aa.col1 = t2aA.col1;
ERROR HY000: The definition of table 'v1aA' prevents operation DELETE on table 'v2aA'
delete t1aA from t1Aa,t2Aa where (select max(col1) from v1Aa) > 0 and t1aA.col1 = t2aA.col1;
diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result
index 6da7eb38655..f55251ac199 100644
--- a/mysql-test/r/merge.result
+++ b/mysql-test/r/merge.result
@@ -3742,48 +3742,6 @@ update m1 set a = ((select max(a) from v1));
ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'm1'
update m1 set a = ((select max(a) from tmp, v1));
ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'm1'
-delete from m1 where a = (select max(a) from m1);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-delete from m1 where a = (select max(a) from m2);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-delete from m1 where a = (select max(a) from t1);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-delete from m1 where a = (select max(a) from t2);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-delete from m1 where a = (select max(a) from t3, m1);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-delete from m1 where a = (select max(a) from t3, m2);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-delete from m1 where a = (select max(a) from t3, t1);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-delete from m1 where a = (select max(a) from t3, t2);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-delete from m1 where a = (select max(a) from tmp, m1);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-delete from m1 where a = (select max(a) from tmp, m2);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-delete from m1 where a = (select max(a) from tmp, t1);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-delete from m1 where a = (select max(a) from tmp, t2);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-delete from m1 where a = (select max(a) from v1);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-delete from m1 where a = (select max(a) from tmp, v1);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
drop view v1;
drop temporary table tmp;
drop table t1, t2, t3, m1, m2;
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 50a48ade3bd..ef2e0935592 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -993,9 +993,6 @@ update v3 set v3.col1 = (select max(col1) from v2);
ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 'v3'
update v3 set v3.col1 = (select max(col1) from v3);
ERROR HY000: Table 'v3' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-delete from v2 where col1 = (select max(col1) from v1);
-delete from v2 where col1 = (select max(col1) from t1);
-delete from v2 where col1 = (select max(col1) from v2);
delete v2 from v2,t2 where (select max(col1) from v1) > 0 and v2.col1 = t2.col1;
ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 'v2'
delete t1 from t1,t2 where (select max(col1) from v1) > 0 and t1.col1 = t2.col1;
diff --git a/mysql-test/t/delete_use_source.test b/mysql-test/t/delete_use_source.test
index ddd7858ee77..a54d12c3d65 100644
--- a/mysql-test/t/delete_use_source.test
+++ b/mysql-test/t/delete_use_source.test
@@ -1,37 +1,12 @@
--- source include/have_innodb.inc
-set sql_mode=oracle;
-use test;
-create or replace table tab_delete(c1 integer not null,c2 integer not null) engine=InnoDb;
-create index tab_delete_c1 on tab_delete(c1);
-create or replace view view_delete as select * from tab_delete where c1 in (0,1);
-delimiter /;
-CREATE or replace PROCEDURE gendata(a int, count int ) AS
- i INT:=0;
-BEGIN
- FOR i IN 1 .. count
- LOOP
- insert into tab_delete values (a,i);
- END LOOP;
-END;
-/
-create or replace trigger trg after delete on tab_delete for each row
-begin
- declare c int;
- begin
- if old.c1 = 1 then
- select count(*) into c from tab_delete where c1!=old.c1;
- SIGNAL SQLSTATE '45000' set table_name=c;
- end if;
- end;
-end;
-/
-delimiter ;/
-set @count=500;
-call gendata(0,@count);
-call gendata(1,50);
-call gendata(2,20);
-call gendata(3,20);
-commit;
+--source include/have_sequence.inc
+--source include/have_innodb.inc
+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 3,seq from seq_1_to_20;
--echo #
--echo # Delete with limit (quick select - range acces)
@@ -39,10 +14,10 @@ commit;
start transaction;
--enable_info
-delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 limit 1;
-delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 limit 1;
+delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) = 500 limit 1;
+delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) = 500 limit 1;
--disable_info
-select count(*) from view_delete where c1=0;
+select count(*) from v1 where c1=0;
rollback;
--echo #
@@ -50,9 +25,8 @@ rollback;
--echo #
start transaction;
---enable_info
-delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 ;
---disable_info
+--enable_info ONCE
+delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) = 500 ;
rollback;
--echo #
@@ -60,12 +34,12 @@ rollback;
--echo #
start transaction;
-select count(*) from view_delete where c1=2;
+select count(*) from v1 where c1=2;
--enable_info
-delete from tab_delete where c1=2 and exists(select 'x' from tab_delete b where b.c2<10);
-delete from tab_delete where c1=2 and exists(select 'x' from tab_delete b where b.c2<10);
+delete from t1 where c1=2 and exists(select 'x' from t1 b where b.c2<10);
+delete from t1 where c1=2 and exists(select 'x' from t1 b where b.c2<10);
--disable_info
-select count(*) from view_delete where c1=2;
+select count(*) from v1 where c1=2;
rollback;
--echo #
@@ -73,19 +47,12 @@ rollback;
--echo #
start transaction;
---echo # Acces by range (quick_select), initied = INDEX
---echo # +------+--------------------+------------+-------+---------------+---------------+---------+--------------------+------+-------------+
---echo # | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---echo # +------+--------------------+------------+-------+---------------+---------------+---------+--------------------+------+-------------+
---echo # | 1 | PRIMARY | tab_delete | range | tab_delete_c1 | tab_delete_c1 | 4 | NULL | 550 | Using where |
---echo # | 2 | DEPENDENT SUBQUERY | b | ref | tab_delete_c1 | tab_delete_c1 | 4 | test.tab_delete.c1 | 73 | Using index |
---echo # +------+--------------------+------------+-------+---------------+---------------+---------+--------------------+------+-------------+
-# explain delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500 limit 1;
+explain delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 limit 1;
--enable_info
-delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500 limit 1;
-delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500 limit 1;
+delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 limit 1;
+delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 limit 1;
--disable_info
-select count(*) from view_delete where c1=0;
+select count(*) from v1 where c1=0;
rollback;
--echo #
@@ -93,80 +60,73 @@ rollback;
--echo #
start transaction;
---echo # Acces by pointer, initied = RND
---echo # +------+--------------------+------------+------+---------------+---------------+---------+--------------------+------+-------------+
---echo # | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---echo # +------+--------------------+------------+------+---------------+---------------+---------+--------------------+------+-------------+
---echo # | 1 | PRIMARY | tab_delete | ALL | tab_delete_c1 | NULL | NULL | NULL | 589 | Using where |
---echo # | 2 | DEPENDENT SUBQUERY | b | ref | tab_delete_c1 | tab_delete_c1 | 4 | test.tab_delete.c1 | 295 | Using index |
---echo # +------+--------------------+------------+------+---------------+---------------+---------+--------------------+------+-------------+
-# explain delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500;
---enable_info
-delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500 ;
---disable_info
-select count(*) from view_delete where c1=0;
+explain delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500;
+--enable_info ONCE
+delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 ;
+select count(*) from v1 where c1=0;
rollback;
-
--echo #
--echo # Delete failed due to trigger
--echo #
+delimiter /;
+create trigger trg after delete on t1 for each row
+begin
+ declare c int;
+ begin
+ if old.c1 = 1 then
+ select count(*) into c from t1 where c1!=old.c1;
+ SIGNAL SQLSTATE '45000' set table_name=c;
+ end if;
+ end;
+end;
+/
+delimiter ;/
+
start transaction;
---enable_info
--error ER_SIGNAL_EXCEPTION
-delete from tab_delete where c1=1 and (select count(*) from tab_delete b where b.c1=tab_delete.c1) > 0 order by c2 asc limit 10;
---disable_info
+delete from t1 where c1=1 and (select count(*) from t1 b where b.c1=t1.c1) > 0 order by c2 asc limit 10;
rollback;
start transaction;
---enable_info
--error ER_SIGNAL_EXCEPTION
-delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) > 0 order by c1 desc limit 100;
---disable_info
-select c1,count(*) from tab_delete group by c1;
+delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) > 0 order by c1 desc limit 100;
+select c1,count(*) from t1 group by c1;
rollback;
+drop trigger trg;
+
--echo #
--echo # Delete throw a view with returning
--echo #
start transaction;
-delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 order by c2 asc limit 10 returning c1,c2;
+delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) = 500 order by c2 asc limit 10 returning c1,c2;
rollback;
start transaction;
-delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 order by c2 desc limit 10 returning c1,c2;
+delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) = 500 order by c2 desc limit 10 returning c1,c2;
rollback;
+drop view v1;
+drop table t1;
--echo #
--echo # Delete from table with more than 150000 rows
--echo #
-insert into tab_delete select * from tab_delete;
-insert into tab_delete select * from tab_delete;
-insert into tab_delete select * from tab_delete;
-insert into tab_delete select * from tab_delete;
-insert into tab_delete select * from tab_delete;
-insert into tab_delete select * from tab_delete;
-insert into tab_delete select * from tab_delete;
-insert into tab_delete select * from tab_delete;
-select count(*) from tab_delete;
-
---echo with high memory for sort_buffer_size
-SET SESSION sort_buffer_size = 1024000;
-start transaction;
---enable_info
-delete from tab_delete where c1=0 and exists(select 'x' from tab_delete b where b.c1<10);
---disable_info
-rollback;
+create table t1(c1 integer not null,c2 integer not null, key (c1));
+insert t1 select 0,seq from seq_1_to_128000;
+insert t1 select 1,seq from seq_1_to_25600;
+select count(*) from t1;
---echo with few memory for sort_buffer_size
-SET SESSION sort_buffer_size = 1024;
-start transaction;
---enable_info
-delete from tab_delete where c1=0 and exists(select 'x' from tab_delete b where b.c1<10);
---disable_info
-rollback;
+--echo # with a lot of memory for sort_buffer_size
+set session sort_buffer_size = 1024000;
+--enable_info ONCE
+delete from t1 where c1=0 and exists(select 'x' from t1 b where b.c1<10);
+
+--echo # with little memory for sort_buffer_size
+insert t1 select 0,seq from seq_1_to_128000;
+set session sort_buffer_size = 1024;
+--enable_info ONCE
+delete from t1 where c1=0 and exists(select 'x' from t1 b where b.c1<10);
-drop procedure if exists gendata;
-drop view if exists view_delete;
-drop table if exists tab_delete;
+drop table t1;
diff --git a/mysql-test/t/lowercase_view.test b/mysql-test/t/lowercase_view.test
index 6d53e6d9130..4c91383db60 100644
--- a/mysql-test/t/lowercase_view.test
+++ b/mysql-test/t/lowercase_view.test
@@ -73,10 +73,6 @@ update v3aA set v3Aa.col1 = (select max(col1) from t1aA);
update v3aA set v3Aa.col1 = (select max(col1) from v2aA);
-- error 1093
update v3aA set v3Aa.col1 = (select max(col1) from v3aA);
-# Works since MDEV-12137 (no more error 1093)
-delete from v2Aa where col1 = (select max(col1) from v1Aa);
-delete from v2aA where col1 = (select max(col1) from t1Aa);
-delete from v2Aa where col1 = (select max(col1) from v2aA);
-- error 1443
delete v2Aa from v2aA,t2Aa where (select max(col1) from v1aA) > 0 and v2Aa.col1 = t2aA.col1;
-- error 1443
diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test
index c35dd39170b..95c78caf034 100644
--- a/mysql-test/t/merge.test
+++ b/mysql-test/t/merge.test
@@ -2763,66 +2763,6 @@ update m1 set a = ((select max(a) from tmp, t2));
update m1 set a = ((select max(a) from v1));
--error ER_VIEW_PREVENT_UPDATE
update m1 set a = ((select max(a) from tmp, v1));
-# Works since MDEV-12137
-# previously :ER_UPDATE_TABLE_USED
-delete from m1 where a = (select max(a) from m1);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-# previously :ER_UPDATE_TABLE_USED
-delete from m1 where a = (select max(a) from m2);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-# previously :ER_UPDATE_TABLE_USED
-delete from m1 where a = (select max(a) from t1);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-# previously :ER_UPDATE_TABLE_USED
-delete from m1 where a = (select max(a) from t2);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-
-# previously :ER_UPDATE_TABLE_USED
-delete from m1 where a = (select max(a) from t3, m1);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-# previously :ER_UPDATE_TABLE_USED
-delete from m1 where a = (select max(a) from t3, m2);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-# previously :ER_UPDATE_TABLE_USED
-delete from m1 where a = (select max(a) from t3, t1);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-# previously :ER_UPDATE_TABLE_USED
-delete from m1 where a = (select max(a) from t3, t2);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-
-# previously :ER_UPDATE_TABLE_USED
-delete from m1 where a = (select max(a) from tmp, m1);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-# previously :ER_UPDATE_TABLE_USED
-delete from m1 where a = (select max(a) from tmp, m2);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-# previously :ER_UPDATE_TABLE_USED
-delete from m1 where a = (select max(a) from tmp, t1);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-# previously :ER_UPDATE_TABLE_USED
-delete from m1 where a = (select max(a) from tmp, t2);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-
-# previously : ER_VIEW_PREVENT_UPDATE
-delete from m1 where a = (select max(a) from v1);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-# previously : ER_VIEW_PREVENT_UPDATE
-delete from m1 where a = (select max(a) from tmp, v1);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
drop view v1;
drop temporary table tmp;
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 9ed37a2bf65..00a040ccfdc 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -914,13 +914,6 @@ update v3 set v3.col1 = (select max(col1) from t1);
update v3 set v3.col1 = (select max(col1) from v2);
-- error ER_UPDATE_TABLE_USED
update v3 set v3.col1 = (select max(col1) from v3);
-# Works since MDEV-12137
-# Previously error ER_VIEW_PREVENT_UPDATE
-delete from v2 where col1 = (select max(col1) from v1);
-# Previously error ER_VIEW_PREVENT_UPDATE
-delete from v2 where col1 = (select max(col1) from t1);
-# Previously error ER_UPDATE_TABLE_USED
-delete from v2 where col1 = (select max(col1) from v2);
-- error ER_VIEW_PREVENT_UPDATE
delete v2 from v2,t2 where (select max(col1) from v1) > 0 and v2.col1 = t2.col1;
-- error ER_VIEW_PREVENT_UPDATE
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index 8d4757e6d31..a9275bd39fd 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -45,7 +45,7 @@
// end_read_record
#include "sql_partition.h" // make_used_partitions_str
-#define MEM_STRIP_BUF_SIZE current_thd->variables.sortbuff_size
+#define MEM_STRIP_BUF_SIZE thd->variables.sortbuff_size
/*
@brief
@@ -214,6 +214,22 @@ void Update_plan::save_explain_data_intern(MEM_ROOT *mem_root,
}
+static bool record_should_be_deleted(THD *thd, TABLE *table, SQL_SELECT *sel,
+ Explain_delete *explain)
+{
+ explain->tracker.on_record_read();
+ thd->inc_examined_row_count(1);
+ if (table->vfield)
+ (void) table->update_virtual_fields(table->file, VCOL_UPDATE_FOR_DELETE);
+ if (!sel || sel->skip_record(thd) > 0)
+ {
+ explain->tracker.on_record_after_where();
+ return true;
+ }
+ return false;
+}
+
+
/**
Implement DELETE SQL word.
@@ -246,13 +262,13 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
bool with_select= !select_lex->item_list.is_empty();
Explain_delete *explain;
Delete_plan query_plan(thd->mem_root);
- query_plan.index= MAX_KEY;
- query_plan.using_filesort= FALSE;
Unique * deltempfile= NULL;
- uint delete_while_scanning= 1;
- uint delete_record= 0;
+ bool delete_record, delete_while_scanning;
DBUG_ENTER("mysql_delete");
+ query_plan.index= MAX_KEY;
+ query_plan.using_filesort= FALSE;
+
create_explain_query(thd->lex, thd->mem_root);
if (open_and_lock_tables(thd, table_list, TRUE, 0))
DBUG_RETURN(TRUE);
@@ -281,7 +297,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
if (mysql_prepare_delete(thd, table_list, select_lex->with_wild,
select_lex->item_list, &conds,
- delete_while_scanning))
+ &delete_while_scanning))
DBUG_RETURN(TRUE);
if (with_select)
@@ -562,67 +578,47 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
explain= (Explain_delete*)thd->lex->explain->get_upd_del_plan();
explain->tracker.on_scan_init();
- if (delete_while_scanning == 0)
+ if (!delete_while_scanning)
{
/*
- The table we are going to delete appears in join.
- Instead of deleting the rows, first mark them deleted.
+ The table we are going to delete appears in subqueries in the where
+ clause. Instead of deleting the rows, first mark them deleted.
*/
ha_rows tmplimit=limit;
- deltempfile= new Unique (refpos_order_cmp,
- (void *) table->file,
- table->file->ref_length,
- MEM_STRIP_BUF_SIZE);
+ deltempfile= new (thd->mem_root) Unique (refpos_order_cmp, table->file,
+ table->file->ref_length,
+ MEM_STRIP_BUF_SIZE);
while (!(error=info.read_record(&info)) && !thd->killed &&
! thd->is_error())
{
- explain->tracker.on_record_read();
- thd->inc_examined_row_count(1);
- if (table->vfield)
- (void) table->update_virtual_fields(table->file,
- VCOL_UPDATE_FOR_DELETE);
- if (!select || select->skip_record(thd) > 0)
+ if (record_should_be_deleted(thd, table, select, explain))
{
- explain->tracker.on_record_after_where();
table->file->position(table->record[0]);
if ((error= deltempfile->unique_add((char*) table->file->ref)))
{
error= 1;
goto terminate_delete;
}
- if (!--tmplimit && using_limit)
- {
- break;
- }
+ if (!--tmplimit && using_limit)
+ break;
}
}
end_read_record(&info);
- if (deltempfile->get(table) ||
- table->file->ha_index_or_rnd_end() ||
- init_read_record(&info, thd, table, NULL , &deltempfile->sort, 0, 1,
- FALSE))
+ if (deltempfile->get(table) || table->file->ha_index_or_rnd_end() ||
+ init_read_record(&info, thd, table, 0, &deltempfile->sort, 0, 1, false))
{
error= 1;
goto terminate_delete;
}
- delete_record= 1;
+ delete_record= true;
}
while (!(error=info.read_record(&info)) && !thd->killed &&
! thd->is_error())
{
- if (delete_while_scanning == 1)
- {
- explain->tracker.on_record_read();
- thd->inc_examined_row_count(1);
- if (table->vfield)
- (void) table->update_virtual_fields(table->file,
- VCOL_UPDATE_FOR_DELETE);
- delete_record=(!select || select->skip_record(thd) > 0) ? 1 : 0;
- if (delete_record)
- explain->tracker.on_record_after_where();
- }
- if (delete_record == 1)
+ if (delete_while_scanning)
+ delete_record= record_should_be_deleted(thd, table, select, explain);
+ if (delete_record)
{
if (table->triggers &&
table->triggers->process_triggers(thd, TRG_EVENT_DELETE,
@@ -808,13 +804,14 @@ l
*/
int mysql_prepare_delete(THD *thd, TABLE_LIST *table_list,
uint wild_num, List<Item> &field_list, Item **conds,
- uint &delete_while_scanning)
+ bool *delete_while_scanning)
{
Item *fake_conds= 0;
SELECT_LEX *select_lex= &thd->lex->select_lex;
DBUG_ENTER("mysql_prepare_delete");
List<Item> all_fields;
+ *delete_while_scanning= true;
thd->lex->allow_sum_func= 0;
if (setup_tables_and_check_access(thd, &thd->lex->select_lex.context,
&thd->lex->select_lex.top_join_list,
@@ -834,11 +831,9 @@ l
my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias, "DELETE");
DBUG_RETURN(TRUE);
}
- {
- TABLE_LIST *duplicate;
- if ((duplicate= unique_table(thd, table_list, table_list->next_global, 0)))
- delete_while_scanning= 0;
- }
+
+ if (unique_table(thd, table_list, table_list->next_global, 0))
+ *delete_while_scanning= false;
if (select_lex->inner_refs_list.elements &&
fix_inner_refs(thd, all_fields, select_lex, select_lex->ref_pointer_array))
@@ -1002,7 +997,7 @@ multi_delete::initialize_tables(JOIN *join)
DBUG_RETURN(1);
table_map tables_to_delete_from=0;
- delete_while_scanning= 1;
+ delete_while_scanning= true;
for (walk= delete_tables; walk; walk= walk->next_local)
{
TABLE_LIST *tbl= walk->correspondent_table->find_table_for_update();
@@ -1015,7 +1010,7 @@ multi_delete::initialize_tables(JOIN *join)
in join, we need to defer delete. So the delete
doesn't interfers with the scaning of results.
*/
- delete_while_scanning= 0;
+ delete_while_scanning= false;
}
}
@@ -1051,7 +1046,7 @@ multi_delete::initialize_tables(JOIN *join)
case send_data() shouldn't delete any rows a we may touch
the rows in the deleted table many times
*/
- delete_while_scanning= 0;
+ delete_while_scanning= false;
}
}
walk= delete_tables;
@@ -1064,10 +1059,9 @@ multi_delete::initialize_tables(JOIN *join)
for (;walk ;walk= walk->next_local)
{
TABLE *table=walk->table;
- *tempfiles_ptr++= new Unique (refpos_order_cmp,
- (void *) table->file,
- table->file->ref_length,
- MEM_STRIP_BUF_SIZE);
+ *tempfiles_ptr++= new (thd->mem_root) Unique (refpos_order_cmp, table->file,
+ table->file->ref_length,
+ MEM_STRIP_BUF_SIZE);
}
init_ftfuncs(thd, thd->lex->current_select, 1);
DBUG_RETURN(thd->is_fatal_error != 0);
diff --git a/sql/sql_delete.h b/sql/sql_delete.h
index d49b0114c52..54d6cf146b5 100644
--- a/sql/sql_delete.h
+++ b/sql/sql_delete.h
@@ -28,7 +28,7 @@ template <typename T> class SQL_I_List;
int mysql_prepare_delete(THD *thd, TABLE_LIST *table_list,
uint wild_num, List<Item> &field_list, Item **conds,
- uint &delete_while_scanning);
+ bool *delete_while_scanning);
bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
SQL_I_List<ORDER> *order, ha_rows rows,
ulonglong options, select_result *result);
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index dc0dcbac43f..1095c6ead99 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -1496,7 +1496,7 @@ static bool mysql_test_delete(Prepared_statement *stmt,
uint table_count= 0;
THD *thd= stmt->thd;
LEX *lex= stmt->lex;
- uint delete_while_scanning=1;
+ bool delete_while_scanning;
DBUG_ENTER("mysql_test_delete");
if (delete_precheck(thd, table_list) ||
@@ -1526,7 +1526,7 @@ static bool mysql_test_delete(Prepared_statement *stmt,
lex->select_lex.with_wild,
lex->select_lex.item_list,
&lex->select_lex.where,
- delete_while_scanning));
+ &delete_while_scanning));
error:
DBUG_RETURN(TRUE);
}
View Lorry Controller Status