sql,opt: composite_value and composite_value.* should be identical in some contexts (e.g. as a function argument)

[rafiss]

The syntax select row_to_json(x) from my_table x is not supported. CockroachDB assumes x is a column rather than a table reference. A workaround is to use select row_to_json(x.*) from my_table x.

Tools like PostGraphile use this. See graphile/crystal#645 (comment)

The PostgreSQL docs explain more: https://www.postgresql.org/docs/13/rowtypes.html

The composite_value.* syntax results in column expansion of this kind when it appears at the top level of a SELECT output list, a RETURNING list in INSERT/UPDATE/DELETE, a VALUES clause, or a row constructor. In all other contexts (including when nested inside one of those constructs), attaching .* to a composite value does not change the value, since it means “all columns” and so the same composite value is produced again. For example, if somefunc() accepts a composite-valued argument, these queries are the same:

SELECT somefunc(c.*) FROM inventory_item c;
SELECT somefunc(c) FROM inventory_item c;

In both cases, the current row of inventory_item is passed to the function as a single composite-valued argument. Even though .* does nothing in such cases, using it is good style, since it makes clear that a composite value is intended. In particular, the parser will consider c in c.* to refer to a table name or alias, not to a column name, so that there is no ambiguity; whereas without .*, it is not clear whether c means a table name or a column name, and in fact the column-name interpretation will be preferred if there is a column named c.

It seems like we need to update

cockroach/pkg/sql/opt/optbuilder/scope.go

Lines 923 to 928 in 0c4fc8e

	case *tree.UnresolvedName:
	vn, err := t.NormalizeVarName()
	if err != nil {
	panic(err)
	}
	return s.VisitPre(vn)

Right now, t.NormalizeVarName returns either a tree.AllColumnsSelector or a tree.ColumnItem. Currently it decides that based solely on if there is a star expansion in the expression. Perhaps it should be updated to return tree.AllColumnsSelector according to the rules above.

Relates to:

• sql: support attribute notation for single-param functions (x.f equivalent to f(x)) #47477
• sql: resolve table names as tuples #26719

[rafiss]

I've renamed this issue since it's not really specific to row_to_json.

cc @RaduBerinde to triage / determine feasibility.

[rafiss]

Further complicated by the case where table a also has a column a:

Postgres:

rafiss@127:postgres> \d a
+----------+---------+-------------+
| Column   | Type    | Modifiers   |
|----------+---------+-------------|
| a        | integer |  not null   |
| b        | integer |             |
+----------+---------+-------------+
Indexes:
    "a_pkey" PRIMARY KEY, btree (a)


rafiss@127:postgres> select row_to_json(a) from a limit 1;
2021-02-17 13:18:32.571 EST [21919] ERROR:  function row_to_json(integer) does not exist at character 8
2021-02-17 13:18:32.571 EST [21919] HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
2021-02-17 13:18:32.571 EST [21919] STATEMENT:  select row_to_json(a) from a limit 1
function row_to_json(integer) does not exist
LINE 1: select row_to_json(a) from a limit 1
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.


rafiss@127:postgres> select row_to_json(a_alias) from a a_alias limit 1;
+----------------+
| row_to_json    |
|----------------|
| {"a":1,"b":10} |
+----------------+