UPDATE x FROM y RETURNING y.something over SQL adapter

[aljazerzen]

We want to also support such queries:

UPDATE x
SET a = y.a
FROM (...) AS y
RETURNING y.b

... because PostgreSQL supports it.

Note that RETURNING contains a reference to y.b,
which is not used anywhere in the SET clause.

When compiling UPDATE, we:

• construct a "value input" relation, that provides the values for the SET clause,
• compile insert x { a := ... }, using value relation for the inserted values,
• resolve RETURNING in scope with a single rel var, which is the rel var of the
  table we have just updated (x in this example), with all of its columns, as
  they are after the update.

To support refs to arbitrary columns of y, we will have to:

• export all columns in scope from the value relation,
• when resolving RETURNING, join the updated relation with the value relation
  so we can pull columns of y out of it.

Why do we need all columns of y, why not just the ones we need?

Because resolver does not "pull columns out of provided rel vars" as the edgeql
backend compiler does. Resolver needs a set of "tables in scope" where each of the
columns has a defined materialization. Most of the time, this is ColumnByName,
which means that you can materialize it into a single pgast.ColumnRef.
Other ways of materialization are even simpler (static or a provided expression).

what happens if there are multiple FROM clauses?

So that would be:

UPDATE x
SET a = y.a
FROM (...) AS y, (...) AS z, (...) AS v
RETURNING y.b, z.c, v.d

We need to add all columns of all tables in scope (except the table we are updating).

[aljazerzen]

A test case:

    async def test_sql_dml_update_18(self):
        # Test that RETURNING has FROM in scope

        users = await self.squery_values(
            '''
            INSERT INTO "User" (id) VALUES (DEFAULT), (DEFAULT) RETURNING id
            '''
        )
        await self.squery_values(
            '''
            WITH u(id) as (VALUES ($1), ($2))
            INSERT INTO "Document" (title, owner_id)
            SELECT 'Report', u.id FROM u
            RETURNING title, owner_id
            ''',
            str(users[0][0]),
            str(users[1][0]),
        )

        res = await self.squery_values(
            '''
            UPDATE "Document"
            SET owner_id = owner_id
            FROM "User" u
            WHERE "Document".owner_id = u.id
            RETURNING title, u.id
            ''',
        )
        self.assertEqual(
            set(res),
            set([
                ['Report (insert) (updated)', users[0][0]],
                ['Report (insert) (updated)', users[1][0]],
            ])
        )