summaryrefslogtreecommitdiff
path: root/mysql-test/main/delete_returning.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main/delete_returning.result')
-rw-r--r--mysql-test/main/delete_returning.result213
1 files changed, 213 insertions, 0 deletions
diff --git a/mysql-test/main/delete_returning.result b/mysql-test/main/delete_returning.result
new file mode 100644
index 00000000000..3a95de0cdca
--- /dev/null
+++ b/mysql-test/main/delete_returning.result
@@ -0,0 +1,213 @@
+drop table if exists t1,t2;
+drop view if exists v1;
+drop procedure if exists p1;
+CREATE TABLE t1 (a int, b varchar(32));
+INSERT INTO t1 VALUES
+(7,'ggggggg'), (1,'a'), (3,'ccc'),
+(4,'dddd'), (1,'A'), (2,'BB'), (4,'DDDD'),
+(5,'EEEEE'), (7,'GGGGGGG'), (2,'bb');
+CREATE TABLE t1c SELECT * FROM t1;
+CREATE TABLE t2 (c int);
+INSERT INTO t2 VALUES
+(4), (5), (7), (1);
+CREATE TABLE t2c SELECT * FROM t2;
+CREATE VIEW v1 AS SELECT a, UPPER(b) FROM t1;
+DELETE FROM t1 WHERE a=2 RETURNING * ;
+a b
+2 BB
+2 bb
+SELECT * FROM t1;
+a b
+7 ggggggg
+1 a
+3 ccc
+4 dddd
+1 A
+4 DDDD
+5 EEEEE
+7 GGGGGGG
+INSERT INTO t1 VALUES (2,'BB'), (2,'bb');
+DELETE FROM t1 WHERE a=2 RETURNING b;
+b
+bb
+BB
+SELECT * FROM t1;
+a b
+7 ggggggg
+1 a
+3 ccc
+4 dddd
+1 A
+4 DDDD
+5 EEEEE
+7 GGGGGGG
+DELETE FROM t1 WHERE a=2 RETURNING c;
+ERROR 42S22: Unknown column 'c' in 'field list'
+INSERT INTO t1 VALUES (2,'BB'), (2,'bb');
+DELETE FROM t1 WHERE a=2 RETURNING a, UPPER(b);
+a UPPER(b)
+2 BB
+2 BB
+SELECT * FROM t1;
+a b
+7 ggggggg
+1 a
+3 ccc
+4 dddd
+1 A
+4 DDDD
+5 EEEEE
+7 GGGGGGG
+INSERT INTO t1 VALUES (2,'BB'), (2,'bb');
+DELETE FROM t1 WHERE a=6 RETURNING b;
+b
+SELECT * FROM t1;
+a b
+7 ggggggg
+1 a
+3 ccc
+4 dddd
+1 A
+2 bb
+4 DDDD
+5 EEEEE
+7 GGGGGGG
+2 BB
+DELETE FROM t1 WHERE a=2 RETURNING MAX(b);
+ERROR HY000: Invalid use of group function
+DELETE FROM t1 WHERE a < 5 RETURNING a, (SELECT MIN(c) FROM t2 WHERE c=a+1);
+a (SELECT MIN(c) FROM t2 WHERE c=a+1)
+1 NULL
+3 4
+4 5
+1 NULL
+2 NULL
+4 5
+2 NULL
+SELECT * FROM t1;
+a b
+7 ggggggg
+5 EEEEE
+7 GGGGGGG
+DELETE FROM t1;
+INSERT INTO t1 SELECT * FROM t1c;
+DELETE FROM t2 WHERE c < 5
+RETURNING (SELECT GROUP_CONCAT(b) FROM t1 GROUP BY a HAVING a=c);
+(SELECT GROUP_CONCAT(b) FROM t1 GROUP BY a HAVING a=c)
+dddd,DDDD
+a,A
+SELECT * FROM t2;
+c
+5
+7
+DELETE FROM t2;
+INSERT INTO t2 SELECT * FROM t2c;
+CREATE FUNCTION f(arg INT) RETURNS TEXT
+BEGIN
+RETURN (SELECT GROUP_CONCAT(b) FROM t1 WHERE a=arg);
+END|
+DELETE FROM t2 WHERE c < 5 RETURNING f(c);
+f(c)
+dddd,DDDD
+a,A
+SELECT * FROM t2;
+c
+5
+7
+DELETE FROM t2;
+INSERT INTO t2 SELECT * FROM t2c;
+DROP FUNCTION f;
+DELETE FROM v1 WHERE a < 5 RETURNING * ;
+a UPPER(b)
+1 A
+3 CCC
+4 DDDD
+1 A
+2 BB
+4 DDDD
+2 BB
+SELECT * FROM t1;
+a b
+7 ggggggg
+5 EEEEE
+7 GGGGGGG
+DELETE FROM t1;
+INSERT INTO t1 SELECT * FROM t1c;
+CREATE VIEW v11(a,c) AS SELECT a, COUNT(b) FROM t1 GROUP BY a;
+DELETE FROM v11 WHERE a < 5 RETURNING * ;
+ERROR HY000: The target table v11 of the DELETE is not updatable
+DROP VIEW v11;
+PREPARE stmt FROM
+"DELETE FROM t1 WHERE a=2 ORDER BY b LIMIT 1 RETURNING a, UPPER(b)";
+EXECUTE stmt;
+a UPPER(b)
+2 BB
+SELECT * FROM t1;
+a b
+7 ggggggg
+1 a
+3 ccc
+4 dddd
+1 A
+4 DDDD
+5 EEEEE
+7 GGGGGGG
+2 bb
+EXECUTE stmt;
+a UPPER(b)
+2 BB
+SELECT * FROM t1;
+a b
+7 ggggggg
+1 a
+3 ccc
+4 dddd
+1 A
+4 DDDD
+5 EEEEE
+7 GGGGGGG
+DEALLOCATE PREPARE stmt;
+DROP VIEW v1;
+DROP TABLE t1,t2;
+DROP TABLE t1c,t2c;
+#
+# Bug mdev-4918: DELETE ... RETURNING subquery with more than 1 row
+#
+CREATE TABLE t1 (i1 int);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (i2 int);
+INSERT INTO t2 VALUES (1),(2);
+DELETE FROM t1 ORDER BY i1 RETURNING ( SELECT i2 FROM t2 );
+ERROR 21000: Subquery returns more than 1 row
+DROP TABLE t1,t2;
+#
+# MDEV-4919: Packets out of order on a SELECT after calling a procedure with DELETE .. RETURNING
+#
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE PROCEDURE p1 (a INT)
+BEGIN
+DELETE FROM t1 WHERE i = a RETURNING *;
+INSERT INTO t1 VALUES (a);
+END |
+CALL p1(1);
+i
+1
+SELECT * FROM t1;
+i
+1
+2
+DROP PROCEDURE p1;
+DROP TABLE t1;
+#
+# MDEV-13776: DELETE ... RETURNING with sql_mode='ONLY_FULL_GROUP_BY'
+#
+set @sql_mode_save= @@sql_mode;
+set sql_mode='ONLY_FULL_GROUP_BY';
+CREATE TABLE t1 (id INT);
+INSERT INTO t1 VALUE(1),(2),(3);
+DELETE FROM t1 WHERE id > 2 RETURNING *;
+id
+3
+set sql_mode=@sql_mode_save;
+DROP TABLE t1;