1# 2# Tests for DELETE FROM <table> ... RETURNING <expr>,... 3# 4--disable_warnings 5drop table if exists t1,t2; 6drop view if exists v1; 7drop procedure if exists p1; 8--enable_warnings 9 10CREATE TABLE t1 (a int, b varchar(32)); 11INSERT INTO t1 VALUES 12 (7,'ggggggg'), (1,'a'), (3,'ccc'), 13 (4,'dddd'), (1,'A'), (2,'BB'), (4,'DDDD'), 14 (5,'EEEEE'), (7,'GGGGGGG'), (2,'bb'); 15 16CREATE TABLE t1c SELECT * FROM t1; 17 18CREATE TABLE t2 (c int); 19INSERT INTO t2 VALUES 20 (4), (5), (7), (1); 21 22CREATE TABLE t2c SELECT * FROM t2; 23 24CREATE VIEW v1 AS SELECT a, UPPER(b) FROM t1; 25 26# DELETE FROM <table> ... RETURNING * 27 28DELETE FROM t1 WHERE a=2 RETURNING * ; 29SELECT * FROM t1; 30 31INSERT INTO t1 VALUES (2,'BB'), (2,'bb'); 32 33# DELETE FROM <table> ... RETURNING <col> 34 35DELETE FROM t1 WHERE a=2 RETURNING b; 36SELECT * FROM t1; 37 38# DELETE FROM <table> ... RETURNING <not existing col> 39--error ER_BAD_FIELD_ERROR 40DELETE FROM t1 WHERE a=2 RETURNING c; 41 42INSERT INTO t1 VALUES (2,'BB'), (2,'bb'); 43 44# DELETE FROM <table> ... RETURNING <col>, <expr> 45 46DELETE FROM t1 WHERE a=2 RETURNING a, UPPER(b); 47SELECT * FROM t1; 48 49INSERT INTO t1 VALUES (2,'BB'), (2,'bb'); 50 51# DELETE FROM <table> ... RETURNING <col> with no rows to be deleted 52 53DELETE FROM t1 WHERE a=6 RETURNING b; 54SELECT * FROM t1; 55 56# DELETE FROM <table> ... RETURNING <expr with aggr function> 57 58--error ER_INVALID_GROUP_FUNC_USE 59DELETE FROM t1 WHERE a=2 RETURNING MAX(b); 60 61# DELETE FROM <table> ... RETURNING <expr with subquery> 62 63DELETE FROM t1 WHERE a < 5 RETURNING a, (SELECT MIN(c) FROM t2 WHERE c=a+1); 64SELECT * FROM t1; 65 66DELETE FROM t1; 67INSERT INTO t1 SELECT * FROM t1c; 68 69DELETE FROM t2 WHERE c < 5 70 RETURNING (SELECT GROUP_CONCAT(b) FROM t1 GROUP BY a HAVING a=c); 71SELECT * FROM t2; 72 73DELETE FROM t2; 74INSERT INTO t2 SELECT * FROM t2c; 75 76# DELETE FROM <table> ... RETURNING <expr with function invocation> 77 78DELIMITER |; 79 80CREATE FUNCTION f(arg INT) RETURNS TEXT 81BEGIN 82 RETURN (SELECT GROUP_CONCAT(b) FROM t1 WHERE a=arg); 83END| 84 85DELIMITER ;| 86 87DELETE FROM t2 WHERE c < 5 RETURNING f(c); 88SELECT * FROM t2; 89 90DELETE FROM t2; 91INSERT INTO t2 SELECT * FROM t2c; 92 93DROP FUNCTION f; 94 95# DELETE FROM <view> ... RETURNING <col>, <col> 96 97DELETE FROM v1 WHERE a < 5 RETURNING * ; 98SELECT * FROM t1; 99 100DELETE FROM t1; 101INSERT INTO t1 SELECT * FROM t1c; 102 103# DELETE FROM <view> ... RETURNING <expr> 104 105CREATE VIEW v11(a,c) AS SELECT a, COUNT(b) FROM t1 GROUP BY a; 106-- error ER_NON_UPDATABLE_TABLE 107DELETE FROM v11 WHERE a < 5 RETURNING * ; 108DROP VIEW v11; 109 110# prepared DELETE FROM <table> ... RETURNING <expr> 111 112PREPARE stmt FROM 113"DELETE FROM t1 WHERE a=2 ORDER BY b LIMIT 1 RETURNING a, UPPER(b)"; 114EXECUTE stmt; 115SELECT * FROM t1; 116EXECUTE stmt; 117SELECT * FROM t1; 118DEALLOCATE PREPARE stmt; 119 120# Cleanup 121DROP VIEW v1; 122DROP TABLE t1,t2; 123DROP TABLE t1c,t2c; 124 125--echo # 126--echo # Bug mdev-4918: DELETE ... RETURNING subquery with more than 1 row 127--echo # 128 129CREATE TABLE t1 (i1 int); 130INSERT INTO t1 VALUES (1),(2); 131 132CREATE TABLE t2 (i2 int); 133INSERT INTO t2 VALUES (1),(2); 134 135--error ER_SUBQUERY_NO_1_ROW 136DELETE FROM t1 ORDER BY i1 RETURNING ( SELECT i2 FROM t2 ); 137 138DROP TABLE t1,t2; 139 140--echo # 141--echo # MDEV-4919: Packets out of order on a SELECT after calling a procedure with DELETE .. RETURNING 142--echo # 143CREATE TABLE t1 (i INT); 144INSERT INTO t1 VALUES (1),(2); 145--delimiter | 146CREATE PROCEDURE p1 (a INT) 147BEGIN 148 DELETE FROM t1 WHERE i = a RETURNING *; 149 INSERT INTO t1 VALUES (a); 150END | 151--delimiter ; 152 153CALL p1(1); 154SELECT * FROM t1; 155DROP PROCEDURE p1; 156DROP TABLE t1; 157 158--echo # 159--echo # MDEV-13776: DELETE ... RETURNING with sql_mode='ONLY_FULL_GROUP_BY' 160--echo # 161 162set @sql_mode_save= @@sql_mode; 163set sql_mode='ONLY_FULL_GROUP_BY'; 164 165CREATE TABLE t1 (id INT); 166INSERT INTO t1 VALUE(1),(2),(3); 167 168DELETE FROM t1 WHERE id > 2 RETURNING *; 169 170set sql_mode=@sql_mode_save; 171 172DROP TABLE t1; 173