Invalid sql type for null string constant in generated sql query brokes union all operation

[parus95]

I have (in PostgreSQL 10.21 (Ubuntu 10.21-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit) such a table:

create table "MyTable" (
	"Id" int primary key,
	"Name" text not null
)

I have such a code (C#, netcoreapp3.1):

using LinqToDB;
using System;
using System.Linq;

namespace L2DbNullStringTest
{
    internal class Program
    {
        static void Main(string[] args)
        {
            var db = new LinqToDB.Data.DataConnection(
                LinqToDB.ProviderName.PostgreSQL95,
                "Server=192.168.1.2;Database=rkuznetsov_sandbox;User Id=developer;Password=secret;");

            var table = db.GetTable<MyTable>();

            var query1 = table.Select(x => new QueryItem
            {
                Id = "I-" + x.Id,
                Name = x.Name
            });
            var query2 = table.Select(x => new QueryItem
            {
                Id = null,
                Name = "QUASI-" + x.Name,
            });
            var resultingQuery = query1.Concat(query2);

            var sql = resultingQuery.ToString();

            Console.WriteLine("-- Linq2db version: " + table.GetType().Assembly.GetName().Version);
            Console.WriteLine(sql);
        }

        public sealed class QueryItem
        {
            public string Id { get; set; }
            public string Name { get; set; }
        }
        public sealed class MyTable
        {
            public int Id { get; set; }
            public string Name { get; set; }
        }
    }
}

With linq2db version 3.6.0.0
it's generates a valid sql query:

-- Linq2db version: 3.6.0.0
--  PostgreSQL.9.5 PostgreSQL
-- VALID query
SELECT
        'I-' || Cast(x."Id" as VarChar(11)),
        x."Name"
FROM
        "MyTable" x
UNION ALL
SELECT
        Cast(NULL as text),
        'QUASI-' || x_1."Name"
FROM
        "MyTable" x_1

But with linq2db versions 3.7.0.0 and newer (4.1.1.0, 4.1.0.0, 4.0.1.0, 4.0.0.0) it's generates a invalid sql query:

-- Linq2db version: 4.1.1.0
-- Linq2db version: 4.1.0.0
-- Linq2db version: 4.0.1.0
-- Linq2db version: 4.0.0.0
-- Linq2db version: 3.7.0.0
--  PostgreSQL.9.5 PostgreSQL
-- INVALID query
SELECT
        'I-' || Cast(x."Id" as VarChar(11)),
        x."Name"
FROM
        "MyTable" x
UNION ALL
SELECT
        Cast(NULL as Int), --- < INVALID TYPE,MUST BE text BUT NOT Int
        'QUASI-' || x_1."Name"
FROM
        "MyTable" x_1

executing this query in postgres throws error: SQL Error [42804]: ERROR: UNION types text and integer cannot be matched

L2DbNullStringTest.zip

[MaceWindu]

That's strange. Could you try latest preview? https://dev.azure.com/linq2db/linq2db/_artifacts/feed/linq2db/NuGet/linq2db/overview/4.2.0-rc.9222

[parus95]

@MaceWindu

same (incorrect) result at 4.2.0-rc.9222

-- Linq2db version: 4.2.0.0
--  PostgreSQL.9.5 PostgreSQL
SELECT
        'I-' || Cast(x."Id" as VarChar(11)),
        x."Name"
FROM
        "MyTable" x
UNION ALL
SELECT
        Cast(NULL as Int),
        'QUASI-' || x_1."Name"
FROM
        "MyTable" x_1

L2DbNullStringTest_4.2.0-rc.9222.zip

[MaceWindu]

Thanks, will look into it. Int is completely wrong here...