Hi everyone. Ran into an interesting problem today, couldn’t figure out where to submit an issue so I’m posting it here. Thanks!
Problem
When passing unix-nano-timestamp integers (19bytes, 64bit) to the Couchbase Server via
the /query/service endpoint, some numbers are not evaluated correctly using the
lt (<) and gt (>) n1ql query operators.
Environment
Couchbase Server 5.0.0 (5.0.0-3519)
MacOS High Sierra 10.13.1 (17B1003)
Methodology
Discovered when querying for a group of documents where [field < $1].
Initially suspected issue lay in gocb, exploration suggests issue is
further upstream - most likely in Couchbase Server API [args] parser.
Three (two shown) sets of four (a, b, c, and d) nano-timestamps were generated sequentially
until a clear pass fail scenarios were evident. Each int64 value was assigned
to a simple struct, and then inserted into a test bucket (foobar). Queries were
generated for each set, using the c integer as the test value.
The queries were run in two formats - first using an embedded value (in
which the value c was converted to a string and embedded into the query string
to be sent to the API), then passing the the c integer as a positional
parameter to bucket.ExecuteN1qlQuery.
Test data
-
Good Values
a: 1515026631803155000
b: 1515026631804568000
c: 1515026631804859000 ← test value
d: 1515026631804990000 -
Bad Values
a: 1515026765603621000
b: 1515026765604796000
c: 1515026765605058000 ← test value
d: 1515026765605259000
Queries
-
Embedded Values
– Q1. SELECT longint, t from foobar WHERE longint < 1515026631804859000 AND t = ‘good’ ORDER BY longint DESC limit 10
– Q2. SELECT longint, t from foobar WHERE longint < 1515026765605058000 AND t = ‘bad’ ORDER BY longint DESC limit 10 -
Positional Values
– Q3. SELECT longint, t from foobar WHERE longint < $1 AND t = ‘good’ ORDER BY longint DESC limit 10
PARAMS: [1515026631804859000]
– Q4. SELECT longint, t from foobar WHERE longint < $1 AND t = ‘bad’ ORDER BY longint DESC limit 10
PARAMS: [1515026765605058000]
Expected Result
All four queries should return a maximum of 2 results (a, b).
Actual Result
Queries Q1, Q2, Q3 return 2 results (b, a).
(!) Query Q4 returns 3 results (c, b, a).
Steps to Reproduce
Attached to this Gist simple main.go file which demonstrates the above scenario.
Create bucket, set user and pass, run.
The JSON payloads were printed from gocb/cluster_n1qlquery[1] and then
POSTED to the server using Postman 5.3.2 (5.3.2). These JSON payloads
replicate the above findings, suggesting the problem lies in the API server.
Embedded value query, returns 2/2 expected results:
{“args”:,“statement”:"SELECT longint, t from foobar WHERE longint \u003c 1515026765605058000 AND t = ‘bad’ ORDER BY longint DESC limit 10 ",“timeout”:“1m15s”}
POST
POST /query/service HTTP/1.1
Host: 127.0.0.1:8093
Content-Type: application/json
Authorization: Basic Zm9vYmFyOnRlc3Rpbmc=
Cache-Control: no-cache
Postman-Token: e116b450-3af6-250d-f9dc-61a54b0336b1{“args”:,“statement”:"SELECT longint, t from foobar WHERE longint \u003c 1515026765605058000 AND t = ‘bad’ ORDER BY longint DESC limit 10 ",“timeout”:“1m15s”}
Results
{ "requestID": "50257527-f9e2-4808-847f-533f204c1a49", "signature": { "longint": "json", "t": "json" }, "results": [ { "longint": 1515026765604796000, "t": "bad" }, { "longint": 1515026765603621000, "t": "bad" } ], "status": "success", "metrics": { "elapsedTime": "14.851ms", "executionTime": "14.786ms", "resultCount": 2, "resultSize": 82, "sortCount": 2 } }
Positional value query, returns 3/2 expected results:
{“args”:[1515026765605058000],“statement”:"SELECT longint, t from foobar WHERE longint \u003c $1 AND t = ‘bad’ ORDER BY longint DESC limit 10 ",“timeout”:“1m15s”}
POST
POST /query/service HTTP/1.1
Host: 127.0.0.1:8093
Content-Type: application/json
Authorization: Basic Zm9vYmFyOnRlc3Rpbmc=
Cache-Control: no-cache
Postman-Token: 873a6908-b121-cc8b-06d4-318db7529a47{“args”:[1515026765605058000],“statement”:"SELECT longint, t from foobar WHERE longint \u003c $1 AND t = ‘bad’ ORDER BY longint DESC limit 10 ",“timeout”:“1m15s”}
Results
{ "requestID": "03d9c090-ffcc-446d-ae41-af130a65a84c", "signature": { "longint": "json", "t": "json" }, "results": [ { "longint": 1515026765605058000, "t": "bad" }, { "longint": 1515026765604796000, "t": "bad" }, { "longint": 1515026765603621000, "t": "bad" } ], "status": "success", "metrics": { "elapsedTime": "10.981ms", "executionTime": "10.956ms", "resultCount": 3, "resultSize": 123, "sortCount": 3 } }
An additional number was created in an attempt to determine the range
in which the value fails to evaluate correctly.
1515026765604874880 passes
1515026765604874881 fails
Gist + Go proof of concept:
https://gist.github.com/mochihub/bae9e745b7abcce72457ac1ab4b80d8d