Hello

During migration of an application from SQL Server to Postgresql I found some queries which cause LLBLGen to generate invalid code. All problems are related to mismatched types in CASE clause.

Example 1:

QueryableFactory f = new QueryableFactory();
var fetchDeleted = true;
f.Create<AccountEntity>(ReadOnlySession.GetLinqMetaData<LinqMetaData>())
                    .Where(p => p.AccountTypeId == 1 && fetchDeleted ? p.IsDeleted : !p.IsDeleted)
                     .Select(p => p.ExternalId)

Ends up as:

2014-01-08 17:26:58 CET LOG: statement: SELECT "LPLA_1"."external_id" AS "ExternalId" FROM "public"."account" "LPLA_1" WHERE ( ( ( ( (CASE WHEN CASE WHEN ( ( "LPLA_1"."account_type_id" = ((1)::int4)) AND ( 1=1)) THEN 1 ELSE 0 END=1 THEN "LPLA_1"."is_deleted" ELSE CASE WHEN NOT ( "LPLA_1"."is_deleted" = ((TRUE)::bool)) THEN 1 ELSE 0 END END)=1)))) 2014-01-08 17:26:58 CET ERROR: CASE types integer and boolean cannot be matched at character 199

This example can be trivially fixed by using "fetchDeleted==p.isDeleted".

Another case:

f.Create<EndPointConfigurationEntity>(ReadOnlySession.GetLinqMetaData<LinqMetaData>()).Where(p => p.ApiTypeId == 1)
.Select(p => new {
                        isDefaultCatalog = p.Catalog == null ? (bool?) null : p.Catalog.AccountDefaultCatalogs.Any(adc => adc.AccountId == p.AccountId),
                        externalId = p.ExternalId
                     })

Generates:

SELECT CASE WHEN CASE WHEN ( ( "LPA_L1"."id" IS NULL)) THEN 1 ELSE 0 END=1 THEN ((NULL)::varchar) ELSE CASE WHEN EXISTS (SELECT "LPLA_3"."id" AS "Id" FROM "public"."account_default_catalog" "LPLA_3" WHERE ( "LPA_L1"."id" = "LPLA_3"."catalog_id" AND ( "LPLA_3"."account_id" = "LPA_L2"."account_id"))) THEN 1 ELSE 0 END END AS "isDefaultCatalog", "LPA_L2"."external_id" AS "externalId" FROM ( "public"."catalog" "LPA_L1" RIGHT JOIN "public"."end_point_configuration" "LPA_L2" ON "LPA_L1"."id"="LPA_L2"."catalog_id") WHERE ( ( ( ( "LPA_L2"."api_type_id" = ((1)::int4))))) 2014-01-08 17:26:58 CET ERROR: CASE types integer and character varying cannot be matched at character 83

In this case I cannot just drop "p.Catalog == null ? (bool?) null : " part (which fixes the problem), because I expect isDefaultCatalog to return null if endpoint is linked to no catalog or TRUE/FALSE if linked catalog is or not among account defaults.

The same problem can be demonstrated with simpler code:

f.Create<EndPointConfigurationEntity>(ReadOnlySession.GetLinqMetaData<LinqMetaData>())
                     .Where(p => p.ApiTypeId == 1)
                     .Select(p => p.Catalog == null ? (bool?) null : true)

Which gives:

2014-01-08 17:26:58 CET LOG: statement: SELECT CASE WHEN CASE WHEN ( ( "LPA_L1"."id" IS NULL)) THEN 1 ELSE 0 END=1 THEN ((NULL)::varchar) ELSE ((1)) END AS "LPFA_3" FROM ( "public"."catalog" "LPA_L1" RIGHT JOIN "public"."end_point_configuration" "LPA_L2" ON "LPA_L1"."id"="LPA_L2"."catalog_id") WHERE ( ( ( ( "LPA_L2"."api_type_id" = ((1)::int4))))) 2014-01-08 17:26:58 CET ERROR: CASE types integer and character varying cannot be matched at character 83

In general ternary operator seems quite buggy when used with Postgresql. I tested this with .Net 4.0, LLBLGen Pro runtime v 3.5.12.1101 (that's what we use in production) and v 4.1.13.1213 (trial). Postgresql version 9.3.

You are using old runtime libraries. For the sake of testing, please download the latest v3.5 version, reinstall and try again.

I will do that. However 4.1 runtime libraries also used in test (and also exhibiting this bug) should be newest, as I downloaded them 2 days ago.

Unfortunately upgrade to newest 3.5 build (ORM support library v3.5.13.1015) changed nothing in generated code:

2014-01-10 10:39:01 CET LOG:  statement: SELECT "LPLA_1"."external_id" AS "ExternalId" FROM "public"."account"  "LPLA_1" WHERE ( ( ( ( ( (CASE WHEN CASE WHEN ( ( "LPLA_1"."account_type_id" = ((1)::int4)) AND ( ((1)) = ((1)))) THEN 1 ELSE 0 END=1 THEN "LPLA_1"."is_deleted" ELSE CASE WHEN NOT ( "LPLA_1"."is_deleted" = ((TRUE)::bool)) THEN 1 ELSE 0 END END)=1)))))
2014-01-10 10:39:01 CET ERROR:  CASE types integer and boolean cannot be matched at character 211

2014-01-10 10:39:01 CET LOG: SELECT CASE WHEN CASE WHEN ( ( "LPA_L1"."id" IS NULL)) THEN 1 ELSE 0 END=1 THEN ((NULL)::varchar) ELSE CASE WHEN  EXISTS (SELECT "LPLA_3"."id" AS "Id" FROM "public"."account_default_catalog"  "LPLA_3" WHERE ( "LPA_L1"."id" = "LPLA_3"."catalog_id" AND ( "LPLA_3"."account_id" = "LPA_L2"."account_id"))) THEN 1 ELSE 0 END END AS "isDefaultCatalog", "LPA_L2"."external_id" AS "externalId" FROM ( "public"."catalog" "LPA_L1"  RIGHT JOIN "public"."end_point_configuration" "LPA_L2"  ON  "LPA_L1"."id"="LPA_L2"."catalog_id") WHERE ( ( ( ( ( "LPA_L2"."api_type_id" = ((1)::int4))))))
2014-01-10 10:39:01 CET ERROR:  CASE types integer and character varying cannot be matched at character 83

2014-01-10 10:39:01 CET LOG:  statement: SELECT CASE WHEN CASE WHEN ( ( "LPA_L1"."id" IS NULL)) THEN 1 ELSE 0 END=1 THEN ((NULL)::varchar) ELSE ((1)) END AS "LPFA_3" FROM ( "public"."catalog" "LPA_L1"  RIGHT JOIN "public"."end_point_configuration" "LPA_L2"  ON  "LPA_L1"."id"="LPA_L2"."catalog_id") WHERE ( ( ( ( ( "LPA_L2"."api_type_id" = ((1)::int4))))))
2014-01-10 10:39:01 CET ERROR:  CASE types integer and character varying cannot be matched at character 83

We'll look into it, looks like a bug in the IIF -> case mapping in the postgresql dqe.

The query more properly formatted.

SELECT 
    CASE 
        WHEN 
            -- p.Catalog == null
            CASE 
                WHEN 
                    ( ( "LPA_L1"."id" IS NULL)) 
                THEN 1 
                ELSE 0 
            END=1 
        THEN
            -- (bool?) null 
            ((NULL)::varchar) 
        ELSE 
            -- p.Catalog.AccountDefaultCatalogs.Any(adc => adc.AccountId == p.AccountId)
            CASE 
                WHEN EXISTS 
                        (
                            SELECT "LPLA_3"."id" AS "Id" 
                            FROM "public"."account_default_catalog" "LPLA_3" 
                            WHERE ( "LPA_L1"."id" = "LPLA_3"."catalog_id" AND ( "LPLA_3"."account_id" = "LPA_L2"."account_id"))
                        ) 
                THEN 1 
                ELSE 0 
            END 
    END AS "isDefaultCatalog", 
    "LPA_L2"."external_id" AS "externalId" 
FROM ( "public"."catalog" "LPA_L1" 
            RIGHT JOIN "public"."end_point_configuration" "LPA_L2" 
                ON "LPA_L1"."id"="LPA_L2"."catalog_id") 
WHERE ( ( ( ( ( "LPA_L2"."api_type_id" = ((1)::int4))))))

It's immediately clear why the error is given: the nested cases return an integer, while the NULL is casted to varchar. We'll look into why this is. It seems easy enough to reproduce, so we'll setup test queries to see whether we can indeed reproduce it locally.

It's likely caused by the fact the parameter used for the value has no target type information, so it has to fall back onto what the value itself represents: a bool, an int etc. A null value has no type so the default 'varchar' is used. I have to check whether this is true, but I think this is the case. Looking into it.

(edit) simple repro:

[Test]
public void IIFInLinqTest()
{
    using(var adapter = new DataAccessAdapter())
    {
        var metaData = new LinqMetaData(adapter);
        var q = from o in metaData.Orders
                select new
                {
                    OrderId = o.Orderid,
                    NullCID = o.Customerid == null ? (bool?)null : true
                };
        foreach(var v in q)
        {
        }
    }
}

If I don't set the parameter type, it works (in case of null, and when there's no type known, as is the case here, the dbfunctioncall created from the IIF function has 3 parameters, a field, null and 1, no types.

Looking into how to do this reliably for postgresql...

(edit): when not setting the parameter type, the parameter still has the type 'string' (for dbtype), which is equal to when I set the npgsql type enum of the parameter to varchar. However the latter crashes, the former doesn't.

I can make this fix in the postgresql dqe for null values which are otherwise sent to the db in parameters of type 'varchar'. It doesn't solve the problem with non null values, (i.e. when the value is a bool), as those values are still passed to the db as bool typed parameters. This isn't easily solvable, as the value itself IS a bool value, though it needs to be converted to an int, because it's used inside a case statement which works with ints, not bool values (this is one of those silly things in some databases: a predicate results in a bool value, however that's a different bool value than a value of type bool passed in). I do find it odd that there's a real TRUE passed to the db. I'll look into why that is.

You mean in my two last examples? First one is a little different - it assumes that Postgresql will implicitly cast 0/1 to bool type. Maybe it should just use TRUE/FALSE instead of 0/1 for IIF functions in Postgresql?

This for example works:

SELECT "LPLA_1"."external_id" AS "ExternalId" FROM "public"."account" "LPLA_1" WHERE ( ( ( ( ( 
(CASE WHEN 
   CASE WHEN ( ( "LPLA_1"."account_type_id" = ((1)::int4)) AND ( ((TRUE)) = ((TRUE))))
    THEN TRUE ELSE FALSE END=TRUE THEN "LPLA_1"."is_deleted" ELSE CASE WHEN NOT ( "LPLA_1"."is_deleted" = ((TRUE)::bool)) THEN TRUE ELSE FALSE END END)=TRUE)))))

qbast wrote:

You mean in my two last examples? First one is a little different - it assumes that Postgresql will implicitly cast 0/1 to bool type. Maybe it should just use TRUE/FALSE instead of 0/1 for IIF functions in Postgresql?

This for example works:

SELECT "LPLA_1"."external_id" AS "ExternalId" FROM "public"."account" "LPLA_1" WHERE ( ( ( ( ( 
(CASE WHEN 
   CASE WHEN ( ( "LPLA_1"."account_type_id" = ((1)::int4)) AND ( ((TRUE)) = ((TRUE))))
    THEN TRUE ELSE FALSE END=TRUE THEN "LPLA_1"."is_deleted" ELSE CASE WHEN NOT ( "LPLA_1"."is_deleted" = ((TRUE)::bool)) THEN TRUE ELSE FALSE END END)=TRUE)))))

yes, but there still have to be comparisons. This doesn't work:

SELECT CASE WHEN TRUE THEN 'X' ELSE 'Y' END AS 'FOO' FROM ...

as 'TRUE' is a boolean, but it's not as in the result of a comparison. So the comparisons have to be there, and they're all designed to use ints. The null value is converted to a varchar parameter as the code which creates the parameter simply gets a 'null' value and doesn't know any context so it picks varchar. I now have changed that to 'if null, don't set the parameter'. That fixes the null using IIF issue. The other issue where a bool is passed and ends up as a true bool in the case statement is different. The issue is that the IIF always uses 1 or 0, so how that bool ends up there is unclear to me so I'll look into that now too.

The bool issue can be reproduced with:

[Test]
public void IIFInLinqTest2()
{
    using(var adapter = new DataAccessAdapter())
    {
        var metaData = new LinqMetaData(adapter);
        var q = from p in metaData.Products
                select new
                {
                    ProductId = p.Productid,
                    X = p.Categoryid > 0 ? p.Discontinued.Value : !p.Discontinued.Value
                };
        foreach(var v in q)
        {
        }
    }
}

Looking into why there's a hard 'bool' parameter passed and not an int.

(edit)

SELECT  "LPLA_1"."productid" AS "ProductId", 
        CASE 
            WHEN (
                CASE 
                    WHEN 
                        CASE 
                            WHEN ( "LPLA_1"."categoryid" > :p1) 
                            THEN 1 
                            ELSE 0 
                        END=1 
                    THEN "LPLA_1"."discontinued" 
                    ELSE 
                        CASE 
                            WHEN NOT ( "LPLA_1"."discontinued" = :p2) 
                            THEN 1 
                            ELSE 0 
                        END 
                    END)=1 
            THEN 1 
            ELSE 0 
        END AS "X" 
FROM "public"."products"  "LPLA_1"

p2 is of type bool. This is logical, as the not in the select is actual a negated predicate (one can't do NOT (Field) in sql, as that's mixing the two bool types as described earlier). So there's a field compare value predicate, which is simply negated. The query above shows no bool value mixed with ints, still postgresql barfs on this.~~ I don't know why. The former issue, with the null passed in as a varchar parameter is understandable, as it was a result of the case statement, so it would be compared with 1 or 0 in the outer case statement, but not here: everything is 1 or 0, the bool parameter is inside the NOT, not outside of it.

I don't know what to do about this, as there's little else we can do... ~~

(edit) right after I re-read my post I see what the problem is: the 'THEN "LPLA_1"."discontinued" ' statement, as that's of type bool! It's not the parameter, but the field and the case statements which work with 1 and 0. At least that's the only way this can fail...

This can be rewritten to:

var q = from p in metaData.Products
        select new
        {
            ProductId = p.Productid,
            X = p.Categoryid > 0 ? (p.Discontinued.Value==true) : (p.Discontinued.Value==false)
        };

which works.

(edit) cleaning up. So the question now is: The IIF with null, do you want us to backport that fix to v3.5 (it currently is committed to the v4.1 code base) ? (if possible, the code base is different in 3.5, though I expect in this case it should work)

It would be great if you could backport it to 3.5 .

No problem. We'll do that on Monday and will attach it to this thread for your convenience

Please test the attached dll, it's for v3.5. It should fix the null related issue. The boolean related issue, as described, can't be fixed (though a simple query rewrite will fix that).

I tested it and it works fine.