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:10: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.

*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:
Currently Drill's SQL parser uses backtick as identifier quotes, the same as what MySQL does.
However, this is different from ANSI SQL specification, where double quote is used as identifier
quotes.  
MySQL has an option "ANSI_QUOTES", which could be switched on/off by user:
{code}
SET sql_mode='ANSI_QUOTES';
{code}


*Changes that were made in this fix:*
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.
> *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