Details
-
Bug
-
Resolution: Fixed
-
Major
-
7.1.0
-
Untriaged
-
1
-
Unknown
Description
query is from Keshav Murthy
EXPLAIN SELECT ticker, month, AVG(st[2]) AS avgopen, AVG(st[3]) AS avgclose |
FROM default AS s |
UNNEST s AS st
|
WHERE (META(s).id BETWEEN "IIBM:2018-01" AND "IIBM:2021-12" OR |
META(s).id BETWEEN "ORCL:2018-01" AND "ORCL:2021-12") |
GROUP BY SUBSTR(META(s).id, 0, 4) AS ticker, |
SUBSTR(META(s).id, 0, 12) AS month; |
|
Above query should able to cover via
DROP INDEX default.ix1; |
CREATE INDEX ix1 ON default(ALL ARRAY FLATTEN_KEYS(META().id, st) FOR st IN self END); |
|
If covered it can use Index aggregation too.
Use cases for ANY, UNNEST
DROP INDEX default.ix1; |
CREATE INDEX ix1 ON default(ALL ARRAY FLATTEN_KEYS(META().id, st) FOR st IN self END); |
UPSERT INTO default VALUES("IIBM:2021-01:709", [ [ "2021-01-04", 120.382408, 117.629066, 120.315491, 118.489487, 5417443 ], [ "2021-01-05", 121.108986, 119.13002, 119.512428, 120.592735, 6395872 ], [ "2021-01-06", 126.080307, 121.147224, 121.319313, 123.60421, 8322708 ], [ "2021-01-07", 124.722755, 122.619499, 124.32122, 123.317398, 4714740 ], [ "2021-01-08", 123.632889, 121.39579, 122.915871, 122.877632, 4891305 ], [ "2021-01-11", 123.977058, 122.045891, 122.323135, 122.92543, 5859587 ], [ "2021-01-12", 124.13958, 122.313576, 123.413002, 123.527725, 3921663 ], [ "2021-01-13", 124.043976, 120.898659, 123.47036, 121.338432, 8030874 ] ]); |
|
EXPLAIN SELECT META(s).id, st
|
FROM default AS s |
UNNEST s AS st
|
WHERE META(s).id BETWEEN "IIBM:2021-01" AND "IIBM:2021-12"; |
|
EXPLAIN SELECT META(s).id
|
FROM default AS s |
WHERE ANY st IN s SATISFIES META(s).id BETWEEN "IIBM:2021-01" AND "IIBM:2021-12" END; |
|
EXPLAIN SELECT ticker, month, AVG(st[2]) AS avgopen, AVG(st[3]) AS avgclose |
FROM default AS s |
UNNEST s AS st
|
WHERE META(s).id BETWEEN "IIBM:2021-01" AND "IIBM:2021-12" |
GROUP BY SUBSTR(META(s).id, 0, 4) AS ticker, |
SUBSTR(META(s).id, 0, 12) AS month; |
DROP INDEX default.ix2; |
UPSERT INTO default VALUES("k01",{"a2": [1, 5, 15, 25]}); |
CREATE INDEX ix2 ON default(ALL ARRAY FLATTEN_KEYS(v) FOR v IN a2 END); |
|
EXPLAIN SELECT v
|
FROM default AS d |
UNNEST d.a2 AS v
|
WHERE v > 10; |
|
EXPLAIN SELECT META(d).id
|
FROM default AS d |
WHERE ANY v IN d.a2 SATISFIES v > 10 END; |
|
Indexing whole binding variable and query uses unnest alias as identifier not field. This required special handling.
DROP INDEX default.ix3; |
UPSERT INTO default VALUES("k02",[1, 5, 15, 25]); |
CREATE INDEX ix3 ON default(ALL ARRAY FLATTEN_KEYS(v) FOR v IN self END); |
|
EXPLAIN SELECT v
|
FROM default AS d |
UNNEST d AS v
|
WHERE v > 10; |
|
EXPLAIN SELECT META(d).id
|
FROM default AS d |
WHERE ANY v IN d SATISFIES v > 10 END; |
|
In addition to previous case, In this we are using whole document (self in index + UNNEST d) for unnest (not field. As whole document can't be covered via index. Handle special case for UNNEST.
FYI: examples refers FLATTEN_KEYS(), this applicable for non FLATTEN_KEYS()