Internal error on generated columns

[flovouin]

I've tried following the sharding patterns described in this blog post and the documentation.
While experimenting, I came across two issues.

Invalid generated column being accepted by the emulator

This DDL should not be accepted by the emulator, as the production Spanner API rejects it:

CREATE TABLE Entity (
  id STRING(MAX) NOT NULL,
  leaseExpiration TIMESTAMP,
  -- NOT NULL is not allowed here.
  shard INT64 NOT NULL AS (MOD(ABS(FARM_FINGERPRINT(id)), 20)),
) PRIMARY KEY (id);
CREATE INDEX EntitiesByShardAndLeaseExpiration ON Entity(shard, leaseExpiration)

The production API returns the following error: Generated column 'Entity.shard' that are not marked as STORED cannot be specified as NOT NULL..

Internal error when querying an index on the generated column

Independently of whether the generated column is marked as NON NULL or not, an error is sometimes returned by the emulator when querying the index on the shard column. For example when running the following query:

UPDATE
  Entity
SET
  leaseExpiration = TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
WHERE
  id IN (
    SELECT
      id
    FROM
      Entity@{FORCE_INDEX=EntitiesByShardAndLeaseExpiration}
    WHERE
      shard BETWEEN 0 AND 19
      AND (
        leaseExpiration IS NULL
        OR leaseExpiration < CURRENT_TIMESTAMP()
      )
    LIMIT
      5
  )
THEN RETURN
  id,
  leaseExpiration

The result is sometimes {"code":5, "message":"Failed to find column: Entity.id#4"}.
In other tests (not the minimal reproducible example), I even got a 13: INTERNAL error. In the emulator logs corresponding to the internal error, there was a link to this assert.

I'll provide gcloud commands to reproduce the bug in the first comment.

[flovouin]

Setup:

gcloud spanner instances delete \
  test \
  --quiet

gcloud spanner instances create \
  test \
  --config=emulator-config \
  --description="Test Instance" \
  --nodes=1

gcloud spanner databases create \
  test \
  --instance test \
  --ddl="
CREATE TABLE Entity (
  id STRING(MAX) NOT NULL,
  leaseExpiration TIMESTAMP,
  -- shard INT64 NOT NULL AS (MOD(ABS(FARM_FINGERPRINT(id)), 20)),
  shard INT64 AS (MOD(ABS(FARM_FINGERPRINT(id)), 20)),
) PRIMARY KEY (id);

CREATE INDEX EntitiesByShardAndLeaseExpiration ON Entity(shard, leaseExpiration)"

Query:

gcloud spanner databases execute-sql \
  test \
  --instance test \
  --sql="DELETE FROM Entity WHERE TRUE"

gcloud spanner databases execute-sql \
  test \
  --instance test \
  --sql="
INSERT INTO
  Entity(id, leaseExpiration)
VALUES
  ('1', '2021-01-01'),
  ('2', NULL),
  ('3', '2100-01-01')"

gcloud spanner databases execute-sql \
  test \
  --instance test \
  --sql="
UPDATE
  Entity
SET
  leaseExpiration = TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
WHERE
  id IN (
    SELECT
      id
    FROM
      Entity@{FORCE_INDEX=EntitiesByShardAndLeaseExpiration}
    WHERE
      shard BETWEEN 0 AND 19
      AND (
        leaseExpiration IS NULL
        OR leaseExpiration < CURRENT_TIMESTAMP()
      )
    LIMIT
      5
  )
THEN RETURN
  id,
  leaseExpiration"

This should be run several times, the bug occurs at random.