Re: row_to_json(), NULL values, and AS

[ Hi Neil, long time no see ]

Neil Conway <neil(dot)conway(at)gmail(dot)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