SQLProvider icon indicating copy to clipboard operation
SQLProvider copied to clipboard

Simple group by query with postgresql causes doubling up of double quotes when using double quoted declared fields

Open kgday opened this issue 1 year ago • 3 comments

In PostGreSq with a table where the fields are declared double quotedl, I have the following query:

                let! costs =
                    query {
                        for pol in ctx.Public.PurchaseOrderLine do
                            where (pol.JobId.Value = prodOrderID)
                            groupBy (pol.JobId, pol.ChargeAccount) into g
                            select 
                                {
                                    JobID = g.Key |> fst |> Option.defaultValue 0L
                                    ChargeAccount = g.Key |> snd
                                    TotalCost = g.Sum(fun o -> o.TotalCost)
                                }
                    }
                    |> Seq.executeQueryAsync   

The following sql is emitted:

SELECT "pol"."JobID" as ""pol"."JobID"", "pol"."ChargeAccount" as ""pol"."ChargeAccount"", SUM("pol"."TotalCost") as "pol.SUM_TotalCost" FROM "public"."PurchaseOrderLine" as "pol" WHERE (("pol"."JobID" = @param1)) GROUP BY "pol"."JobID", "pol"."ChargeAccount">, Parameters<@param1=12482>

Note the as parts have been double quoted twice. This causes postgresql to return an error:

zero-length delimited identifier at or near """"

In fairness, groupby is discouraged in the docs.

The work arround obviously for me at present is to create a view or do the grouping in the client. But I would have thought this was a simple enough group by.

kgday avatar Jul 06 '23 03:07 kgday