--
-- SUBSELECT
--
SELECT 1 AS one WHERE 1 IN (SELECT 1);
 one 
-----
   1
(1 row)

SELECT 1 AS zero WHERE 1 NOT IN (SELECT 1);
 zero 
------
(0 rows)

SELECT 1 AS zero WHERE 1 IN (SELECT 2);
 zero 
------
(0 rows)

-- 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;
 eight | f1 | f2 | f3 
-------+----+----+----
       |  1 |  2 |  3
       |  2 |  3 |  4
       |  3 |  4 |  5
       |  1 |  1 |  1
       |  2 |  2 |  2
       |  3 |  3 |  3
       |  6 |  7 |  8
       |  8 |  9 |   
(8 rows)

-- Uncorrelated subselects
SELECT '' AS two, f1 AS "Constant Select" FROM SUBSELECT_TBL
  WHERE f1 IN (SELECT 1);
 two | Constant Select 
-----+-----------------
     |               1
     |               1
(2 rows)

SELECT '' AS six, f1 AS "Uncorrelated Field" FROM SUBSELECT_TBL
  WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL);
 six | Uncorrelated Field 
-----+--------------------
     |                  1
     |                  2
     |                  3
     |                  1
     |                  2
     |                  3
(6 rows)

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));
 six | Uncorrelated Field 
-----+--------------------
     |                  1
     |                  2
     |                  3
     |                  1
     |                  2
     |                  3
(6 rows)

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);
 three | f1 | f2 
-------+----+----
       |  1 |  2
       |  6 |  7
       |  8 |  9
(3 rows)

-- 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);
 six | Correlated Field | Second Field 
-----+------------------+--------------
     |                1 |            2
     |                2 |            3
     |                3 |            4
     |                1 |            1
     |                2 |            2
     |                3 |            3
(6 rows)

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);
 six | Correlated Field | Second Field 
-----+------------------+--------------
     |                2 |            4
     |                3 |            5
     |                1 |            1
     |                2 |            2
     |                3 |            3
(5 rows)

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));
 six | Correlated Field | Second Field 
-----+------------------+--------------
     |                1 |            3
     |                2 |            4
     |                3 |            5
     |                6 |            8
(4 rows)

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);
 five | Correlated Field 
------+------------------
      |                2
      |                3
      |                1
      |                2
      |                3
(5 rows)

--
-- 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);
 eight | Correlated Field | Second Field 
-------+------------------+--------------
       |                2 |            4
       |                3 |            5
       |                2 |            2
       |                3 |            3
       |                6 |            8
       |                8 |             
(6 rows)

select q1, float8(count(*)) / (select count(*) from int8_tbl)
from int8_tbl group by q1 order by q1;
        q1        | ?column? 
------------------+----------
              123 |      0.4
 4567890123456789 |      0.6
(2 rows)

--
-- 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;
 count 
-------
   100
(1 row)

select count(distinct ss.ten) from
  (select ten from tenk1 a
   where unique1 IN (select hundred from tenk1 b)) ss;
 count 
-------
    10
(1 row)

select count(*) from
  (select 1 from tenk1 a
   where unique1 IN (select distinct hundred from tenk1 b)) ss;
 count 
-------
   100
(1 row)

select count(distinct ss.ten) from
  (select ten from tenk1 a
   where unique1 IN (select distinct hundred from tenk1 b)) ss;
 count 
-------
    10
(1 row)

--
-- 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);
 id 
----
  1
(1 row)

SELECT * FROM foo WHERE id IN
    (SELECT id2 FROM (SELECT id1,id2 FROM bar GROUP BY id1,id2) AS s);
 id 
----
  1
(1 row)

SELECT * FROM foo WHERE id IN
    (SELECT id2 FROM (SELECT id1, id2 FROM bar UNION
                      SELECT id1, id2 FROM bar) AS s);
 id 
----
  1
(1 row)

-- These cases do not
SELECT * FROM foo WHERE id IN
    (SELECT id2 FROM (SELECT DISTINCT ON (id2) id1, id2 FROM bar) AS s);
 id 
----
  1
(1 row)

SELECT * FROM foo WHERE id IN
    (SELECT id2 FROM (SELECT id2 FROM bar GROUP BY id2) AS s);
 id 
----
  1
(1 row)

SELECT * FROM foo WHERE id IN
    (SELECT id2 FROM (SELECT id2 FROM bar UNION
                      SELECT id2 FROM bar) AS s);
 id 
----
  1
(1 row)

--
-- 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;
 approver_ref | po_ref | ordercancelled | Approved |  Status   | Status_OK 
--------------+--------+----------------+----------+-----------+-----------
            1 |      1 | f              | ---      | ---       | ---
           66 |      5 | f              | Approved | PO        | PO
           66 |      6 | f              | Approved | PO        | PO
           66 |      7 | f              | Approved | PO        | PO
           66 |      1 | t              | Approved | Cancelled | Cancelled
           66 |      8 | f              | Approved | PO        | PO
           66 |      1 | f              | Approved | Approved  | Approved
           77 |      1 | f              | Approved | Approved  | Approved
            1 |      1 | f              | ---      | ---       | ---
           66 |      1 | f              | Approved | Approved  | Approved
            1 |      1 | f              | ---      | ---       | ---
(11 rows)

DROP TABLE orderstest cascade;
NOTICE:  drop cascades to view orders_view
--
-- 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;
 ttype | ordnum | partnum |  value  
-------+--------+---------+---------
 wt    |      0 | 1       | 1234.56
(1 row)

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;
 ttype | ordnum | partnum | value 
-------+--------+---------+-------
 wt    |      0 | 1       |    11
(1 row)

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;
     f1      |  relabel   
-------------+------------
           0 | 2147607103
      123456 | 2147607103
     -123456 | 2147483647
  2147483647 | 2147483647
 -2147483647 |          0
(5 rows)

--
-- 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;
 max  
------
 9997
(1 row)

select * from (
  select min(unique1) from tenk1 as a
  where not exists (select 1 from tenk1 as b where b.unique2 = 10000)
) ss;
 min 
-----
   0
(1 row)

--
-- 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);
 float_col 
-----------
         1
         2
         3
(3 rows)

select * from numeric_table
  where num_col in (select float_col from float_table);
         num_col         
-------------------------
                       1
 1.000000000000000000001
                       2
                       3
(4 rows)

--
-- Test case for bug #4290: bogus calculation of subplan param sets
--
create temp table ta (id int primary key, val int);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "ta_pkey" for table "ta"
insert into ta values(1,1);
insert into ta values(2,2);
create temp table tb (id int primary key, aval int);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tb_pkey" for table "tb"
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);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tc_pkey" for table "tc"
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;
 min_tb_id 
-----------
         1
         3
(2 rows)

--
-- 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;
 f1 | f2 | fs 
----+----+----
(0 rows)

--
-- 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;
 view_a 
--------
 (42)
(1 row)

select (select view_a) from view_a;
 ?column? 
----------
 (42)
(1 row)

select (select (select view_a)) from view_a;
 ?column? 
----------
 (42)
(1 row)

select (select (a.*)::text) from view_a a;
 ?column? 
----------
 (42)
(1 row)

--
-- 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;
 qq1 
-----
(0 rows)

--
-- Test case for premature memory release during hashing of subplan output
--
select '1'::text in (select '1'::name union all select '1'::name);
 ?column? 
----------
 t
(1 row)

--
-- 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 ) );
 thousand 
----------
(0 rows)