[MB-48266] Array Index WHEN clause and query named parameters not working - Couchbase database

XML

Word

Printable

Details

Type: Bug
Status: Closed
Priority: Major
Resolution: Fixed
Affects Version/s: 7.0.0, 6.6.3, Neo, 7.0.2, 7.0.1
Fix Version/s: Neo, 7.0.2, 6.6.4
Component/s: query
Labels:
- approved-for-6.6.4
- approved-for-7.0.2

Triage:
Untriaged
Story Points:
1
Is this a Regression?:
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

MB-33009 N1QL plans different while using Named Parameters with Array Indexes

Closed

Gerrit Reviews

- Issue Only
- Show All Reviews
- Show Open Reviews
- Show All Issues
- Show Open Issues

No reviews matched the request. Check your Options in the drop-down menu of this sections header.

Activity

Ascending order - Click to sort in descending order

Sitaram Vemulapalli created issue - 31/Aug/21 8:27 PM

Sitaram Vemulapalli made changes - 31/Aug/21 8:28 PM

Field	Original Value	New Value
Link		This issue is triggered by CBSE-10662 [ CBSE-10662 ]

Sitaram Vemulapalli made changes - 31/Aug/21 8:34 PM

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 ~~MB-33009~~

Bingjie Miao made changes - 01/Sep/21 10:39 AM

Assignee

Kamini Jagtiani [ kamini.jagtiani ]

Bingjie Miao [ bingjie.miao ]

Bingjie Miao made changes - 01/Sep/21 2:52 PM

Link

This issue is caused by ~~MB-33009~~ [ ~~MB-33009~~ ]

Bingjie Miao made changes - 01/Sep/21 3:47 PM

Fix Version/s

7.0.2 [ 18012 ]

Wayne Siu made changes - 01/Sep/21 4:59 PM

Link

This issue blocks MB-46308 [ MB-46308 ]

Wayne Siu made changes - 01/Sep/21 5:00 PM

Labels

approved-for-7.0.2

Wayne Siu made changes - 01/Sep/21 5:00 PM

Is this a Regression?

Unknown [ 10452 ]

Yes [ 10450 ]

Couchbase Build Team added a comment - 01/Sep/21 5:19 PM

Build couchbase-server-7.1.0-1234 contains query commit fe9b8af with commit message:
~~MB-48266~~ Replace query parameters for WHEN clause of ANY predicates

Couchbase Build Team added a comment - 01/Sep/21 5:19 PM Build couchbase-server-7.1.0-1234 contains query commit fe9b8af with commit message: MB-48266 Replace query parameters for WHEN clause of ANY predicates

Couchbase Build Team added a comment - 01/Sep/21 6:00 PM

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

Couchbase Build Team added a comment - 01/Sep/21 6:00 PM 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

Couchbase Build Team added a comment - 01/Sep/21 10:03 PM

Build couchbase-server-7.0.2-6635 contains query commit c9abaf2 with commit message:
~~MB-48266~~ Improve previous fix

Couchbase Build Team added a comment - 01/Sep/21 10:03 PM Build couchbase-server-7.0.2-6635 contains query commit c9abaf2 with commit message: MB-48266 Improve previous fix

Couchbase Build Team added a comment - 02/Sep/21 1:29 AM

Build couchbase-server-7.1.0-1235 contains query commit e9af516 with commit message:
~~MB-48266~~ Improve previous fix

Couchbase Build Team added a comment - 02/Sep/21 1:29 AM Build couchbase-server-7.1.0-1235 contains query commit e9af516 with commit message: MB-48266 Improve previous fix

Bingjie Miao made changes - 02/Sep/21 8:09 AM

Assignee	Bingjie Miao [ bingjie.miao ]	Mihir Kamdar [ mihir.kamdar ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Resolved [ 5 ]

Pierre Regazzoni made changes - 02/Sep/21 4:22 PM

Assignee

Mihir Kamdar [ mihir.kamdar ]

Pierre Regazzoni [ JIRAUSER25157 ]

Pierre Regazzoni added a comment - 02/Sep/21 4:49 PM

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

Pierre Regazzoni added a comment - 02/Sep/21 4:49 PM 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 } }

Pierre Regazzoni made changes - 02/Sep/21 4:49 PM

Status

Resolved [ 5 ]

Closed [ 6 ]

Wayne Siu made changes - 08/Sep/21 10:22 AM

Link

This issue blocks MB-47673 [ MB-47673 ]

Wayne Siu made changes - 08/Sep/21 10:25 AM

Labels

approved-for-7.0.2

approved-for-6.6.4 approved-for-7.0.2

Wayne Siu added a comment - 08/Sep/21 10:26 AM

Re-opening it for 6.6.4.

Wayne Siu added a comment - 08/Sep/21 10:26 AM Re-opening it for 6.6.4.

Wayne Siu made changes - 08/Sep/21 10:26 AM

Assignee	Pierre Regazzoni [ JIRAUSER25157 ]	Bingjie Miao [ bingjie.miao ]
Resolution	Fixed [ 1 ]
Status	Closed [ 6 ]	Reopened [ 4 ]

Wayne Siu made changes - 08/Sep/21 10:31 AM

Fix Version/s

6.6.4 [ 17614 ]

Couchbase Build Team added a comment - 08/Sep/21 2:18 PM

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

Couchbase Build Team added a comment - 08/Sep/21 2:18 PM 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

Bingjie Miao made changes - 08/Sep/21 3:01 PM