DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1 (a INT, b CHAR(8), pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=xengine;
INSERT INTO t1 (a,b) VALUES (10000,'foobar'),(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');
INSERT INTO t1 (a,b) SELECT a, b FROM t1;
DELETE FROM t1 WHERE b IN ('c');
SELECT a,b FROM t1;
a	b
1	a
1	a
10000	foobar
10000	foobar
2	b
2	b
4	d
4	d
5	e
5	e
DELETE FROM t1 WHERE a < 0 OR b = 'a';
SELECT a,b FROM t1;
a	b
10000	foobar
10000	foobar
2	b
2	b
4	d
4	d
5	e
5	e
DELETE FROM t1 WHERE a <= 4 ORDER BY b DESC LIMIT 1;
SELECT a,b FROM t1;
a	b
10000	foobar
10000	foobar
2	b
2	b
4	d
5	e
5	e
CREATE TABLE t2 (c CHAR(8), d INT, pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=xengine;
INSERT INTO t2 (c,d) SELECT b, a FROM t1;
SELECT c,d FROM t2;
c	d
b	2
b	2
d	4
e	5
e	5
foobar	10000
foobar	10000
DELETE t2.* FROM t1, t2 WHERE c < b AND a + d != 1;
SELECT a,b FROM t1;
a	b
10000	foobar
10000	foobar
2	b
2	b
4	d
5	e
5	e
SELECT c,d FROM t2;
c	d
foobar	10000
foobar	10000
DELETE FROM t2, t1.* USING t2, t1 WHERE c = 'foobar' and b = c;
SELECT a,b FROM t1;
a	b
2	b
2	b
4	d
5	e
5	e
SELECT c,d FROM t2;
c	d
DELETE FROM t1;
SELECT a,b FROM t1;
a	b
DROP TABLE t1, t2;
CREATE TABLE t1 (a INT, b CHAR(8), pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=xengine;
INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'),(6,'f'),(7,'g'),(8,'h'),(10000,'foobar');
INSERT INTO t1 (a,b) SELECT a, b FROM t1;
BEGIN;
DELETE FROM t1 WHERE b IN ('c');
SELECT a,b FROM t1;
a	b
1	a
1	a
10000	foobar
10000	foobar
2	b
2	b
4	d
4	d
5	e
5	e
6	f
6	f
7	g
7	g
8	h
8	h
DELETE FROM t1 WHERE a < 0 OR b = 'a';
COMMIT;
SELECT a,b FROM t1;
a	b
10000	foobar
10000	foobar
2	b
2	b
4	d
4	d
5	e
5	e
6	f
6	f
7	g
7	g
8	h
8	h
BEGIN;
DELETE FROM t1 WHERE a <= 4 ORDER BY b DESC LIMIT 1;
SAVEPOINT spt1;
DELETE FROM t1;
RELEASE SAVEPOINT spt1;
ROLLBACK;
SELECT a,b FROM t1;
a	b
10000	foobar
10000	foobar
2	b
2	b
4	d
4	d
5	e
5	e
6	f
6	f
7	g
7	g
8	h
8	h
BEGIN;
DELETE FROM t1 WHERE a <= 4 ORDER BY b DESC LIMIT 1;
SAVEPOINT spt1;
DELETE FROM t1;
INSERT INTO t1 (a,b) VALUES (1,'a');
ROLLBACK TO SAVEPOINT spt1;
ERROR HY000: XEngine currently does not support ROLLBACK TO SAVEPOINT if modifying rows.
COMMIT;
ERROR HY000: This transaction was rolled back and cannot be committed. Only supported operation is to roll it back, so all pending changes will be discarded. Please restart another transaction.
SELECT a,b FROM t1;
a	b
10000	foobar
10000	foobar
2	b
2	b
4	d
4	d
5	e
5	e
6	f
6	f
7	g
7	g
8	h
8	h
DROP TABLE t1;