feat(spanner/spansql): Add support for tokenlist and create search index

[rasviitanen]

Adds support for parsing tokenlist and create search index in spanner/spansql.

Fixes #11466

[google-cla]

Thanks for your pull request! It looks like this may be your first contribution to a Google open source project. Before we can look at your pull request, you'll need to sign a Contributor License Agreement (CLA).

View this failed invocation of the CLA check for more information.

For the most up to date status, view the checks section at the bottom of the pull request.

[rasviitanen]

Thanks for the review, I have now fixed your comments.
I expanded the tests a bit and also fixed some issues with ORDER BY and OPTIONS.

[neophenix]

Awesome thanks! I did not do an exhaustive review, these were just things I ran into when I pulled your branch and was running some local tests, but the changes all seem to work for me, so fingers crossed :)

[karunacybozu]

@rasviitanen it's not working if we combine it with other function.
For example I need NORMALIZE_AND_CASEFOLD() function to normalize Katakana for searching: https://cloud.google.com/spanner/docs/reference/standard-sql/string_functions#normalize_and_casefold

Example would be:

CREATE TABLE `Posts` (
    `TenantId` STRING(8) NOT NULL,
    `UserId` STRING(26) NOT NULL,
    `Id` STRING(26) NOT NULL,
    `Title` STRING(256) NOT NULL,
    `Body` STRING(1024) NOT NULL,
    `Optional` STRING(256),
    `Title_Tokens` TOKENLIST AS (TOKENIZE_FULLTEXT(Title)) HIDDEN,
    `Body_Tokens` TOKENLIST AS (TOKENIZE_FULLTEXT(Body)) HIDDEN,
    `Combined_Tokens` TOKENLIST AS (TOKENLIST_CONCAT([Title_Tokens, Body_Tokens])) HIDDEN,
    `Title_SubStrTokens` TOKENLIST AS (TOKENIZE_SUBSTRING(Title)) HIDDEN,
    `Body_SubStrTokens` TOKENLIST AS (TOKENIZE_SUBSTRING(Body)) HIDDEN,
    `Combined_SubStrTokens` TOKENLIST AS (TOKENLIST_CONCAT([Title_SubStrTokens, Body_SubStrTokens])) HIDDEN,
    `Title_NFKC_SubStrTokens` TOKENLIST AS (TOKENIZE_SUBSTRING(NORMALIZE_AND_CASEFOLD(Title, NFKC))) HIDDEN,
    `Body_NFKC_SubStrTokens` TOKENLIST AS (TOKENIZE_SUBSTRING(NORMALIZE_AND_CASEFOLD(Body, NFKC))) HIDDEN,
    `Combined_NFKC_SubStrTokens` TOKENLIST AS (TOKENLIST_CONCAT([Title_NFKC_SubStrTokens, Body_NFKC_SubStrTokens])) HIDDEN,
) PRIMARY KEY (`TenantId`, `UserId`, `Id`)

It's working just fine in spanner studio.

But in Go code:

panic: 01-Posts.sql:14: got "(", want ")" or ","

[rasviitanen]

@karunacybozu Thanks for your comments!
The issue here is not the parsing of the TOKENIZE_* function arugments in this PR. The parsing I added here actually supports nested functions and is correct (you can try with UPPER for example). The problem is rather that NORMALIZE_AND_CASEFOLD isn't recognized as a known string function. I would prefer to not add support for it in this PR, as it isn't required by TOKENLIST/TOKENIZE_/SEARCH INDEX.

I would suggest to submit a PR of your own to add additional string functions such as NORMALIZE and NORMALIZE_AND_CASEFOLD to spanner/spansql/keywords.go. That should be all that's necessary.

One could argue that the error message from the parser is a bit cryptic though, maybe we could improve it in a follow-up.
It looks like the lib has the same problem for all the other argument parsers, but I don't want to add any more to this already quite big PR. It's probably better to solve error handling for everything at once and in that case we are better off keeping the scope of this PR limited.
Hope that works!🙏

[rasviitanen]

@rahul2393 It would be greatly appreciated if this could be reviewed, not sure who to ping 🙏

[rasviitanen]

@rahul2393 looks like my branch was outdated, had to merge main again. Could you readd kokoro:force-run? Sorry for the inconvenience.