The generated SQL doesn't use the uuid() function for constant GUIDs

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
zszs
Posts: 3
Joined: Wed 20 Jan 2016 15:18

The generated SQL doesn't use the uuid() function for constant GUIDs

Post by zszs » Wed 20 Jan 2016 15:58

We are using the dotConnect for PostgreSQL together with Entity Framework.

It seems that the generated SQL code doesn't wrap text constants containing Guid values with the uuid() function of Postgres.

This can lead to problems in some complex situations.

For example we've just ran into the error: "CASE types uuid and text cannot be matched" and indeed, Postgres tries to run a statement like:

Code: Select all

CASE WHEN <condition> THEN
    CASE WHEN <condition> THEN
         '56ad6f60-2448-4d1c-97e6-829fa272ed41'
    ELSE
         '10a8d873-0bbd-4135-b9b6-cec2f8b165ad'
    END
ELSE
     "SomeTable"."UUID_Column"
END


Which is not correct. For some reason Postgres (we are using 9.4.5) is not smart enough to recognize that the "inner" CASE statement also have a result of type UUID, not a text.

The following query could be fixed using the uuid() conversion function:

Code: Select all

CASE WHEN <condition> THEN
    CASE WHEN <condition> THEN
         uuid('56ad6f60-2448-4d1c-97e6-829fa272ed41')
    ELSE
         uuid('10a8d873-0bbd-4135-b9b6-cec2f8b165ad')
    END
ELSE
     "SomeTable"."UUID_Column"
END
But since we are talking to the DB through dotConnect (using Entity Framework), we should configure dotConnect to use the uuid() function anytime our query refers some constant Guid value. Is there any setting for this? Or is this a bug?

We are using dotConnect for Postgres 7.4.543.

And the Linq query which produced my first SQL sample was something like:

Code: Select all

Guid g1 = Guid.NewGuid();
Guid g2 = Guid.NewGuid();

var query = baseQuery.SelectMany(a => a.DS, (a, ds) => new {A = a, DS = ds})
.GroupBy(flat => new
{
    UB = flat.DS.UBCatalogValueId,
    TT =  (condition1 ? (condition2 ? g1 : g2) : flat.A.TTCatalogValueId
},
flat => flat.A,
(key, group) => new
{
    key.UB,
    key.TT,
    Count = group.Select(a => a.Id).Distinct().Count()
});

Shalex
Devart Team
Posts: 8033
Joined: Thu 14 Aug 2008 12:44

Re: The generated SQL doesn't use the uuid() function for constant GUIDs

Post by Shalex » Thu 21 Jan 2016 08:28

Please send us a small complete test project with the corresponding DDL/DML script for reproducing.

zszs
Posts: 3
Joined: Wed 20 Jan 2016 15:18

Re: The generated SQL doesn't use the uuid() function for constant GUIDs

Post by zszs » Tue 26 Jan 2016 14:50

Hi, thanks for your answer, I sent you a repro.

Shalex
Devart Team
Posts: 8033
Joined: Thu 14 Aug 2008 12:44

Re: The generated SQL doesn't use the uuid() function for constant GUIDs

Post by Shalex » Tue 26 Jan 2016 17:25

Thank you for the test project. We will investigate the issue and notify you about the result.

Shalex
Devart Team
Posts: 8033
Joined: Thu 14 Aug 2008 12:44

Re: The generated SQL doesn't use the uuid() function for constant GUIDs

Post by Shalex » Tue 09 Feb 2016 10:51

The bug with using the uuid() function for constant GUIDs in the generated SQL is fixed. We will notify you when the corresponding build of dotConnect for PostgreSQL is available for download.

Shalex
Devart Team
Posts: 8033
Joined: Thu 14 Aug 2008 12:44

Re: The generated SQL doesn't use the uuid() function for constant GUIDs

Post by Shalex » Thu 11 Feb 2016 15:38

The new build of dotConnect for PostgreSQL 7.4.592 is available for download: http://forums.devart.com/viewtopic.php?f=3&t=33199.

Post Reply