Opened Calcite-1256. https://issues.apache.org/jira/browse/CALCITE-1256
On Tue, May 24, 2016 at 10:05 PM, Julian Hyde <[email protected]> wrote: > 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 >>> >
