sql: resolve table names as tuples

[knz]

This is a postgres oddity that was reported in #24866 but not actually fixed by #26628:

> CREATE TABLE t(a INT, b INT);
> SELECT (t).a FROM t;

The syntax (E).C requires E to have tuple type. The name t inside the parentheses refers to a table, not a column. In a sane world the query should fail with " t is not a column in this FROM context". Instead, pg accepts the syntax and resolves t as a table name and treats (t).a as equivalent to t.a.

So the query above fails in CockroachDB (currently) and succeeds in pg. This is the problem to solve.

The caveat emptor is that this special logic kicks in after regular name resolution. Consider the following query:

> SELECT (t).t FROM (SELECT ((1,2) AS t,u) AS t) AS t;

In this context we have in the FROM clause:

• a relational expression (a table) called t
• containing a single column called t
• containing a tuple with a label t

So the expression on the left (t).t can refer either to:

• the field with label t in the tuple contained in column t (column name resolution has priority over table name resolution), or
• the entire tuple column t in table t (table name resolution has priority over column name resolution).

Which one is it? To reveal the behavior in postgres, which does not support on-the-fly label tuples, we have to make an equivalent query using a pre-defined record type:

> SELECT (word).word FROM (SELECT pg_get_keywords() AS word) AS word LIMIT 1;

And the result is... The field word in the tuple column. So column name resolution has priority.
(Which CockroachDB currently gets right because there is no other form of name resolution)

cc @rytaft

---

Noted in Postgres docs: https://www.postgresql.org/docs/12/rowtypes.html#ROWTYPES-USAGE

There are various special syntax rules and behaviors associated with composite types in queries. These rules provide useful shortcuts, but can be confusing if you don't know the logic behind them.

In PostgreSQL, a reference to a table name (or alias) in a query is effectively a reference to the composite value of the table's current row. For example, if we had a table inventory_item as shown above, we could write:

SELECT c FROM inventory_item c;

This query produces a single composite-valued column, so we might get output like:

           c
------------------------
 ("fuzzy dice",42,1.99)
(1 row)`

Note however that simple names are matched to column names before table names, so this example works only because there is no column named c in the query's tables.