Repository: GoogleCloudPlatform/pgadapter
Pull request #347 (2022-09-08 06:23:10)

feat: make guess types configurable
Makes the 'guess types' feature configurable and only enabled by default for JDBC. The reason that this is required for JDBC is that JDBC can use a couple of legacy date/time classes (`java.sql.Date` and `java.sql.Timestamp`). These are always sent as untyped parameters by JDBC, which means that we some times need to guess the type based purely on the value that we receive.

codecov[bot]: # [Codecov](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/347?src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform) Report
> Merging [#347](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/347?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform) (37783f1) into [postgresql-dialect](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/commit/4dcceb1916fb63ed8b27d247aaa51d5a4404d0dd?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform) (4dcceb1) will **increase** coverage by `0.00%`.
> The diff coverage is `85.29%`.

```diff
@@                  Coverage Diff                  @@
##             postgresql-dialect     #347   +/-   ##
=====================================================
  Coverage                 84.76%   84.77%           
- Complexity                 1642     1647    +5     
=====================================================
  Files                       114      114           
  Lines                      5493     5522   +29     
  Branches                    742      745    +3     
=====================================================
+ Hits                       4656     4681   +25     
- Misses                      613      616    +3     
- Partials                    224      225    +1     
```

| Flag | Coverage Δ | |
|---|---|---|
| all_tests | `84.77% <85.29%> (+<0.01%)` | :arrow_up: |

Flags with carried forward coverage won't be shown. [Click here](https://docs.codecov.io/docs/carryforward-flags?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform#carryforward-flags-in-the-pull-request-comment) to find out more.

| [Impacted Files](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/347?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform) | Coverage Δ | |
|---|---|---|
| [...google/cloud/spanner/pgadapter/parsers/Parser.java](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/347/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform#diff-c3JjL21haW4vamF2YS9jb20vZ29vZ2xlL2Nsb3VkL3NwYW5uZXIvcGdhZGFwdGVyL3BhcnNlcnMvUGFyc2VyLmphdmE=) | `78.82% <40.00%> (-1.90%)` | :arrow_down: |
| [.../cloud/spanner/pgadapter/session/SessionState.java](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/347/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform#diff-c3JjL21haW4vamF2YS9jb20vZ29vZ2xlL2Nsb3VkL3NwYW5uZXIvcGdhZGFwdGVyL3Nlc3Npb24vU2Vzc2lvblN0YXRlLmphdmE=) | `97.46% <87.50%> (-1.13%)` | :arrow_down: |
| [...pter/statements/IntermediatePreparedStatement.java](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/347/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform#diff-c3JjL21haW4vamF2YS9jb20vZ29vZ2xlL2Nsb3VkL3NwYW5uZXIvcGdhZGFwdGVyL3N0YXRlbWVudHMvSW50ZXJtZWRpYXRlUHJlcGFyZWRTdGF0ZW1lbnQuamF2YQ==) | `84.81% <100.00%> (+0.49%)` | :arrow_up: |
| [...ud/spanner/pgadapter/utils/ClientAutoDetector.java](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/347/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform#diff-c3JjL21haW4vamF2YS9jb20vZ29vZ2xlL2Nsb3VkL3NwYW5uZXIvcGdhZGFwdGVyL3V0aWxzL0NsaWVudEF1dG9EZXRlY3Rvci5qYXZh) | `93.18% <100.00%> (+0.49%)` | :arrow_up: |
| [...spanner/pgadapter/wireprotocol/StartupMessage.java](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/347/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform#diff-c3JjL21haW4vamF2YS9jb20vZ29vZ2xlL2Nsb3VkL3NwYW5uZXIvcGdhZGFwdGVyL3dpcmVwcm90b2NvbC9TdGFydHVwTWVzc2FnZS5qYXZh) | `88.23% <100.00%> (+0.48%)` | :arrow_up: |

:mega: We’re building smart automated test selection to slash your CI/CD build times. [Learn more](https://about.codecov.io/iterative-testing/?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform)


Patch:
diff --git a/src/main/java/com/google/cloud/spanner/pgadapter/parsers/Parser.java b/src/main/java/com/google/cloud/spanner/pgadapter/parsers/Parser.java
index 1192a672..0b7964f1 100644
--- a/src/main/java/com/google/cloud/spanner/pgadapter/parsers/Parser.java
+++ b/src/main/java/com/google/cloud/spanner/pgadapter/parsers/Parser.java
@@ -24,6 +24,7 @@ import com.google.cloud.spanner.Type.Code;
 import com.google.cloud.spanner.pgadapter.ProxyServer.DataFormat;
 import java.nio.charset.Charset;
 import java.nio.charset.StandardCharsets;
+import java.util.Set;
 import org.postgresql.core.Oid;
 
 /**
@@ -59,14 +60,13 @@ public abstract class Parser<T> {
    * @return The {@link Oid} type code that is guessed for the value or {@link Oid#UNSPECIFIED} if
    *     no type could be guessed.
    */
-  private static int guessType(byte[] item, FormatCode formatCode) {
-    // TODO: Put 'guessType' behind a session setting so it is only enabled when wanted.
+  private static int guessType(Set<Integer> guessTypes, byte[] item, FormatCode formatCode) {
     if (formatCode == FormatCode.TEXT && item != null) {
       String value = new String(item, StandardCharsets.UTF_8);
-      if (TimestampParser.isTimestamp(value)) {
+      if (guessTypes.contains(Oid.TIMESTAMPTZ) && TimestampParser.isTimestamp(value)) {
         return Oid.TIMESTAMPTZ;
       }
-      if (DateParser.isDate(value)) {
+      if (guessTypes.contains(Oid.DATE) && DateParser.isDate(value)) {
         return Oid.DATE;
       }
     }
@@ -79,9 +79,11 @@ public abstract class Parser<T> {
    * @param item The data to be parsed
    * @param oidType The type of the designated data
    * @param formatCode The format of the data to be parsed
+   * @param guessTypes The OIDs of the types that may be 'guessed' based on the input value
    * @return The parser object for the designated data type.
    */
-  public static Parser<?> create(byte[] item, int oidType, FormatCode formatCode) {
+  public static Parser<?> create(
+      Set<Integer> guessTypes, byte[] item, int oidType, FormatCode formatCode) {
     switch (oidType) {
       case Oid.BOOL:
       case Oid.BIT:
@@ -114,11 +116,11 @@ public abstract class Parser<T> {
       case Oid.UNSPECIFIED:
         // Try to guess the type based on the value. Use an unspecified parser if no type could be
         // determined.
-        int type = guessType(item, formatCode);
+        int type = guessType(guessTypes, item, formatCode);
         if (type == Oid.UNSPECIFIED) {
           return new UnspecifiedParser(item, formatCode);
         }
-        return create(item, type, formatCode);
+        return create(guessTypes, item, type, formatCode);
       default:
         throw new IllegalArgumentException("Unsupported parameter type: " + oidType);
     }
diff --git a/src/main/java/com/google/cloud/spanner/pgadapter/session/SessionState.java b/src/main/java/com/google/cloud/spanner/pgadapter/session/SessionState.java
index a425a4b3..6825e135 100644
--- a/src/main/java/com/google/cloud/spanner/pgadapter/session/SessionState.java
+++ b/src/main/java/com/google/cloud/spanner/pgadapter/session/SessionState.java
@@ -16,6 +16,7 @@ package com.google.cloud.spanner.pgadapter.session;
 
 import static com.google.cloud.spanner.pgadapter.session.CopySettings.initCopySettings;
 
+import com.google.api.client.util.Strings;
 import com.google.api.core.InternalApi;
 import com.google.cloud.spanner.ErrorCode;
 import com.google.cloud.spanner.SpannerException;
@@ -29,6 +30,7 @@ import com.google.common.base.MoreObjects;
 import com.google.common.base.Preconditions;
 import com.google.common.collect.ImmutableMap;
 import com.google.common.collect.ImmutableSet;
+import com.google.common.collect.ImmutableSet.Builder;
 import com.google.common.collect.Sets;
 import java.util.ArrayList;
 import java.util.Collections;
@@ -42,6 +44,7 @@ import java.util.Objects;
 import java.util.Set;
 import java.util.concurrent.Callable;
 import java.util.stream.Collectors;
+import javax.annotation.Nonnull;
 
 /** {@link SessionState} contains all session variables for a connection. */
 @InternalApi
@@ -385,6 +388,43 @@ public class SessionState {
         () -> DdlTransactionMode.valueOf(setting.getBootVal()));
   }
 
+  /**
+   * Returns a set of OIDs that PGAdapter should try to guess if it receives an untyped parameter
+   * value. This is needed because some clients (JDBC) deliberately send parameters without a type
+   * code to force the server to infer the type. This specifically applies to date/timestamp
+   * parameters.
+   */
+  public Set<Integer> getGuessTypes() {
+    PGSetting setting = internalGet(toKey("spanner", "guess_types"), false);
+    if (setting == null || Strings.isNullOrEmpty(setting.getSetting())) {
+      return ImmutableSet.of();
+    }
+    return convertOidListToSet(setting.getSetting());
+  }
+
+  /** Keep a cache of 1 element ready as the setting is not likely to change often. */
+  private final Map<String, Set<Integer>> cachedGuessTypes = new HashMap<>(1);
+
+  Set<Integer> convertOidListToSet(@Nonnull String value) {
+    if (cachedGuessTypes.containsKey(value)) {
+      return cachedGuessTypes.get(value);
+    }
+
+    Builder<Integer> builder = ImmutableSet.builder();
+    String[] oids = value.split(",");
+    for (String oid : oids) {
+      try {
+        builder.add(Integer.valueOf(oid));
+      } catch (Exception ignore) {
+        // ignore invalid oids.
+      }
+    }
+    cachedGuessTypes.clear();
+    cachedGuessTypes.put(value, builder.build());
+
+    return cachedGuessTypes.get(value);
+  }
+
   @SafeVarargs
   static <T> T tryGetFirstNonNull(T defaultResult, Callable<T>... callables) {
     T value;
diff --git a/src/main/java/com/google/cloud/spanner/pgadapter/statements/IntermediatePreparedStatement.java b/src/main/java/com/google/cloud/spanner/pgadapter/statements/IntermediatePreparedStatement.java
index 2abe6e09..60af1fac 100644
--- a/src/main/java/com/google/cloud/spanner/pgadapter/statements/IntermediatePreparedStatement.java
+++ b/src/main/java/com/google/cloud/spanner/pgadapter/statements/IntermediatePreparedStatement.java
@@ -123,7 +123,16 @@ public class IntermediatePreparedStatement extends IntermediateStatement {
     for (int index = 0; index < parameters.length; index++) {
       short formatCode = portal.getParameterFormatCode(index);
       int type = this.parseType(parameters, index);
-      Parser<?> parser = Parser.create(parameters[index], type, FormatCode.of(formatCode));
+      Parser<?> parser =
+          Parser.create(
+              connectionHandler
+                  .getExtendedQueryProtocolHandler()
+                  .getBackendConnection()
+                  .getSessionState()
+                  .getGuessTypes(),
+              parameters[index],
+              type,
+              FormatCode.of(formatCode));
       parser.bind(builder, "p" + (index + 1));
     }
     this.statement = builder.build();
diff --git a/src/main/java/com/google/cloud/spanner/pgadapter/utils/ClientAutoDetector.java b/src/main/java/com/google/cloud/spanner/pgadapter/utils/ClientAutoDetector.java
index 5f3f17c9..1cfb94a8 100644
--- a/src/main/java/com/google/cloud/spanner/pgadapter/utils/ClientAutoDetector.java
+++ b/src/main/java/com/google/cloud/spanner/pgadapter/utils/ClientAutoDetector.java
@@ -22,9 +22,11 @@ import com.google.cloud.spanner.pgadapter.statements.local.SelectCurrentCatalogS
 import com.google.cloud.spanner.pgadapter.statements.local.SelectCurrentDatabaseStatement;
 import com.google.cloud.spanner.pgadapter.statements.local.SelectCurrentSchemaStatement;
 import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableMap;
 import java.util.List;
 import java.util.Map;
 import javax.annotation.Nonnull;
+import org.postgresql.core.Oid;
 
 /**
  * Utility class that tries to automatically detect well-known clients and drivers that are
@@ -93,6 +95,12 @@ public class ClientAutoDetector {
         }
         return parameters.get("DateStyle").equals("ISO");
       }
+
+      @Override
+      public ImmutableMap<String, String> getDefaultParameters() {
+        return ImmutableMap.of(
+            "spanner.guess_types", String.format("%d,%d", Oid.TIMESTAMPTZ, Oid.DATE));
+      }
     },
     PGX {
       @Override
@@ -118,6 +126,10 @@ public class ClientAutoDetector {
       }
       return EMPTY_LOCAL_STATEMENTS;
     }
+
+    public ImmutableMap<String, String> getDefaultParameters() {
+      return ImmutableMap.of();
+    }
   }
 
   /**
diff --git a/src/main/java/com/google/cloud/spanner/pgadapter/wireprotocol/StartupMessage.java b/src/main/java/com/google/cloud/spanner/pgadapter/wireprotocol/StartupMessage.java
index 56d3744b..c2c7f001 100644
--- a/src/main/java/com/google/cloud/spanner/pgadapter/wireprotocol/StartupMessage.java
+++ b/src/main/java/com/google/cloud/spanner/pgadapter/wireprotocol/StartupMessage.java
@@ -21,6 +21,7 @@ import com.google.cloud.spanner.pgadapter.ConnectionHandler.ConnectionStatus;
 import com.google.cloud.spanner.pgadapter.utils.ClientAutoDetector;
 import com.google.cloud.spanner.pgadapter.utils.ClientAutoDetector.WellKnownClient;
 import com.google.cloud.spanner.pgadapter.wireoutput.AuthenticationCleartextPasswordResponse;
+import com.google.common.collect.Iterables;
 import java.io.IOException;
 import java.text.MessageFormat;
 import java.util.Map;
@@ -82,7 +83,10 @@ public class StartupMessage extends BootstrapMessage {
       @Nullable Credentials credentials)
       throws Exception {
     connection.connectToSpanner(database, credentials);
-    for (Entry<String, String> parameter : parameters.entrySet()) {
+    for (Entry<String, String> parameter :
+        Iterables.concat(
+            connection.getWellKnownClient().getDefaultParameters().entrySet(),
+            parameters.entrySet())) {
       connection
           .getExtendedQueryProtocolHandler()
           .getBackendConnection()


Test patch:
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/JdbcMockServerTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/JdbcMockServerTest.java
index 90f7bc1b..09e5399b 100644
--- a/src/test/java/com/google/cloud/spanner/pgadapter/JdbcMockServerTest.java
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/JdbcMockServerTest.java
@@ -61,6 +61,7 @@ import java.time.OffsetDateTime;
 import java.time.ZoneOffset;
 import java.time.temporal.ChronoUnit;
 import java.util.Base64;
+import java.util.Calendar;
 import java.util.List;
 import java.util.Map;
 import java.util.stream.Collectors;
@@ -73,6 +74,7 @@ import org.junit.runners.Parameterized.Parameter;
 import org.junit.runners.Parameterized.Parameters;
 import org.postgresql.PGConnection;
 import org.postgresql.PGStatement;
+import org.postgresql.core.Oid;
 import org.postgresql.jdbc.PgStatement;
 import org.postgresql.util.PSQLException;
 
@@ -428,6 +430,82 @@ public class JdbcMockServerTest extends AbstractMockServerTest {
     }
   }
 
+  @Test
+  public void testQueryWithLegacyDateParameter() throws SQLException {
+    String jdbcSql = "select col_date from all_types where col_date=?";
+    String pgSql = "select col_date from all_types where col_date=$1";
+    mockSpanner.putStatementResult(StatementResult.query(Statement.of(pgSql), ALL_TYPES_RESULTSET));
+    mockSpanner.putStatementResult(
+        StatementResult.query(
+            Statement.newBuilder(pgSql).bind("p1").to(Date.parseDate("2022-03-29")).build(),
+            ALL_TYPES_RESULTSET));
+
+    // Threshold 5 is the default. Use a named prepared statement if it is executed 5 times or more.
+    // Threshold 1 means always use a named prepared statement.
+    // Threshold 0 means never use a named prepared statement.
+    // Threshold -1 means use binary transfer of values and use DESCRIBE statement.
+    // (10 points to you if you guessed the last one up front!).
+    for (int preparedThreshold : new int[] {5, 1, 0, -1}) {
+      try (Connection connection = DriverManager.getConnection(createUrl())) {
+        try (PreparedStatement preparedStatement = connection.prepareStatement(jdbcSql)) {
+          preparedStatement.unwrap(PgStatement.class).setPrepareThreshold(preparedThreshold);
+          int index = 0;
+          preparedStatement.setDate(++index, new java.sql.Date(2022 - 1900, Calendar.MARCH, 29));
+          try (ResultSet resultSet = preparedStatement.executeQuery()) {
+            assertTrue(resultSet.next());
+            assertEquals(
+                new java.sql.Date(2022 - 1900, Calendar.MARCH, 29), resultSet.getDate("col_date"));
+            assertFalse(resultSet.next());
+          }
+        }
+
+        List<ExecuteSqlRequest> requests = mockSpanner.getRequestsOfType(ExecuteSqlRequest.class);
+        // Prepare threshold less than 0 means use binary transfer + DESCRIBE statement.
+        // However, the legacy date type will never use BINARY transfer and will always be sent with
+        // unspecified type by the JDBC driver the first time. This means that we need 3 round trips
+        // for a query that uses a prepared statement the first time.
+        assertEquals(
+            "Prepare threshold: " + preparedThreshold,
+            preparedThreshold == 1 || preparedThreshold < 0 ? 3 : 1,
+            requests.size());
+
+        ExecuteSqlRequest executeRequest;
+        if (preparedThreshold == 1) {
+          // The order of statements here is a little strange. The execution of the statement is
+          // executed first, and the describe statements are then executed afterwards. The reason
+          // for
+          // this is that JDBC does the following when it encounters a statement parameter that is
+          // 'unknown' (it considers the legacy date type as unknown, as it does not know if the
+          // user
+          // means date, timestamp or timestamptz):
+          // 1. It sends a DescribeStatement message, but without a flush or a sync, as it is not
+          //    planning on using the information for this request.
+          // 2. It then sends the Execute message followed by a sync. This causes PGAdapter to sync
+          //    the backend connection and execute everything in the actual execution pipeline.
+          // 3. PGAdapter then executes anything left in the message queue. The DescribeMessage is
+          //    still there, and is therefore executed after the Execute message.
+          // All the above still works as intended, as the responses are sent in the expected order.
+          executeRequest = requests.get(0);
+          for (int i = 1; i < requests.size(); i++) {
+            assertEquals(QueryMode.PLAN, requests.get(i).getQueryMode());
+          }
+        } else {
+          executeRequest = requests.get(requests.size() - 1);
+        }
+        assertEquals(QueryMode.NORMAL, executeRequest.getQueryMode());
+        assertEquals(pgSql, executeRequest.getSql());
+
+        Map<String, Value> params = executeRequest.getParams().getFieldsMap();
+        Map<String, Type> types = executeRequest.getParamTypesMap();
+
+        assertEquals(TypeCode.DATE, types.get("p1").getCode());
+        assertEquals("2022-03-29", params.get("p1").getStringValue());
+
+        mockSpanner.clearRequests();
+      }
+    }
+  }
+
   @Test
   public void testMultipleQueriesInTransaction() throws SQLException {
     String sql = "SELECT 1";
@@ -1702,6 +1780,31 @@ public class JdbcMockServerTest extends AbstractMockServerTest {
     assertEquals(0, mockSpanner.countRequestsOfType(CommitRequest.class));
   }
 
+  @Test
+  public void testShowGuessTypes() throws SQLException {
+    try (Connection connection = DriverManager.getConnection(createUrl())) {
+      try (ResultSet resultSet =
+          connection.createStatement().executeQuery("show spanner.guess_types")) {
+        assertTrue(resultSet.next());
+        assertEquals(String.format("%d,%d", Oid.TIMESTAMPTZ, Oid.DATE), resultSet.getString(1));
+        assertFalse(resultSet.next());
+      }
+    }
+  }
+
+  @Test
+  public void testShowGuessTypesOverwritten() throws SQLException {
+    try (Connection connection =
+        DriverManager.getConnection(createUrl() + "?options=-c%20spanner.guess_types=0")) {
+      try (ResultSet resultSet =
+          connection.createStatement().executeQuery("show spanner.guess_types")) {
+        assertTrue(resultSet.next());
+        assertEquals("0", resultSet.getString(1));
+        assertFalse(resultSet.next());
+      }
+    }
+  }
+
   @Test
   public void testShowValidSetting() throws SQLException {
     try (Connection connection = DriverManager.getConnection(createUrl())) {
@@ -2072,7 +2175,7 @@ public class JdbcMockServerTest extends AbstractMockServerTest {
           }
           count++;
         }
-        assertEquals(356, count);
+        assertEquals(357, count);
       }
     }
   }
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/nodejs/TypeORMMockServerTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/nodejs/TypeORMMockServerTest.java
index 0866ec26..d92fdfb0 100644
--- a/src/test/java/com/google/cloud/spanner/pgadapter/nodejs/TypeORMMockServerTest.java
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/nodejs/TypeORMMockServerTest.java
@@ -477,13 +477,7 @@ public class TypeORMMockServerTest extends AbstractMockServerTest {
         "INSERT INTO \"all_types\""
             + "(\"col_bigint\", \"col_bool\", \"col_bytea\", \"col_float8\", \"col_int\", \"col_numeric\", \"col_timestamptz\", \"col_date\", \"col_varchar\") "
             + "VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)";
-    mockSpanner.putStatementResult(
-        StatementResult.update(
-            Statement.newBuilder(insertSql)
-                .bind("p7")
-                .to(Timestamp.parseTimestamp("2022-07-22T18:15:42.011000000Z"))
-                .build(),
-            1L));
+    mockSpanner.putStatementResult(StatementResult.update(Statement.of(insertSql), 1L));
 
     String output = runTest("createAllTypes");
 
@@ -500,10 +494,7 @@ public class TypeORMMockServerTest extends AbstractMockServerTest {
 
     // The NodeJS PostgreSQL driver sends parameters without any type information to the backend.
     // This means that all parameters are sent as untyped string values.
-    // PGAdapter however automatically recognizes untyped Timestamp values and converts these into
-    // typed Timestamp parameters to prevent confusion in the PostgreSQL parser whether the value
-    // should be interpreted as a timestamptz or timestamp (without time zone).
-    assertEquals(1, insertRequest.getParamTypesMap().size());
+    assertEquals(0, insertRequest.getParamTypesMap().size());
     assertEquals(9, insertRequest.getParams().getFieldsCount());
     assertEquals("2", insertRequest.getParams().getFieldsMap().get("p1").getStringValue());
     assertEquals("true", insertRequest.getParams().getFieldsMap().get("p2").getStringValue());
@@ -515,8 +506,9 @@ public class TypeORMMockServerTest extends AbstractMockServerTest {
     assertEquals("123456789", insertRequest.getParams().getFieldsMap().get("p5").getStringValue());
     assertEquals("234.54235", insertRequest.getParams().getFieldsMap().get("p6").getStringValue());
     assertEquals(
-        "2022-07-22T18:15:42.011000000Z",
-        insertRequest.getParams().getFieldsMap().get("p7").getStringValue());
+        Timestamp.parseTimestamp("2022-07-22T20:15:42.011+02:00"),
+        Timestamp.parseTimestamp(
+            insertRequest.getParams().getFieldsMap().get("p7").getStringValue()));
     assertEquals("2022-07-22", insertRequest.getParams().getFieldsMap().get("p8").getStringValue());
     assertEquals(
         "some random string", insertRequest.getParams().getFieldsMap().get("p9").getStringValue());
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/parsers/ParserTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/parsers/ParserTest.java
index 454814a5..270ff1bc 100644
--- a/src/test/java/com/google/cloud/spanner/pgadapter/parsers/ParserTest.java
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/parsers/ParserTest.java
@@ -42,6 +42,7 @@ import com.google.cloud.spanner.pgadapter.parsers.copy.CopyTreeParser.CopyOption
 import com.google.cloud.spanner.pgadapter.parsers.copy.ParseException;
 import com.google.cloud.spanner.pgadapter.parsers.copy.TokenMgrError;
 import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableSet;
 import java.math.BigDecimal;
 import java.nio.charset.StandardCharsets;
 import java.util.Arrays;
@@ -73,13 +74,13 @@ public class ParserTest {
   }
 
   private void validateCreateBinary(byte[] item, int oid, Object value) {
-    Parser<?> binary = Parser.create(item, oid, FormatCode.BINARY);
+    Parser<?> binary = Parser.create(ImmutableSet.of(), item, oid, FormatCode.BINARY);
 
     assertParserValueEqual(binary, value);
   }
 
   private void validateCreateText(byte[] item, int oid, Object value) {
-    Parser<?> text = Parser.create(item, oid, FormatCode.TEXT);
+    Parser<?> text = Parser.create(ImmutableSet.of(), item, oid, FormatCode.TEXT);
 
     assertParserValueEqual(text, value);
   }
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/session/SessionStateTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/session/SessionStateTest.java
index d8dd98bf..caf50701 100644
--- a/src/test/java/com/google/cloud/spanner/pgadapter/session/SessionStateTest.java
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/session/SessionStateTest.java
@@ -31,10 +31,12 @@ import com.google.cloud.spanner.pgadapter.metadata.OptionsMetadata;
 import com.google.cloud.spanner.pgadapter.metadata.OptionsMetadata.DdlTransactionMode;
 import com.google.cloud.spanner.pgadapter.statements.PgCatalog;
 import com.google.common.collect.ImmutableMap;
+import com.google.common.collect.ImmutableSet;
 import java.util.List;
 import org.junit.Test;
 import org.junit.runner.RunWith;
 import org.junit.runners.JUnit4;
+import org.postgresql.core.Oid;
 
 @RunWith(JUnit4.class)
 public class SessionStateTest {
@@ -801,4 +803,27 @@ public class SessionStateTest {
 
     assertEquals(DdlTransactionMode.Single, state.getDdlTransactionMode());
   }
+
+  @Test
+  public void testGuessTypes_defaultNonJdbc() {
+    OptionsMetadata optionsMetadata = mock(OptionsMetadata.class);
+    SessionState state = new SessionState(ImmutableMap.of(), optionsMetadata);
+    assertEquals(ImmutableSet.of(), state.getGuessTypes());
+  }
+
+  @Test
+  public void testGuessTypes_defaultJdbc() {
+    OptionsMetadata optionsMetadata = mock(OptionsMetadata.class);
+    SessionState state = new SessionState(ImmutableMap.of(), optionsMetadata);
+    state.set("spanner", "guess_types", String.format("%d,%d", Oid.TIMESTAMPTZ, Oid.DATE));
+    assertEquals(ImmutableSet.of(Oid.TIMESTAMPTZ, Oid.DATE), state.getGuessTypes());
+  }
+
+  @Test
+  public void testGuessTypes_invalidOids() {
+    OptionsMetadata optionsMetadata = mock(OptionsMetadata.class);
+    SessionState state = new SessionState(ImmutableMap.of(), optionsMetadata);
+    state.set("spanner", "guess_types", String.format("%d,%d,foo", Oid.TIMESTAMPTZ, Oid.DATE));
+    assertEquals(ImmutableSet.of(Oid.TIMESTAMPTZ, Oid.DATE), state.getGuessTypes());
+  }
 }
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/statements/StatementTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/statements/StatementTest.java
index 333963f4..a1e130ed 100644
--- a/src/test/java/com/google/cloud/spanner/pgadapter/statements/StatementTest.java
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/statements/StatementTest.java
@@ -51,6 +51,7 @@ import com.google.cloud.spanner.pgadapter.wireprotocol.ControlMessage;
 import com.google.cloud.spanner.pgadapter.wireprotocol.QueryMessage;
 import com.google.cloud.spanner.pgadapter.wireprotocol.WireMessage;
 import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableSet;
 import com.google.common.primitives.Bytes;
 import java.io.ByteArrayInputStream;
 import java.io.DataInputStream;
@@ -263,6 +264,14 @@ public class StatementTest {
   public void testPreparedStatement() {
     when(connectionHandler.getSpannerConnection()).thenReturn(connection);
     when(connectionHandler.getConnectionMetadata()).thenReturn(connectionMetadata);
+    ExtendedQueryProtocolHandler extendedQueryProtocolHandler =
+        mock(ExtendedQueryProtocolHandler.class);
+    when(connectionHandler.getExtendedQueryProtocolHandler())
+        .thenReturn(extendedQueryProtocolHandler);
+    when(extendedQueryProtocolHandler.getBackendConnection()).thenReturn(backendConnection);
+    SessionState sessionState = mock(SessionState.class);
+    when(backendConnection.getSessionState()).thenReturn(sessionState);
+    when(sessionState.getGuessTypes()).thenReturn(ImmutableSet.of());
     String sqlStatement = "SELECT * FROM users WHERE age > $2 AND age < $3 AND name = $1";
     int[] parameterDataTypes = new int[] {Oid.VARCHAR, Oid.INT8, Oid.INT4};
 
@@ -304,6 +313,15 @@ public class StatementTest {
   @Test
   public void testPreparedStatementIllegalTypeThrowsException() {
     when(connectionHandler.getConnectionMetadata()).thenReturn(connectionMetadata);
+    ExtendedQueryProtocolHandler extendedQueryProtocolHandler =
+        mock(ExtendedQueryProtocolHandler.class);
+    when(connectionHandler.getExtendedQueryProtocolHandler())
+        .thenReturn(extendedQueryProtocolHandler);
+    when(extendedQueryProtocolHandler.getBackendConnection()).thenReturn(backendConnection);
+    SessionState sessionState = mock(SessionState.class);
+    when(backendConnection.getSessionState()).thenReturn(sessionState);
+    when(sessionState.getGuessTypes()).thenReturn(ImmutableSet.of());
+
     String sqlStatement = "SELECT * FROM users WHERE metadata = $1";
     int[] parameterDataTypes = new int[] {Oid.JSON};
 
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/wireprotocol/ProtocolTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/wireprotocol/ProtocolTest.java
index 1a549c27..7526574d 100644
--- a/src/test/java/com/google/cloud/spanner/pgadapter/wireprotocol/ProtocolTest.java
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/wireprotocol/ProtocolTest.java
@@ -58,6 +58,7 @@ import com.google.cloud.spanner.pgadapter.statements.CopyStatement;
 import com.google.cloud.spanner.pgadapter.statements.ExtendedQueryProtocolHandler;
 import com.google.cloud.spanner.pgadapter.statements.IntermediatePortalStatement;
 import com.google.cloud.spanner.pgadapter.statements.IntermediatePreparedStatement;
+import com.google.cloud.spanner.pgadapter.utils.ClientAutoDetector.WellKnownClient;
 import com.google.cloud.spanner.pgadapter.utils.MutationWriter;
 import com.google.cloud.spanner.pgadapter.wireprotocol.ControlMessage.ManuallyCreatedToken;
 import com.google.cloud.spanner.pgadapter.wireprotocol.ControlMessage.PreparedType;
@@ -1536,6 +1537,7 @@ public class ProtocolTest {
     when(connectionHandler.getConnectionId()).thenReturn(1);
     when(connectionHandler.getExtendedQueryProtocolHandler())
         .thenReturn(extendedQueryProtocolHandler);
+    when(connectionHandler.getWellKnownClient()).thenReturn(WellKnownClient.UNSPECIFIED);
     when(extendedQueryProtocolHandler.getBackendConnection()).thenReturn(backendConnection);
     SessionState sessionState = mock(SessionState.class);
     PGSetting serverVersionSetting = mock(PGSetting.class);