spanner/spansql: generated columns

[geerttouquet]

Client

spansql Version v1.13.0

Environment

windows

Go Environment

$ go version : go version go1.13 windows/amd64
$ go env

If you extract the following example
CREATE TABLE users (
Id STRING(20) NOT NULL,
FirstName STRING(50),
LastName STRING(50),
Age INT64 NOT NULL,
FullName STRING(100) AS (ARRAY_TO_STRING([FirstName, LastName], " ")) STORED,
) PRIMARY KEY (Id);

You got error : ParseDDL("CREATE TABLE users (\n\tId STRING(20) NOT NULL,\n\tFirstName STRING(50),\n\tLastName STRING(50),\n\tAge INT64 NOT NULL,\n\tFullName STRING(100) AS (ARRAY_TO_STRING([FirstName, LastName], " ")) STORED,\n) PRIMARY KEY (Id);"): filename:6: got "(" while expecting ")"

[geerttouquet]

a strange thing
CREATE TABLE GenCol (
Name STRING(MAX) NOT NULL,
NameLen INT64 AS (CHAR_LENGTH(Name)) STORED,
) PRIMARY KEY (Name);
this succeeds as in test
CREATE TABLE GenCol (
Name STRING(MAX) NOT NULL,
NameLen INT64 AS (UPPER(Name)) STORED,
) PRIMARY KEY (Name);
fails

[dsymonds]

The problem is not with generated columns, but with the limited support for builtin functions. ARRAY_TO_STRING and UPPER are not implemented, but CHAR_LENGTH is. We can add more functions to the list easily enough, but they take a fair bit of effort to implement in spannertest, so it depends on whether you're just trying to parse these or want spannertest to act on them.

Note that generated columns remain a TODO for spannertest (see spanner/spannertest/README.md).

[geerttouquet]

We use UPPER in our configuration already, now this gives an error to parse :-) It would be nice if this succeeds

[dsymonds]

Sure, no problem. Easy enough to flesh out the list of string funcs at least. See #3625.

[geerttouquet]

thanks ! spansql works very good

[geerttouquet]

I have test with NameLen INT64 AS (UPPER(Name)) STORED, -> this works perfect, thanks

[geerttouquet]

example
CREATE TABLE users (
Id STRING(20) NOT NULL,
FirstName STRING(50),
LastName STRING(50),
Age INT64 NOT NULL,
FullName STRING(100) AS (ARRAY_TO_STRING([FirstName, LastName], " ")) STORED,
) PRIMARY KEY (Id);

this still gives error
ParseDDL("CREATE TABLE users (\n\t\t\tId STRING(20) NOT NULL,\n\t\t\tFirstName STRING(50),\n\t\t\tLastName STRING(50),\n\t\t\tAge INT64 NOT NULL,\n\t\t\tFullName STRING(100) AS (ARRAY_TO_STRING([FirstName, LastName], " ")) STORED,\n\t\t) PRIMARY KEY (Id);"): filename:6: got "(" while expecting ")"

[dsymonds]

Ah, that's an array function rather than a string function, but I can add those too.

[geerttouquet]

David

thanks it works