primary-complex-sub-query.test

-- echo initialization
CREATE TABLE csq_1(id int, col1 int, feat1 float);
CREATE TABLE csq_2(id int, col2 int, feat2 float);
CREATE TABLE csq_3(id int, col3 int, feat3 float);
CREATE TABLE csq_4(id int, col4 int, feat4 float);

INSERT INTO csq_1 VALUES (1, 4, 11.2);
INSERT INTO csq_1 VALUES (2, 2, 12.0);
INSERT INTO csq_1 VALUES (3, 3, 13.5);
INSERT INTO csq_2 VALUES (1, 2, 13.0);
INSERT INTO csq_2 VALUES (2, 7, 10.5);
INSERT INTO csq_2 VALUES (5, 3, 12.6);
INSERT INTO csq_3 VALUES (1, 2, 11.0);
INSERT INTO csq_3 VALUES (3, 6, 16.5);
INSERT INTO csq_3 VALUES (5, 5, 14.6);

-- echo 1. Select
-- sort select * from csq_1 where id in (select csq_2.id from csq_2 where csq_2.id in (select csq_3.id from csq_3));

-- sort select * from csq_1 where id in (select csq_2.id from csq_2 where csq_2.id not in (select csq_3.id from csq_3));

-- sort select * from csq_1 where col1 not in (select csq_2.col2 from csq_2 where csq_2.id not in (select csq_3.id from csq_3));

-- sort select * from csq_1 where col1 not in (select csq_2.col2 from csq_2 where csq_2.id in (select csq_3.id from csq_3));

-- sort select * from csq_1 where col1 > (select avg(csq_2.col2) from csq_2 where csq_2.feat2 >= (select min(csq_3.feat3) from csq_3));

-- sort select * from csq_1 where (select avg(csq_2.col2) from csq_2 where csq_2.feat2 > (select min(csq_3.feat3) from csq_3)) = col1;

-- sort select * from csq_1 where (select avg(csq_2.col2) from csq_2) <> (select avg(csq_3.col3) from csq_3);

-- sort select * from csq_1 where feat1 > (select min(csq_2.feat2) from csq_2) and col1 <= (select min(csq_3.col3) from csq_3);

-- sort select * from csq_1 where (select max(csq_2.feat2) from csq_2) > feat1 and col1 > (select min(csq_3.col3) from csq_3);

-- sort select * from csq_1 where (select max(csq_2.feat2) from csq_2) > feat1 and (select min(csq_3.col3) from csq_3) < col1;

-- sort select * from csq_1 where feat1 <> (select avg(csq_2.feat2) from csq_2 where csq_2.feat2 > csq_1.feat1);

-- sort select * from csq_1 where col1 not in (select csq_2.col2 from csq_2 where csq_2.id in (select csq_3.id from csq_3 where csq_1.id = csq_3.id));

-- echo 2. Select with empty table
-- sort select * from csq_1 where id in (select csq_2.id from csq_2 where csq_2.id in (select csq_3.id from csq_3 where 1=0));
-- sort select * from csq_1 where id in (select csq_2.id from csq_2 where csq_2.id in (select csq_3.id from csq_3 where 1=0) and 1=0);
-- sort select * from csq_1 where col1 not in (select csq_2.col2 from csq_2 where csq_2.id not in (select csq_3.id from csq_3 where 1=0));
-- sort select * from csq_1 where col1 not in (select csq_2.col2 from csq_2 where csq_2.id not in (select csq_3.id from csq_3) and 1=0);
-- sort select * from csq_3 where feat3 < (select max(csq_2.feat2) from csq_2 where csq_2.id not in (select csq_3.id from csq_3 where 1=0));
-- sort select * from csq_3 where feat3 < (select max(csq_2.feat2) from csq_2 where csq_2.id not in (select csq_3.id from csq_3 ) and 1=0);

--echo 3. error
select * from csq_1 where col1 = (select csq_2.col2 from csq_2);
select * from csq_1 where col1 = (select * from csq_2);
select * from csq_1 where col1 in (select * from csq_2);
select * from csq_1 where col1 not in (select * from csq_2);