Rethink syntax extension

[wangkuiyi]

During our efforts to deploy SQLFlow in some real cases, we tried to extend the syntax of some SQL dialects, including MySQL and Hive. In these efforts, we learned an art:

Try reusing existing SQL reserved words in the extended syntax.

Why this art? Let us look at an example in #473. Because TRAIN isn't a reserved word, users could name a field by TRAIN, and this might confuse our parser. The solution is to either replace TRAIN by a reserved word, where we couldn't find one that expresses the meaning of "train", or, extend TRAIN into TO TRAIN, where, please be aware that TO is a reserved word.

It seems that other clause extensions after TO TRAIN, TO PREDICT, and TO EXPLAIN can use arbitrary words, as they are not going to be parsed by a SQL (dialect) parser, but the SQLFlow parser. However, it is not that simple. Consider that a table might have a field named label, and this field happens to be a label when we train a model. The SQL statement would look like

SELECT a, b, label FROM tbl
TO TRAIN Model
LABEL label

This would confuse the parser. Our current workaround is this. However, a complete solutions seems that we don't use OUTPUT to replace LABEL. OUTPUT is a SQL keyword, so users are not supposed to name a field by OUTPUT. (This might not be true always, but, at least, it seems that the probability of OUTPUT output is smaller than that of LABEL label.)

Please vote for the following syntax changes:

[wangkuiyi]

TRAIN to TO TRAIN, as marked in #473

[wangkuiyi]

PREDICT to TO PREDICT

[wangkuiyi]

ANALYSE to TO EXPLAIN. Both ANALYZE and EXPLAIN are SQL reserved words. It seems that EXPLAIN is more comprehensive when we do the model explanation. And the use of TO is to keep consistent style with TO TRAIN and TO PREDICT.

[wangkuiyi]

LABEL to OUTPUT, which is a SQL reserved word, or other SQL reserved word, which is listed in https://www.drupal.org/docs/develop/coding-standards/list-of-sql-reserved-words.

[wangkuiyi]

USING to USE, which is a SQL reserved word.

---

Updated: Thanks to @typhoonzero to point this out -- both USING and USE are SQL reserved words. So, it seems no need to change here.

[typhoonzero]

Seems USING is a reserved word: https://www.geeksforgeeks.org/sql-using-clause/

[shendiaomo]

LABEL to OUTPUT, which is a SQL reserved word, or other SQL reserved word, which is listed in https://www.drupal.org/docs/develop/coding-standards/list-of-sql-reserved-words.

It seems OUTPUT is only a SQL Server keyword: https://stackoverflow.com/questions/5817414/output-clause-in-mysql
If this is true, changing LABEL to OUTPUT may not improve the situation.

To leverage existing parsers of SQL dialects to avoid the problem, it seems that we should choose a keyword that is used by most of SQL dialects. As a counter example, MySQL will not utter a word when executing the following statement:

CREATE TABLE OUTPUT (OUTPUT int);

though OUTPUT is a reserved keyword.

Can we consider a preposition rather than nouns or verbs? For instance:

SELECT a, b, label FROM tbl TO TRAIN Model ON label;

This comes to me when I saw a paper using the same expression: Using trusted data to train deep networks on labels corrupted by severe noise

[weiguoz]

LABEL to OUTPUT, which is a SQL reserved word, or other SQL reserved word, which is listed in https://www.drupal.org/docs/develop/coding-standards/list-of-sql-reserved-words.

How about using the MATCH?

[tonyyang-svail]

@wangkuiyi How about LABEL => OUT.

All MySQL, MaxCompute, and Hive consider OUT as a reversed keyword.

1. MaxCompute reserved keyword list
2. Hive reserved keyword list
3. MySQL reserved keyword list

[tonyyang-svail]

Here are my thoughts on the following art:

Try reusing existing SQL reserved words in the extended syntax.

SQLFlow should not add any keyword to the SQL standard. For example, if SQLFlow adds TRAIN as a keyword, the user needs to rewrite select * from train; to select * from 'train';, which hurts the user experience.

[typhoonzero]

Thanks @tonyyang-svail, we should definitely use TO TRAIN to keep the user experience the same.