Details
-
Bug
-
Resolution: Fixed
-
Major
-
7.6.2, 7.2.6
-
7.6.2-3625
-
Untriaged
-
0
-
Yes
Description
During regression we see following error:
2024-05-13 11:41:53 | INFO | MainProcess | test_thread | [tuq.run_cbq_query] RUN QUERY explain select count(*) from default where _id = 'query-testemployee10194.855617-0' and address[1][0].country = 'United States of America' and gender = 'M' |
2024-05-13 11:41:53 | INFO | MainProcess | test_thread | [on_prem_rest_client.query_tool] query params : statement=explain+select+count%28%2A%29+from+default+where+_id+%3D+%27query-testemployee10194.855617-0%27+and+address%5B1%5D%5B0%5D.country+%3D+%27United+States+of+America%27+and+gender+%3D+%27M%27&scan_consistency=REQUEST_PLUS |
2024-05-13 11:41:53 | ERROR | MainProcess | test_thread | [on_prem_rest_client._http_request] POST http://172.23.217.166:8093/query?statement=explain+select+count%28%2A%29+from+default+where+_id+%3D+%27query-testemployee10194.855617-0%27+and+address%5B1%5D%5B0%5D.country+%3D+%27United+States+of+America%27+and+gender+%3D+%27M%27&scan_consistency=REQUEST_PLUS body: headers: {'Authorization': 'Basic QWRtaW5pc3RyYXRvcjpwYXNzd29yZA=='} error: 404 reason: unknown b'{\n"requestID": "25fae3b1-cb32-45be-b58e-bb5594204a16",\n"errors": [{"code":4000,"msg":"Sargable index not sarged; pred=(((((((`default`.`_id`) = \\"query-testemployee10194.855617-0\\") and any `p` in pairs(`default`) satisfies (`p` = [\\"_id\\", \\"query-testemployee10194.855617-0\\"]) end) and (((((`default`.`address`)[1])[0]).`country`) = \\"United States of America\\")) and any `p` in pairs(`default`) satisfies (`p` = [\\"country\\", \\"United States of America\\"]) end) and ((`default`.`gender`) = \\"M\\")) and any `p` in pairs(`default`) satisfies (`p` = [\\"gender\\", \\"M\\"]) end), sarg_keys=[(distinct (pairs(`default`)))], error=Plan error: sarg.VisitAny: unexpected array id (0) for ANY expression any `p` in pairs(`default`) satisfies (`p` = [\\"_id\\", \\"query-testemployee10194.855617-0\\"]) end"}],\n"status": "fatal",\n"metrics": {"elapsedTime": "13.342505ms","executionTime": "13.243992ms","resultCount": 0,"resultSize": 0,"serviceLoad": 1,"errorCount": 1}\n}\n' auth: Administrator:password |
2024-05-13 11:41:53 | ERROR | MainProcess | test_thread | [tuq.run_cbq_query] PROBLEM WITH RESULT. TYPE IS: <class 'dict'> AND CONTENT IS: {'requestID': '25fae3b1-cb32-45be-b58e-bb5594204a16', 'errors': [{'code': 4000, 'msg': 'Sargable index not sarged; pred=(((((((`default`.`_id`) = "query-testemployee10194.855617-0") and any `p` in pairs(`default`) satisfies (`p` = ["_id", "query-testemployee10194.855617-0"]) end) and (((((`default`.`address`)[1])[0]).`country`) = "United States of America")) and any `p` in pairs(`default`) satisfies (`p` = ["country", "United States of America"]) end) and ((`default`.`gender`) = "M")) and any `p` in pairs(`default`) satisfies (`p` = ["gender", "M"]) end), sarg_keys=[(distinct (pairs(`default`)))], error=Plan error: sarg.VisitAny: unexpected array id (0) for ANY expression any `p` in pairs(`default`) satisfies (`p` = ["_id", "query-testemployee10194.855617-0"]) end'}], 'status': 'fatal', 'metrics': {'elapsedTime': '13.342505ms', 'executionTime': '13.243992ms', 'resultCount': 0, 'resultSize': 0, 'serviceLoad': 1, 'errorCount': 1}} |
|
|
To repro:
- create bucket default
- CREATE INDEX idx_country ON default(`address`[1][0].`country`) WHERE (`_id` = 'query-testemployee10194.855617-0');
- CREATE INDEX gix_USMales ON default(distinct (pairs(self))) WHERE (`_id` = 'query-testemployee10194.855617-0') and (`gender` = 'M') and (`address`[1][0].`country` = 'United States of America');
- EXPLAIN select count from default where _id = 'query-testemployee10194.855617-0' and address[1][0].country = 'United States of America' and gender = 'M';
From query.log:
2024-05-13T15:34:53.588-07:00 [ERROR] Sargable index not sarged: pred:<ud>(((((((`default`.`_id`) = "query-testemployee10194.855617-0") and any `p` in pairs(`default`) satisfies (`p` = ["_id", "query-testemployee10194.855617-0"]) end) and (((((`default`.`address`)[1])[0]).`country`) = "United States of America")) and any `p` in pairs(`default`) satisfies (`p` = ["country", "United States of America"]) end) and ((`default`.`gender`) = "M")) and any `p` in pairs(`default`) satisfies (`p` = ["gender", "M"]) end)</ud> sarg_keys:<ud>[(distinct (pairs(`default`)))]</ud> error:Plan error: sarg.VisitAny: unexpected array id (0) for ANY expression any `p` in pairs(`default`) satisfies (`p` = ["_id", "query-testemployee10194.855617-0"]) end |
Attachments
Issue Links
- is caused by
-
MB-61549 Multiple ANY clause on FLATTEN array index gives wrong results
- Closed
For Gerrit Dashboard: MB-61870 | ||||||
---|---|---|---|---|---|---|
# | Subject | Branch | Project | Status | CR | V |
209963,2 | MB-61870 Ensure array id is generated for internally generated prediates | trinity | query | Status: MERGED | +2 | +1 |
209966,2 | MB-61870 Ensure array id is generated for internally generated prediates | neo | query | Status: MERGED | +2 | +1 |