JdbcDecoder/Encoder derived from Schema doesn't work for UUIDs

[erdeszt]

When using JdbcDecoder.fromSchema & JdbcEncoder.fromSchema with case classes containing UUIDs I get runtime errors(both with postgres and h2). I have added some test cases here: https://github.com/zio/zio-jdbc/compare/main...erdeszt:zio-jdbc:jdbc-decoder-from-schema?expand=1

The manual encoder and decoder versions seem to work fine.

With postgres I get: zio.jdbc.JdbcDecoderError: Unsupported SQL type 1111 when attempting to decode result set from a ZIO Schema CaseClass3(Nominal(Chunk(),Chunk(),U), Field(internalId,Primitive(uuid,Chunk())),Field(name,Primitive(string,Chunk())),Field(age,Primitive(int,Chunk())))

With H2 I get: zio.jdbc.JdbcDecoderError: Failed to cast Primitive(Chunk(117,67,-36,57,-104,-8,68,-90,-82,-4,-67,-112,-39,18,78,70),binary) to schema Primitive(uuid,Chunk())

I'll try to look into the issue myself but I'm new to both zio-jdbc and zio-schema so it might take a while. I would appreciate some pointers if someone has an idea what might be the issue here.

I've ran the test on latest main commit (cfd8a7e)

[erdeszt]

Note to myself: Adding this to createDynamicDecoder fixes the issue for postgres:

case SqlTypes.OTHER =>
              val result = resultSet.getObject(columnIndex)
              result match {
                case _: UUID =>
                  DynamicValue.Primitive(result.asInstanceOf[UUID], StandardType.UUIDType)
                case other   =>
                  throw new SQLException(
                    s"Unsupported SQL type ${other} when attempting to decode result set from a ZIO Schema ${schema}"
                  )
              }

TODO: Fix for H2, check if nulls are handled correctly.

H2 returns the type as SqlTypes.BINARY which causes an issues in zio-schema's toTypedValueLazyError as standardType.Binary doesn't match standardType.uuid not sure how to fix that in a general way. I guess this can also happen for postgres depending on how the column is defined.