Bulk delete fails when schema has DATE column

For a table with this schema:

CREATE TABLE geo_views (
    city STRING(MAX) NOT NULL,
    client_id INT64 NOT NULL,
    date DATE NOT NULL,
    domain STRING(MAX) NOT NULL,
    url STRING(MAX) NOT NULL,
    pageviews INT64 NOT NULL,
) PRIMARY KEY (client_id, domain, date DESC, city, url)

if I try this query:

    NamedDB('spanner) autoCommit { implicit session =>
      sql"DELETE FROM ${SQLSyntax.createUnsafely(geoViews.tableName)} WHERE client_id > $TEST_ID_BASE".update.apply()
    }

I get this exception:

Exception in thread "Google Cloud Spanner JDBC Transaction Thread-0" java.lang.AssertionError: Illegal key part: class nl.topicus.jdbc.shaded.com.google.cloud.Date
    at nl.topicus.jdbc.shaded.com.google.cloud.spanner.Key.toProto(Key.java:287)
    at nl.topicus.jdbc.shaded.com.google.cloud.spanner.KeySet.appendToProto(KeySet.java:204)
    at nl.topicus.jdbc.shaded.com.google.cloud.spanner.Mutation.toProto(Mutation.java:381)
    at nl.topicus.jdbc.shaded.com.google.cloud.spanner.SpannerImpl$TransactionContextImpl.commit(SpannerImpl.java:1394)
    at nl.topicus.jdbc.shaded.com.google.cloud.spanner.SpannerImpl$TransactionRunnerImpl.runInternal(SpannerImpl.java:1299)
    at nl.topicus.jdbc.shaded.com.google.cloud.spanner.SpannerImpl$TransactionRunnerImpl.run(SpannerImpl.java:1242)
    at nl.topicus.jdbc.shaded.com.google.cloud.spanner.SessionPool$PooledSession$1.run(SessionPool.java:398)
    at nl.topicus.jdbc.transaction.TransactionThread.run(TransactionThread.java:115)

It looks like the toProto code in the shaded com.google.cloud.spanner.Key code is missing a case for the Date type.

From: https://github.com/googleapis/google-cloud-java/blob/master/google-cloud-clients/google-cloud-spanner/src/main/java/com/google/cloud/spanner/Key.java#L285

  ListValue toProto() {
    ListValue.Builder builder = ListValue.newBuilder();
    for (Object part : parts) {
      if (part == null) {
        builder.addValues(NULL_PROTO);
      } else if (part instanceof Boolean) {
        builder.addValuesBuilder().setBoolValue((Boolean) part);
      } else if (part instanceof Long) {
        builder.addValuesBuilder().setStringValue(part.toString());
      } else if (part instanceof Double) {
        builder.addValuesBuilder().setNumberValue((Double) part);
      } else if (part instanceof String) {
        builder.addValuesBuilder().setStringValue((String) part);
      } else if (part instanceof ByteArray) {
        builder.addValuesBuilder().setStringValue(((ByteArray) part).toBase64());
      } else if (part instanceof Timestamp) {
        builder.addValuesBuilder().setStringValue(((Timestamp) part).toString());
      } else {
        throw new AssertionError("Illegal key part: " + part.getClass());
      }
    }
    return builder.build();
  }

I know that isn't your code but maybe there is a workaround. Either way just wanted to report it here in case people are hitting the problem and Googling for an answer. I've created an issue on the Google Spanner client.

I have tried with and without AllowExtendedMode=true and removing the query parameter in the WHERE condition by making it a constant in the query and it still fails. The only solution was to remove the WHERE condition completely.

olavloite / spanner-jdbc

Bulk delete fails when schema has DATE column #134