BUG #8213: Set-valued function error in union
От | eric-postgresql@soroos.net |
---|---|
Тема | BUG #8213: Set-valued function error in union |
Дата | |
Msg-id | E1UkHmL-0005No-GM@wrigleys.postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #8213: Set-valued function error in union
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-bugs |
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$ =
В списке pgsql-bugs по дате отправления:
Предыдущее
От: David JohnstonДата:
Сообщение: Re: BUG #8211: Syntax error when creating index on expression