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

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: