Details
-
Bug
-
Status: Closed
-
Critical
-
Resolution: Fixed
-
6.0.4
-
None
-
Untriaged
-
Centos 64-bit
-
-
No
Description
Build 6.5.0-4890
query
SELECT MIN(d.age) from default d USE INDEX (`travel_history_name_age`) UNNEST d.`travel_history` AS t where t = 'India' |
returns 45
query
SELECT MIN(d.age) from default d USE INDEX (`#primary`) UNNEST d.`travel_history` AS t where t = 'India' |
|
returns 28
Indexes definitions:
CREATE PRIMARY INDEX `#primary` ON `default` |
|
CREATE INDEX `name_age_travel_history` ON `default`(`name`,`age`,(all (array `t` for `t` in `travel_history` end))) |
|
CREATE INDEX `name_travel_history_age` ON `default`(`name`,(all (array `t` for `t` in `travel_history` end)),`age`) |
|
CREATE INDEX `travel_history_name_age` ON `default`((all (array `t` for `t` in `travel_history` end)),`name`,`age`) |
default bucket data is attached
logs are attached
Attachments
Issue Links
- Clones
-
MB-37078 Query: Different results for different indexes in use.
-
- Closed
-
Comment from Sitaram in
MB-37078, is it an issue in Alice?Sitaram.VemulapalliSitaram Vemulapalli added a comment - 01/Dec/19 9:42 PM - edited
Plan looks right. Index aggregates giving wrong results. made aa.sql
SELECT MIN(d.age) from default d USE INDEX (`travel_history_name_age`) UNNEST d.`travel_history` AS t where t = 'India';
2nd Range is full span. If add d.name IS NOT MISSING it gives right results.
Scan seems fine. it look like this is side affect of MIN optimization. If i change age DESC it gives right results.
The following gives right results.
SELECT d.age from default d USE INDEX (`travel_history_name_age`) UNNEST d.`travel_history` AS t where t = 'India' order by d.age limit 1;
*It gives right result in 6.0.3*