drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Vitalii Diravka (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DRILL-3510) Add ANSI_QUOTES option so that Drill's SQL Parser will recognize ANSI_SQL identifiers
Date Mon, 31 Jul 2017 12:13:00 GMT

     [ https://issues.apache.org/jira/browse/DRILL-3510?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Vitalii Diravka updated DRILL-3510:
-----------------------------------
    Description: 
*Added a possibility of changing characters for quoting identifiers by setting QUOTING_IDENTIFIERS
system/session option:*
{code}planner.parser.quoting_identifiers{code}
There are three modes for quoting identifiers:
1. "BACK TICKS" (default quoting mode):
Unicode U+0060; "GRAVE ACCENT"  {code}`{code}
The character is used for setting system/session option and for quoting identifiers;
2. "DOUBLE QUOTES"  Unicode U+0022; 'QUOTATION MARK' 
{code}"{code}
The character is used for setting system/session option and for quoting identifiers;
3. "BRACKETS" 
Unicode U+005B; 'LEFT SQUARE BRACKET' 
{code}[{code}
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'
{code}]{code}
Examples of using QUOTING_IDENTIFIERS option:
{code}
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)
{code}

*Other quoting characters are not acceptable while particular one is chosen:*
{code}
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)
{code}

*There is a possibility of setting QUOTING_IDENTIFIERS by using the "quoting_identifiers"
property in the jdbc connection URL string.* 
For example:
{code}
jdbc:drill:zk=local;quoting_identifiers=[
{code}


   


  was:
*Added a possibility of changing characters for quoting identifiers by setting QUOTING_IDENTIFIERS
system/session option:*
{code}planner.parser.quoting_identifiers{code}
There are three modes for quoting identifiers:
1. "BACK TICKS" (default quoting mode):
Unicode U+0060; "GRAVE ACCENT"  {code}`{code}
The character is used for setting system/session option and for quoting identifiers;
2. "DOUBLE QUOTES"  Unicode U+0022; 'QUOTATION MARK' 
{code}"{code}
The character is used for setting system/session option and for quoting identifiers;
3. "BRACKETS" 
Unicode U+005B; 'LEFT SQUARE BRACKET' 
{code}[{code}
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'
{code}]{code}
Examples of using QUOTING_IDENTIFIERS option:
{code}
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)
{code}

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

*There is a possibility of setting QUOTING_IDENTIFIERS by using the "quoting_identifiers"
property in the jdbc connection URL string.* 
For example:
{code}
jdbc:drill:zk=local;quoting_identifiers=[
{code}


   



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

> --------------------------------------------------------------------------------------
>
>                 Key: DRILL-3510
>                 URL: https://issues.apache.org/jira/browse/DRILL-3510
>             Project: Apache Drill
>          Issue Type: Improvement
>          Components: SQL Parser
>            Reporter: Jinfeng Ni
>            Assignee: Vitalii Diravka
>              Labels: doc-impacting, ready-to-commit
>             Fix For: 1.11.0
>
>         Attachments: DRILL-3510.patch, DRILL-3510.patch
>
>
> *Added a possibility of changing characters for quoting identifiers by setting QUOTING_IDENTIFIERS
system/session option:*
> {code}planner.parser.quoting_identifiers{code}
> There are three modes for quoting identifiers:
> 1. "BACK TICKS" (default quoting mode):
> Unicode U+0060; "GRAVE ACCENT"  {code}`{code}
> The character is used for setting system/session option and for quoting identifiers;
> 2. "DOUBLE QUOTES"  Unicode U+0022; 'QUOTATION MARK' 
> {code}"{code}
> The character is used for setting system/session option and for quoting identifiers;
> 3. "BRACKETS" 
> Unicode U+005B; 'LEFT SQUARE BRACKET' 
> {code}[{code}
> 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'
> {code}]{code}
> Examples of using QUOTING_IDENTIFIERS option:
> {code}
> 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)
> {code}
> *Other quoting characters are not acceptable while particular one is chosen:*
> {code}
> 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)
> {code}
> *There is a possibility of setting QUOTING_IDENTIFIERS by using the "quoting_identifiers"
property in the jdbc connection URL string.* 
> For example:
> {code}
> jdbc:drill:zk=local;quoting_identifiers=[
> {code}
>    



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

Mime
View raw message