Failed to parse query due to offending symbol [.15]

Plugins SQL
1

We have ES 7.4 and OD 1.4
When ever we try a basic query that includes a index with a period we get the following error.
We have not been able to get past and have not found documentation that would help us.

POST _opendistro/_sql
{
“query”: “”“SELECT *
from 44699_pcf-2020.15
LIMIT 50
“””
}

{
“error”: {
“reason”: “Invalid SQL query”,
“details”: “Failed to parse query due to offending symbol [.15] at: ‘SELECT * \r\n from 44699_pcf-2020.15’ <— HERE… More details: Expecting tokens in {, ‘AS’, ‘CROSS’, ‘GROUP’, ‘HAVING’, ‘INNER’, ‘JOIN’, ‘LEFT’, ‘LIMIT’, ‘NATURAL’, ‘ORDER’, ‘RIGHT’, ‘WHERE’, ‘MISSING’, ‘AVG’, ‘COUNT’, ‘MAX’, ‘MIN’, ‘SUM’, ‘SUBSTRING’, ‘TRIM’, ‘YEAR’, ‘FULL’, ‘ABS’, ‘ACOS’, ‘ADD’, ‘ASCII’, ‘ASIN’, ‘ATAN’, ‘ATAN2’, ‘CBRT’, ‘CEIL’, ‘CONCAT’, ‘CONCAT_WS’, ‘COS’, ‘COSH’, ‘COT’, ‘CURDATE’, ‘DATE’, ‘DATE_FORMAT’, ‘DAYOFMONTH’, ‘DEGREES’, ‘E’, ‘EXP’, ‘EXPM1’, ‘FLOOR’, ‘IF’, ‘IFNULL’, ‘ISNULL’, ‘LENGTH’, ‘LN’, ‘LOCATE’, ‘LOG’, ‘LOG10’, ‘LOG2’, ‘LOWER’, ‘LTRIM’, ‘MAKETIME’, ‘MODULUS’, ‘MONTH’, ‘MONTHNAME’, ‘MULTIPLY’, ‘NOW’, ‘PI’, ‘POW’, ‘POWER’, ‘RADIANS’, ‘RAND’, ‘REPLACE’, ‘RINT’, ‘ROUND’, ‘RTRIM’, ‘SIGN’, ‘SIGNUM’, ‘SIN’, ‘SINH’, ‘SQRT’, ‘SUBTRACT’, ‘TAN’, ‘TIMESTAMP’, ‘UPPER’, ‘D’, ‘T’, ‘TS’, ‘DATE_HISTOGRAM’, ‘DAY_OF_MONTH’, ‘DAY_OF_YEAR’, ‘DAY_OF_WEEK’, ‘EXCLUDE’, ‘EXTENDED_STATS’, ‘FIELD’, ‘FILTER’, ‘GEO_BOUNDING_BOX’, ‘GEO_CELL’, ‘GEO_DISTANCE’, ‘GEO_DISTANCE_RANGE’, ‘GEO_INTERSECTS’, ‘GEO_POLYGON’, ‘HISTOGRAM’, ‘HOUR_OF_DAY’, ‘INCLUDE’, ‘IN_TERMS’, ‘MATCHPHRASE’, ‘MATCH_PHRASE’, ‘MATCHQUERY’, ‘MATCH_QUERY’, ‘MINUTE_OF_DAY’, ‘MINUTE_OF_HOUR’, ‘MONTH_OF_YEAR’, ‘MULTIMATCH’, ‘MULTI_MATCH’, ‘NESTED’, ‘PERCENTILES’, ‘REGEXP_QUERY’, ‘REVERSE_NESTED’, ‘QUERY’, ‘RANGE’, ‘SCORE’, ‘SECOND_OF_MINUTE’, ‘STATS’, ‘TERM’, ‘TERMS’, ‘TOPHITS’, ‘WEEK_OF_YEAR’, ‘WILDCARDQUERY’, ‘WILDCARD_QUERY’, ‘.’, ‘,’, STRING_LITERAL, DOT_ID, ID, REVERSE_QUOTE_ID, ADDDATE, ADDTIME, GREATEST, LEAST}”,
“type”: “SyntaxAnalysisException”
},
“status”: 400
}

2

@nico007 Try wrapping the index name within single quotes (e.g. SELECT * from ‘44699_pcf-2020.15’
LIMIT 50. I don’t know if that will work but I believe I’ve seen that syntax work in some other software when an identifier includes a special character.

3

Thanks for the reply, this is what we get with the single quote
{
“error”: {
“reason”: “Invalid SQL query”,
“details”: “Field [‘44699_pcf-2020.15’] cannot be found or used here.”,
“type”: “SemanticAnalysisException”
},
“status”: 400
}

4

Hi @nico007, thanks for reporting! Please try to disable semantic analyzer: Error when an index name contains a dot midstream · Issue #322 · opendistro-for-elasticsearch/sql · GitHub

5

Daichen,
Appreciate the response, still missing something :neutral_face:

POST _opendistro/_sql
{
“query”: “SELECT * FROM 44699_pcf-2020.17”
}
#######
{
“error”: {
“reason”: “Invalid SQL query”,
“details”: “Right side of the expression [2020.17] is expected to be an identifier”,
“type”: “ParserException”
},
“status”: 400
}

6

Any update on this issue?

having issue with the character as ’ in the query.

Failed to parse query due to offending symbol [k] at: 'SELECT * FROM orders WHERE nested(orderLines, orderLines.data like ‘*sample’k’ <— HERE… More details: Expecting tokens in {‘SELECT’, ‘(’}

7

Hi @sandeepjn - may I see your whole query and the whole response?