GoogleCloudPlatform / GoogleCloudPlatform/cloud-spanner-emulator

Interleaved search index incorrectly requires parent key in token_column_list

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

Description

Interleaved search indexes incorrectly require the parent key in the `token_column_list` parameter.

```sql
CREATE TABLE one (
one_id STRING(MAX) NOT NULL
)
PRIMARY KEY (one_id);

CREATE TABLE two (
one_id STRING(MAX) NOT NULL,
two_id STRING(MAX) NOT NULL,
title STRING(MAX),
title_tokens TOKENLIST AS (TOKENIZE_SUBSTRING(title)) HIDDEN
)
PRIMARY KEY (one_id, two_id),
INTERLEAVE IN PARENT one;
```

Creating the valid search index fails:

```sql
CREATE SEARCH INDEX search_title ON two (title_tokens)
PARTITION BY one_id,
INTERLEAVE IN one
OPTIONS (sort_order_sharding = true);
```

With the following error:

```
code = FailedPrecondition desc = Index search_title does not reference index parent key column one_id.
```

You can work around this in the emulator by adding the column, but this won't work in Google Cloud:

```sql
CREATE SEARCH INDEX search_title ON two (one_id, title_tokens)
PARTITION BY one_id,
INTERLEAVE IN one
OPTIONS (sort_order_sharding = true);
```

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.