Repodb: Bug: Exception is being thrown on date time queries (Postgresql)

[Swoorup]

On postgres, queries with a date does not appear to work.

It fails with the exception message "Can't write CLR type System.DateTime with handler type TextHandler"

conn.Query(where=(fun (e: EventReadEntity) -> e.timestamp >= startDate && e.timestamp <= endDate), orderBy=orderByPositionDesc)

The entity is defined as

  type EventReadEntity =
    { id: Guid
      position: int64
      correlation_id: Guid
      causation_id: Guid
      stream_key: string
      version: int
      name: string
      [<NpgsqlTypeMap(NpgsqlDbType.Jsonb)>]
      payload: string
      [<NpgsqlTypeMap(NpgsqlDbType.Jsonb)>]
      metadata: string
      schema_revision: int
      [<NpgsqlTypeMap(NpgsqlDbType.TimestampTz)>]
      timestamp: DateTime }

Tested with

RepoDb.PostgreSql/1.1.1
RepoDb/1.12.5-beta2

[mikependon]

I have made a simulation on this scenario within our DEV ENV and we cannot replicate this problem. We are using the C# programming language.

I also added the identical Integration Tests as the one you wrote (see here) and it is working as expected. Just to ensure that this case is guarded.

Would you be able to check if the issue is on the Query or on the parsing of the Query Tree expression itself?

[mikependon]

Simply test the following.

• Can you try use the QueryGroup.Parse<EventReadEntity>(e => e.timestamp >= startDate && e.timestamp <= endDate); method and see if the exception occurs there? You can as well call the GetString() method afterwards to extract the generated WHERE expression.

• Can you check whether your code is actually inferred properly to the proper method? Was not it the type must be passed when querying? See below.

  conn.Query<EventReadEntity>(where=(fun (e: EventReadEntity) -> e.timestamp >= startDate && e.timestamp <= endDate), orderBy=orderByPositionDesc)
  

[Swoorup]

Hi @mikependon,

The inferred method I have is the following,

(extension) IDbConnection.Query<'TEntity (requires reference type)>(where: Expressions.Expression<Func<'TEntity,bool>>,?fields: Collections.Generic.IEnumerable<Field>,?orderBy: Collections.Generic.IEnumerable<OrderField>,?top: Nullable<int>,?hints: string,?cacheKey: string,?cacheItemExpiration: Nullable<int>,?commandTimeout: Nullable<int>,?transaction: IDbTransaction,?cache: Interfaces.ICache,?trace: Interfaces.ITrace,?statementBuilder: Interfaces.IStatementBuilder) : Collections.Generic.IEnumerable<'TEntity>  

'TEntity is EventReadEntity

And also the stack trace I have is

   at lambda_method(Closure , NpgsqlTypeHandler , Object , NpgsqlLengthCache& , NpgsqlParameter )
   at Npgsql.TypeHandling.NpgsqlTypeHandler`1.ValidateObjectAndGetLength(Object value, NpgsqlLengthCache& lengthCache, NpgsqlParameter parameter)
   at Npgsql.NpgsqlParameter.ValidateAndGetLength()
   at Npgsql.NpgsqlCommand.ValidateParameters(ConnectorTypeMapper typeMapper)
   at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at RepoDb.DbConnectionExtension.ExecuteQueryInternalForType[TResult](IDbConnection connection, String commandText, Object param, Nullable`1 commandType, String cacheKey, Nullable`1 cacheItemExpiration, Nullable`1 commandTimeout, IDbTransaction transaction, ICache cache, String tableName, Boolean skipCommandArrayParametersCheck)
   at RepoDb.DbConnectionExtension.ExecuteQueryInternal[TResult](IDbConnection connection, String commandText, Object param, Nullable`1 commandType, String cacheKey, Nullable`1 cacheItemExpiration, Nullable`1 commandTimeout, IDbTransaction transaction, ICache cache, String tableName, Boolean skipCommandArrayParametersCheck)
   at RepoDb.DbConnectionExtension.QueryInternalBase[TEntity](IDbConnection connection, String tableName, QueryGroup where, IEnumerable`1 fields, IEnumerable`1 orderBy, Nullable`1 top, String hints, String cacheKey, Nullable`1 cacheItemExpiration, Nullable`1 commandTimeout, IDbTransaction transaction, ICache cache, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.QueryInternal[TEntity](IDbConnection connection, String tableName, QueryGroup where, IEnumerable`1 fields, IEnumerable`1 orderBy, Nullable`1 top, String hints, String cacheKey, Nullable`1 cacheItemExpiration, Nullable`1 commandTimeout, IDbTransaction transaction, ICache cache, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.Query[TEntity](IDbConnection connection, Expression`1 where, IEnumerable`1 fields, IEnumerable`1 orderBy, Nullable`1 top, String hints, String cacheKey, Nullable`1 cacheItemExpiration, Nullable`1 commandTimeout, IDbTransaction transaction, ICache cache, ITrace trace, IStatementBuilder statementBuilder)

The creation of the query itself appear to be fine, it's the parameter that seems weird. Will update shortly on the result for QueryGroup.

[Swoorup]

The result of queryGroup statement is

QueryGroup: "(("timestamp" >= @timestamp) AND ("timestamp" <= @timestamp_1))"

[Swoorup]

@mikependon Issue turned out to be this line.

Converter.ConversionType <- ConversionType.Automatic

Is this no longer required?

[mikependon]

@Swoorup - thanks for the follow-up. It is unfortunate, but in the 2nd round, we still cannot replicate your problem. 🙈 Anyway, maybe we missed some of your configuration, would you be able to provide more insights to it? Or, maybe compare the different of our Integration Tests for PostgreSQL. See below.

Firstly, can you verify if your timestamp column is of type is timestamp with time zone or timestamp without time zone? Our Integration Tests is of that type (see here).

Secondly, as I can see, your property is also of type System.DateTime, same as our Integration Tests (see here). Also, I can see we have an identical setup in relation to NpgsqlDbType attribute. But just ensure, please revalidate our implementation.

Third, is our query is identical to yours? See here, otherwise, please leave a comment.

Hope to hear from you soon.

P.S: Btw, your result in the QueryGroup.Parse() and GetString() method is correct as per our expectation,

[mikependon]

So even adding the automatic conversion (see here) is not a problem to us. I think, it is good if you can just provide me the small project/solution that simulate this issue.

[mikependon]

@Swoorup - any updates on your side? Would you be able to share a small project that replicates this?

[mikependon]

@Swoorup - we are about to push the next version v1.12.5. This may not be a part of that since we cannot replicate this issue. Would you be able to help us replicate this? Otherwise, we will tag this as "working-as-expected". Many thanks on reporting this issue.

[Swoorup]

I'll have a look today.

[Swoorup]

@mikependon

Playground.zip

Instructions:

1. createdb test -h localhost -U postgres 
2. dotnet tool restore
3. dotnet migrondi up
4. dotnet run

Db.fs:109 is where it fails when querying by date.

[mikependon]

I encountered the problem from your attachment.

And I find it hard to replicate, TBH. So I tried referencing the projects (RepoDb and RepoDb.PostgreSql) manually and the error is not there.

Note: I did not do any changes at all, nor did any targeted fix for this one. I would assume the version RepoDb v1.12.5-beta3 and RepoDb.PostgreSql v1.1.1 is of the same version as mine.

Result:

All Events: 1
All Events By Date: 11
Event: { id = b3d9ca2d-0514-4306-be53-427ee4463246
  position = 34L
  correlation_id = 46196e6d-9fd7-4d0b-9a0a-435a2ee92acd
  causation_id = 3bec51f4-f83e-4d8d-bd85-855b51add799
  stream_key = "bla-0"
  version = 0
  name = "testevent"
  payload = seq []
  metadata = seq []
  schema_revision = 0
  timestamp = 11/30/2020 11:00:06 PM }
Event: { id = ab7ca357-1c66-4708-990b-226c3874af16
  position = 35L
  correlation_id = 6fac87e6-2aca-40e9-9c2f-6cf831d7d38d
  causation_id = bb9d8e49-e543-4c78-9680-dcd813bf4bfa
  stream_key = "bla-1"
  version = 1
  name = "testevent"
  payload = seq []
  metadata = seq []
  schema_revision = 0
  timestamp = 11/30/2020 11:00:06 PM }
Event: { id = 7e33ba55-1311-4fcf-a22e-b21d4d85ae7b
  position = 36L
  correlation_id = 7bc59419-460a-4fdc-a087-aab633ce6de1
  causation_id = 8ff4540f-ba6b-40ce-ab95-cc5efe41f601
  stream_key = "bla-2"
  version = 2
  name = "testevent"
  payload = seq []
  metadata = seq []
  schema_revision = 0
  timestamp = 11/30/2020 11:00:06 PM }
Event: { id = fb870532-7989-4121-ae07-3715e9183731
  position = 37L
  correlation_id = 267f099e-fad6-41d7-9c9c-0f444962dbd2
  causation_id = d7f88215-28f7-4dd4-a7c3-a304c5bd5eca
  stream_key = "bla-3"
  version = 3
  name = "testevent"
  payload = seq []
  metadata = seq []
  schema_revision = 0
  timestamp = 11/30/2020 11:00:06 PM }
Event: { id = 0e1e4398-378a-49f8-ac1d-0f5b255d5759
  position = 38L
  correlation_id = 4f117caf-4cae-43a9-860f-eecb59fa1d81
  causation_id = 38113be1-f1d8-4825-9a2f-8943899ea371
  stream_key = "bla-4"
  version = 4
  name = "testevent"
  payload = seq []
  metadata = seq []
  schema_revision = 0
  timestamp = 11/30/2020 11:00:06 PM }
Event: { id = edf9afd2-7377-43fd-a502-69b58abfcab5
  position = 39L
  correlation_id = 20403de0-effe-4f22-87b1-f6e659a35804
  causation_id = 2125d9ed-d129-406d-a6d5-17b09a8b3088
  stream_key = "bla-5"
  version = 5
  name = "testevent"
  payload = seq []
  metadata = seq []
  schema_revision = 0
  timestamp = 11/30/2020 11:00:06 PM }
Event: { id = b56ef7b7-0637-415c-bda0-4f89fea613d0
  position = 40L
  correlation_id = e3695735-5148-4726-b363-4f1d1b29c5e7
  causation_id = 5d870048-495b-4a30-b3de-ecc3a7f1087b
  stream_key = "bla-6"
  version = 6
  name = "testevent"
  payload = seq []
  metadata = seq []
  schema_revision = 0
  timestamp = 11/30/2020 11:00:06 PM }
Event: { id = 3b36e808-e892-4e45-8e3a-15e207704093
  position = 41L
  correlation_id = 2dc701f3-493f-45af-8787-c159dd56deaa
  causation_id = d5683fc6-6246-480d-8e97-4d934626e4c4
  stream_key = "bla-7"
  version = 7
  name = "testevent"
  payload = seq []
  metadata = seq []
  schema_revision = 0
  timestamp = 11/30/2020 11:00:06 PM }
Event: { id = a82771cc-f847-4946-9858-b37cc15da4ee
  position = 42L
  correlation_id = 1d181eb1-8e9a-43a4-bc6c-d72f75e6f686
  causation_id = 066f7499-5c22-45b8-89d7-f1f1d874df1b
  stream_key = "bla-8"
  version = 8
  name = "testevent"
  payload = seq []
  metadata = seq []
  schema_revision = 0
  timestamp = 11/30/2020 11:00:06 PM }
Event: { id = e9de4faf-d8b6-496a-b9a8-445b3e1d51b5
  position = 43L
  correlation_id = 2fad5cb7-852f-47af-9bb0-7752c0ee33a7
  causation_id = 371b70b0-7503-4929-8b86-8f416e61e2ba
  stream_key = "bla-9"
  version = 9
  name = "testevent"
  payload = seq []
  metadata = seq []
  schema_revision = 0
  timestamp = 11/30/2020 11:00:06 PM }
Event: { id = 3d2853ac-279e-491f-9aa7-de1b6753d0b2
  position = 44L
  correlation_id = fb468d21-21d5-483a-817e-c32619065b16
  causation_id = 53e1188a-bbb5-4f39-9e7e-d0009c92e2a7
  stream_key = "bla-10"
  version = 10
  name = "testevent"
  payload = seq []
  metadata = seq []
  schema_revision = 0
  timestamp = 11/30/2020 11:00:06 PM }

C:\Users\MichaelP\Downloads\Playground\Playground\bin\Debug\net5.0\Playground.exe (process 26268) exited with code 0.
To automatically close the console when debugging stops, enable Tools->Options->Debugging->Automatically close the console when debugging stops.
Press any key to close this window . . .

[Swoorup]

Odd, maybe something missed in the package that was released, which is now fixed?

[Swoorup]

Since there is a workaround, happy for this to be closed. I can retest it in future versions, and reopen if needed.