sql: allow unaliased subqueries within UDFs

[rharding6373]

There is a compatibility difference with postgres for queries with ambiguous columns.

Examples that fail in postgres with "subquery in FROM must have an alias" but not crdb:

CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT);
SELECT * FROM (SELECT a FROM t1) JOIN (SELECT a FROM t2) ON true;
SELECT * FROM (SELECT * FROM (SELECT a FROM t1));
SELECT a FROM (SELECT * FROM (SELECT a FROM t1));

The following example fails correctly in crdb with "column reference "a" is ambiguous":

SELECT a FROM (SELECT * FROM (SELECT a FROM t1) JOIN (SELECT a FROM  t2) ON true);

Jira issue: CRDB-24094

[mgartner]

Postgres doesn't allow unaliased subqueries in FROM at all. I don't think we can support these within UDFs easily, because we have to qualify column names expanded from *.

So the only decision that I can see us needing to make is whether we remove support for unaliased subqueries in FROM clauses entirely, or live with this oddity where they are allowed only outside a UDF definition.

[mgartner]

Actually, it might be possible to "assign" a unique alias for unaliased subqueries when the UDF is created, removing any ambiguity from the rewritten function body.

[michae2]

[idea from triage] could we come up with a unique alias and use that to fix the UDF problem?

[mgartner]

Here's an explanation of why we currently disallow unaliased subqueries in UDFs: #95710 (review)

[knz]

Actually, it might be possible to "assign" a unique alias for unaliased subqueries when the UDF is created, removing any ambiguity from the rewritten function body.

Yes this seems to be the preferable approach.

[DrewKimball]

It looks like postgres doesn't qualify the column names during star expansion for views, but it does for UDFs:

postgres=# CREATE VIEW v AS SELECT * FROM (SELECT a FROM t1) foo JOIN (SELECT a FROM t2) bar ON true;
ERROR:  column "a" specified more than once

postgres=# CREATE FUNCTION f3() RETURNS RECORD LANGUAGE SQL BEGIN ATOMIC; SELECT * FROM (SELECT a FROM t1) foo JOIN (SELECT a FROM t2) bar ON true; END;
CREATE FUNCTION

[knz]

It looks like postgres doesn't qualify the column names during star expansion for views, but it does for UDFs:

In both cases, the column names are not qualified. It's always valid for a pg projection to contain multple columns with the same name, for example SELECT 1 AS a, 2 AS a is valid/works.

The extra thing that happens with CREATE VIEW is that the DDL code validates that the resulting table descriptor has unique column names.

This is not needed in your UDF example because it returns a RECORD type, which doesn't include column labels. There's no additional uniqueness check for the column names in that case.

[DrewKimball]

The output of \df+ looks like this for that function:

 Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel |     Owner     | Security | Access privileges | Language |             Source code              | Description
--------+------+------------------+---------------------+------+------------+----------+---------------+----------+-------------------+----------+--------------------------------------+-------------
 public | f3   | record           |                     | func | volatile   | unsafe   | andrewkimball | invoker  |                   | sql      | BEGIN ATOMIC                        +|
        |      |                  |                     |      |            |          |               |          |                   |          |  SELECT foo.a,                      +|
        |      |                  |                     |      |            |          |               |          |                   |          |      bar.a                          +|
        |      |                  |                     |      |            |          |               |          |                   |          |     FROM (( SELECT t1.a             +|
        |      |                  |                     |      |            |          |               |          |                   |          |             FROM t1) foo            +|
        |      |                  |                     |      |            |          |               |          |                   |          |       JOIN ( SELECT t2.a            +|
        |      |                  |                     |      |            |          |               |          |                   |          |             FROM t2) bar ON (true));+|
        |      |                  |                     |      |            |          |               |          |                   |          | END                                  |
(1 row)