spanner/spansql, spanner/spannertest: support OFFSET clause
This parses the OFFSET clause and implements it in spannertest queries.
Fixes #1819.
Change-Id: If0455a385875982be1825d53d8572ba23fcdf8b1
Reviewed-on: https://code-review.googlesource.com/c/gocloud/+/52993
Reviewed-by: kokoro <noreply+kokoro@google.com>
Reviewed-by: Shanika Kuruppu <skuruppu@google.com>
diff --git a/spanner/spannertest/README.md b/spanner/spannertest/README.md
index ca5b602..df2363f 100644
--- a/spanner/spannertest/README.md
+++ b/spanner/spannertest/README.md
@@ -25,7 +25,6 @@
- joins
- transaction simulation
- expression type casting, coercion
-- query offset
- SELECT aliases in FROM clause, ORDER BY
- subselects
- set operations (UNION, INTERSECT, EXCEPT)
diff --git a/spanner/spannertest/db_eval.go b/spanner/spannertest/db_eval.go
index a03cb82..5537799 100644
--- a/spanner/spannertest/db_eval.go
+++ b/spanner/spannertest/db_eval.go
@@ -400,14 +400,14 @@
return nil, fmt.Errorf("couldn't resolve identifier %s", string(id))
}
-func evalLimit(lim spansql.Limit, params queryParams) (int64, error) {
- switch lim := lim.(type) {
+func evalLiteralOrParam(lop spansql.LiteralOrParam, params queryParams) (int64, error) {
+ switch v := lop.(type) {
case spansql.IntegerLiteral:
- return int64(lim), nil
+ return int64(v), nil
case spansql.Param:
- return paramAsInteger(lim, params)
+ return paramAsInteger(v, params)
default:
- return 0, fmt.Errorf("LIMIT with %T not supported", lim)
+ return 0, fmt.Errorf("LiteralOrParam with %T not supported", v)
}
}
diff --git a/spanner/spannertest/db_query.go b/spanner/spannertest/db_query.go
index 0f3037a..86ddf5b 100644
--- a/spanner/spannertest/db_query.go
+++ b/spanner/spannertest/db_query.go
@@ -39,7 +39,7 @@
SELECT
DISTINCT
ORDER BY
- OFFSET [TODO]
+ OFFSET
LIMIT
*/
@@ -223,6 +223,28 @@
}
}
+// offsetIter applies an OFFSET clause.
+type offsetIter struct {
+ ri rowIter
+ skip int64
+}
+
+func (oi *offsetIter) Cols() []colInfo { return oi.ri.Cols() }
+func (oi *offsetIter) Next() (row, error) {
+ for oi.skip > 0 {
+ _, err := oi.ri.Next()
+ if err != nil {
+ return nil, err
+ }
+ oi.skip--
+ }
+ row, err := oi.ri.Next()
+ if err != nil {
+ return nil, err
+ }
+ return row, nil
+}
+
// limitIter applies a LIMIT clause.
type limitIter struct {
ri rowIter
@@ -284,11 +306,17 @@
ri = raw
}
- // TODO: OFFSET
-
- // Apply LIMIT.
+ // Apply LIMIT, OFFSET.
if q.Limit != nil {
- lim, err := evalLimit(q.Limit, params)
+ if q.Offset != nil {
+ off, err := evalLiteralOrParam(q.Offset, params)
+ if err != nil {
+ return nil, err
+ }
+ ri = &offsetIter{ri: ri, skip: off}
+ }
+
+ lim, err := evalLiteralOrParam(q.Limit, params)
if err != nil {
return nil, err
}
diff --git a/spanner/spansql/parser.go b/spanner/spansql/parser.go
index 07b15e1..e1ec959 100644
--- a/spanner/spansql/parser.go
+++ b/spanner/spansql/parser.go
@@ -1558,11 +1558,22 @@
}
if p.eat("LIMIT") {
- lim, err := p.parseLimitCount()
+ // "only literal or parameter values"
+ // https://cloud.google.com/spanner/docs/query-syntax#limit-clause-and-offset-clause
+
+ lim, err := p.parseLiteralOrParam()
if err != nil {
return Query{}, err
}
q.Limit = lim
+
+ if p.eat("OFFSET") {
+ off, err := p.parseLiteralOrParam()
+ if err != nil {
+ return Query{}, err
+ }
+ q.Offset = off
+ }
}
return q, nil
@@ -1761,10 +1772,7 @@
return o, nil
}
-func (p *parser) parseLimitCount() (Limit, *parseError) {
- // "only literal or parameter values"
- // https://cloud.google.com/spanner/docs/query-syntax#limit-clause-and-offset-clause
-
+func (p *parser) parseLiteralOrParam() (LiteralOrParam, *parseError) {
tok := p.next()
if tok.err != nil {
return nil, tok.err
diff --git a/spanner/spansql/parser_test.go b/spanner/spansql/parser_test.go
index c4cf530..2c3d690 100644
--- a/spanner/spansql/parser_test.go
+++ b/spanner/spansql/parser_test.go
@@ -29,7 +29,7 @@
want Query
}{
{`SELECT 17`, Query{Select: Select{List: []Expr{IntegerLiteral(17)}}}},
- {`SELECT Alias AS aka FROM Characters WHERE Age < @ageLimit AND Alias IS NOT NULL ORDER BY Age DESC LIMIT @limit` + "\n\t",
+ {`SELECT Alias AS aka FROM Characters WHERE Age < @ageLimit AND Alias IS NOT NULL ORDER BY Age DESC LIMIT @limit OFFSET 3` + "\n\t",
Query{
Select: Select{
List: []Expr{ID("Alias")},
@@ -55,7 +55,8 @@
Expr: ID("Age"),
Desc: true,
}},
- Limit: Param("limit"),
+ Limit: Param("limit"),
+ Offset: IntegerLiteral(3),
},
},
{`SELECT COUNT(*) FROM Packages`,
diff --git a/spanner/spansql/sql.go b/spanner/spansql/sql.go
index d9011b2..361409c 100644
--- a/spanner/spansql/sql.go
+++ b/spanner/spansql/sql.go
@@ -214,6 +214,9 @@
}
if q.Limit != nil {
str += " LIMIT " + q.Limit.SQL()
+ if q.Offset != nil {
+ str += " OFFSET " + q.Offset.SQL()
+ }
}
return str
}
diff --git a/spanner/spansql/types.go b/spanner/spansql/types.go
index bae3e51..1b5c840 100644
--- a/spanner/spansql/types.go
+++ b/spanner/spansql/types.go
@@ -253,7 +253,8 @@
type Query struct {
Select Select
Order []Order
- Limit Limit
+
+ Limit, Offset LiteralOrParam
}
// Select represents a SELECT statement.
@@ -313,8 +314,9 @@
SQL() string
}
-type Limit interface {
- isLimit()
+// LiteralOrParam is implemented by integer literal and parameter values.
+type LiteralOrParam interface {
+ isLiteralOrParam()
SQL() string
}
@@ -428,9 +430,9 @@
// Param represents a query parameter.
type Param string
-func (Param) isBoolExpr() {} // possibly bool
-func (Param) isExpr() {}
-func (Param) isLimit() {}
+func (Param) isBoolExpr() {} // possibly bool
+func (Param) isExpr() {}
+func (Param) isLiteralOrParam() {}
type BoolLiteral bool
@@ -454,8 +456,8 @@
// https://cloud.google.com/spanner/docs/lexical#integer-literals
type IntegerLiteral int64
-func (IntegerLiteral) isLimit() {}
-func (IntegerLiteral) isExpr() {}
+func (IntegerLiteral) isLiteralOrParam() {}
+func (IntegerLiteral) isExpr() {}
// FloatLiteral represents a floating point literal.
// https://cloud.google.com/spanner/docs/lexical#floating-point-literals
