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

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

Postby 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()
});
zszs
 
Posts: 3
Joined: Wed 20 Jan 2016 15:18

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

Postby Shalex » Thu 21 Jan 2016 08:28

Please send us a small complete test project with the corresponding DDL/DML script for reproducing.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

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

Postby zszs » Tue 26 Jan 2016 14:50

Hi, thanks for your answer, I sent you a repro.
zszs
 
Posts: 3
Joined: Wed 20 Jan 2016 15:18

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

Postby 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: 7654
Joined: Thu 14 Aug 2008 12:44

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

Postby 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: 7654
Joined: Thu 14 Aug 2008 12:44

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

Postby 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.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for PostgreSQL