Details
-
Bug
-
Resolution: Fixed
-
Major
-
4.6.2, 5.0.0
-
5.0.0-2509
-
Untriaged
-
No
Description
create two indexes on int fields:
CREATE INDEX `idx1` ON `default`(`k1`)
CREATE INDEX `idx2` ON `default`(`k0`)
Document in default containing k1 and k0 integers:
{
|
"k0": 1, |
"k1": 2 |
}
|
Query issued:
explain SELECT 1 FROM default WHERE not (k0 != 234 AND k1 != 123);
Result of this query:
[
|
{
|
"code": 4000, |
"msg": "No index available on keyspace default that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online.", |
"query_from_user": "explain SELECT 1 FROM default WHERE not (k0 != 234 AND k1 != 123);" |
}
|
]
|
On translating above not(AND) to OR, following query uses the right unionscan of both indexes:
explain SELECT 1 FROM default WHERE (k0 = 234 OR k1 = 123);
This query uses right index: idx2
explain SELECT 1 FROM default WHERE NOT (k0 < 234 );
This query uses right index:idx1
explain SELECT 1 FROM default WHERE NOT (k1 > 123);
This query issues an error,requiring primary index:
explain SELECT 1 FROM default WHERE NOT (k0 < 234 AND k1 > 123);
Same is true if k0 is a boolean field.
Attachments
Issue Links
- relates to
-
MB-25763 [Backport MB-23733] - Port union scan enhancements to 4.6.4
- Closed