Array Index order changed in 6.0.0 CE when upgraded from 4.5.1 CE!

SQL++
,
1

Thank you in advance for the help.
We were using 4.5.1 CE. We have a few document like below.
{
" refId":“123”
“entries”: [1234567890, 1234567891, 1234567892]
“_class”: “some.package.Class”,
“creationTime”: 1234567890,
}
I had a index to get the documents in the ascending order of the entries.
CREATE INDEX entryIndex ON entry-data(distinct (array (i) for i in entries end),refId,creationTime) WHERE (_class = “com.package.Class”) USING GSI;

My Query is
select refId, creationTime from entry-data b USE INDEX (entryIndex) where _class = ‘com.package.Class’
and any i in dates satisfies i >= 1234567890 and i <= 1234567892 end and any id in dates satisfies id >= 1234567891
and id >= 1234567896 end offset 0 limit 100

In 4.5.1 we were getting the result in the index order. So we didn’t specifyied order by.
So when we upgrade to 6.0.0, the same index and query returning the result without any order. Result is getting shuffled. Is there any implementation logic changed in 6.0.0.

When I query with order by entries, It returns correct order always. Should we use this? Please suggest
Tried query:
select refId, creationTime from entry-data b USE INDEX (entryIndex) where _class = ‘com.package.Class’
and any i in dates satisfies i >= 1234567890 and i <= 1234567892 end and any id in dates satisfies id >= 1234567891
and id >= 1234567896 end order by entries offset 0 limit 100

2

If query doesn’t have ORDER BY, order is not guaranteed. If it is giving the order you want in 4.5.1 by luck.

Checkout EXPLAIN.

ARRAY index is on entries , but your queries using ANY … IN dates … END. The index will never qualify in both 4.5.1, 6.0.0. It might used different index.

Assume it is typo and query is ANY … IN entries … END.
You have two independent ANY clauses i.e. both must be true (ARRAY must contain 2 values that follows in your ranges) to qualify query.

In 4.5.1 Array index binding variable must match with ANY clause binding variable (in this case i) to use ARRAY index. So one any clause might used for array index scan and other one might applied post index scan. So happened to use index order.

In 6.0.0 binding variable of query can be any thing from index. Both ANY clauses qualify so it does 2 independent index scan does intersect scan. so results can be any order based on scans.

If query expects specific order it must specify ORDER BY.

3

Well explained @vsr1 . It was a typo. Supposed to be entries instead of dates.
Thank you for the answer. I am using order by entries.