Exception on creating a query with index and predicate including DateTimeOffset using Postgresql dialect

[mluatgmxdotch]

Hi all,

The following (simplified) query using an predicate and an index

var tokens = session.Query<OpenIdToken, OpenIdTokenIndex>(
    token => token.CreationDate < threshold.UtcDateTime, collection: OpenIdToken.OpenIdCollection)
    .Take(100).ListAsync();

results in the following exception using postgresql dialect but not on sqlite:

[09:00:29 Error] OrchardCore.OpenId.Tasks.OpenIdBackgroundTask
An error occurred while pruning authorizations from the database.
System.Reflection.TargetException: Object does not match target type.
   at System.Reflection.MethodInvokerCommon.ValidateInvokeTarget(Object target, MethodBase method)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at YesSql.Services.DefaultQuery.Evaluate(Expression expression)
   at YesSql.Services.DefaultQuery.ConvertFragment(IStringBuilder builder, Expression expression)
   at YesSql.Services.DefaultQuery.ConvertFragment(IStringBuilder builder, Expression expression)
   at YesSql.Services.DefaultQuery.ConvertComparisonBinaryExpression(IStringBuilder builder, BinaryExpression expression, String operation)
   at YesSql.Services.DefaultQuery.ConvertFragment(IStringBuilder builder, Expression expression)
   at YesSql.Services.DefaultQuery.ConvertPredicate(IStringBuilder builder, Expression expression)
   at YesSql.Services.DefaultQuery.ConvertEqualityBinaryExpression(IStringBuilder builder, BinaryExpression expression, String operation)
   at YesSql.Services.DefaultQuery.ConvertFragment(IStringBuilder builder, Expression expression)
   at YesSql.Services.DefaultQuery.ConvertPredicate(IStringBuilder builder, Expression expression)
   at YesSql.Services.DefaultQuery.Filter[TIndex](Expression`1 predicate)
   at YesSql.Services.DefaultQuery.Query`1.YesSql.IQuery<T>.With[TIndex](Expression`1 predicate)
   at YesSql.QueryExtensions.Query[T,TIndex](ISession session, Expression`1 predicate, Boolean filterType, String collection)
   at YesSql.QueryExtensions.Query[T,TIndex](ISession session, Expression`1 predicate, String collection)
   at OrchardCore.OpenId.YesSql.Stores.OpenIdAuthorizationStore`1.PruneAsync(DateTimeOffset threshold, CancellationToken cancellationToken)
   at OrchardCore.OpenId.Tasks.OpenIdBackgroundTask.DoWorkAsync(IServiceProvider serviceProvider, CancellationToken cancellationToken)

Full original query can be found here.

Used Version:

• YesSql 3.4.0
• OrchardCore 1.7.2

This only occurs when using PostgreSQL dialect.
Workaround that helped but I'm not sure about the side effects:

Removing the DateTimeOffset type handler in PostgreSqlDialect

// DateTimeOffset are stored as Utc DateTimes in timesstamptz fields
// Represents a moment in time
AddTypeHandler<DateTimeOffset, DateTime>(x => x.UtcDateTime);

https://github.com/sebastienros/yessql/blob/main/src/YesSql.Provider.PostgreSql/PostgreSqlDialect.cs#L96

or changing the predicate to use a temporary date-time variable (not sure why this helped)

var thresholdUtcDateTime = threshold.UtcDateTime
var tokens = session.Query<OpenIdToken, OpenIdTokenIndex>(
    token => token.CreationDate < thresholdUtcDateTime, collection: OpenIdToken.OpenIdCollection)
    .Take(100).ListAsync();

[sebastienros]

not sure why this helped

Because it didn't have to convert a DateTimeOffset in that case, which must be the issue.

The bug is probably happening because a DateTimeOffset is compared to a DateTime and it's not correctly handled. Should be easy to repro, thanks.

[Kees-Schouten]

I'm running into the same issue with Postgresql. The reproduction with Orchard Core is very simple.

1. Debug/Start "OrchardCore.Cms.Web"
2. Run setup wizard and use Postgresql connection.
3. Enable OpenId and OpenId Background task management feature in Orchard Core admin.
4. Change the Task "OpenID Cleaner" to run every minute instead of the default of 30 minutes.
5. Wait one minute and It will throw the Exception and won't clean expired sessions.