Repository: eclipse-vertx/vertx-sql-client
Issue: PreparedQuery does not take parameter types into account

This bug reports follows-up on https://github.com/quarkusio/quarkus/issues/11501

The user initially reported they were not able to insert into jsonb column with Pg Client whereas the same query with Postgres JDBC works.

I proposed a better query to the user that works with both Postgres JDBC and the Pg Client as a workaround.

As for the issue, I did a little research and it seems the Pg client does not always send parameter types when preparing a query.

Consider the following table:

```sql
CREATE TABLE object_data (data JSONB)
```

And the following query:

```
INSERT INTO object_data (data)  VALUES (to_json($1)) RETURNING (data)
```

If you execute:

```java
PreparedQuery<RowSet<Row>> insertQuery = pgConnection.preparedQuery("INSERT INTO object_data (data)  VALUES (to_json($1)) RETURNING (data)");
insertQuery.execute(Tuple.of("toto"), ctx.asyncAssertSuccess(rs -> {
  Row row = rs.iterator().next();
  Object value = row.getJson(0);
  System.out.println("value = " + value);
}));
```

You will see:

```
io.vertx.pgclient.PgException: { "message": "could not determine polymorphic type because input has type unknown", "severity": "ERROR", "code": "42804", "file": "parse_coerce.c", "line": "1866", "routine": "enforce_generic_type_consistency" }
```

But if you change this:

https://github.com/eclipse-vertx/vertx-sql-client/blob/a08a7d7d97bdfa9b209afd76d866f4915d448712/vertx-sql-client/src/main/java/io/vertx/sqlclient/impl/SocketConnectionBase.java#L194

to

```java
PrepareStatementCommand prepareCmd = prepareCommand(queryCmd, cache, true);
```

Then you get the expected result:

```
value = toto
```

My understanding is that we do not want to send the parameters because we wouldn't be able to cache the prepared statement, correct?

I believe Posgres JDBC checks if the types are the same before reusing a cached statement and, if they are not the same, prepares the statement again with new types.

Changed files:
vertx-pg-client/src/main/java/io/vertx/pgclient/impl/PgSocketConnection.java, vertx-pg-client/src/test/java/io/vertx/pgclient/PreparedStatementTestBase.java

diff --git a/vertx-pg-client/src/main/java/io/vertx/pgclient/impl/PgSocketConnection.java b/vertx-pg-client/src/main/java/io/vertx/pgclient/impl/PgSocketConnection.java
index 0820a4c3..6b2bfb84 100644
--- a/vertx-pg-client/src/main/java/io/vertx/pgclient/impl/PgSocketConnection.java
+++ b/vertx-pg-client/src/main/java/io/vertx/pgclient/impl/PgSocketConnection.java
@@ -19,23 +19,17 @@ package io.vertx.pgclient.impl;
 
 import io.netty.channel.ChannelPipeline;
 import io.netty.handler.codec.DecoderException;
-import io.vertx.core.impl.ContextInternal;
+import io.vertx.core.AsyncResult;
+import io.vertx.core.Future;
+import io.vertx.core.Handler;
+import io.vertx.core.Promise;
+import io.vertx.core.buffer.Buffer;
 import io.vertx.core.impl.EventLoopContext;
+import io.vertx.core.net.impl.NetSocketInternal;
 import io.vertx.pgclient.PgException;
 import io.vertx.pgclient.impl.codec.PgCodec;
-import io.vertx.sqlclient.impl.Connection;
-import io.vertx.sqlclient.impl.Notice;
-import io.vertx.sqlclient.impl.Notification;
-import io.vertx.sqlclient.impl.QueryResultHandler;
-import io.vertx.sqlclient.impl.SocketConnectionBase;
-import io.vertx.sqlclient.impl.command.CommandBase;
-import io.vertx.sqlclient.impl.command.InitCommand;
-import io.vertx.core.*;
-import io.vertx.core.buffer.Buffer;
-import io.vertx.core.net.impl.NetSocketInternal;
-import io.vertx.sqlclient.impl.command.QueryCommandBase;
-import io.vertx.sqlclient.impl.command.SimpleQueryCommand;
-import io.vertx.sqlclient.impl.command.TxCommand;
+import io.vertx.sqlclient.impl.*;
+import io.vertx.sqlclient.impl.command.*;
 import io.vertx.sqlclient.spi.DatabaseMetadata;
 
 import java.util.Map;
@@ -161,8 +155,8 @@ public class PgSocketConnection extends SocketConnectionBase {
   @Override
   public boolean isIndeterminatePreparedStatementError(Throwable error) {
     if (error instanceof PgException) {
-      PgException e = (PgException) error;
-      return "42P18".equals(e.getCode());
+      String code = ((PgException) error).getCode();
+      return "42P18".equals(code) || "42804".equals(code);
     }
     return false;
   }
diff --git a/vertx-pg-client/src/test/java/io/vertx/pgclient/PreparedStatementTestBase.java b/vertx-pg-client/src/test/java/io/vertx/pgclient/PreparedStatementTestBase.java
index a84ef22d..8b7cd99a 100644
--- a/vertx-pg-client/src/test/java/io/vertx/pgclient/PreparedStatementTestBase.java
+++ b/vertx-pg-client/src/test/java/io/vertx/pgclient/PreparedStatementTestBase.java
@@ -24,31 +24,14 @@ import io.vertx.core.json.JsonArray;
 import io.vertx.core.json.JsonObject;
 import io.vertx.ext.unit.Async;
 import io.vertx.ext.unit.TestContext;
-import io.vertx.pgclient.data.Box;
-import io.vertx.pgclient.data.Circle;
-import io.vertx.pgclient.data.Interval;
-import io.vertx.pgclient.data.Line;
-import io.vertx.pgclient.data.LineSegment;
-import io.vertx.pgclient.data.Path;
-import io.vertx.pgclient.data.Point;
-import io.vertx.pgclient.data.Polygon;
-import io.vertx.sqlclient.Cursor;
-import io.vertx.sqlclient.Row;
-import io.vertx.sqlclient.RowIterator;
-import io.vertx.sqlclient.RowSet;
-import io.vertx.sqlclient.RowStream;
-import io.vertx.sqlclient.Tuple;
+import io.vertx.pgclient.data.*;
+import io.vertx.sqlclient.*;
 import org.junit.After;
 import org.junit.Before;
-import org.junit.Rule;
 import org.junit.Test;
 
 import java.lang.reflect.Array;
-import java.time.LocalDate;
-import java.time.LocalDateTime;
-import java.time.LocalTime;
-import java.time.OffsetDateTime;
-import java.time.ZoneOffset;
+import java.time.*;
 import java.time.format.DateTimeFormatter;
 import java.util.Collections;
 import java.util.List;
@@ -552,4 +535,16 @@ public abstract class PreparedStatementTestBase extends PgTestBase {
       }));
     }));
   }
+
+  @Test
+  public void testInferDataTypeLazyPolymorphic(TestContext ctx) {
+    PgConnection.connect(vertx, options(), ctx.asyncAssertSuccess(conn -> {
+      conn.prepare("SELECT to_jsonb($1)", ctx.asyncAssertSuccess(ps -> {
+        ps.query().execute(Tuple.of("foo"), ctx.asyncAssertSuccess(result -> {
+          ctx.assertEquals("foo", result.iterator().next().getString(0));
+          conn.close();
+        }));
+      }));
+    }));
+  }
 }