sql
sql copied to clipboard
opensearch-project
[BUG] Advanced functions causes SyntaxAnalysisException
Describe the bug I am using AWS OpenDistro 7.10 with SQL queries. Using advanced functions in select clause gives SyntaxAnalysisException whereas simple functions works.
Example Working example:
-
SELECT DATE_FORMAT(data.timestamp, 'yyyy-MM-dd HH:mm:00.000') data__timestamp1m FROM data* AS data WHERE (data.timestamp between '2021-07-19T08:40:00Z' AND '2021-07-20T13:00:00Z') GROUP BY DATE_FORMAT(data.timestamp, 'yyyy-MM-dd HH:mm:00.000') ORDER BY DATE_FORMAT(data.timestamp, 'yyyy-MM-dd HH:mm:00.000') DESC LIMIT 1000
Failing example:
-
SELECT CASE WHEN MINUTE(data.timestamp) % 2 = 1 THEN DATE_FORMAT(ADDDATE(data.timestamp, INTERVAL 1 minute), 'yyyy-MM-dd HH:mm:00.000') ELSE DATE_FORMAT(data.timestamp, 'yyyy-MM-dd HH:mm:00.000') END data__timestamp2m FROM data* AS data WHERE (telemetry_yuri.timestamp between '2021-07-19T08:40:00Z' AND '2021-07-20T13:00:00Z') GROUP BY data__timestamp2m ORDER BY data__timestamp2m DESC LIMIT 1000
To Reproduce Steps to reproduce the behavior:
- check that a simple query give results without errors
- check that an advances query give a SyntaxAnalysisException
Expected behavior Advanced query to work or having a workaround
Host/Environment (please complete the following information):
- OS: aws linux 2
- Version 7.10.2
Additional context
Our use case is we want to group data into buckets of some period of time, on 1 min for example it works easily but on other we need more complex logic. It worked when I replace the between in the where clause to a >= and <= but this creates painless scripts which slows down the query.
