Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-3510

Add ANSI_QUOTES option so that Drill's SQL Parser will recognize ANSI_SQL identifiers

    XMLWordPrintableJSON

Details

    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=[

      Attachments

        1. DRILL-3510.patch
          5 kB
          Jacques Nadeau
        2. DRILL-3510.patch
          5 kB
          Jacques Nadeau

        Issue Links

          depends upon

          Bug - A problem which impairs or prevents the functions of the product. DRILL-3542 Rebase Drill on Calcite 1.4.0 release

          • Major - Major loss of function.
          • Closed

          Improvement - An improvement or enhancement to an existing feature or task. CALCITE-803 Add a MYSQL_ANSI option for Lex options

          • Major - Major loss of function.
          • Closed
          is depended upon by

          Bug - A problem which impairs or prevents the functions of the product. DRILL-3153 DatabaseMetaData.getIdentifierQuoteString() returns (standard) double-quote, not Drill's back quote

          • Major - Major loss of function.
          • Resolved
          relates to

          Improvement - An improvement or enhancement to an existing feature or task. DRILL-5402 ServerMeta should be updated during the session, if the last has been changed.

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open
          links to

          Web Link GitHub Pull Request #520

          Activity

            People

              vitalii Vitalii Diravka
              jni Jinfeng Ni
              Sudheesh Katkam Sudheesh Katkam
              Votes:
              1 Vote for this issue
              Watchers:
              10 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: