PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15777
PG Version11.2
OSDebian 9.8
Opened2019-04-24 13:13:29+00
Reported byDanil Mihailov
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15777
Logged by:          Danil Mihailov
Email address:      (redacted)
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@inetstat ОШИБКА:  0A000:
функция, возвращающая множество, вызвана в контексте, где ему нет места
2019-04-24 15:59:42.143 MSK [112107] danila@inetstat ПОЛОЖЕНИЕ:
ExecInitFunc, execExpr.c:2212
2019-04-24 15:59:42.143 MSK [112107] danila@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.

Messages

DateAuthorSubject
2019-04-24 13:13:29+00PG Bug reporting formBUG #15777: Unexpected error in select from view with set-returning function and union
2019-04-24 13:46:19+00Sergei KornilovRe: BUG #15777: Unexpected error in select from view with set-returning function and union