GoogleCloudPlatform / GoogleCloudPlatform/cloud-spanner-emulator
DEFAULT (PENDING_COMMIT_TIMESTAMP()) fails with FailedPrecondition when table has a generated stored primary key.
- 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
Assessment
This issue has not been assessed yet.