Agreed. Log a JIRA.
> On May 24, 2016, at 9:14 PM, Jinfeng Ni <[email protected]> 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 <[email protected]> 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 <[email protected]> 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
>>
