text: --
  -- SUBSELECT
  --
  
  SELECT 1 AS one WHERE 1 IN (SELECT 1);
  
  SELECT 1 AS zero WHERE 1 NOT IN (SELECT 1);
  
  SELECT 1 AS zero WHERE 1 IN (SELECT 2);
  
  -- Check grammar's handling of extra parens in assorted contexts
  
  SELECT * FROM (SELECT 1 AS x) ss;
  SELECT * FROM ((SELECT 1 AS x)) ss;
  
  (SELECT 2) UNION SELECT 2;
  ((SELECT 2)) UNION SELECT 2;
  
  SELECT ((SELECT 2) UNION SELECT 2);
  SELECT (((SELECT 2)) UNION SELECT 2);
  
  SELECT (SELECT ARRAY[1,2,3])[1];
  SELECT ((SELECT ARRAY[1,2,3]))[2];
  SELECT (((SELECT ARRAY[1,2,3])))[3];
  
  -- Set up some simple test tables
  
  CREATE TABLE SUBSELECT_TBL (
    f1 integer,
    f2 integer,
    f3 float
  );
  
  INSERT INTO SUBSELECT_TBL VALUES (1, 2, 3);
  INSERT INTO SUBSELECT_TBL VALUES (2, 3, 4);
  INSERT INTO SUBSELECT_TBL VALUES (3, 4, 5);
  INSERT INTO SUBSELECT_TBL VALUES (1, 1, 1);
  INSERT INTO SUBSELECT_TBL VALUES (2, 2, 2);
  INSERT INTO SUBSELECT_TBL VALUES (3, 3, 3);
  INSERT INTO SUBSELECT_TBL VALUES (6, 7, 8);
  INSERT INTO SUBSELECT_TBL VALUES (8, 9, NULL);
  
  SELECT '' AS eight, * FROM SUBSELECT_TBL;
  
  -- Uncorrelated subselects
  
  SELECT '' AS two, f1 AS "Constant Select" FROM SUBSELECT_TBL
    WHERE f1 IN (SELECT 1);
  
  SELECT '' AS six, f1 AS "Uncorrelated Field" FROM SUBSELECT_TBL
    WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL);
  
  SELECT '' AS six, f1 AS "Uncorrelated Field" FROM SUBSELECT_TBL
    WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE
      f2 IN (SELECT f1 FROM SUBSELECT_TBL));
  
  SELECT '' AS three, f1, f2
    FROM SUBSELECT_TBL
    WHERE (f1, f2) NOT IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL
                           WHERE f3 IS NOT NULL);
  
  -- Correlated subselects
  
  SELECT '' AS six, f1 AS "Correlated Field", f2 AS "Second Field"
    FROM SUBSELECT_TBL upper
    WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE f1 = upper.f1);
  
  SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field"
    FROM SUBSELECT_TBL upper
    WHERE f1 IN
      (SELECT f2 FROM SUBSELECT_TBL WHERE CAST(upper.f2 AS float) = f3);
  
  SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field"
    FROM SUBSELECT_TBL upper
    WHERE f3 IN (SELECT upper.f1 + f2 FROM SUBSELECT_TBL
                 WHERE f2 = CAST(f3 AS integer));
  
  SELECT '' AS five, f1 AS "Correlated Field"
    FROM SUBSELECT_TBL
    WHERE (f1, f2) IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL
                       WHERE f3 IS NOT NULL);
  
  --
  -- Use some existing tables in the regression test
  --
  
  SELECT '' AS eight, ss.f1 AS "Correlated Field", ss.f3 AS "Second Field"
    FROM SUBSELECT_TBL ss
    WHERE f1 NOT IN (SELECT f1+1 FROM INT4_TBL
                     WHERE f1 != ss.f1 AND f1 < 2147483647);
  
  select q1, float8(count(*)) / (select count(*) from int8_tbl)
  from int8_tbl group by q1 order by q1;
  
  --
  -- Test cases to catch unpleasant interactions between IN-join processing
  -- and subquery pullup.
  --
  
  select count(*) from
    (select 1 from tenk1 a
     where unique1 IN (select hundred from tenk1 b)) ss;
  select count(distinct ss.ten) from
    (select ten from tenk1 a
     where unique1 IN (select hundred from tenk1 b)) ss;
  select count(*) from
    (select 1 from tenk1 a
     where unique1 IN (select distinct hundred from tenk1 b)) ss;
  select count(distinct ss.ten) from
    (select ten from tenk1 a
     where unique1 IN (select distinct hundred from tenk1 b)) ss;
  
  --
  -- Test cases to check for overenthusiastic optimization of
  -- "IN (SELECT DISTINCT ...)" and related cases.  Per example from
  -- Luca Pireddu and Michael Fuhr.
  --
  
  CREATE TEMP TABLE foo (id integer);
  CREATE TEMP TABLE bar (id1 integer, id2 integer);
  
  INSERT INTO foo VALUES (1);
  
  INSERT INTO bar VALUES (1, 1);
  INSERT INTO bar VALUES (2, 2);
  INSERT INTO bar VALUES (3, 1);
  
  -- These cases require an extra level of distinct-ing above subquery s
  SELECT * FROM foo WHERE id IN
      (SELECT id2 FROM (SELECT DISTINCT id1, id2 FROM bar) AS s);
  SELECT * FROM foo WHERE id IN
      (SELECT id2 FROM (SELECT id1,id2 FROM bar GROUP BY id1,id2) AS s);
  SELECT * FROM foo WHERE id IN
      (SELECT id2 FROM (SELECT id1, id2 FROM bar UNION
                        SELECT id1, id2 FROM bar) AS s);
  
  -- These cases do not
  SELECT * FROM foo WHERE id IN
      (SELECT id2 FROM (SELECT DISTINCT ON (id2) id1, id2 FROM bar) AS s);
  SELECT * FROM foo WHERE id IN
      (SELECT id2 FROM (SELECT id2 FROM bar GROUP BY id2) AS s);
  SELECT * FROM foo WHERE id IN
      (SELECT id2 FROM (SELECT id2 FROM bar UNION
                        SELECT id2 FROM bar) AS s);
  
  --
  -- Test case to catch problems with multiply nested sub-SELECTs not getting
  -- recalculated properly.  Per bug report from Didier Moens.
  --
  
  CREATE TABLE orderstest (
      approver_ref integer,
      po_ref integer,
      ordercancelled boolean
  );
  
  INSERT INTO orderstest VALUES (1, 1, false);
  INSERT INTO orderstest VALUES (66, 5, false);
  INSERT INTO orderstest VALUES (66, 6, false);
  INSERT INTO orderstest VALUES (66, 7, false);
  INSERT INTO orderstest VALUES (66, 1, true);
  INSERT INTO orderstest VALUES (66, 8, false);
  INSERT INTO orderstest VALUES (66, 1, false);
  INSERT INTO orderstest VALUES (77, 1, false);
  INSERT INTO orderstest VALUES (1, 1, false);
  INSERT INTO orderstest VALUES (66, 1, false);
  INSERT INTO orderstest VALUES (1, 1, false);
  
  CREATE VIEW orders_view AS
  SELECT *,
  (SELECT CASE
     WHEN ord.approver_ref=1 THEN '---' ELSE 'Approved'
   END) AS "Approved",
  (SELECT CASE
   WHEN ord.ordercancelled
   THEN 'Cancelled'
   ELSE
    (SELECT CASE
  		WHEN ord.po_ref=1
  		THEN
  		 (SELECT CASE
  				WHEN ord.approver_ref=1
  				THEN '---'
  				ELSE 'Approved'
  			END)
  		ELSE 'PO'
  	END) 
  END) AS "Status",
  (CASE
   WHEN ord.ordercancelled
   THEN 'Cancelled'
   ELSE
    (CASE
  		WHEN ord.po_ref=1
  		THEN
  		 (CASE
  				WHEN ord.approver_ref=1
  				THEN '---'
  				ELSE 'Approved'
  			END)
  		ELSE 'PO'
  	END) 
  END) AS "Status_OK"
  FROM orderstest ord;
  
  SELECT * FROM orders_view;
  
  DROP TABLE orderstest cascade;
  
  --
  -- Test cases to catch situations where rule rewriter fails to propagate
  -- hasSubLinks flag correctly.  Per example from Kyle Bateman.
  --
  
  create temp table parts (
      partnum     text,
      cost        float8
  );
  
  create temp table shipped (
      ttype       char(2),
      ordnum      int4,
      partnum     text,
      value       float8
  );
  
  create temp view shipped_view as
      select * from shipped where ttype = 'wt';
  
  create rule shipped_view_insert as on insert to shipped_view do instead
      insert into shipped values('wt', new.ordnum, new.partnum, new.value);
  
  insert into parts (partnum, cost) values (1, 1234.56);
  
  insert into shipped_view (ordnum, partnum, value)
      values (0, 1, (select cost from parts where partnum = '1'));
  
  select * from shipped_view;
  
  create rule shipped_view_update as on update to shipped_view do instead
      update shipped set partnum = new.partnum, value = new.value
          where ttype = new.ttype and ordnum = new.ordnum;
  
  update shipped_view set value = 11
      from int4_tbl a join int4_tbl b
        on (a.f1 = (select f1 from int4_tbl c where c.f1=b.f1))
      where ordnum = a.f1;
  
  select * from shipped_view;
  
  select f1, ss1 as relabel from
      (select *, (select sum(f1) from int4_tbl b where f1 >= a.f1) as ss1
       from int4_tbl a) ss;
  
  --
  -- Test cases involving PARAM_EXEC parameters and min/max index optimizations.
  -- Per bug report from David Sanchez i Gregori.
  --
  
  select * from (
    select max(unique1) from tenk1 as a
    where exists (select 1 from tenk1 as b where b.thousand = a.unique2)
  ) ss;
  
  select * from (
    select min(unique1) from tenk1 as a
    where not exists (select 1 from tenk1 as b where b.unique2 = 10000)
  ) ss;
  
  --
  -- Test that an IN implemented using a UniquePath does unique-ification
  -- with the right semantics, as per bug #4113.  (Unfortunately we have
  -- no simple way to ensure that this test case actually chooses that type
  -- of plan, but it does in releases 7.4-8.3.  Note that an ordering difference
  -- here might mean that some other plan type is being used, rendering the test
  -- pointless.)
  --
  
  create temp table numeric_table (num_col numeric);
  insert into numeric_table values (1), (1.000000000000000000001), (2), (3);
  
  create temp table float_table (float_col float8);
  insert into float_table values (1), (2), (3);
  
  select * from float_table
    where float_col in (select num_col from numeric_table);
  
  select * from numeric_table
    where num_col in (select float_col from float_table);
  
  --
  -- Test case for bug #4290: bogus calculation of subplan param sets
  --
  
  create temp table ta (id int primary key, val int);
  
  insert into ta values(1,1);
  insert into ta values(2,2);
  
  create temp table tb (id int primary key, aval int);
  
  insert into tb values(1,1);
  insert into tb values(2,1);
  insert into tb values(3,2);
  insert into tb values(4,2);
  
  create temp table tc (id int primary key, aid int);
  
  insert into tc values(1,1);
  insert into tc values(2,2);
  
  select
    ( select min(tb.id) from tb
      where tb.aval = (select ta.val from ta where ta.id = tc.aid) ) as min_tb_id
  from tc;
  
  --
  -- Test case for 8.3 "failed to locate grouping columns" bug
  --
  
  create temp table t1 (f1 numeric(14,0), f2 varchar(30));
  
  select * from
    (select distinct f1, f2, (select f2 from t1 x where x.f1 = up.f1) as fs
     from t1 up) ss
  group by f1,f2,fs;
  
  --
  -- Test case for bug #5514 (mishandling of whole-row Vars in subselects)
  --
  
  create temp table table_a(id integer);
  insert into table_a values (42);
  
  create temp view view_a as select * from table_a;
  
  select view_a from view_a;
  select (select view_a) from view_a;
  select (select (select view_a)) from view_a;
  select (select (a.*)::text) from view_a a;
  
  --
  -- Check that whole-row Vars reading the result of a subselect don't include
  -- any junk columns therein
  --
  
  select q from (select max(f1) from int4_tbl group by f1 order by f1) q;
  with q as (select max(f1) from int4_tbl group by f1 order by f1)
    select q from q;
  
  --
  -- Test case for sublinks pushed down into subselects via join alias expansion
  --
  
  select
    (select sq1) as qq1
  from
    (select exists(select 1 from int4_tbl where f1 = q2) as sq1, 42 as dummy
     from int8_tbl) sq0
    join
    int4_tbl i4 on dummy = i4.f1;
  
  --
  -- Test case for cross-type partial matching in hashed subplan (bug #7597)
  --
  
  create temp table outer_7597 (f1 int4, f2 int4);
  insert into outer_7597 values (0, 0);
  insert into outer_7597 values (1, 0);
  insert into outer_7597 values (0, null);
  insert into outer_7597 values (1, null);
  
  create temp table inner_7597(c1 int8, c2 int8);
  insert into inner_7597 values(0, null);
  
  select * from outer_7597 where (f1, f2) not in (select * from inner_7597);
  
  --
  -- Test case for premature memory release during hashing of subplan output
  --
  
  select '1'::text in (select '1'::name union all select '1'::name);
  
  --
  -- Test case for planner bug with nested EXISTS handling
  --
  select a.thousand from tenk1 a, tenk1 b
  where a.thousand = b.thousand
    and exists ( select 1 from tenk1 c where b.hundred = c.hundred
                     and not exists ( select 1 from tenk1 d
                                      where a.thousand = d.thousand ) );
  
  --
  -- Check sane behavior with nested IN SubLinks
  --
  select * from int4_tbl where
    (case when f1 in (select unique1 from tenk1 a) then f1 else null end) in
    (select ten from tenk1 b);
  
  --
  -- Check for incorrect optimization when IN subquery contains a SRF
  --
  set enable_hashjoin to 0;
  select * from int4_tbl o where (f1, f1) in
    (select f1, generate_series(1,2) / 10 g from int4_tbl i group by f1);
  reset enable_hashjoin;
  
  --
  -- check for over-optimization of whole-row Var referencing an Append plan
  --
  select (select q from
           (select 1,2,3 where f1 > 0
            union all
            select 4,5,6.0 where f1 <= 0
           ) q )
  from int4_tbl;
  