[BUG] Get "mismatched input 'BETWEEN' expecting {<EOF>, ';'}" warning when running a SQL query with BETWEEN function #1115
Description
Describe the bug
If we run a SQL query that contains the BETWEEN function having the response format set to jdbc then, in the opensearch log, appears a warning message, although the execution was successful:
POST /_plugins/_sql?format=jdbc
{
"query" : "SELECT `kubernetes.pod.name` as Name, 100 - AVG(kubernetes.volume.fs.used.pct) as Backup_free_space FROM *metricbeat*ccad* WHERE kubernetes.namespace = 'ccad-gch-cadm-analytics-os' AND event.module = 'kubernetes' AND metricset.name = 'volume' AND kubernetes.pod.name LIKE '%-opensearch-data-%' AND kubernetes.volume.name = 'backup-volume' AND @timestamp BETWEEN '2022-11-29T07:00:00Z' AND '2022-11-29T07:15:00Z' GROUP BY 1"
}
{
"schema": [
{
"name": "Name",
"alias": "Name",
"type": "double"
},
{
"name": "Backup_free_space",
"alias": "Backup_free_space",
"type": "double"
}
],
"total": 2,
"datarows": [
[
"gch-cadm-os-opensearch-data-0",
99.986
],
[
"gch-cadm-os-opensearch-data-1",
99.986
]
],
"size": 2,
"status": 200
}
[2022-11-29T10:00:02,311][INFO ][o.o.s.l.p.RestSqlAction ] [gch-cadm-os-opensearch-masters-0] [3bbb13c6-af65-407e-8bf4-b8f3c9f12718] Incoming request /_plugins/_sql?format=jdbc&pretty=true: ( SELECT identifier AS Name, number - AVG(identifier) AS Backup_free_space FROM * metricbeat*ccad* WHERE identifier = 'string_literal' AND identifier = 'string_literal' AND identifier = 'string_literal' AND identifier LIKE 'string_literal' AND identifier = 'string_literal' AND @timestamp BETWEEN 'string_literal' AND 'string_literal' GROUP BY number )
[2022-11-29T10:00:02,353][WARN ][stderr ] [gch-cadm-os-opensearch-masters-0] line 1:354 mismatched input 'BETWEEN' expecting {<EOF>, ';'}
Note: The warning message does not appear in case the SQL query is executed using json format.
To Reproduce
Prerequisites:
- Have an index with a field for which the BETWEEN function can be used. For example:
PUT /car?pretty
{
"mappings":{
"properties": {
"model" : { "type" : "text" },
"year": {"type" : "integer"}
}
}
}
- Add several documents to that index with different values for the field for which the BETWEEN function is applied. For example:
POST car/_doc/1
{
"model": "Porshe",
"year": 1972
}
POST car/_doc/2
{
"model": "Porshe",
"year": 1973
}
POST car/_doc/3
{
"model": "Porshe",
"year": 1974
}
POST car/_doc/4
{
"model": "Porshe",
"year": 1975
}
POST car/_doc/5
{
"model": "Porshe",
"year": 1976
}
Steps to reproduce the behavior:
- Go to Opensearch Dashboards Dev Tools
- Execute the following SQL query:
POST /_plugins/_sql?format=jdbc
{
"query" : "SELECT `year` AS CarYear FROM car WHERE `year` BETWEEN 1972 AND 1975"
}
- The response is correctly returned:
{
"schema": [{
"name": "year",
"alias": "CarYear",
"type": "integer"
}],
"total": 4,
"datarows": [
[1972],
[1973],
[1974],
[1975]
],
"size": 4,
"status": 200
}
- Go to opensearch log and see the warning message:
[2022-11-29T12:54:51,997][INFO ][o.o.s.l.p.RestSqlAction ] [gch-cadm-os-opensearch-masters-1] [d33d5d6c-897f-43ab-ada3-b630634b93fb] Incoming request /_plugins/_sql?format=jdbc&pretty=true: ( SELECT identifier AS CarYear FROM table WHERE identifier BETWEEN number AND number )
[2022-11-29T12:54:52,024][WARN ][stderr ] [gch-cadm-os-opensearch-masters-1] line 1:47 mismatched input 'BETWEEN' expecting {<EOF>, ';'}
Expected behavior
The warning message should not occur as the execution was successful.
What can be done to not have this warning present in the opensearch log?
OpenSearch Version
2.4.0