GoogleCloudPlatform / GoogleCloudPlatform/cloud-spanner-emulator

DEFAULT (PENDING_COMMIT_TIMESTAMP()) fails with FailedPrecondition when table has a generated stored primary key.

Open
#316 2 comments 3 reactions 0 assignees View on GitHub
Dominant language
C++
Stars
334
Forks
77
Avg merge
8m
Merged PRs (30d)
2

Description

When a table combines a generated stored column as the primary key (e.g. extracted from a JSON column via JSON_VALUE) with DEFAULT (PENDING_COMMIT_TIMESTAMP()) / ON UPDATE (PENDING_COMMIT_TIMESTAMP()) timestamp columns, both DML and Mutation API inserts fail with:
`FailedPrecondition: Cannot write timestamps in the future, found: 9999-12-31T23:59:59.999999+00:00`
**Steps to reproduce**

1. Start the emulator and create an instance/database
2. Create a table with a generated stored PK + default commit timestamps:
```
CREATE TABLE TestTable (
test_id STRING(100) AS (JSON_VALUE(data, '$.test_id')) STORED,
data JSON,
created_at TIMESTAMP DEFAULT (PENDING_COMMIT_TIMESTAMP()) OPTIONS (allow_commit_timestamp = true),
updated_at TIMESTAMP DEFAULT (PENDING_COMMIT_TIMESTAMP()) ON UPDATE (PENDING_COMMIT_TIMESTAMP()) OPTIONS (allow_commit_timestamp = true),
) PRIMARY KEY (test_id)
```
3. Insert a row via DML:
```
INSERT INTO TestTable (data) VALUES (JSON '{"test_id": "dml-1", "name": "DML Insert"}')
```
4. Insert a row via Mutation API (Go example):
```
client.Apply(ctx, []*spanner.Mutation{
spanner.InsertMap("TestTable", map[string]interface{}{
"data": spanner.NullJSON{Value: map[string]interface{}{
"test_id": "mutation-1",
"name": "Mutation API Insert",
}, Valid: true},
}),
})
```

**Expected behavior**
Both inserts succeed. The emulator resolves PENDING_COMMIT_TIMESTAMP() defaults to the actual commit timestamp, as it does for tables without generated stored primary keys.

**Actual behavior**
Both inserts fail with:
`spanner: code = "FailedPrecondition", desc = "Cannot write timestamps in the future, found: 9999-12-31T23:59:59.999999+00:00"
`

**Environment**
- Emulator image: gcr.io/cloud-spanner-emulator/emulator (v1.5.48)
- Client: cloud.google.com/go/spanner v1.87.0

**Reproduction repo**
https://github.com/jmbradshaw79/spanner-emulator-generated-key-bug

**Note:**
- This bug also occurs sourcing from a proto column field, which is my actual use case.
- This bug does not occur in cloud spanner

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.