Instant as string returns unexpected result

[EmadAlblueshi]

• Vert.x version 3.4.2
• JDBC Driver org.postgresql 42.1.4
• PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
• jdbc:postgresql://localhost:5432/vertx", "****", "****"

Always returns false

sqlClient
  .rxGetConnection()
  .flatMap(conn ->
    conn.rxQueryWithParams("SELECT '2017-10-12T09:08:46.096Z' = ? as instant", new JsonArray().add("2017-10-12T09:08:46.096Z"))
      .map(ResultSet::getRows)
      .map(JsonArray::new)
      .doAfterTerminate(conn::close))
  .subscribe(System.out::println, Throwable::printStackTrace);

Always returns true

  PreparedStatement ps = con.prepareStatement("SELECT '2017-10-12T09:08:46.096Z' = ? as instant");
  ps.setString(1, "2017-10-12T09:08:46.096Z");
  ResultSet rs = ps.executeQuery();
  while (rs.next()) {
    System.out.println(rs.getBoolean(1));
  }
  rs.close();
  ps.close();

[pmlopes]

@EmadAlblueshi this is because of the optimistic casting happening on the client where it internally converts the textual representation to a Instant type:

https://github.com/vert-x3/vertx-jdbc-client/blob/master/src/main/java/io/vertx/ext/jdbc/impl/actions/JDBCStatementHelper.java#L268

[EmadAlblueshi]

@pmlopes Yes, my use case is to save and query the value as string only in JSONB data type which I think it should be ignored from the optimistic casting for example :

CREATE TABLE "X" (
  id bigserial primary key,
  body jsonb NOT NULL 
);
INSERT INTO "X" (body) VALUES ('{"timestamp" : "2017-10-12T09:08:46.096Z"}');
SELECT body FROM "X" where body->>'timestamp' = '2017-10-12T09:08:46.096Z';

If I remove the Z character it will work as expected and I'm not sure if there is any workaround to solve the issue.

[pmlopes]

if you remove it you also loose the TimeZone info... it then assumes your system timezone

[pmlopes]

I believe we need to revive this discussion:

https://groups.google.com/forum/#!topic/vertx-dev/TROkyJQRJIU

[EmadAlblueshi]

@pmlopes :+1: