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

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: pg(at)mattyw(dot)net
Subject: BUG #15480: psql 11 array concatenation in CASE takes on values from the CASE expression when using enum_range
Date: 2018-11-01 09:15:33
Message-ID: 15480-b81ba9ae56bbe35e@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: 15480
Logged by: Matt Williams
Email address: pg(at)mattyw(dot)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}

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Wèi Cōngruì 2018-11-01 09:30:17 Re: BUG #15478: 配置文件 pg_hba.conf 异常
Previous Message PG Bug reporting form 2018-11-01 08:36:29 BUG #15479: Documentation claims that client_min_messages is related to logging