You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Jinfeng Ni <ji...@gmail.com> on 2016/05/24 22:50:41 UTC
Simple quoted identifier : column name vs implicit function call ?
This question is raised in Drill's user list [1]. The code logic is
actually in Calcite.
Basically, SQL standard allows couple of reserved identifiers used for
system function call, such as USER, CURRENT_USER, CURRENT_TIME etc.
If someone wants to use those reserved names as column names, he has
to use quoted identifier.
However, looks like Calcite always interprets those simple quoted
identifiers as a system function call, in stead of column name. Such
behavior is different from Postgres/Oracle, which will interpret a
quoted identifier as column name, instead of system function call).
I would argue that Postgres/Oracle's behavior makes more sense. If
someone quotes an reserved word, the expectation is he can use those
reserved words just like a regular identifier.
If this sounds reasonable, I'll open a JIRA.
-------------------------------------------------------------
Oracle:
create table mytemp("user" int);
insert into mytemp values(100);
SQL> select user from mytemp;
USER
------------------------------
user_id
SQL> select "user" from mytemp;
user
----------
100
SQL> select mytemp."user" from mytemp;
user
----------
100
Postgres:
select user from (values(100)) as T("user");
current_user
--------------
user_id
(1 row)
mydb=# select "user" from (values(100)) as T("user");
user
------
100
(1 row)
mydb=# select T."user" from (values(100)) as T("user");
user
------
100
(1 row)
Calcite:
select user from (values(100)) as T("user");
-----
user_id
select "user" from (values(100)) as T("user");
-----
user_id
select T."user" from (values(100)) as T("user");
+------------+
| user |
+------------+
| 100 |
+------------+
[1] http://mail-archives.apache.org/mod_mbox/drill-user/201605.mbox/%3CCAKOFcwrR4m6_EZvKU0ijh5CeSBa-YvZxomBFH6dPmthj7g%2BmWg%40mail.gmail.com%3E
Re: Simple quoted identifier : column name vs implicit function call ?
Posted by Jinfeng Ni <ji...@gmail.com>.
Opened Calcite-1256.
https://issues.apache.org/jira/browse/CALCITE-1256
On Tue, May 24, 2016 at 10:05 PM, Julian Hyde <jh...@apache.org> wrote:
> Agreed. Log a JIRA.
>
>> On May 24, 2016, at 9:14 PM, Jinfeng Ni <ji...@gmail.com> wrote:
>>
>> You are right that Oracle is case-sensitive. Tried with column named
>> as "USER". The quoted identifier "user" would raise "invalid
>> identifier" error, "USER" would match the column. This seems to show
>> Oracle tries to match quoted identifier against columns in the table,
>> not match for the system function.
>>
>> Oracle:
>> create table mytemp ("USER" int);
>>
>> select "user" from mytemp;
>> select "user" from mytemp
>> *
>> ERROR at line 1:
>> ORA-00904: "user": invalid identifier
>>
>> select "USER" from mytemp;
>>
>> USER
>> ----------
>> 100
>>
>> For Calcite, I used Calcite master branch and run the query in
>> Calcite's sqlline.
>>
>> select "user" from (values(100)) as T("USER");
>>
>> +------------+
>> |
>> +-------------
>> | sa
>> +-----
>>
>> I'm going to open a JIRA.
>>
>> On Tue, May 24, 2016 at 8:54 PM, Julian Hyde <jh...@apache.org> wrote:
>>> Sounds reasonable.
>>>
>>> However, can you run the Oracle tests again to make sure. Oracle is case-sensitive when identifiers are quoted, so it would not consider a “user” column to be a match for the “USER” function. Try instead with a column called “USER” and see if you get the same results.
>>>
>>> By a similar argument, I deduce that you are trying Calcite-in-Drill (case insensitive) rather than raw Calcite (case sensitive).
>>>
>>> Then yes, open a JIRA.
>>>
>>> Julian
>>>
>>>
>>>> On May 24, 2016, at 3:50 PM, Jinfeng Ni <ji...@gmail.com> wrote:
>>>>
>>>> This question is raised in Drill's user list [1]. The code logic is
>>>> actually in Calcite.
>>>>
>>>> Basically, SQL standard allows couple of reserved identifiers used for
>>>> system function call, such as USER, CURRENT_USER, CURRENT_TIME etc.
>>>> If someone wants to use those reserved names as column names, he has
>>>> to use quoted identifier.
>>>>
>>>> However, looks like Calcite always interprets those simple quoted
>>>> identifiers as a system function call, in stead of column name. Such
>>>> behavior is different from Postgres/Oracle, which will interpret a
>>>> quoted identifier as column name, instead of system function call).
>>>>
>>>> I would argue that Postgres/Oracle's behavior makes more sense. If
>>>> someone quotes an reserved word, the expectation is he can use those
>>>> reserved words just like a regular identifier.
>>>>
>>>> If this sounds reasonable, I'll open a JIRA.
>>>>
>>>> -------------------------------------------------------------
>>>>
>>>> Oracle:
>>>>
>>>> create table mytemp("user" int);
>>>> insert into mytemp values(100);
>>>>
>>>> SQL> select user from mytemp;
>>>>
>>>> USER
>>>> ------------------------------
>>>> user_id
>>>>
>>>> SQL> select "user" from mytemp;
>>>>
>>>> user
>>>> ----------
>>>> 100
>>>>
>>>> SQL> select mytemp."user" from mytemp;
>>>>
>>>> user
>>>> ----------
>>>> 100
>>>>
>>>>
>>>> Postgres:
>>>> select user from (values(100)) as T("user");
>>>> current_user
>>>> --------------
>>>> user_id
>>>> (1 row)
>>>>
>>>> mydb=# select "user" from (values(100)) as T("user");
>>>> user
>>>> ------
>>>> 100
>>>> (1 row)
>>>>
>>>> mydb=# select T."user" from (values(100)) as T("user");
>>>> user
>>>> ------
>>>> 100
>>>> (1 row)
>>>>
>>>>
>>>> Calcite:
>>>> select user from (values(100)) as T("user");
>>>> -----
>>>> user_id
>>>>
>>>> select "user" from (values(100)) as T("user");
>>>> -----
>>>> user_id
>>>>
>>>> select T."user" from (values(100)) as T("user");
>>>> +------------+
>>>> | user |
>>>> +------------+
>>>> | 100 |
>>>> +------------+
>>>>
>>>> [1] http://mail-archives.apache.org/mod_mbox/drill-user/201605.mbox/%3CCAKOFcwrR4m6_EZvKU0ijh5CeSBa-YvZxomBFH6dPmthj7g%2BmWg%40mail.gmail.com%3E
>>>
>
Re: Simple quoted identifier : column name vs implicit function call ?
Posted by Julian Hyde <jh...@apache.org>.
Agreed. Log a JIRA.
> On May 24, 2016, at 9:14 PM, Jinfeng Ni <ji...@gmail.com> wrote:
>
> You are right that Oracle is case-sensitive. Tried with column named
> as "USER". The quoted identifier "user" would raise "invalid
> identifier" error, "USER" would match the column. This seems to show
> Oracle tries to match quoted identifier against columns in the table,
> not match for the system function.
>
> Oracle:
> create table mytemp ("USER" int);
>
> select "user" from mytemp;
> select "user" from mytemp
> *
> ERROR at line 1:
> ORA-00904: "user": invalid identifier
>
> select "USER" from mytemp;
>
> USER
> ----------
> 100
>
> For Calcite, I used Calcite master branch and run the query in
> Calcite's sqlline.
>
> select "user" from (values(100)) as T("USER");
>
> +------------+
> |
> +-------------
> | sa
> +-----
>
> I'm going to open a JIRA.
>
> On Tue, May 24, 2016 at 8:54 PM, Julian Hyde <jh...@apache.org> wrote:
>> Sounds reasonable.
>>
>> However, can you run the Oracle tests again to make sure. Oracle is case-sensitive when identifiers are quoted, so it would not consider a “user” column to be a match for the “USER” function. Try instead with a column called “USER” and see if you get the same results.
>>
>> By a similar argument, I deduce that you are trying Calcite-in-Drill (case insensitive) rather than raw Calcite (case sensitive).
>>
>> Then yes, open a JIRA.
>>
>> Julian
>>
>>
>>> On May 24, 2016, at 3:50 PM, Jinfeng Ni <ji...@gmail.com> wrote:
>>>
>>> This question is raised in Drill's user list [1]. The code logic is
>>> actually in Calcite.
>>>
>>> Basically, SQL standard allows couple of reserved identifiers used for
>>> system function call, such as USER, CURRENT_USER, CURRENT_TIME etc.
>>> If someone wants to use those reserved names as column names, he has
>>> to use quoted identifier.
>>>
>>> However, looks like Calcite always interprets those simple quoted
>>> identifiers as a system function call, in stead of column name. Such
>>> behavior is different from Postgres/Oracle, which will interpret a
>>> quoted identifier as column name, instead of system function call).
>>>
>>> I would argue that Postgres/Oracle's behavior makes more sense. If
>>> someone quotes an reserved word, the expectation is he can use those
>>> reserved words just like a regular identifier.
>>>
>>> If this sounds reasonable, I'll open a JIRA.
>>>
>>> -------------------------------------------------------------
>>>
>>> Oracle:
>>>
>>> create table mytemp("user" int);
>>> insert into mytemp values(100);
>>>
>>> SQL> select user from mytemp;
>>>
>>> USER
>>> ------------------------------
>>> user_id
>>>
>>> SQL> select "user" from mytemp;
>>>
>>> user
>>> ----------
>>> 100
>>>
>>> SQL> select mytemp."user" from mytemp;
>>>
>>> user
>>> ----------
>>> 100
>>>
>>>
>>> Postgres:
>>> select user from (values(100)) as T("user");
>>> current_user
>>> --------------
>>> user_id
>>> (1 row)
>>>
>>> mydb=# select "user" from (values(100)) as T("user");
>>> user
>>> ------
>>> 100
>>> (1 row)
>>>
>>> mydb=# select T."user" from (values(100)) as T("user");
>>> user
>>> ------
>>> 100
>>> (1 row)
>>>
>>>
>>> Calcite:
>>> select user from (values(100)) as T("user");
>>> -----
>>> user_id
>>>
>>> select "user" from (values(100)) as T("user");
>>> -----
>>> user_id
>>>
>>> select T."user" from (values(100)) as T("user");
>>> +------------+
>>> | user |
>>> +------------+
>>> | 100 |
>>> +------------+
>>>
>>> [1] http://mail-archives.apache.org/mod_mbox/drill-user/201605.mbox/%3CCAKOFcwrR4m6_EZvKU0ijh5CeSBa-YvZxomBFH6dPmthj7g%2BmWg%40mail.gmail.com%3E
>>
Re: Simple quoted identifier : column name vs implicit function call ?
Posted by Jinfeng Ni <ji...@gmail.com>.
You are right that Oracle is case-sensitive. Tried with column named
as "USER". The quoted identifier "user" would raise "invalid
identifier" error, "USER" would match the column. This seems to show
Oracle tries to match quoted identifier against columns in the table,
not match for the system function.
Oracle:
create table mytemp ("USER" int);
select "user" from mytemp;
select "user" from mytemp
*
ERROR at line 1:
ORA-00904: "user": invalid identifier
select "USER" from mytemp;
USER
----------
100
For Calcite, I used Calcite master branch and run the query in
Calcite's sqlline.
select "user" from (values(100)) as T("USER");
+------------+
|
+-------------
| sa
+-----
I'm going to open a JIRA.
On Tue, May 24, 2016 at 8:54 PM, Julian Hyde <jh...@apache.org> wrote:
> Sounds reasonable.
>
> However, can you run the Oracle tests again to make sure. Oracle is case-sensitive when identifiers are quoted, so it would not consider a “user” column to be a match for the “USER” function. Try instead with a column called “USER” and see if you get the same results.
>
> By a similar argument, I deduce that you are trying Calcite-in-Drill (case insensitive) rather than raw Calcite (case sensitive).
>
> Then yes, open a JIRA.
>
> Julian
>
>
>> On May 24, 2016, at 3:50 PM, Jinfeng Ni <ji...@gmail.com> wrote:
>>
>> This question is raised in Drill's user list [1]. The code logic is
>> actually in Calcite.
>>
>> Basically, SQL standard allows couple of reserved identifiers used for
>> system function call, such as USER, CURRENT_USER, CURRENT_TIME etc.
>> If someone wants to use those reserved names as column names, he has
>> to use quoted identifier.
>>
>> However, looks like Calcite always interprets those simple quoted
>> identifiers as a system function call, in stead of column name. Such
>> behavior is different from Postgres/Oracle, which will interpret a
>> quoted identifier as column name, instead of system function call).
>>
>> I would argue that Postgres/Oracle's behavior makes more sense. If
>> someone quotes an reserved word, the expectation is he can use those
>> reserved words just like a regular identifier.
>>
>> If this sounds reasonable, I'll open a JIRA.
>>
>> -------------------------------------------------------------
>>
>> Oracle:
>>
>> create table mytemp("user" int);
>> insert into mytemp values(100);
>>
>> SQL> select user from mytemp;
>>
>> USER
>> ------------------------------
>> user_id
>>
>> SQL> select "user" from mytemp;
>>
>> user
>> ----------
>> 100
>>
>> SQL> select mytemp."user" from mytemp;
>>
>> user
>> ----------
>> 100
>>
>>
>> Postgres:
>> select user from (values(100)) as T("user");
>> current_user
>> --------------
>> user_id
>> (1 row)
>>
>> mydb=# select "user" from (values(100)) as T("user");
>> user
>> ------
>> 100
>> (1 row)
>>
>> mydb=# select T."user" from (values(100)) as T("user");
>> user
>> ------
>> 100
>> (1 row)
>>
>>
>> Calcite:
>> select user from (values(100)) as T("user");
>> -----
>> user_id
>>
>> select "user" from (values(100)) as T("user");
>> -----
>> user_id
>>
>> select T."user" from (values(100)) as T("user");
>> +------------+
>> | user |
>> +------------+
>> | 100 |
>> +------------+
>>
>> [1] http://mail-archives.apache.org/mod_mbox/drill-user/201605.mbox/%3CCAKOFcwrR4m6_EZvKU0ijh5CeSBa-YvZxomBFH6dPmthj7g%2BmWg%40mail.gmail.com%3E
>
Re: Simple quoted identifier : column name vs implicit function call ?
Posted by Julian Hyde <jh...@apache.org>.
Sounds reasonable.
However, can you run the Oracle tests again to make sure. Oracle is case-sensitive when identifiers are quoted, so it would not consider a “user” column to be a match for the “USER” function. Try instead with a column called “USER” and see if you get the same results.
By a similar argument, I deduce that you are trying Calcite-in-Drill (case insensitive) rather than raw Calcite (case sensitive).
Then yes, open a JIRA.
Julian
> On May 24, 2016, at 3:50 PM, Jinfeng Ni <ji...@gmail.com> wrote:
>
> This question is raised in Drill's user list [1]. The code logic is
> actually in Calcite.
>
> Basically, SQL standard allows couple of reserved identifiers used for
> system function call, such as USER, CURRENT_USER, CURRENT_TIME etc.
> If someone wants to use those reserved names as column names, he has
> to use quoted identifier.
>
> However, looks like Calcite always interprets those simple quoted
> identifiers as a system function call, in stead of column name. Such
> behavior is different from Postgres/Oracle, which will interpret a
> quoted identifier as column name, instead of system function call).
>
> I would argue that Postgres/Oracle's behavior makes more sense. If
> someone quotes an reserved word, the expectation is he can use those
> reserved words just like a regular identifier.
>
> If this sounds reasonable, I'll open a JIRA.
>
> -------------------------------------------------------------
>
> Oracle:
>
> create table mytemp("user" int);
> insert into mytemp values(100);
>
> SQL> select user from mytemp;
>
> USER
> ------------------------------
> user_id
>
> SQL> select "user" from mytemp;
>
> user
> ----------
> 100
>
> SQL> select mytemp."user" from mytemp;
>
> user
> ----------
> 100
>
>
> Postgres:
> select user from (values(100)) as T("user");
> current_user
> --------------
> user_id
> (1 row)
>
> mydb=# select "user" from (values(100)) as T("user");
> user
> ------
> 100
> (1 row)
>
> mydb=# select T."user" from (values(100)) as T("user");
> user
> ------
> 100
> (1 row)
>
>
> Calcite:
> select user from (values(100)) as T("user");
> -----
> user_id
>
> select "user" from (values(100)) as T("user");
> -----
> user_id
>
> select T."user" from (values(100)) as T("user");
> +------------+
> | user |
> +------------+
> | 100 |
> +------------+
>
> [1] http://mail-archives.apache.org/mod_mbox/drill-user/201605.mbox/%3CCAKOFcwrR4m6_EZvKU0ijh5CeSBa-YvZxomBFH6dPmthj7g%2BmWg%40mail.gmail.com%3E