One of our BI team reported an issue to me where an SQL query he was running seemed to be returning incorrect data (they know the data well enough that it just count be right)…we figured out a work around, but there’s clearly something odd happening, and more importantly we don’t know what other queries might be affected where the issue hasn’t been spotted.
I’ve added an anonymised index mapping and a sample document below, but the following two queries should surely give the same result, shouldn’t they?
select n from the_test where n = 12345;
select n from the_test where n in (12345);
The first return 0, the second 12345 as I’d expect?!?
Is this a bug, or expected behaviour?
Probably worth a mention, this is running on 7.10.2
PUT the_test
{
"settings": {
"index.number_of_shards": 1,
"index.number_of_replicas": 1
},
"mappings" : {
"properties" : {
"a" : {
"type" : "keyword"
},
"b" : {
"type" : "boolean"
},
"c" : {
"properties" : {
"ca" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"cb" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"cc" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
}
}
},
"d" : {
"type" : "keyword"
},
"e" : {
"type" : "keyword"
},
"f" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"g" : {
"type" : "date"
},
"h" : {
"type" : "keyword"
},
"i" : {
"type" : "keyword"
},
"j" : {
"type" : "boolean"
},
"k" : {
"type" : "integer"
},
"l" : {
"properties" : {
"la" : {
"type" : "long"
},
"lb" : {
"type" : "keyword"
},
"lc" : {
"type" : "integer"
}
}
},
"m" : {
"type" : "date"
},
"n" : {
"type" : "long"
},
"o" : {
"type" : "keyword"
},
"p" : {
"type" : "long"
},
"q" : {
"properties" : {
"qa" : {
"properties" : {
"qaa" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"qab" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"qac" : {
"type" : "float"
},
"qad" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
}
}
},
"qb" : {
"properties" : {
"qba" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"qbb" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
}
}
},
"qc" : {
"properties" : {
"qca" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"qcb" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"qcc" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"qcd" : {
"properties" : {
"qcda" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"qcdb" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"qcdc" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"qcdd" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
}
}
},
"qce" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"qcf" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
}
}
},
"qd" : {
"properties" : {
"qda" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"qdb" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"qdc" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"qdd" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
}
}
},
"qe" : {
"properties" : {
"qea" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"qeb" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"qec" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
}
}
},
"qf" : {
"properties" : {
"qfa" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"qfb" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"qfc" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"qfd" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"qfe" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
}
}
},
"qg" : {
"type" : "object"
},
"qh" : {
"properties" : {
"qha" : {
"type" : "date"
},
"qhb" : {
"properties" : {
"qhba" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"qhbb" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"qhbc" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
}
}
}
}
},
"qi" : {
"type" : "object"
},
"qj" : {
"properties" : {
"qja" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"qjb" : {
"properties" : {
"qjba" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"qjbb" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"qjbc" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"qjbd" : {
"type" : "boolean"
}
}
}
}
},
"qk" : {
"properties" : {
"qka" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"qkb" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"qkc" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
}
}
},
"ql" : {
"type" : "object"
}
}
},
"r" : {
"type" : "keyword"
},
"s" : {
"properties" : {
"sa" : {
"properties" : {
"saa" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
}
}
}
}
},
"t" : {
"type" : "keyword"
},
"u" : {
"type" : "integer"
},
"v" : {
"type" : "long"
},
"w" : {
"type" : "keyword"
},
"x" : {
"properties" : {
"xa" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"xb" : {
"type" : "date"
},
"xc" : {
"type" : "date"
},
"xd" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
}
}
},
"y" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"z" : {
"type" : "text"
},
"aa" : {
"type" : "text"
},
"bb" : {
"type" : "date"
},
"cc" : {
"type" : "integer"
}
}
}
}
POST /the_test/_doc/999
{
"aa" : "",
"v" : "0",
"z" : "",
"o" : "PROFILE",
"n" : "12345",
"i" : "999",
"j" : false,
"g" : "2020-10-11T03:37:42.855Z",
"u" : 1,
"b" : true,
"r" : "AB12345=T",
"p" : "0",
"d" : "",
"h" : "TEST",
"x" : [
{
"xa" : "0",
"xc" : "2020-11-11T03:37:42.855Z",
"xb" : "2020-11-11T03:37:42.855Z",
"xd" : "Creating metadata record"
}
],
"t" : "e9a4c0a9-5ccc-427b-9e2c-bf505794191e",
"e" : "ABC",
"k" : 0,
"a" : "SYNC",
"c" : { },
"m" : "2020-11-11T03:37:42.855Z",
"bb" : 1605065862855,
"l" : {
"lb" : "",
"lc" : 567,
"la" : "987654"
},
"w" : "COMPLETE"
}
Copyright ©2021, Oracle and/or its affiliates
