Sql LEFT function not working

We are running on druid 0.16.

Looking at the sql docs: https://druid.apache.org/docs/latest/querying/sql.html

SELECT SUBSTR(message_id,1,4), message_id FROM messages where __time > ‘2020-02-18 14:50:00’;

This works

Then following the docs we see this function:

LEFT(expr, [length]) - ``Returns the leftmost length characters from expr.

SELECT LEFT(message_id,4) FROM messages where __time > ‘2020-02-18 14:50:00’;

THIS FAILS

error:

Unknown exception / Encountered “LEFT” at line 2, column 8.
Was expecting one of:
“STREAM” …
“DISTINCT” …
“ALL” …
“*” …
“+” …
“-” …
“NOT” …
“EXISTS” …
<UNSIGNED_INTEGER_LITERAL> …
<DECIMAL_NUMERIC_LITERAL> …
<APPROX_NUMERIC_LITERAL> …
<BINARY_STRING_LITERAL> …
<PREFIXED_STRING_LITERAL> …
<QUOTED_STRING> …
<UNICODE_STRING_LITERAL> …
“TRUE” …
“FALSE” …
“UNKNOWN” …
“NULL” …
<LBRACE_D> …
<LBRACE_T> …
<LBRACE_TS> …
“DATE” …
“TIME” …
“TIMESTAMP” …
“INTERVAL” …
“?” …
“CAST” …
“EXTRACT” …
“POSITION” …
“CONVERT” …
“TRANSLATE” …
“OVERLAY” …
“FLOOR” …
“CEIL” …
“CEILING” …
“SUBSTRING” …
“TRIM” …
“CLASSIFIER” …
“MATCH_NUMBER” …
“RUNNING” …
“PREV” …
“NEXT” …
<LBRACE_FN> …
“MULTISET” …
“ARRAY” …
“PERIOD” …
“SPECIFIC” …

<QUOTED_IDENTIFIER> …
<BACK_QUOTED_IDENTIFIER> …
<BRACKET_QUOTED_IDENTIFIER> …
<UNICODE_QUOTED_IDENTIFIER> …
“ABS” …
“AVG” …
“CARDINALITY” …
“CHAR_LENGTH” …
“CHARACTER_LENGTH” …
“COALESCE” …
“COLLECT” …
“COVAR_POP” …
“COVAR_SAMP” …
“CUME_DIST” …
“COUNT” …
“CURRENT_DATE” …
“CURRENT_TIME” …
“CURRENT_TIMESTAMP” …
“DENSE_RANK” …
“ELEMENT” …
“EXP” …
“FIRST_VALUE” …
“FUSION” …
“GROUPING” …
“HOUR” …
“LAG” …
“LEAD” …
“LAST_VALUE” …
“LN” …
“LOCALTIME” …
“LOCALTIMESTAMP” …
“LOWER” …
“MAX” …
“MIN” …
“MINUTE” …
“MOD” …
“MONTH” …
“NTH_VALUE” …
“NTILE” …
“NULLIF” …
“OCTET_LENGTH” …
“PERCENT_RANK” …
“POWER” …
“RANK” …
“REGR_SXX” …
“REGR_SYY” …
“ROW_NUMBER” …
“SECOND” …
“SQRT” …
“STDDEV_POP” …
“STDDEV_SAMP” …
“SUM” …
“UPPER” …
“TRUNCATE” …
“USER” …
“VAR_POP” …
“VAR_SAMP” …
“YEAR” …
“CURRENT_CATALOG” …
“CURRENT_DEFAULT_TRANSFORM_GROUP” …
“CURRENT_PATH” …
“CURRENT_ROLE” …
“CURRENT_SCHEMA” …
“CURRENT_USER” …
“SESSION_USER” …
“SYSTEM_USER” …
“NEW” …
“CASE” …
“CURRENT” …
“CURSOR” …
“ROW” …
“(” …
/ org.apache.calcite.sql.parser.SqlParseException

Hi Daniel,

This is a known issue in Druid 0.16 and Druid 0.15 which is coming from calcite parser. Upgrading calcite to 1.21 fixes this issue.

You can find more details here:
https://github.com/apache/druid/issues/8266

The latest druid 0.17 should work well as it uses calcite-1.21.

<calcite.version>1.21.0</calcite.version>
https://github.com/apache/druid/blob/master/pom.xml

Thanks and Regards,

Vaibhav