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