statement ok
create table t1(id int primary key, a int not null, b int not null);

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

query II
select a, b from (select a, b from t1);
----
1 2
3 4

query II
select x.a, x.b from (select a, b from t1) as x;
----
1 2
3 4

query II
select * from (select a, b from t1);
----
1 2
3 4

query I
select s from (select a + b as s from t1);
----
3
7

query II rowsort
select x.a from (select -a as a from t1) as x;
----
-1
-3

query III
select * from t1 where a <= (select 4) and a > (select 1)
----
1 3 4

query III
select * from t1 where a <= (select 4) and (-a + 1) < (select 1) - 1
----
1 3 4

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

query III
select * from t1 where a in (select 1)
----
0 1 2
3 1 4

query III
select * from t1 where a in (select 1) and b = 4
----
3 1 4

query III
select * from t1 where a not in (select 1)
----
1 3 4
2 3 3

query III
select * from t1 where a not in (select 1) and b = 3
----
2 3 3

statement ok
drop table t1;

# https://github.com/KipData/KiteSQL/issues/169
statement ok
create table t2(id int primary key, a int not null, b int not null);

statement ok
create table t3(id int primary key, a int not null, c int not null);

statement ok
insert into t2 values (0, 1, 2), (3, 4, 5);

statement ok
insert into t3 values (0, 2, 2), (3, 8, 5);

query III
SELECT id, a, b FROM t2 WHERE a*2 in (SELECT a FROM t3 where a/2 in (select a from t2));
----
0 1 2
3 4 5

statement ok
drop table t2;

statement ok
drop table t3;