Set operations: infer type mappings from the other side

[roji]

When performing set operations, if a constant/parameter is specified, we should infer the type mapping from the column on the other side. Currently, queries which do this frequently fail since we don't support any type/facet mismatch between the two sides (e.g. #19129).

var test = await DbContext.Foo
    .Select(f => new
    {
        FooProp = f.FooProp,
        BarProp = "SomeBar"
    })
    .Union(DbContext.Bar
        .Select(b => new
        {
            FooProp = "SomeFoo",
            BarProp = b.Bar
        }))
    .ToListAsync();

[ajcvickers]

Note from triage: make sure that truncation does not occur in cases where inferred type mapping has a max length that may not accommodate values from both sides.

[roji]

Did a quick investigation into this, I think the behavior corresponds to what @smitpatel said in the meeting.

When performing a union, SQL Server picks the bigger length facet from both sides. For columns, that's the length specified in the column definition, but for literals, it's the actual length of the provided literal. I saw similar behavior with the CONCAT_WS function, which returns types based on actual values provided. We can calculate this client-side for literals, but not for parameters.

Note that for PG, the length facet is simply dropped: the union between varchar(x) and varchar(y) yields varchar (which is a variable-length string with no constraint).

SQL Server

DROP TABLE IF EXISTS data;
CREATE TABLE data (
    id int IDENTITY PRIMARY KEY,
    name nvarchar(5) NULL
);

INSERT INTO data (name) VALUES ('hell');

SELECT name FROM data UNION SELECT 'goodbye'; -- shows full hell and goodbye (no truncation)
SELECT name FROM data UNION SELECT CAST('foo' AS nvarchar(3)); -- shows hell and foo

DECLARE @what sql_variant;
SELECT @what = (SELECT TOP(1) * FROM (SELECT name FROM data UNION SELECT 'goodbye') x);
SELECT SQL_VARIANT_PROPERTY(@what, 'BaseType'), SQL_VARIANT_PROPERTY(@what, 'MaxLength');
-- returns nvarchar with length 14. 14 is 7 (the length of goodbye) times 2 (nvarchar).

DECLARE @what sql_variant;
SELECT @what = (SELECT TOP(1) * FROM (SELECT name FROM data UNION SELECT CAST('foo' AS nvarchar(3))) x);
SELECT SQL_VARIANT_PROPERTY(@what, 'BaseType'), SQL_VARIANT_PROPERTY(@what, 'MaxLength');
-- returns nvarchar with length 10. 10 is 5 (from the column definition) times 2 (nvarchar).

PostgreSQL

DROP TABLE IF EXISTS data;
CREATE TABLE data (
    id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    name varchar(5)
);

INSERT INTO data (name) VALUES ('hell');

SELECT name FROM data UNION SELECT 'goodbye'; -- shows full hell and goodbye (no truncation)
SELECT name FROM data UNION SELECT 'foo'::varchar(3); -- shows hell and foo

SELECT pg_typeof((SELECT * FROM (SELECT name FROM data UNION SELECT 'goodbye') x LIMIT 1));
-- shows character varying, no length facet

SELECT pg_typeof((SELECT * FROM (SELECT name FROM data UNION SELECT 'foo'::varchar(3)) x LIMIT 1));
-- also shows character varying, no length facet. So doing union over varchar(x) and varchar(y) returns varchar
-- with no length

[ioaznnis]

Also not work for numeric type.

var test = await DbContext.Foo
    .Select(f => new
    {
        FooPropFirst = 0,
        FooPropSecond = f.FooProp,
    })
    .Union(DbContext.Foo
        .Select(b => new
        {
             FooPropFirst = f.FooProp,
             FooPropSecond = 0,
        }))
    .ToListAsync();

But work with workaround for PostgreSQL numeric:

var test = await DbContext.Foo
    .Select(f => new
    {
        FooPropFirst = f.FooProp * 0,
        FooPropSecond = f.FooProp * 1,
    })
    .Union(DbContext.Foo
        .Select(b => new
        {
             FooPropFirst = f.FooProp * 1,
             FooPropSecond = f.FooProp * 0,
        }))
    .ToListAsync();

[ajcvickers]

Related to #15586