Details
-
Bug
-
Resolution: Won't Fix
-
Major
-
Cheshire-Cat
-
Untriaged
-
Unknown
Description
Build : 7.0.0-1824
On the employee dataset (sample doc below), executing a query using fts leads to an error, while the same query with gsi yields results.
Sample doc
{
|
"name": "Kory Palmer",
|
"salary": 75891.68,
|
"type": "emp",
|
"_id": "emp10000001",
|
"mutated": 0,
|
"manages": {
|
"team_size": 6,
|
"reports": [
|
"Hedda Wright",
|
"Callia Robinson Sr.",
|
"Trista Baker III",
|
"Joby Allen",
|
"Basha Foster",
|
"Salina Allen"
|
]
|
},
|
"emp_id": "10000001",
|
"dept": "Engineering",
|
"email": "kory@mcdiabetes.com",
|
"join_date": "2015-08-13T09:30:00",
|
"languages_known": [
|
"Quechua",
|
"Portuguese",
|
"Africans"
|
],
|
"is_manager": true
|
}
|
FTS index
type mapping = emp
fields in index = dept (keyword analyzer), name (keyword analyzer), salary, join_date
definition attached
GSI secondary index : CREATE INDEX `idx1` ON `default`(`type`,`dept`,`name`,`salary`)
Query for FTS
select name, salary, join_date from default use index(using fts) where (name = "Kory Palmer" and dept="Engineering" ) and `type`="emp" and (salary > 60000 and salary < 90000) and join_date="2015-08-13T09:30:00" order by name limit 5
|
|
|
[
|
{
|
"code": 5000,
|
"msg": "n1fty: SargableFlex Sargable - cause: unsupported datetime format (not RFC3339)",
|
"query": "select name, salary, join_date from default use index(using fts) where (name = \"Kory Palmer\" and dept=\"Engineering\" ) and `type`=\"emp\" and (salary > 60000 and salary < 90000) and join_date=\"2015-08-13T09:30:00\" order by name limit 5"
|
}
|
]
|
Query for GSI
select name, salary, join_date from default where (name = "Kory Palmer" and dept="Engineering" ) and `type`="emp" and (salary > 60000 and salary < 90000) and join_date="2015-08-13T09:30:00" order by name limit 5
|
|
|
[
|
{
|
"join_date": "2015-08-13T09:30:00",
|
"name": "Kory Palmer",
|
"salary": 75891.68
|
}
|
]
|