Details
Description
Create 1 node cluster with all services
found the issue with rqg
We have two indexes for a bucket simple_table. Indexes are GSI: primary and simple_table_index_name_fields_only_char_field1 (shows is the name of the fields it is composed off)
Queries where we see the difference
SQL:: SELECT MIN( decimal_field1 ) FROM simple_table WHERE ((bool_field1 != false AND char_field1 IS NOT NULL)) AND ((decimal_field1 = 5261 OR varchar_field1 IN ( "AAxQrvKIli" , "ACrpEQXiyg" , "AEcRheqYXg" , "AHkHtTqouV" , "AKvhlhYWtY" ))) OR ((bool_field1 != false OR varchar_field1 NOT BETWEEN "AAxQrvKIli" and "zzHiRyTTMy")) AND ((bool_field1 = false AND (varchar_field1 IS NOT NULL) AND (char_field1 NOT BETWEEN "A" and "Z")))
n1ql_query :: SELECT MIN( decimal_field1 ) FROM simple_table WHERE ((bool_field1 != false AND char_field1 IS NOT NULL)) AND ((decimal_field1 = 5261 OR varchar_field1 IN [ "AAxQrvKIli" , "ACrpEQXiyg" , "AEcRheqYXg" , "AHkHtTqouV" , "AKvhlhYWtY" ])) OR ((bool_field1 != false OR varchar_field1 NOT BETWEEN "AAxQrvKIli" and "zzHiRyTTMy")) AND ((bool_field1 = false AND (varchar_field1 IS NOT NULL) AND (char_field1 NOT BETWEEN "A" and "Z")))
Two different results::
THIS ONE USES THE INDEX BEING PICKED UP
cbq> {])) OR ((bool_field1 != false OR varchar_field1 NOT BETWEEN "AAxQrvKIli" and "zzHiRyTTMy")) AND ((bool_field1 = false AND (varchar_field1 IS NOT NULL) AND (char_field1 NOT BETWEEN "A" and "Z")));
{
"requestID": "58d1cf6b-ee5f-44f2-a500-c0f79fdfd06b",
"signature":
,
"results": [
],
"status": "success",
"metrics":
}
THIS ONE USES PRIMARY INDEX AS HINT
cbq> {])) OR ((bool_field1 != false OR varchar_field1 NOT BETWEEN "AAxQrvKIli" and "zzHiRyTTMy")) AND ((bool_field1 = false AND (varchar_field1 IS NOT NULL) AND (char_field1 NOT BETWEEN "A" and "Z")));
{
"requestID": "4e782f81-cbd3-4a57-b450-16d24d8bdfb7",
"signature":
,
"results": [
],
"status": "success",
"metrics":
}
The following explain shows that composite index was being picked up
cbq> {])) OR ((bool_field1 != false OR varchar_field1 NOT BETWEEN "AAxQrvKIli" and "zzHiRyTTMy")) AND ((bool_field1 = false AND (varchar_field1 IS NOT NULL) AND (char_field1 NOT BETWEEN "A" and "Z")));
{
"requestID": "ac3773fc-8200-468a-a6d1-6882aeaf4b2d",
"signature": "json",
"results": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "UnionScan",
"scans": [
{
"#operator": "IndexScan",
"index": "simple_table_index_name_fields_only_char_field1",
"keyspace": "simple_table",
"limit": 9.223372036854776e+18,
"namespace": "default",
"spans": [
{
"Range":
{ "High": [ "true" ], "Inclusion": 1, "Low": [ "false" ] },
"Seek": null
},
{
"Range":
{ "High": [ "\"A\"" ], "Inclusion": 0, "Low": null },
"Seek": null
},
{
"Range":
{ "High": null, "Inclusion": 0, "Low": [ "\"Z\"" ] },
"Seek": null
}
],
"using": "gsi"
}
]
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{ "#operator": "Fetch", "keyspace": "simple_table", "namespace": "default" }
,
{ "#operator": "Filter", "condition": "((((not ((`simple_table`.`bool_field1`) = false)) and ((`simple_table`.`char_field1`) is not null)) and (((`simple_table`.`decimal_field1`) = 5261) or ((`simple_table`.`varchar_field1`) in [\"AAxQrvKIli\", \"ACrpEQXiyg\", \"AEcRheqYXg\", \"AHkHtTqouV\", \"AKvhlhYWtY\"]))) or (((not ((`simple_table`.`bool_field1`) = false)) or (not ((`simple_table`.`varchar_field1`) between \"AAxQrvKIli\" and \"zzHiRyTTMy\"))) and ((((`simple_table`.`bool_field1`) = false) and ((`simple_table`.`varchar_field1`) is not null)) and (not ((`simple_table`.`char_field1`) between \"A\" and \"Z\")))))" }
,
{ "#operator": "InitialGroup", "aggregates": [ "min((`simple_table`.`decimal_field1`))" ], "group_keys": [] }]
}
},
{ "#operator": "IntermediateGroup", "aggregates": [ "min((`simple_table`.`decimal_field1`))" ], "group_keys": [] }
,
{ "#operator": "FinalGroup", "aggregates": [ "min((`simple_table`.`decimal_field1`))" ], "group_keys": [] }
,
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "InitialProject",
"result_terms": [
{ "expr": "min((`simple_table`.`decimal_field1`))" }]
},
{ "#operator": "FinalProject" }]
}
}
]
}
],
"status": "success",
"metrics":
{ "elapsedTime": "20.039475ms", "executionTime": "19.966372ms", "resultCount": 1, "resultSize": 4955 }}