Status

Current state: "Under Discussion"

Discussion thread: here (https://mail-archives.apache.org/mod_mbox/flink-dev/202110.mbox/%3cCAKQOUBh1DoW3pgJLLbqpbiqwfF2wFBAj1Gj-z0Feq=_bvEiUZg@mail.gmail.com%3e)

JIRA: here (<- link to https://issues.apache.org/jira/browse/FLINK-XXXX)

Released: <Flink Version>

Please keep the discussion on the mailing list rather than commenting on the wiki (wiki discussions get unwieldy fast).

Motivation

Flink SQL is a great tool however sometimes it takes time lots of time to understand what is the problem with the current query.

E.g. here from error message below it is not clear that the reason is wrong first word.

Flink SQL> ELECT 1;
[ERROR] Could not execute SQL statement. Reason:
org.apache.calcite.runtime.CalciteException: Non-query expression encountered in illegal context

For larger queries it could be more time consuming to find out the root cause.

There is a number of corner cases when sql client submits a query instead of continuing editing with current parser behavior

e.g. there is a set of some simple queries which fail to execute via sql client

SELECT 'a;
';

SELECT 1; -- comment

SELECT 1; /* my comment;
end comment */

During typing a query in sql client it is easy to have a misprint, miss a quote, bracket and do not notice it, e.g. like here

     Flink SQL>  SELECT COUNT(1) as `field FROM (VALUES(1));

Proposed Changes

Parsing improvements

Parser should have a state in order to answer if a query ends ok or still need to close a quote, a bracket or a comment.

Information about what is missing could be shown in prompt e.g.

Flink SQL>  SELECT word, SUM(frequency) AS `count`  // Imagine back tick was missed here
     ;>                                 FROM (
     )>                                  VALUES ('Hello', 1), ('Ciao', 1), ('Hello', 2)
     )>                                 )
     ;>                                 AS WordTable(word, frequency)
     ;>                                 GROUP BY word;

Query could be submitted only when there is no such violations. And this will help to resolve issues with corner cases queries.

Syntax Highlighting

Having parser state it is also possible to use this info to highlight each part of the query differently by implementation of org.jline.reader.Highlighter interface [1].
e.g. there could be the next groups: keywords, single-quoted strings, string quoted with sql identifier, comments, hints, numbers, default.

Keywords could be everything from
Here probably it makes sense to have ability to support several color schemas as for dark and light terminal usually different colors suit.
There are some existing color schemas [2], [3], [4].

Completion

Currently not all available commands in completion candidates list
Use jline's aggregate completer to be able to apply different completers depending on context e.g.
SET and RESET could suggest key names as completion
SHOW: could be not only SHOW JAR, all options should be in completion candidates list.
ADD/REMOVE jar could suggest path with FileCompleter [5].

Error Message

Currently sql client unwinds exception till end. However error description for some cases does not clarify the reason e.g. missed first letter of SELECT in example in motivation section

Within the trace there is org.apache.calcite.sql.parser.SqlParseException containing line number and line column info of parser's cursor when it was failed.
This info could be used to improve the message.

Other jline features that could be applied

line numbers + editing query consuming more than screen space
autopairing[6] for single quote, sql identifier quote, square and round brackets could be turn on/off via property
(requires update at least till 3.13.3)
Enabling/Disabling description for completion candidates via property. An example how it looks like is here [7]

Summary

After this FLIP finishes, the sql client will have the options.

Supported options in sql client

Option	Default	Type	Description
`sql-client.color-schema`	`'no'`	`Enum`	Determine the color schema for syntax highlighting. Will contain names of available color schemas
`sql-client.autopairing`	`true`	`boolean`	Determine whether autopairing is enabled or not.
`sql-client.completion-description`	`false`	`boolean`	Determine whether description for completion candidates is enabled or not.
`sql-client.prompt.show-hint`	`false`	`boolean`	Determine whether prompt shows hints about current input issue.
`sql-client.prompt.show-line-numbers`	`false`	`boolean`	Determine whether prompt shows line numbers for multiline query.

Supported prompt values in sql client

Prompt	Meaning
;>	Waiting for the next line of multi-line query, waiting for completion of query with semicolon (;)
'>	Waiting for the next line, waiting for a completion of string that began with a single quote (')
`>	Waiting for the next line, waiting for a completion of string that began with a back tick (`)
*\>	Waiting for the next line, waiting for completion of a multi-line comment that began with (/*)
)>	Waiting for the next line, waiting for completion of a string that began with a round bracket (
]>	Waiting for the next line, waiting for completion of a string that began with a square bracket [
extra )>	There is an extra round bracket ), that is not opened with (
extra ]>	There is an extra square bracket ], that is not opened with [
extra *\>	There is an extra closing of multi-line comment \, that is not opened with \