statement ok
drop table if exists kv

statement ok
drop table if exists unindexed

statement ok
CREATE TABLE kv (  k INT PRIMARY KEY,  v INT)

statement ok
CREATE TABLE unindexed (  k INT PRIMARY KEY,  v INT)

statement ok
INSERT INTO kv VALUES (1, 2), (3, 4), (5, 6), (7, 8)

statement ok
INSERT INTO unindexed VALUES (1, 2), (3, 4), (5, 6), (7, 8)

query II
SELECT * FROM kv
----
1 2
3 4
5 6
7 8

statement ok
drop view if exists kview

statement ok
CREATE VIEW kview AS SELECT k,v FROM kv

query II
SELECT * FROM kview
----
1 2
3 4
5 6
7 8

statement error
DELETE FROM kview

query II
SELECT * FROM kview
----
1 2
3 4
5 6
7 8

statement ok
DELETE FROM kv WHERE k=3 OR v=6

query II
SELECT * FROM kv
----
1 2
7 8

statement ok
DELETE FROM kv WHERE k=5

statement ok
DELETE FROM kv

statement ok
SELECT * FROM kv

statement error
DELETE FROM kv WHERE nonexistent = 1

statement ok
DELETE FROM unindexed WHERE k=3 OR v=6

query II
SELECT * FROM unindexed
----
1 2
7 8

statement ok
DELETE FROM unindexed

statement ok
SELECT * FROM unindexed

statement ok
INSERT INTO unindexed VALUES (1, 2), (3, 4), (5, 6), (7, 8)

statement ok
DELETE FROM unindexed WHERE k=3 or v=6

statement ok
DELETE FROM unindexed

statement ok
INSERT INTO unindexed VALUES (1, 2), (3, 4), (5, 6), (7, 8)

query II
SELECT k, v FROM unindexed
----
1 2
3 4
5 6
7 8

statement ok
DELETE FROM unindexed

statement ok
INSERT INTO unindexed VALUES (1, 2), (3, 4), (5, 6), (7, 8)

statement ok
DELETE FROM unindexed WHERE k >= 4

query II
SELECT k, v FROM unindexed
----
1 2
3 4

statement ok
DELETE FROM unindexed

statement ok
SELECT k, v FROM unindexed

statement ok
INSERT INTO unindexed VALUES (1, 2), (3, 4), (5, 6), (7, 8)

statement ok
DELETE FROM unindexed WHERE k >= 4

query II
SELECT k, v FROM unindexed
----
1 2
3 4

statement ok
DELETE FROM unindexed WHERE k >= 7

query II
SELECT k, v FROM unindexed
----
1 2
3 4

statement ok
DELETE FROM unindexed WHERE k <=3

statement ok
SELECT k, v FROM unindexed

statement ok
INSERT INTO unindexed VALUES (1, 9), (8, 2), (3, 7), (6, 4)

statement ok
DELETE FROM unindexed WHERE k > 1 AND v < 7

statement ok
DELETE FROM unindexed

statement ok
drop table if exists family

statement ok
CREATE TABLE family ( x INT PRIMARY KEY, y INT)

statement ok
INSERT INTO family VALUES (1, 1), (2, 2), (3, 3)


statement ok
DELETE FROM family WHERE x=2

statement ok
drop table if exists t1

statement ok
drop table if exists t2

statement ok
create table t1 (a int PRIMARY KEY)

statement ok
create table t2(b int PRIMARY KEY)

statement ok
insert into t1 values(1), (2), (3), (8);

statement ok
insert into t2 values(2), (3);

statement ok
delete from t1 where a in (select b from t2);

query I
select * from t1 order by a;
----
1
8

statement ok
insert into t1 values (2),(3);

# TODO: support `ALL/ANY/SOME` on `WHERE`
# statement ok
# delete from t1 where exists (select * from t2 where t1.a = t2.b);

# query I
# select * from t1 order by a;
# ----
# 1
# 8

# statement ok
# insert into t1 values(2), (3);

# statement ok
# delete from t1 where a < any(select b from t2);

# query I
# select * from t1 order by a;
# ----
# 3
# 8

# statement ok
# insert into t1 values(2), (1);

# statement ok
# delete from t1 where a = all(select b from t2);

# query I
# select * from t1 order by a;
# ----
# 1
# 2
# 3
# 8

# statement ok
# delete from t1 where a in (select b from t2 where a > b);

# query I
# select * from t1 order by a;
# ----
# 1
# 2
# 3
# 8

# statement ok
# delete from t1 where a = any(select b from t2 where t1.a = t2.b) ;

# query I
# select * from t1 order by a;
# ----
# 1
# 8

# statement ok
# insert into t1 values(2), (3);

# statement ok
# delete from t1 where exists(select b from t2 where b > 2);

# query I
# select * from t1;
# ----

# statement ok
# insert into t1 values(1), (2), (3), (8);

# statement ok
# delete from t1 where not exists(select b from t2 where b > 2);

# query I
# select * from t1 order by a;
# ----
# 1
# 2
# 3
# 8

# statement ok
# delete from t1 where a = any(select b from t2 where t1.a = t2.b) or a != any(select b from t2 where t1.a = t2.b);

# query I
# select * from t1 order by a;
# ----
# 1
# 8

# statement ok
# insert into t1 values(2), (3);

# statement ok
# delete from t1 where a = any(select b from t2 where t1.a = t2.b) or a > 1;

# query I
# select * from t1;
# ----
# 1

# statement ok
# insert into t1 values(2), (3), (8);

# statement ok
# delete from t1 where a = any(select b from t2 where t1.a = t2.b) or a < any(select b from t2);

# query I
# select * from t1 order by a;
# ----
# 8

# statement ok
# insert into t1 values(1), (2), (3);

# statement ok
# delete from t1 where exists(select b from t2 where a = b);

# query I
# select * from t1 order by a;
# ----
# 1
# 8

# statement ok
# drop table t1;

# statement ok
# drop table t2;