Hi,

I'm running into an issue where a query doesn't work with PostgreSQL, but works fine with Oracle. Here is a minimal example:

var bucket = new RelationPredicateBucket();
bucket.Relations.Add(CrmRelatieViewEntity.Relations.CrmRelatieEntityUsingIDAsLong, JoinHint.Left);

var sortClause = CrmRelatieViewFields.VolledigeNaam | SortOperator.Ascending;
sortClause.CaseSensitiveCollation = true;

var collection = new EntityCollection<CrmRelatieViewEntity>();
adapter.FetchEntityCollection(
    collectionToFill: collection,
    filterBucket: bucket,
    maxNumberOfItemsToReturn: 10,
    sortClauses: new SortExpression(sortClause),
    pageNumber: 1,
    pageSize: 10);

The error message from the database is:

SQL Error [42P10]: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

I don't think it's possible to add a field to the select list when using FetchEntityCollection. I think the DISTINCT is not strictly necessary here for correct results because in this case the joins will not return multiple rows (1 record in CrmRelatieViewEntity will match 1 record in CrmRelatieEntity).

Removing either the relation (prevents DISTINCT from being generated) or the CaseSensitiveCollation = true (prevents UPPER(...) from being generated) will make it succesfully return results. I don't want to remove the relation because I use it for filtering. I also want to keep the order case insensitive.

Could it be a bug or limitation in the query engine for PostgreSQL? Is there a way to make this work?

Edwin

LLBLGen runtime 5.9.0
PostgreSQL 13.3

What's the SQL query that's been generated and executed?

(edit) Could you set this property (SortClause.EmitAliasForExpressionAggregateField) on the SortClause to false please: https://www.llblgen.com/Documentation/5.9/ReferenceManuals/LLBLGenProRTF/html/D04E34F9.htm Not sure if it's going to work tho, it looks like the query engine has a limitation here.

The Distinct is emited because of the join which could lead to duplicates.

Otis wrote:

What's the SQL query that's been generated and executed?

(edit) Could you set this property (SortClause.EmitAliasForExpressionAggregateField) on the SortClause to false please: https://www.llblgen.com/Documentation/5.9/ReferenceManuals/LLBLGenProRTF/html/D04E34F9.htm Not sure if it's going to work tho, it looks like the query engine has a limitation here.

Thank you for your reply. Unfortunately it does not seem to have any effect. This is the query:

select
    distinct "bouwdba"."vw_crmrelatie"."relatie_id" as "IDAsLong",
    "bouwdba"."vw_crmrelatie"."extern_nr" as "ExternNr",
    "bouwdba"."vw_crmrelatie"."persoon_id" as "PersoonIDa",
    "bouwdba"."vw_crmrelatie"."organisatie_id" as "OrganisatieIDa",
    "bouwdba"."vw_crmrelatie"."klant_id" as "KlantIDa",
    "bouwdba"."vw_crmrelatie"."aanhef_code" as "AanhefCode",
    "bouwdba"."vw_crmrelatie"."aanhef_voluit" as "AanhefVoluit",
    "bouwdba"."vw_crmrelatie"."aanhef_voluit2" as "AanhefVoluit2",
    "bouwdba"."vw_crmrelatie"."aanhef_omschrijving" as "AanhefOmschrijving",
    "bouwdba"."vw_crmrelatie"."volledigenaam" as "VolledigeNaam",
    -- more fields removed here for brevity
    "bouwdba"."vw_crmrelatie"."rechtsvorm_omschrijving" as "RechtsVormOmschrijving"
from
    ("bouwdba"."crmrelatie"
right join "bouwdba"."vw_crmrelatie" on
    "bouwdba"."crmrelatie"."id" = "bouwdba"."vw_crmrelatie"."relatie_id")
order by
    UPPER("bouwdba"."vw_crmrelatie"."volledigenaam") asc
limit 10

Otis wrote:

The Distinct is emited because of the join which could lead to duplicates.

This is what I thought... but is it necessary to emit distinct if the join is known to only have one matching record in the other table (defined as 1:1 as it is in this case or from the Many-side of a 1:Many relation)?

In the non-minimal version of this code I have 3 more joins from CrmRelatieEntity to an entity named CrmRelatieRisicoClassificatieEntity (also matching at most one record), but I could more easily replace those with something something like a WHERE IN / FieldCompareSetPredicate as they're only being used to check for (non-)existence (somewhat clumsily). If I do this it would match the minimal example exactly when it comes to joins.

PostgreSQL support is being added to this application that previously always used Oracle. I'm trying to avoid a complete overhaul of queries where possible to reduce the chance of introducing errors into the existing code. In many cases there is no problem, but there are edge cases like this one :-)

The engine looks at relationships and determines it might result in duplicates, as it doesn't know if the entire resultset is in the projection. It then checks if the sort clause field is present in the projection, and herein lies the problem.

By specifying the case sensitivity on the sortclause, the field itself has no expression. So the engine sees the field, it has no expression and it is present in the projection as-is, so it flags it 'ok', and distinct can be emitted.

To avoid this, use:

var sortClause = CrmRelatieViewFields.VolledigeNaam.ToUpper().Ascending();

(this uses the QuerySpec extension methods)

This will place the UPPER() function call on the field, so this time it does have an expression on it, and therefore the engine will flag it as distinct violating and the query won't have distinct. e.g. this query works fine:

[Test]
public void FetchEntityQueryWithJoinAndDistinctAndCaseInsensitiveSortTest()
{
    using(var adapter = new DataAccessAdapter())
    {
        var qf = new QueryFactory();
        var q = qf.Order
                  .From(QueryTarget.LeftJoin(qf.Customer).On(OrderFields.CustomerId.Equal(CustomerFields.CustomerId)))
                  .Where(CustomerFields.Country=="USA")
                  .OrderBy(OrderFields.ShipName.ToUpper().Ascending());
        var results = adapter.FetchQuery(q);
        Assert.AreEqual(121, results.Count);
    }
}

which results in the query:

SELECT [Northwind].[dbo].[Orders].[CustomerID] AS [CustomerId],
       [Northwind].[dbo].[Orders].[EmployeeID] AS [EmployeeId],
       [Northwind].[dbo].[Orders].[Freight],
       [Northwind].[dbo].[Orders].[OrderDate],
       [Northwind].[dbo].[Orders].[OrderID]    AS [OrderId],
       [Northwind].[dbo].[Orders].[RequiredDate],
       [Northwind].[dbo].[Orders].[ShipAddress],
       [Northwind].[dbo].[Orders].[ShipCity],
       [Northwind].[dbo].[Orders].[ShipCountry],
       [Northwind].[dbo].[Orders].[ShipName],
       [Northwind].[dbo].[Orders].[ShippedDate],
       [Northwind].[dbo].[Orders].[ShipPostalCode],
       [Northwind].[dbo].[Orders].[ShipRegion],
       [Northwind].[dbo].[Orders].[ShipVia]
FROM   ([Northwind].[dbo].[Orders]
        LEFT JOIN [Northwind].[dbo].[Customers]
            ON [Northwind].[dbo].[Orders].[CustomerID] = [Northwind].[dbo].[Customers].[CustomerID])
WHERE  (([Northwind].[dbo].[Customers].[Country] = @p1))
ORDER  BY UPPER([Northwind].[dbo].[Orders].[ShipName]) ASC 

It will postpone the duplicate filtering to the client. This means that if you fetch 1000s of rows, the materializer will be a bit slower as it has to do duplicate filtering on the client.

Your suggestion helped me get a working solution. Thanks for the help.

However, it doesn't work exactly how I expected it to work:

// doesn't work:
var sortClause = CrmRelatieViewFields.VolledigeNaam
    .ToUpper()
    .Ascending();
// System.ArgumentNullException: 'Value cannot be null. (Parameter 'collection')'
//   at System.ThrowHelper.ThrowArgumentNullException(ExceptionArgument argument)
//   at System.Collections.Generic.List`1.InsertRange(Int32 index, IEnumerable`1 collection)
//   at SD.LLBLGen.Pro.ORMSupportClasses.SortExpression.ToQueryText(Boolean aliasesForExpressionsAggregates)
//   at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.AppendOrderByClause(ISortExpression sortClauses, QueryFragments destination, IQuery query)
//   at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQImpl(QueryParameters parameters, DbConnection connectionToUse, Boolean emitQueryHints)
//   at SD.LLBLGen.Pro.DQE.PostgreSql.DynamicQueryEngine.CreatePagingSelectDQ(QueryParameters parameters, DbConnection connectionToUse)
//   at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(QueryParameters parameters, DbConnection connectionToUse)
//   at SD.LLBLGen.Pro.ORMSupportClasses.Adapter.QueryCreationManager.CreateSelectDQ(QueryParameters parameters)
//   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchEntityCollectionInternal(QueryParameters parameters)
//   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchEntityCollection(QueryParameters parameters)
//   ...


// works:
var sortClause = CrmRelatieViewFields.VolledigeNaam
    .SetExpression(new DbFunctionCall("UPPER", new object[] { CrmRelatieViewFields.VolledigeNaam }))
    .Ascending();

This is in the example code I posted earlier, and with sortClause.CaseSensitiveCollation = true; also removed.

The code I have here will probably need to be rewritten to do the query in a more efficient way, because it can return thousands of records in some cases. I'm OK with the DbFunctionCall for now, but I don't understand why it is necessary because it seems like this should do basically the same thing.

As Otis mentions

(this uses the QuerySpec extension methods)

You have used a QuerySpec construct in a LowLevel fetch method (FetchEntityCollection), that's why it didn't work with you.

So either use Otis complete fetch example, or use yours with the DBFunctionCall.

Indeed. Field.ToUpper() returns a FunctionMappingExpression, which is something the low level api can't deal with.

Ah, ok. Good to know.

Thanks 👍