[DRILL-3510] Add ANSI_QUOTES option so that Drill's SQL Parser will recognize ANSI_SQL identifiers

• Type: Improvement
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.11.0
• Component/s: SQL Parser
• Labels: doc-impacting ready-to-commit
• Assignee: Vitalii Diravka
• Reporter: Jinfeng Ni
• Reviewer: Sudheesh Katkam
• Created: 17/Jul/15 18:31
• Updated: 31/Jul/17 12:12
• Resolved: 03/Apr/17 05:19

Description

Added a possibility of changing characters for quoting identifiers by setting QUOTING_IDENTIFIERS system/session option:

planner.parser.quoting_identifiers

There are three modes for quoting identifiers:
1. "BACK TICKS" (default quoting mode):
Unicode U+0060; "GRAVE ACCENT"

`

The character is used for setting system/session option and for quoting identifiers;
2. "DOUBLE QUOTES" Unicode U+0022; 'QUOTATION MARK'

"

The character is used for setting system/session option and for quoting identifiers;
3. "BRACKETS"
Unicode U+005B; 'LEFT SQUARE BRACKET'

[

The character is used for setting system/session option and for quoting identifiers as left quote character. The right quote character for quoting identifiers with this mode is Unicode U+005D; 'RIGHT SQUARE BRACKET'

]

Examples of using QUOTING_IDENTIFIERS option:

0: jdbc:drill:zk=local> select * from sys.options where name = 'planner.parser.quoting_identifiers';
+-------------------------------------+---------+---------+----------+----------+-------------+-----------+------------+
|                name                 |  kind   |  type   |  status  | num_val  | string_val  | bool_val  | float_val  |
+-------------------------------------+---------+---------+----------+----------+-------------+-----------+------------+
| planner.parser.quoting_identifiers  | STRING  | SYSTEM  | DEFAULT  | null     | `           | null      | null       |
+-------------------------------------+---------+---------+----------+----------+-------------+-----------+------------+
1 row selected (0.189 seconds)
0: jdbc:drill:zk=local> select `employee_id`, `full_name` from cp.`employee.json` limit 1;
+--------------+---------------+
| employee_id  |   full_name   |
+--------------+---------------+
| 1            | Sheri Nowmer  |
+--------------+---------------+
1 row selected (0.148 seconds)
0: jdbc:drill:zk=local> ALTER SESSION SET planner.parser.quoting_identifiers = '"';
+-------+----------------------------------------------+
|  ok   |                   summary                    |
+-------+----------------------------------------------+
| true  | planner.parser.quoting_identifiers updated.  |
+-------+----------------------------------------------+
1 row selected (0.107 seconds)
0: jdbc:drill:zk=local> select "employee_id", "full_name" from cp."employee.json" limit 1;
+--------------+---------------+
| employee_id  |   full_name   |
+--------------+---------------+
| 1            | Sheri Nowmer  |
+--------------+---------------+
1 row selected (0.129 seconds)
0: jdbc:drill:zk=local> ALTER SESSION SET planner.parser.quoting_identifiers = '[';
+-------+----------------------------------------------+
|  ok   |                   summary                    |
+-------+----------------------------------------------+
| true  | planner.parser.quoting_identifiers updated.  |
+-------+----------------------------------------------+
1 row selected (0.102 seconds)
0: jdbc:drill:zk=local> select [employee_id], [full_name] from cp.[employee.json] limit 1;
+--------------+---------------+
| employee_id  |   full_name   |
+--------------+---------------+
| 1            | Sheri Nowmer  |
+--------------+---------------+
1 row selected (0.14 seconds)
0: jdbc:drill:zk=local> ALTER SESSION SET planner.parser.quoting_identifiers = '`';
+-------+----------------------------------------------+
|  ok   |                   summary                    |
+-------+----------------------------------------------+
| true  | planner.parser.quoting_identifiers updated.  |
+-------+----------------------------------------------+
1 row selected (0.1 seconds)
0: jdbc:drill:zk=local> select `employee_id`, `full_name` from cp.`employee.json` limit 1;
+--------------+---------------+
| employee_id  |   full_name   |
+--------------+---------------+
| 1            | Sheri Nowmer  |
+--------------+---------------+
1 row selected (0.139 seconds)

Other quoting characters are not acceptable while particular one is chosen:

0: jdbc:drill:zk=local> ALTER SESSION SET planner.parser.quoting_identifiers = '"';
+-------+----------------------------------------------+
|  ok   |                   summary                    |
+-------+----------------------------------------------+
| true  | planner.parser.quoting_identifiers updated.  |
+-------+----------------------------------------------+
1 row selected (0.561 seconds)
0: jdbc:drill:zk=local> select `employee_id`, `full_name` from cp.`employee.json` limit 1;
Error: PARSE ERROR: Lexical error at line 1, column 8.  Encountered: "`" (96), after : ""

SQL Query select `employee_id`, `full_name` from cp.`employee.json` limit 1
       ^
[Error Id: 9bfcb6b7-7d9d-46d7-8ea0-78d1d88b5c3b on vitalii-pc:31010] (state=,code=0)

There is a possibility of setting QUOTING_IDENTIFIERS by using the "quoting_identifiers" property in the jdbc connection URL string.
For example:

jdbc:drill:zk=local;quoting_identifiers=[

Issue Links

• depends upon: DRILL-3542 Rebase Drill on Calcite 1.4.0 release
• depends upon: CALCITE-803 Add a MYSQL_ANSI option for Lex options
• is depended upon by: DRILL-3153 DatabaseMetaData.getIdentifierQuoteString() returns (standard) double-quote, not Drill's back quote
• relates to: DRILL-5402 ServerMeta should be updated during the session, if the last has been changed.
• links to: GitHub Pull Request #520