SQL: Type resolution within VALUES

[philrz]

The following query currently fails in SuperDB but works in Postgres.

$ super -version
Version: 7f23f6560

$ super -c "SELECT * FROM (VALUES (3), ('42')) v1;"
values clause must contain uniformly typed values at line 1, column 28:
SELECT * FROM (VALUES (3), ('42')) v1;
                           ~~~~~~

Details

Repro is with super commit 7f23f65. This was found via a query from a sqllogictest.

I was somewhat surprised to see that query works fine in Postgres.

$ psql postgres
psql (17.5 (Homebrew))
Type "help" for help.

postgres=# SELECT * FROM (VALUES (3), ('42')) v1;
 column1 
---------
       3
      42
(2 rows)

At this point I learned about how the data types for columns in VALUES lists are assigned using the same rules as for UNION. So for instance here the rules meant that in the end an integer type was assigned for the column:

postgres=# SELECT column1, pg_typeof(column1)
FROM (VALUES (3), ('42')) v1(column1);
 column1 | pg_typeof 
---------+-----------
       3 | integer
      42 | integer
(2 rows)

Though we could have made the rule tilt toward a text type if we'd added a cast:

postgres=# SELECT column1, pg_typeof(column1)
FROM (VALUES (3::text), ('42')) v1(column1);
 column1 | pg_typeof 
---------+-----------
 3       | text
 42      | text
(2 rows)

Or made it fail if we'd made the type values wholly incompatible.

postgres=# SELECT column1, pg_typeof(column1)
FROM (VALUES (3), ('foo')) v1(column1);
ERROR:  invalid input syntax for type integer: "foo"
LINE 2: FROM (VALUES (3), ('foo')) v1(column1);
                           ^

I'd opened #6012 before I bumped into this issue, so that one now looks like it might be a special case of this one.