spanner/spannertest: support STARTS_WITH

[sryoya]

Is your feature request related to a problem? Please describe.
I suppose spannertest doesn't support the STARTS_WITH clause yet.
It would be great if it's supported.
As the official doc recommends using it rather than LIKE for a forward match, there should be certain motivation to use it.

Describe the solution you'd like
I tried to implement it, but, due to the following characteristic of STARTS_WITH, it looks there is a certain point to consider in spansql.

• STARTS_WITH is a kind of comparison clause same as LIKE and BETWEEN
  But,
• It takes both comparison targets as arguments unlike the other comparison clauses take one of them before the clause.

Due to the above, we cannot simply parse it as with the other comparison clauses even though what it does is the same as them.

I think there are two implementation options to handle that (I suppose the first one is better).

• Create another Op struct and the parsing func for STARTS_WITH.
• Put the parsing code for STARTS_WITH in the same func with the other comparison clauses, and add code in the parent parsing funcs so that STARTS_WITH won't be parsed in the different code.

[hengfengli]

@olavloite Any thoughts about this?

[olavloite]

STARTS_WITH is a function that will return a BOOL, so I don't think we should parse this in the same way as normal comparison operators. There are are a lot of other functions that also return BOOL, and all other functions could in theory also be used in comparisons, so the correct way to implement this would be:

1. Add generic support for parsing function expressions (currently the highest item on the 'not implemented' list: https://github.com/googleapis/google-cloud-go/blob/master/spanner/spannertest/README.md)
2. Add an implementation for this specific function.

By doing the above you would get a solution that would work automatically, as the implementation of STARTS_WITH would be an expression that returns a BOOL, and the further evaluation of the entire query expression would just use that as any other expression that would return a BOOL value.

[sryoya]

@olavloite Thanks for the answer.
I agree with your view that STARTS_WITH should be treated as a function that returns BOOL rather than a comparison operator.

Also, your solution looks fine and I'm trying to implement with that way.

Let me ask you a bit about implementing the generic support.
Is it ok to have a dedicated operator for functions that return BOOL ?
spansql already has the Func struct, which seems the generic operator for function expressions. But, what you meant and "function expressions" in the "not implemented" list are different from that, I guess?
I feel we may want to have the dedicated operator for functions that returns BOOL apart from this struct as it doesn't know if an operation has the "NOT" clause and there are a lot of functions that also return BOOL

[olavloite]

Is it ok to have a dedicated operator for functions that return BOOL ?

I'm not entirely sure I understand what you mean by that, but more in general: I don't think you need to (or should) implement function parsing differently for functions that return a BOOL compared to any other function. A function call is an expression that in the end will just return a value, just like any other expression. If that value is part of a SELECT list, then the value should be returned in the query result. If the value is used in a WHERE condition, then the value should be used in the comparison that you were already parsing.

spansql already has the Func struct, which seems the generic operator for function expressions. But, what you meant and "function expressions" in the "not implemented" list are different from that, I guess?

No, that is what I meant, but there seems to be more implemented already than I thought. So there is a bare-bone implementation for functions already, but it's very limited at the moment:

1. The only functions that are implemented are aggregate functions (COUNT, SUM, ...):

   google-cloud-go/spanner/spannertest/funcs.go

   Line 39 in ade8ab1

   var aggregateFuncs = map[string]aggregateFunc{

2. It seems to only be used for parsing functions in the SELECT expression list:

   google-cloud-go/spanner/spansql/parser.go

   Line 2730 in ade8ab1

   if name := strings.ToUpper(tok.value); funcs[name] && p.sniff("(") {

   . It should however also work (at least in theory) in a WHERE clause.

The above means that:

1. A statement like SELECT COUNT(*) FROM Foo will work.
2. A statement like SELECT STARTS_WITH(Bar, 'B') FROM Foo will (probably) be recognized as a valid statement with a function expression in the select, but there is no implementation for the function STARTS_WITH, so the query engine has no idea what value to return.
3. A statement like SELECT * FROM Foo WHERE STARTS_WITH(Bar, 'B') will (probably) also be recognized as a valid statement, but will also fail as the query engine does not know how to calculate the value of the function expression for each row in the table.

I feel we may want to have the dedicated operator for functions that returns BOOL apart from this struct as it doesn't know if an operation has the "NOT" clause and there are a lot of functions that also return BOOL

No. Parsing a function should be completely independent of any operators before or after the function itself. The expression that is returned by the parser is added to the evaluation tree, just as the NOT operator that preceded it, and the evaluator is responsible for evaluating the entire tree once all expressions have been parsed.

You should think of a function exactly as any other expression, such as for example a constant literal (for example the boolean value TRUE). Any literal in a SQL string could be replaced by a random function call and the parser should not care or know. All it does is parse the different expressions and add it to the expression tree. The entire tree will then be evaluated once all expressions have been parsed.

Consider the following two statements:

SELECT * FROM Foo WHERE NOT STARTS_WITH(Bar, 'B')

SELECT * FROM Foo WHERE NOT TRUE

There's nothing different or magical about the NOT before the STARTS_WITH function call compared to the NOT before the literal TRUE.

[olavloite]

@sryoya I've added an implementation in #4670 for this specific function. I think that also shows how other functions could be implemented if there are additional functions that you are using and would like to include in the testing using spansql/spannertest.

[sryoya]

Thanks for the very clear and detailed explanation 😆 Now I understand the design concept of the SQL parsing for function expression.

Also, thanks for #4670. According to your explanation, your solution looks reasonable.
Looking forward to seeing this PR merged and released 🐈