From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | svfront(at)mail(dot)ru |
Subject: | BUG #15777: Unexpected error in select from view with set-returning function and union |
Date: | 2019-04-24 13:13:29 |
Message-ID: | 15777-5af1fd5daf30bfcc@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 15777
Logged by: Danil Mihailov
Email address: svfront(at)mail(dot)ru
PostgreSQL version: 11.2
Operating system: Debian 9.8
Description:
uname -a
Linux bnode-test 4.9.0-8-amd64 #1 SMP Debian 4.9.144-3.1 (2019-02-19) x86_64
GNU/Linux
Test stand:
CREATE TABLE public.test_dummy
(
a integer[],
b integer[]
);
CREATE OR REPLACE VIEW public.test_dummy_view AS
SELECT unnest(test_dummy.a) AS id,
'a'::text AS c
FROM test_dummy
WHERE test_dummy.a IS NOT NULL
UNION
SELECT unnest(test_dummy.b) AS id,
'b'::text AS c
FROM test_dummy
WHERE test_dummy.b IS NOT NULL;
INSERT INTO test_dummy(a, b)
VALUES
(ARRAY[1,2], null),
(null, ARRAY[3,4]);
Test case:
SELECT id, c FROM test_dummy_view; -- works fine
SELECT id, c FROM test_dummy_view WHERE c = 'a'; -- error
Verbose log message:
2019-04-24 15:59:42.143 MSK [112107] danila(at)inetstat ОШИБКА: 0A000:
функция, возвращающая множество, вызвана в контексте, где ему нет места
2019-04-24 15:59:42.143 MSK [112107] danila(at)inetstat ПОЛОЖЕНИЕ:
ExecInitFunc, execExpr.c:2212
2019-04-24 15:59:42.143 MSK [112107] danila(at)inetstat ОПЕРАТОР: SELECT id, c
FROM test_dummy_view WHERE c = 'a';
Translation:
set-valued function called in context that cannot accept a set
But:
WITH foo AS (
SELECT unnest(a) AS id, 'a' AS c
FROM test_dummy WHERE a IS NOT NULL
UNION
SELECT unnest(b) AS id, 'b' AS c
FROM test_dummy WHERE b IS NOT NULL
)
SELECT * FROM foo WHERE c = 'a'; -- works fine
Also workaround with LATERAL in view works too;
In Postgres 9.6 all queries work as expected.
From | Date | Subject | |
---|---|---|---|
Next Message | Sergei Kornilov | 2019-04-24 13:46:19 | Re: BUG #15777: Unexpected error in select from view with set-returning function and union |
Previous Message | Alvaro Herrera | 2019-04-24 12:44:59 | Re: bug: evil autoConcat when each string is on new line |