Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Fixed
-
7.0.0, 6.6.3, 7.1.0, 7.0.2, 7.0.1
-
Untriaged
-
1
-
Yes
Description
create index ix1 on default(DISTINCT ARRAY v.lname FOR v IN names WHEN v.mname = "abc" END); |
EXPLAIN SELECT 1 FROM default WHERE ANY v IN names SATISFIES v.lname = "xyz" AND v.mname = "abc" END; -- works |
cbq> \set -$mname "abc"; |
cbq> EXPLAIN SELECT 1 FROM default WHERE ANY v IN names SATISFIES v.lname = "xyz" AND v.mname = $mname END; |
{
|
"requestID": "adc9c3a6-4904-492c-a15b-94c6b3cca802", |
"errors": [ |
{
|
"code": 4000, |
"msg": "No index available on keyspace default that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online." |
}
|
],
|
"status": "fatal", |
"metrics": { |
"elapsedTime": "2.189019ms", |
"executionTime": "2.021909ms", |
"resultCount": 0, |
"resultSize": 0, |
"errorCount": 1 |
}
|
}
|
|
last one is adhoc = true, If when clause value is parameterized it is not working. In adhoc=true we suppose to replace parameter with value before index selection.
Looks like some case is missed in MB-33009
Attachments
Issue Links
- is caused by
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Link | This issue is triggered by CBSE-10662 [ CBSE-10662 ] |
| Description |
{code:java} create index ix1 on default(DISTINCT ARRAY v.lname FOR v IN names WHEN v.mname = "abc" END); EXPLAIN SELECT 1 FROM default WHERE ANY v IN names SATISFIES v.lname = "xyz" AND v.mname = "abc" END; -- works cbq> \set -$mname "abc"; cbq> EXPLAIN SELECT 1 FROM default WHERE ANY v IN names SATISFIES v.lname = "xyz" AND v.mname = $mname END; { "requestID": "adc9c3a6-4904-492c-a15b-94c6b3cca802", "errors": [ { "code": 4000, "msg": "No index available on keyspace default that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online." } ], "status": "fatal", "metrics": { "elapsedTime": "2.189019ms", "executionTime": "2.021909ms", "resultCount": 0, "resultSize": 0, "errorCount": 1 } } {code} last one is adhoc = true Looks like some case is missed in |
{code:java}
create index ix1 on default(DISTINCT ARRAY v.lname FOR v IN names WHEN v.mname = "abc" END); EXPLAIN SELECT 1 FROM default WHERE ANY v IN names SATISFIES v.lname = "xyz" AND v.mname = "abc" END; -- works cbq> \set -$mname "abc"; cbq> EXPLAIN SELECT 1 FROM default WHERE ANY v IN names SATISFIES v.lname = "xyz" AND v.mname = $mname END; { "requestID": "adc9c3a6-4904-492c-a15b-94c6b3cca802", "errors": [ { "code": 4000, "msg": "No index available on keyspace default that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online." } ], "status": "fatal", "metrics": { "elapsedTime": "2.189019ms", "executionTime": "2.021909ms", "resultCount": 0, "resultSize": 0, "errorCount": 1 } } {code} last one is adhoc = true, If when clause value is parameterized it is not working. In adhoc=true we suppose to replace parameter with value before index selection. Looks like some case is missed in |
| Assignee | Kamini Jagtiani [ kamini.jagtiani ] | Bingjie Miao [ bingjie.miao ] |
| Fix Version/s | 7.0.2 [ 18012 ] |
| Link | This issue blocks MB-46308 [ MB-46308 ] |
| Labels | approved-for-7.0.2 |
| Is this a Regression? | Unknown [ 10452 ] | Yes [ 10450 ] |
Build couchbase-server-7.0.2-6634 contains query commit 8644cbb with commit message:
MB-48266 Replace query parameters for WHEN clause of ANY predicates
Build couchbase-server-7.0.2-6635 contains query commit c9abaf2 with commit message:
MB-48266 Improve previous fix
Build couchbase-server-7.1.0-1235 contains query commit e9af516 with commit message:
MB-48266 Improve previous fix
| Assignee | Bingjie Miao [ bingjie.miao ] | Mihir Kamdar [ mihir.kamdar ] |
| Resolution | Fixed [ 1 ] | |
| Status | Open [ 1 ] | Resolved [ 5 ] |
| Assignee | Mihir Kamdar [ mihir.kamdar ] | Pierre Regazzoni [ JIRAUSER25157 ] |
Verified on 7.0.2-6640 and 7.1.0-1235
cbq> EXPLAIN SELECT 1 FROM default WHERE ANY v IN names SATISFIES v.lname = "xyz" AND v.mname = $mname END; |
{
|
"requestID": "587e4bef-e554-4037-bbf8-ef639c1543b9", |
"signature": "json", |
"results": [ |
{
|
"plan": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "DistinctScan", |
"scan": { |
"#operator": "IndexScan3", |
"covers": [ |
"cover ((distinct (array (`v`.`lname`) for `v` in (`default`.`names`) when ((`v`.`mname`) = \"abc\") end)))", |
"cover ((meta(`default`).`id`))" |
],
|
"filter": "cover (any `v` in (`default`.`names`) satisfies (((`v`.`lname`) = \"xyz\") and ((`v`.`mname`) = $mname)) end)", |
"filter_covers": { |
"cover (any `v` in (`default`.`names`) satisfies (((`v`.`lname`) = \"xyz\") and ((`v`.`mname`) = $mname)) end)": true |
},
|
"index": "ix1", |
"index_id": "e9b7d7f5e2870a16", |
"index_projection": { |
"primary_key": true |
},
|
"keyspace": "default", |
"namespace": "default", |
"spans": [ |
{
|
"exact": true, |
"range": [ |
{
|
"high": "\"xyz\"", |
"inclusion": 3, |
"index_key": "(distinct (array (`v`.`lname`) for `v` in `names` when ((`v`.`mname`) = \"abc\") end))", |
"low": "\"xyz\"" |
}
|
]
|
}
|
],
|
"using": "gsi" |
}
|
},
|
{
|
"#operator": "Parallel", |
"~child": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "InitialProject", |
"result_terms": [ |
{
|
"expr": "1" |
}
|
]
|
}
|
]
|
}
|
}
|
]
|
},
|
"text": "SELECT 1 FROM default WHERE ANY v IN names SATISFIES v.lname = \"xyz\" AND v.mname = $mname END;" |
}
|
],
|
"status": "success", |
"metrics": { |
"elapsedTime": "2.065132ms", |
"executionTime": "1.964994ms", |
"resultCount": 1, |
"resultSize": 2616, |
"serviceLoad": 6 |
}
|
}
|
| Status | Resolved [ 5 ] | Closed [ 6 ] |
| Link | This issue blocks MB-47673 [ MB-47673 ] |
| Labels | approved-for-7.0.2 | approved-for-6.6.4 approved-for-7.0.2 |
| Assignee | Pierre Regazzoni [ JIRAUSER25157 ] | Bingjie Miao [ bingjie.miao ] |
| Resolution | Fixed [ 1 ] | |
| Status | Closed [ 6 ] | Reopened [ 4 ] |
| Fix Version/s | 6.6.4 [ 17614 ] |
Build couchbase-server-6.6.4-9915 contains query commit a2b428e with commit message:
MB-48266 Replace query parameters for WHEN clause of ANY predicates
| Assignee | Bingjie Miao [ bingjie.miao ] | Pierre Regazzoni [ JIRAUSER25157 ] |
| Resolution | Fixed [ 1 ] | |
| Status | Reopened [ 4 ] | Resolved [ 5 ] |
| Status | Resolved [ 5 ] | Closed [ 6 ] |
| Link | This issue blocks MB-50048 [ MB-50048 ] |
Build couchbase-server-7.1.0-1234 contains query commit fe9b8af with commit message:
MB-48266Replace query parameters for WHEN clause of ANY predicates