statement ok
create table t1(id int primary key, c1 int, c2 varchar)

statement ok
create table t2(id int primary key, c3 int, c4 varchar)

query T
COPY t1 FROM 'tests/data/row_20000.csv' ( DELIMITER '|' );
----
import 20000 rows

statement ok
analyze table t1

query I
explain select * from t1
----
Projection [id, c1, c2] [Project]
  Scan t1 -> [id, c1, c2] [SeqScan]

query T
explain select c1 from t1
----
Projection [c1] [Project]
  Scan t1 -> [c1] [SeqScan]

query T
explain select c1 from t1 limit 10
----
Projection [c1] [Project]
  Scan t1 -> [c1], Limit: 10 [SeqScan]

query T
explain select c1 from t1 limit 10 offset 5
----
Projection [c1] [Project]
  Scan t1 -> [c1], Limit: 10, Offset: 5 [SeqScan]

query T
explain select c1 from t1 where c1 + 1 = 1 or c2 > 1
----
Projection [c1] [Project]
  Filter ((c1 = 0) || (c2 > 1)), Is Having: false [Filter]
    Scan t1 -> [c1, c2] [SeqScan]

query T
explain select c1 from t1 where c1 > 1 + 3 and c1 < 10
----
Projection [c1] [Project]
  Filter ((c1 > 4) && (c1 < 10)), Is Having: false [Filter]
    Scan t1 -> [c1] [SeqScan]

query T
explain select c1 from t1 where c1 in (1, 2, 3)
----
Projection [c1] [Project]
  Filter ((c1 = 3) || ((c1 = 2) || ((c1 = 1)))), Is Having: false [Filter]
    Scan t1 -> [c1] [SeqScan]

query T
explain select c1 from t1 where c1 not in (1, 2, 3)
----
Projection [c1] [Project]
  Filter ((c1 != 3) && ((c1 != 2) && ((c1 != 1)))), Is Having: false [Filter]
    Scan t1 -> [c1] [SeqScan]

query T
explain select c1 from t1 where c2 like 'lol%'
----
Projection [c1] [Project]
  Filter (c2 like lol%), Is Having: false [Filter]
    Scan t1 -> [c1, c2] [SeqScan]

query T
explain select c1 from t1 where c3 not like 'lol%'
----
Projection [c1] [Project]
  Filter (c2 not like lol%), Is Having: false [Filter]
    Scan t1 -> [c1, c2] [SeqScan]

query T
explain select c1 from t1 where c2 is null
----
Projection [c1] [Project]
  Filter c2 is null, Is Having: false [Filter]
    Scan t1 -> [c1, c2] [SeqScan]

query T
explain select c1 from t1 where c2 is not null
----
Projection [c1] [Project]
  Filter c2 is not null, Is Having: false [Filter]
    Scan t1 -> [c1, c2] [SeqScan]

query T
explain select c1 from t1 order by c1
----
Projection [c1] [Project]
  Sort By c1 Asc Nulls First [RadixSort]
    Scan t1 -> [id, c1, c2] [SeqScan]

query T
explain select c1 from t1 order by c1 desc, c2
----
Projection [c1] [Project]
  Sort By c1 Desc Null First, c2 Asc Nulls First [RadixSort]
    Scan t1 -> [id, c1, c2] [SeqScan]

query T
explain select c1 from t1 order by c1 nulls last
----
Projection [c1] [Project]
  Sort By c1 Asc Nulls Last [RadixSort]
    Scan t1 -> [id, c1, c2] [SeqScan]

query T
explain select sum(c1) from t1
----
Projection [Sum(c1)] [Project]
  Aggregate [Sum(c1)] [SimpleAggregate]
    Scan t1 -> [c1] [SeqScan]

query T
explain select c1, sum(c2) from t1 group by c1
----
Projection [c1, Sum(c2)] [Project]
  Aggregate [Sum(c2)] -> Group By [c1] [HashAggregate]
    Scan t1 -> [c1, c2] [SeqScan]

query T
explain select c1, sum(c2) from t1 where c1 > 10 group by c1
----
Projection [c1, Sum(c2)] [Project]
  Aggregate [Sum(c2)] -> Group By [c1] [HashAggregate]
    Filter (c1 > 10), Is Having: false [Filter]
      Scan t1 -> [c1, c2] [SeqScan]

query T
explain select c1, sum(c2) from t1 group by c1 having c1 > 10
----
Projection [c1, Sum(c2)] [Project]
  Filter (c1 > 10), Is Having: true [Filter]
    Aggregate [Sum(c2)] -> Group By [c1] [HashAggregate]
      Scan t1 -> [c1, c2] [SeqScan]

query T
explain select * from t1 left join t2 on c1 = c2 and c1 > 10
----
Projection [id, c1, c2, id, c3, c4] [Project]
  Left Join On Where ((c1 = c2) && (c1 > 10)) [HashJoin]
    Scan t1 -> [id, c1, c2] [SeqScan]
    Scan t2 -> [id, c3, c4] [SeqScan]

query T
explain select * from t1 right join t2 on c1 = c2 and c1 > 10
----
Projection [id, c1, c2, id, c3, c4] [Project]
  Right Join On Where ((c1 = c2) && (c1 > 10)) [HashJoin]
    Scan t1 -> [id, c1, c2] [SeqScan]
    Scan t2 -> [id, c3, c4] [SeqScan]

query T
explain select * from t1 inner join t2 on c1 = c2 and c1 > 10
----
Projection [id, c1, c2, id, c3, c4] [Project]
  Inner Join On Where ((c1 = c2) && (c1 > 10)) [HashJoin]
    Scan t1 -> [id, c1, c2] [SeqScan]
    Scan t2 -> [id, c3, c4] [SeqScan]

query T
explain select * from t1 full join t2 on c1 = c2 and c1 > 10
----
Projection [id, c1, c2, id, c3, c4] [Project]
  Full Join On Where ((c1 = c2) && (c1 > 10)) [HashJoin]
    Scan t1 -> [id, c1, c2] [SeqScan]
    Scan t2 -> [id, c3, c4] [SeqScan]

query T
explain show tables
----
Show Tables

query T
explain insert into t1 values (200001,1,10)
----
Insert t1, Is Overwrite: false [Insert]
  Values [id, c1, c2], RowsLen: 1 [Values]

query T
explain insert overwrite t1 values (200001,1,10)
----
Insert t1, Is Overwrite: true [Insert]
  Values [id, c1, c2], RowsLen: 1 [Values]

query T
explain update t1 set c1 = 0 where id = 0
----
Update t1 [Update]
  Filter (id = 0), Is Having: false [Filter]
    Scan t1 -> [id, c1, c2] [IndexScan By pk_id => 0]
  Values [c1], RowsLen: 1 [Values]

query T
explain delete from t1 where id = 0
----
Delete t1 [Delete]
  Filter (id = 0), Is Having: false [Filter]
    Scan t1 -> [id, c1, c2] [IndexScan By pk_id => 0]

query T
explain alter table t1 add column if not exists c8 int default 0
----
Add c8 -> t1, If Not Exists: true [AddColumn]
  Scan t1 -> [id, c1, c2] [SeqScan]

query T
explain alter table t1 drop column if exists c2
----
Drop c2 -> t1, If Exists: true [DropColumn]
  Scan t1 -> [id, c1, c2] [SeqScan]

query T
explain truncate t1
----
Truncate t1

query T
explain copy t1 from 'tests/data/row_20000.csv' ( DELIMITER '|' );
----
Copy tests/data/row_20000.tbl -> t1 [id, c1, c2] [CopyFromFile]

statement ok
drop table t1

statement ok
drop table t2