Re: BUG #15471: psql 11 array concatenation in CASE takes on values from the CASE expression when using enum_range - Mailing list pgsql-bugs

=?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.

Wow, that's ... bizarre.  I'm thinking that we probably did something
silly in the big expression-execution rewrite, but it's not clear exactly
where.  Anyway, will look into it if Andres doesn't beat me to it.

Some poking at the examples finds that it seems to be necessary to have
the coercion to text[] to show the problem, eg this seems fine:

SELECT
  CASE 1
    WHEN 1 THEN ARRAY['e', 'f']::myenum[] || enum_range(NULL::myenum)
    WHEN 2 THEN ARRAY['f', 'g']::myenum[]
END;

What's really strange is that EXPLAIN VERBOSE shows that the planner has
correctly elided the CASE altogether in each case, eg for ex5:

explain verbose 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;
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Result  (cost=0.00..0.07 rows=1 width=32)
   Output: ('{a,b,c,d}'::text[] || (enum_range(NULL::myenum))::text[])
(2 rows)

So how come it's still affecting the result of the array coercion?

Looking at the generated plan tree with debug_print_plan narrows
things down quite a bit: the array coercion looks like, eg,

               {ARRAYCOERCEEXPR 
               :arg 
                  {FUNCEXPR 
                  :funcid 3531 
                  :funcresulttype 197116 
                  :funcretset false 
                  :funcvariadic false 
                  :funcformat 0 
                  :funccollid 0 
                  :inputcollid 0 
                  :args (
                     {CONST 
                     :consttype 197117 
                     :consttypmod -1 
                     :constcollid 0 
                     :constlen 4 
                     :constbyval true 
                     :constisnull true 
                     :location 72 
                     :constvalue <>
                     }
                  )
                  :location 61
                  }
               :elemexpr 
                  {COERCEVIAIO 
                  :arg 
                     {CONST 
                     :consttype 23 
                     :consttypmod -1 
                     :constcollid 0 
                     :constlen 4 
                     :constbyval true 
                     :constisnull false 
                     :location 14 
                     :constvalue 4 [ 1 0 0 0 0 0 0 0 ]
                     }
                  :resulttype 25 
                  :resultcollid 100 
                  :coerceformat 1 
                  :location 85
                  }
               :resulttype 1009 
               :resulttypmod -1 
               :resultcollid 100 
               :coerceformat 1 
               :location 85
               }

So somehow the planner is messing up and inserting an outer CaseTestExpr
value as the source of the elemexpr's coercion expression.  IIRC,
ruleutils doesn't print the elemexpr at all, which is how come we're not
seeing the mistake in the EXPLAIN output.

However, ArrayCoerceExpr has abused CaseTestExpr that way for awhile, so
it's still not very clear why it broke in v11 and not before ...

            regards, tom lane