Re: row_to_json(), NULL values, and AS - Mailing list pgsql-bugs

[ Hi Neil, long time no see ]

Neil Conway <neil.conway@gmail.com> writes:
> The following behavior does not seem self-consistent to me:

Likewise.

> In particular, it is unclear to me why removing the targetlist alias
> in the subquery in the third example should change the result set of
> the parent query.

Looking at "explain verbose" output, it seems like it's not row_to_json's
fault; rather, we seem to be mishandling expansion of the whole-row Var:

regression=# explain verbose select json_agg(row_to_json(x)) from (select *,
row_to_json(null) as jjj from generate_series(1, 3)) x;
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Aggregate  (cost=15.00..15.01 rows=1 width=32)
   Output: json_agg(row_to_json(ROW(generate_series.generate_series, NULL::json)))
   ->  Function Scan on pg_catalog.generate_series  (cost=0.00..10.00 rows=1000 width=4)
         Output: generate_series.generate_series
         Function Call: generate_series(1, 3)
(5 rows)

That's fine, but:

regression=# explain verbose select json_agg(row_to_json(x)) from (select *,
row_to_json(null) from generate_series(1, 3)) x;
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Aggregate  (cost=12.50..12.51 rows=1 width=32)
   Output: json_agg(NULL::json)
   ->  Function Scan on pg_catalog.generate_series  (cost=0.00..10.00 rows=1000 width=0)
         Output: generate_series.generate_series
         Function Call: generate_series(1, 3)
(5 rows)

That looks like it might be a bug in what we do with whole-row Vars
during subquery flattening.  But if you put an "offset 0" into the
subquery to prevent flattening, you get different but just as weird
misbehavior:

regression=# explain verbose select json_agg(row_to_json(x)) from (select *,
row_to_json(null) as jjj from generate_series(1, 3) offset 0) x;
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Aggregate  (cost=25.00..25.02 rows=1 width=32)
   Output: json_agg(row_to_json(x.*))
   ->  Subquery Scan on x  (cost=0.00..20.00 rows=1000 width=28)
         Output: x.*
         ->  Function Scan on pg_catalog.generate_series  (cost=0.00..10.00 rows=1000 width=36)
               Output: generate_series.generate_series, NULL::json
               Function Call: generate_series(1, 3)
(7 rows)

regression=# explain verbose select json_agg(row_to_json(x)) from (select *,
row_to_json(null) from generate_series(1, 3) offset 0) x;
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Aggregate  (cost=22.50..22.52 rows=1 width=32)
   Output: json_agg((NULL::json))
   ->  Function Scan on pg_catalog.generate_series  (cost=0.00..10.00 rows=1000 width=36)
         Output: NULL::integer, NULL::json
         Function Call: generate_series(1, 3)
(5 rows)

I'm not sure if this is just another artifact of the same problem.
The extra parens in the json_agg() argument are suspicious to put
it mildly, but I've not dug into it to see what the plan tree
really looks like.

            regards, tom lane