PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15471
PG Version11.0
OSConfirmed on MacOs and Alpine Linux
Opened2018-10-30 07:39:52+00
Reported byMatt Williams
StatusOpen

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15471
Logged by:          Matt Williams
Email address:      (redacted)
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}

Messages

DateAuthorSubject
2018-10-30 07:39:52+00=?utf-8?q?PG_Bug_reporting_form?=BUG #15471: psql 11 array concatenation in CASE takes on values from the CASE expression when using enum_range
2018-10-30 11:59:35+00Tom LaneRe: BUG #15471: psql 11 array concatenation in CASE takes on values from the CASE expression when using enum_range
2018-10-30 12:49:25+00Andrew GierthRe: BUG #15471: psql 11 array concatenation in CASE takes on values from the CASE expression when using enum_range
2018-10-30 13:30:54+00Tom LaneRe: BUG #15471: psql 11 array concatenation in CASE takes on values from the CASE expression when using enum_range
2018-10-30 17:42:00+00Andres FreundRe: BUG #15471: psql 11 array concatenation in CASE takes on values from the CASE expression when using enum_range
2018-10-30 17:44:11+00Andres FreundRe: BUG #15471: psql 11 array concatenation in CASE takes on values from the CASE expression when using enum_range