Re: BUG #8213: Set-valued function error in union - Mailing list pgsql-bugs

From Tom Lane
Subject Re: BUG #8213: Set-valued function error in union
Date
Msg-id 3281.1370475014@sss.pgh.pa.us
Whole thread Raw
In response to BUG #8213: Set-valued function error in union  (eric-postgresql@soroos.net)
List pgsql-bugs
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

pgsql-bugs by date:

Previous
From: eric-postgresql@soroos.net
Date:
Subject: BUG #8213: Set-valued function error in union
Next
From: m+psql@tradehill.com
Date:
Subject: BUG #8214: SIGSEGV in PyEval_EvalFrameEx