spanner/spansql, spanner/spannertest: implement support for IN expressions
This handles most forms of IN except for subqueries and STRUCT values.
Fixes #1821.
Change-Id: I2fe4aceee789a00160f0a99b50351a90dca25a38
Reviewed-on: https://code-review.googlesource.com/c/gocloud/+/53250
Reviewed-by: Knut Olav Løite <koloite@gmail.com>
diff --git a/spanner/spannertest/db_eval.go b/spanner/spannertest/db_eval.go
index 5537799..2b57d04 100644
--- a/spanner/spannertest/db_eval.go
+++ b/spanner/spannertest/db_eval.go
@@ -58,7 +58,7 @@
return false, fmt.Errorf("unhandled BoolExpr %T", be)
case spansql.BoolLiteral:
return bool(be), nil
- case spansql.ID, spansql.Paren:
+ case spansql.ID, spansql.Paren, spansql.InOp: // InOp is a bit weird.
e, err := ec.evalExpr(be)
if err != nil {
return false, err
@@ -360,6 +360,53 @@
return ec.evalBoolExpr(e)
case spansql.ComparisonOp:
return ec.evalBoolExpr(e)
+ case spansql.InOp:
+ // This is implemented here in evalExpr instead of evalBoolExpr
+ // because it can return FALSE/TRUE/NULL.
+ // The docs are a bit confusing here, so there's probably some bugs here around NULL handling.
+
+ if len(e.RHS) == 0 {
+ // "IN with an empty right side expression is always FALSE".
+ return e.Neg, nil
+ }
+ lhs, err := ec.evalExpr(e.LHS)
+ if err != nil {
+ return false, err
+ }
+ if lhs == nil {
+ // "IN with a NULL left side expression and a non-empty right side expression is always NULL".
+ return nil, nil
+ }
+ var b bool
+ for _, rhse := range e.RHS {
+ rhs, err := ec.evalExpr(rhse)
+ if err != nil {
+ return false, err
+ }
+ if !e.Unnest {
+ if lhs == rhs {
+ b = true
+ }
+ } else {
+ if rhs == nil {
+ // "IN UNNEST(<NULL array>) returns FALSE (not NULL)".
+ return e.Neg, nil
+ }
+ arr, ok := rhs.([]interface{})
+ if !ok {
+ return nil, fmt.Errorf("UNNEST argument evaluated as %T, want array", rhs)
+ }
+ for _, rhs := range arr {
+ if lhs == rhs {
+ b = true
+ }
+ }
+ }
+ }
+ if e.Neg {
+ b = !b
+ }
+ return b, nil
case spansql.IsOp:
return ec.evalBoolExpr(e)
case aggSentinel:
diff --git a/spanner/spannertest/db_test.go b/spanner/spannertest/db_test.go
index 2c28197..b8fa1b9 100644
--- a/spanner/spannertest/db_test.go
+++ b/spanner/spannertest/db_test.go
@@ -483,6 +483,17 @@
{true, false},
},
},
+ {
+ `SELECT Name FROM Staff WHERE ID IN UNNEST(@ids)`,
+ queryParams{"ids": queryParam{
+ Value: []interface{}{int64(3), int64(1)},
+ Type: spansql.Type{Base: spansql.Int64, Array: true},
+ }},
+ [][]interface{}{
+ {"Jack"},
+ {"Sam"},
+ },
+ },
// From https://cloud.google.com/spanner/docs/query-syntax#group-by-clause_1:
{
// TODO: Ordering matters? Our implementation sorts by the GROUP BY key,
diff --git a/spanner/spansql/parser.go b/spanner/spansql/parser.go
index 308073f..3ecfda4 100644
--- a/spanner/spansql/parser.go
+++ b/spanner/spansql/parser.go
@@ -1859,6 +1859,7 @@
orParser
andParser
parseIsOp
+ parseInOp
parseComparisonOp
parseArithOp: |, ^, &, << and >>, + and -, * and / and ||
parseUnaryArithOp: - and ~
@@ -1977,7 +1978,7 @@
func (p *parser) parseIsOp() (Expr, *parseError) {
debugf("parseIsOp: %v", p)
- expr, err := p.parseComparisonOp()
+ expr, err := p.parseInOp()
if err != nil {
return nil, err
}
@@ -2011,6 +2012,37 @@
return isOp, nil
}
+func (p *parser) parseInOp() (Expr, *parseError) {
+ debugf("parseInOp: %v", p)
+
+ expr, err := p.parseComparisonOp()
+ if err != nil {
+ return nil, err
+ }
+
+ // TODO: do we need to do lookahead?
+
+ inOp := InOp{LHS: expr}
+ if p.eat("NOT") {
+ inOp.Neg = true
+ }
+
+ if !p.eat("IN") {
+ // TODO: push back the "NOT"?
+ return expr, nil
+ }
+
+ if p.eat("UNNEST") {
+ inOp.Unnest = true
+ }
+
+ inOp.RHS, err = p.parseParenExprList()
+ if err != nil {
+ return nil, err
+ }
+ return inOp, nil
+}
+
var symbolicOperators = map[string]ComparisonOperator{
"<": Lt,
"<=": Le,
diff --git a/spanner/spansql/parser_test.go b/spanner/spansql/parser_test.go
index af3be98..1317646 100644
--- a/spanner/spansql/parser_test.go
+++ b/spanner/spansql/parser_test.go
@@ -137,6 +137,7 @@
{`Speech NOT LIKE "_oo"`, ComparisonOp{LHS: ID("Speech"), Op: NotLike, RHS: StringLiteral("_oo")}},
{`A AND NOT B`, LogicalOp{LHS: ID("A"), Op: And, RHS: LogicalOp{Op: Not, RHS: ID("B")}}},
{`X BETWEEN Y AND Z`, ComparisonOp{LHS: ID("X"), Op: Between, RHS: ID("Y"), RHS2: ID("Z")}},
+ {`@needle IN UNNEST(@haystack)`, InOp{LHS: Param("needle"), RHS: []Expr{Param("haystack")}, Unnest: true}},
// String literal:
// Accept double quote and single quote.
diff --git a/spanner/spansql/sql.go b/spanner/spansql/sql.go
index 0e8c63c..e3116d2 100644
--- a/spanner/spansql/sql.go
+++ b/spanner/spansql/sql.go
@@ -334,6 +334,26 @@
return s
}
+func (io InOp) SQL() string {
+ str := io.LHS.SQL()
+ if io.Neg {
+ str += " NOT"
+ }
+ str += " IN "
+ if io.Unnest {
+ str += "UNNEST"
+ }
+ str += "("
+ for i, e := range io.RHS {
+ if i > 0 {
+ str += ", "
+ }
+ str += e.SQL()
+ }
+ str += ")"
+ return str
+}
+
func (io IsOp) SQL() string {
str := io.LHS.SQL() + " IS "
if io.Neg {
diff --git a/spanner/spansql/types.go b/spanner/spansql/types.go
index 6c3b8c0..257c793 100644
--- a/spanner/spansql/types.go
+++ b/spanner/spansql/types.go
@@ -388,6 +388,18 @@
NotBetween
)
+type InOp struct {
+ LHS Expr
+ Neg bool
+ RHS []Expr
+ Unnest bool
+
+ // TODO: support subquery form
+}
+
+func (InOp) isBoolExpr() {} // usually
+func (InOp) isExpr() {}
+
type IsOp struct {
LHS Expr
Neg bool
