Druid: [Unified Web Console] EXPLAIN PLAN FOR throws org.apache.calcite.sql.parser.SqlParseException

[sashidhar]

Affected Version

0.15.0

Description

1. A simple SQL statement like "EXPLAIN PLAN FOR SELECT 1 + 1;" from the unified web console is resulting in a org.apache.calcite.sql.parser.SqlParseException.

Unknown exception / Encountered "( EXPLAIN" at line 1, column 15. Was expecting one of: <IDENTIFIER> ... <QUOTED_IDENTIFIER> ... <BACK_QUOTED_IDENTIFIER> ... <BRACKET_QUOTED_IDENTIFIER> ... <UNICODE_QUOTED_IDENTIFIER> ... "LATERAL" ... "(" ... "(" "WITH" ... "(" "+" ... "(" "-" ... "(" "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" ... "(" <IDENTIFIER> ... "(" <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" ... "(" "(" ... "(" "SELECT" ... "(" "VALUES" ... "(" "TABLE" ... "UNNEST" ... "TABLE" ... / org.apache.calcite.sql.parser.SqlParseException

While issuing a curl for the same query works and outputs a plan.

curl -X POST \
   -H "Content-Type:application/json" \
   -d \
'{
  "query":"EXPLAIN PLAN FOR SELECT 1+1"
}' \
'http://localhost:8888/druid/v2/sql'

[{"PLAN":"BindableValues(tuples=[[{ 2 }]])\n"}]

=> Inspecting the request payload from the Network activity console, the query issued is

"SELECT * FROM (EXPLAIN PLAN FOR SELECT 1 + 1) LIMIT 5000"

Looks like the console is modifying the query with the prefix "SELECT * FROM" in this case which is resulting in the issue.

curl of the modified query "SELECT * FROM (EXPLAIN PLAN FOR SELECT 1 + 1) LIMIT 5000"fails as expected.

1. Same behaviour is observed for WITH clause.

WITH wikipedia as (SELECT 1 + 1) FOR SELECT * FROM wikipedia LIMIT 1; throws org.apache.calcite.sql.parser.SqlParseException for the same reasons mentioned in 1.

[vogievetsky]

Yeah the query UI tries to wrap your query, that does not work well for the queries you posted.

You can disable that like so:

Make sure your run button says Run as is

[vogievetsky]

We should make the query wrapper smarter

[vogievetsky]

We have made the query wrapper smarter as part of Druid 0.16.0