BUG #8213: Set-valued function error in union

The following bug has been logged on the website:

Bug reference: 8213
Logged by: Eric Soroos
Email address: eric-postgresql(at)soroos(dot)net
PostgreSQL version: 9.0.13
Operating system: Ubuntu 10.04, 32bit
Description:

This has been replicated on 9.2.4 and HEAD by ilmari_ and johto.

erics(at)dev:~/trunk/sql$ psql -a -h 192.168.10.249 -f pg_bug_report.sql
\set VERBOSITY verbose
\set echo all
select version();
version

------------------------------------------------------------------------------------------------------------
PostgreSQL 9.0.13 on i686-pc-linux-gnu, compiled by GCC gcc-4.4.real
(Ubuntu 4.4.3-4ubuntu5) 4.4.3, 32-bit
(1 row)

-- this fails. I'd expect it to succeed.
select id, dt from
(select 1 as id, generate_series(now()::date, now()::date + '1
month'::interval, '1 day')::date as dt
union
select 2, now()::date
) as foo
where dt < now()+'15 days'::interval;
psql:pg_bug_report.sql:13: ERROR: 0A000: set-valued function called in
context that cannot accept a set
LOCATION: ExecMakeFunctionResult, execQual.c:1733
-- this succeeds, but returns a timestamp
select id, dt from
(select 1 as id, generate_series(now()::date, now()::date + '1
month'::interval, '1 day') as dt
union
select 2, now()::date
) as foo
where dt < now()+'15 days'::interval;
id | dt
----+---------------------
1 | 2013-06-05 00:00:00
1 | 2013-06-06 00:00:00
1 | 2013-06-07 00:00:00
1 | 2013-06-08 00:00:00
1 | 2013-06-09 00:00:00
1 | 2013-06-10 00:00:00
1 | 2013-06-11 00:00:00
1 | 2013-06-12 00:00:00
1 | 2013-06-13 00:00:00
1 | 2013-06-14 00:00:00
1 | 2013-06-15 00:00:00
1 | 2013-06-16 00:00:00
1 | 2013-06-17 00:00:00
1 | 2013-06-18 00:00:00
1 | 2013-06-19 00:00:00
1 | 2013-06-20 00:00:00
2 | 2013-06-05 00:00:00
(17 rows)

--this also succeeds, without the where clause
select id, dt from
(select 1 as id, generate_series(now()::date, now()::date + '1
month'::interval, '1 day')::date as dt
union
select 2, now()::date
) as foo;
id | dt
----+------------
1 | 2013-06-05
1 | 2013-06-06
1 | 2013-06-07
1 | 2013-06-08
1 | 2013-06-09
1 | 2013-06-10
1 | 2013-06-11
1 | 2013-06-12
1 | 2013-06-13
1 | 2013-06-14
1 | 2013-06-15
1 | 2013-06-16
1 | 2013-06-17
1 | 2013-06-18
1 | 2013-06-19
1 | 2013-06-20
1 | 2013-06-21
1 | 2013-06-22
1 | 2013-06-23
1 | 2013-06-24
1 | 2013-06-25
1 | 2013-06-26
1 | 2013-06-27
1 | 2013-06-28
1 | 2013-06-29
1 | 2013-06-30
1 | 2013-07-01
1 | 2013-07-02
1 | 2013-07-03
1 | 2013-07-04
1 | 2013-07-05
2 | 2013-06-05
(32 rows)

--this also succeeds, without the union
select id, dt from
(select 1 as id, generate_series(now()::date, now()::date + '1
month'::interval, '1 day')::date as dt
) as foo
where dt < now()+'15 days'::interval;
id | dt
----+------------
1 | 2013-06-05
1 | 2013-06-06
1 | 2013-06-07
1 | 2013-06-08
1 | 2013-06-09
1 | 2013-06-10
1 | 2013-06-11
1 | 2013-06-12
1 | 2013-06-13
1 | 2013-06-14
1 | 2013-06-15
1 | 2013-06-16
1 | 2013-06-17
1 | 2013-06-18
1 | 2013-06-19
1 | 2013-06-20
(16 rows)

-- this is the workaround.
select id, dt from
(select 1 as id, generate_series(now()::date, now()::date + '1
month'::interval, '1 day')::date as dt
union all
select 2, now()::date
) as foo
where dt < now()+'15 days'::interval;
id | dt
----+------------
1 | 2013-06-05
1 | 2013-06-06
1 | 2013-06-07
1 | 2013-06-08
1 | 2013-06-09
1 | 2013-06-10
1 | 2013-06-11
1 | 2013-06-12
1 | 2013-06-13
1 | 2013-06-14
1 | 2013-06-15
1 | 2013-06-16
1 | 2013-06-17
1 | 2013-06-18
1 | 2013-06-19
1 | 2013-06-20
2 | 2013-06-05
(17 rows)

-- this is another workaround:
begin;
BEGIN
create temp view gs as
select 1 as id, generate_series(now()::date, now()::date + '1
month'::interval, '1 day') as dt;
CREATE VIEW
create temp view container as
select id, dt::date from gs
union
select 2, now()::date;
CREATE VIEW
select * from container where dt < now()+'15 days'::interval;
id | dt
----+------------
1 | 2013-06-05
1 | 2013-06-06
1 | 2013-06-07
1 | 2013-06-08
1 | 2013-06-09
1 | 2013-06-10
1 | 2013-06-11
1 | 2013-06-12
1 | 2013-06-13
1 | 2013-06-14
1 | 2013-06-15
1 | 2013-06-16
1 | 2013-06-17
1 | 2013-06-18
1 | 2013-06-19
1 | 2013-06-20
2 | 2013-06-05
(17 rows)

rollback;
ROLLBACK
-- another workaround
select id, dt from
(select 1 as id, generate_series(now()::date, now()::date + '1
month'::interval, '1 day')::date as dt
union
select 2, now()::date offset 0
) as foo
where dt < now()+'15 days'::interval;
id | dt
----+------------
1 | 2013-06-05
1 | 2013-06-06
1 | 2013-06-07
1 | 2013-06-08
1 | 2013-06-09
1 | 2013-06-10
1 | 2013-06-11
1 | 2013-06-12
1 | 2013-06-13
1 | 2013-06-14
1 | 2013-06-15
1 | 2013-06-16
1 | 2013-06-17
1 | 2013-06-18
1 | 2013-06-19
1 | 2013-06-20
2 | 2013-06-05
(17 rows)

erics(at)dev:~/trunk/sql$