Обсуждение: 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@soroos.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@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@dev:~/trunk/sql$ =

eric-postgresql@soroos.net writes:
> -- 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

Fascinating.  This has been broken at least since 7.4 --- surprising
nobody noticed before.  We need to fix allpaths.c so it realizes it's
unsafe to push down a WHERE condition into a set operation when there
are set-returning functions in the tlist of any arm of the set operation.
Right now, you're getting this plan:

 HashAggregate  (cost=20.09..30.10 rows=1001 width=0)
   ->  Append  (cost=0.03..15.09 rows=1001 width=0)
         ->  Result  (cost=0.03..5.05 rows=1000 width=0)
               One-Time Filter: ((generate_series(((now())::date)::timestamp without time zone, ((now())::date + '1
mon'::interval),'1 day'::interval))::date < (now() + '15 days'::interval)) 
         ->  Result  (cost=0.01..0.03 rows=1 width=0)
               One-Time Filter: ((now())::date < (now() + '15 days'::interval))

and of course trying to evaluate a filter that contains a SRF is pretty
nonsensical (or even if you think it could be well-defined, it's not
implemented).

Shouldn't be too hard to fix though.  I'm thinking of moving most of the
detection logic for this into subquery_is_pushdown_safe, and having it
return an additional flag array that says "this output column is unsafe
to reference in quals at all".

            regards, tom lane