Details
-
Bug
-
Status: Closed
-
Critical
-
Resolution: Fixed
-
6.5.0
-
Untriaged
-
Centos 64-bit
-
-
Yes
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
- is cloned by
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Component/s | indexing [ 15110 ] |
| Fix Version/s | Mad-Hatter [ 15037 ] |
| Attachment | aa.sql [ 78410 ] |
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
| Component/s | secondary-index [ 11211 ] | |
| Component/s | query [ 10718 ] |
| Assignee | Kamini Jagtiani [ kamini.jagtiani ] | Jeelan Poola [ jeelan.poola ] |
| Is this a Regression? | Unknown [ 10452 ] | Yes [ 10450 ] |
Enterprise Edition 7.0.0 build 1075: Reproducible
Enterprise Edition 6.5.0 build 4405 (Beta 2, I think): Also reproducible.
| Assignee | Jeelan Poola [ jeelan.poola ] | Amit Kulkarni [ amit.kulkarni ] |
| Priority | Major [ 3 ] | Critical [ 2 ] |
No reversion, It will cause performance regression. My guess the use case described only one might be issue, please fix that.
If any uses cases found later we address at that time.
| Attachment | ExplainAlice.txt [ 78446 ] | |
| Attachment | ExplainMH.txt [ 78447 ] |
Both plans are right respective releases. In MH (MB-30422) there is full range of key i.e all values, not in pre-MH
i.e
{"inclusive":0}low, high nil
range is equal only when
func (this *Range2) EqualRange() bool { |
return (this.Inclusion == datastore.BOTH) && (this.Low != nil && this.High != nil && (this.Low == this.High || this.Low.EquivalentTo(this.High))) |
}
|
MIN optimization can be done only when MIN expression is equal to index key and left side of index keys have equal ranges, then MIN can return first VALUE (i.e NOT MISSING, NOT NULL). Otherwise it needs to follow regular MIN calculation (NO special optimization).
This issue got exposed from build 6.5.0-4156 onwards (when the fix for MB-29605 was merged). It was kind of hidden until then. Nonetheless, this is serious issue and must be fixed in MH. GSI patches are under review.
| Labels | approved-for-mad-hatter |
| Due Date | 05/Dec/19 |
| Link | This issue blocks MB-36676 [ MB-36676 ] |
Build couchbase-server-6.5.0-4924 contains indexing commit 7d0d066 with commit message:
MB-37078: [Backport to mad-hatter] Fix equality check for nil in fast count
Build couchbase-server-6.5.0-4924 contains indexing commit f1cd70d with commit message:
MB-37078: [Backport to mad-hatter] Add functional tests for min/max aggregate optimisations
Build couchbase-server-6.5.0-4924 contains indexing commit d937caa with commit message:
MB-37078: [Backport to mad-hatter] Fix equality filter check for nil values of high and low
| VERIFICATION STEPS |
QE Testing:
Various scenarios around MIN aggregate queries (with index on multiple composite keys and multiple different filter values specified for each composite key). |
|
| Resolution | Fixed [ 1 ] | |
| Status | Open [ 1 ] | Resolved [ 5 ] |
Build couchbase-server-7.0.0-1110 contains indexing commit e1ad363 with commit message:
MB-37078: Add functional tests for min/max aggregate optimisations
Build couchbase-server-7.0.0-1110 contains indexing commit 08e81e9 with commit message:
MB-37078 Fix equality check for nil in fast count
Build couchbase-server-7.0.0-1110 contains indexing commit 88c256b with commit message:
MB-37078: Fix equality filter check for nil values of high and low
| Status | Resolved [ 5 ] | Closed [ 6 ] |
Evgeny Makarenko Is this a regression? Can you please figure it out?