opensearch-project / opensearch-project/sql
[BUG] SELECT in SQL returns 0 rather than expected data
Open
Nobody has claimed this yet.
bug
SQL
- Dominant language
- Java
- Stars
- 176
- Forks
- 229
- Avg merge
- 2d 21h
- Merged PRs (30d)
- 43
Description
Describe the bug
A SELECT statement using = returns 0 when it should return a number. A SELECT for the same data using IN returns the correct data
To Reproduce
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"
}
}
}
}
Next add the following document
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"
}
Expected behavior
Both of the following SQL queries should now return the same thing, the number 12345
select n from the_test where n = 12345;
select n from the_test where n in (12345);
Plugins
Please list all plugins currently enabled.
Screenshots
If applicable, add screenshots to help explain your problem.
Host/Environment (please complete the following information):
- ES 7.10.2
- opendistro-sql 1.13.2.0
Additional context
Add any other context about the problem here.
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
No source file or test is named. Start by reproducing the reported SELECT behavior against the supplied mapping, then compare the query using = with the equivalent IN query. Done means the equality form returns the expected number rather than 0, with a regression test covering the case.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- java, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 25/100