Thread: BUG #15480: psql 11 array concatenation in CASE takes on values fromthe CASE expression when using enum_range

The following bug has been logged on the website:

Bug reference:      15480
Logged by:          Matt Williams
Email address:      pg@mattyw.net
PostgreSQL version: 11.0
Operating system:   Confirmed on MacOs and Alpine Linux
Description:

Below is an example .sql file that replicates the problem. Put simply, when
we array concat with enum_range in the result of a CASE statement the
concatenation takes the expression from the CASE statement, not the enum
range.

if you run the below .sql file against postgres 10 and 11 you'll see ex1 and
ex2 return the same array. However ex3,4,5 all show arrays concatenated with
the value in the CASE condition (TRUE, 'true' and 1). They all return the
expected array under postgres 9.6 and 10. The change only appears in
postgres 11.
    
The last example (ex6) shows the CASE rewritten to remove the expression,
ex6 works the same under postgres 10 and 11.

These results for ex 3, 4 and 5 were surprising, I couldn't find anything in
the docs that alludes to this change, and I've been unable to find any
online discussion regarding this.

--- Start of sql file ---
SELECT version();

DROP TYPE IF EXISTS myenum;

CREATE TYPE myenum AS ENUM ('e', 'f', 'g');

SELECT enum_range(NULL::myenum);

SELECT ARRAY['a', 'b', 'c', 'd'] || enum_range(NULL::myenum)::text[] as
ex1;

SELECT
    CASE TRUE
        WHEN TRUE THEN ARRAY['a', 'b', 'c', 'd'] || ARRAY['e', 'f', 'g']
        WHEN FALSE THEN ARRAY['a', 'b', 'c', 'd']
END as ex2;

-- All of the above works as expected

SELECT
    CASE TRUE
        WHEN TRUE THEN ARRAY['a', 'b', 'c', 'd'] ||
enum_range(NULL::myenum)::text[]
        WHEN FALSE THEN ARRAY['a', 'b', 'c', 'd']
END as ex3;

-- In the above case statement we'd expected the output: {a,b,c,d,e,f,g}
-- However under postres 11 we get the following output: {a,b,c,d,t,t,t}


SELECT
    CASE 'true'
        WHEN 'true' THEN ARRAY['a', 'b', 'c', 'd'] ||
enum_range(NULL::myenum)::text[]
        WHEN 'false' THEN ARRAY['a', 'b', 'c', 'd']
END as ex4;

-- Postgres 10: {a,b,c,d,e,f,g}
-- Postgres 11: {a,b,c,d,true,true,true}


SELECT
    CASE 1
        WHEN 1 THEN ARRAY['a', 'b', 'c', 'd'] ||
enum_range(NULL::myenum)::text[]
        WHEN 2 THEN ARRAY['a', 'b', 'c', 'd']
END as ex5;

-- Postgres 10: {a,b,c,d,e,f,g}
-- Postgres 11: {a,b,c,d,1,1,1}

SELECT
    CASE
        WHEN TRUE THEN ARRAY['a', 'b', 'c', 'd'] ||
enum_range(NULL::myenum)::text[]
        ELSE ARRAY['a', 'b', 'c', 'd']
END as ex6;

-- In this form we get the same answer for both postgres 10 and 11:
{a,b,c,d,e,f,g}

=?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
> Below is an example .sql file that replicates the problem. Put simply, when
> we array concat with enum_range in the result of a CASE statement the
> concatenation takes the expression from the CASE statement, not the enum
> range.

Isn't this a duplicate of bug #15471 ?

            regards, tom lane

Apologies, I had an old safari tab open on the submission of the original bug (15471). It appears it's resubmitted the
form.

Matt

> On 1 Nov 2018, at 10:26 pm, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> =?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
>> Below is an example .sql file that replicates the problem. Put simply, when
>> we array concat with enum_range in the result of a CASE statement the
>> concatenation takes the expression from the CASE statement, not the enum
>> range.
>
> Isn't this a duplicate of bug #15471 ?
>
>             regards, tom lane