sinmetal
commented
2 years ago spanner_emulator.disable_query_null_filtered_index_check
spanner emulatorで NULL_FILTERED INDEX に対してFORCE_INDEXでQueryを実行すると以下のように言われる。
(FORCE_INDEXじゃなくても言われることがあるのかもしれない)
The emulator is not able to determine whether the null filtered index SampleByNameNoNulls can be used to answer this query as it may filter out nulls that may be required to answer the query. Please test this query against Cloud Spanner. If you confirm against Cloud Spanner that the null filtered index can be used to answer the query, set the hint @{spanner_emulator.disable_query_null_filtered_index_check=true} on the table to bypass this check in the emulator. This hint will be ignored by the production Cloud Spanner service and the emulator will accept the query and return a valid result when it is run with the check disabled.
言われたとおりに spanner_emulator.disable_query_null_filtered_index_check=true を入れると動く
Sample Schema and Query
gcloud spanner databases ddl update sinmetal --instance test-instance --ddl='CREATE TABLE Sample (
Id INT64 NOT NULL,
Name STRING(255),
) PRIMARY KEY(Id);
CREATE NULL_FILTERED INDEX SampleByNameNoNulls
ON Sample (
Name
);
gcloud spanner databases execute-sql sinmetal --instance test-instance --sql='SELECT Id,Name FROM Sample@{FORCE_INDEX=SampleByNameNoNulls,spanner_emulator.disable_query_null_filtered_index_check=true} WHERE Name = "hoge" LIMIT 1'
https://github.com/GoogleCloudPlatform/cloud-spanner-emulator には固有のhintが存在する どこかにdocumentがあるのかもしれないが、よく分からなかったので、メモしておく