Hi,

After updating npgsql from version 2.2.4.1 to 3.2.5 following code doesn't work any longer.

var q = from p in metaData.PracticePageVisit
        group p by new
        {
            Time = MyFunctions.DateTrunc("hour", p.DateCreated),
        } into g
        orderby g.Key.Time
        select new PracticePageVisitCounts
        {
            Count = g.Count(),
            Time = g.Key.Time,
        };

I also tried with FetchTypedListAsync, same error.

Error is actually from Npgsql/PostgreSQL:

"42803: column "LPLA_1.DateCreated" must appear in the GROUP BY clause or be used in an aggregate function"

Routine: "check_ungrouped_columns_walker"

Issue seems to be with sending two different parameters, p2 in select and p6 in group by. There is one more parameter p4 and it's not used anywhere.

I tried sending same parameter two both select and group by and it works. I did that by using npgsql directly, I'm not sure how to do that with LLBLGen.

Generated sql:

Query: SELECT "LPA_L1"."LPAV_" AS "Count", "LPA_L1"."Time" FROM (SELECT date_trunc(:p2, "LPLA_1"."DateCreated") AS "Time", COUNT(*) AS "LPAV_" FROM "public"."PracticePageVisit"  "LPLA_1" GROUP BY date_trunc(:p6, "LPLA_1"."DateCreated")) "LPA_L1" ORDER BY "LPA_L1"."Time" ASC
Parameter: :p2 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: "hour".
Parameter: :p4 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: "hour".
Parameter: :p6 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: "hour".

Postgresql 9.4 LLBLGen 4.2

Thank you for your help, David.

I think they compare strings here and the parameter names differ so their code thinks they're two different values and thus gives the error. We emit different parameters as we don't have a central parameter collector when creating the query (only at the end), as the various parts are converted to SQL in various objects. It's not always possible to re-use parameters (some DBs don't like it, but this is generic code) even tho the value is the same.

To work around this issue, I think it's best if you create a different function mapping specific for DateTrunc("Hour", ... ) and, like DateTruncHour(field), and pass "hour" hardcoded in the function call. This way it's not transformed to a parameter and the strings will match for the npgsql checker.

that, or convince Roji that he'll update his 'check_ungrouped_columns_walker' function so that it takes into account parameter values.

(edit) I think I'll file an issue on npgsql about this, as it doesn't really makes sense for them to parse it at that level: the DB will do it again, and it causes false positives in this case.

(edit) I see you already opened an issue, I'll comment on that

For reference: https://github.com/npgsql/npgsql/issues/1716 npgsql explanation why this happens.

At the moment we don't have any way to force the re-use of the same parameter in this case. The only way to work around this, is to avoid the constant here, which is 'hour', by creating a function mapping which includes the constant, so not: date_trunc({0}, {1})

but create a mapping for Hour: DateTruncHour('hour', {0})

which then recieves 1 parameter and not 2 and the parameter issue is then avoided as no parameter is issued for the constant. The field name is embedded in the fragment which is equal in both the projection and group by clause.

Thank you.