From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: pg@mattyw.net
Subject: BUG #15471: psql 11 array concatenation in CASE takes on values from the CASE expression when using enum_range
Date: Tue, 30 Oct 2018 07:39:52 +0000
Message-ID: <15471-1117f49271989bad@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 15471
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}
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, pg@mattyw.net
Subject: Re: BUG #15471: psql 11 array concatenation in CASE takes on values from the CASE expression when using enum_range
In-Reply-To: <15471-1117f49271989bad@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox