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
>
