[
https://issues.apache.org/jira/browse/DRILL-3435?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14610800#comment-14610800
]
Kristine Hahn edited comment on DRILL-3435 at 7/1/15 6:40 PM:
--------------------------------------------------------------
[~bbevens] How about we change:
FROM:
In other table references, aliases are optional.
TO:
Aliases might be required for [querying nested
JSON|https://drill.apache.org/docs/json-data-model/#analyzing-json]. Aliases
are definitely required to resolve ambiguous references, such as using the name
"user" to query the Drill profiles. Drill treats "user" as a function in this
case, and the returns unexpected results. If you use a table alias, Drill
treats "user" as a column identifier, and the query returns expected results.
[~bbevens] Is there somewhere in your profile querying doc where the bit about
using "user" should be mentioned?
was (Author: krishahn):
[~bbevens] How about we change:
FROM:
In other table references, aliases are optional.
TO:
Aliases might be required for [querying nested
JSON|https://drill.apache.org/docs/json-data-model/#analyzing-json]. Aliases
are definitely required to resolve ambiguous references, such as using the name
"user" to query the Drill profiles. Drill treats "user as a function in this
case, and the returns unexpected results. If you use a table alias, Drill
treats "user" as a column identifier, and the query returns expected results.
[~bbevens] Is there somewhere in your profile querying doc where the bit about
using "user" should be mentioned?
> Some reserved-keywords require table aliasing
> ---------------------------------------------
>
> Key: DRILL-3435
> URL: https://issues.apache.org/jira/browse/DRILL-3435
> Project: Apache Drill
> Issue Type: Bug
> Components: Documentation
> Affects Versions: 1.0.0
> Reporter: Andy Pernsteiner
> Assignee: Bridget Bevens
> Priority: Minor
> Labels: documentation
>
> Not only does drill have a number of reserved keywords that require
> backticking (``), there also appear to be some reserved words that require
> extra care, using table aliases to be able to perform queries. One that
> we've found so far is 'user' . EG, consider the following scenario:
> bq. /usr/bin/sqlline -u jdbc:drill: -n root
> then:
> {code} select user from
> `profiles/2aa32e9e-bdae-8949-8461-c14dafe63ee0.sys.drill` ;
> +-------+
> | user |
> +-------+
> | root |
> +-------+
> {code}
> But the actual file in question has the 'user' as a different user:
> {code} cat 2aa32e9e-bdae-8949-8461-c14dafe63ee0.sys.drill|egrep -o
> 'user\":\"[a-z]+\"'
> user":"apernsteiner"
> {code}
> The workaround is to alias the table (t) and prefix the 'user' column in the
> resultset w/ the table alias :
> {code}
> 0: jdbc:drill:> select t.`user` from
> `profiles/2aa32e9e-bdae-8949-8461-c14dafe63ee0.sys.drill` t ;
> +---------+
> | user |
> +---------+
> | apernsteiner |
> +---------+
> {code}
> @jinfeng gave the following explanation on the user@ list:
> {quote}
> 'user' is a SQL reserved word.
> When it's used alone, it is a system function, just like CURRENT_USER. See
> http://calcite.incubator.apache.org/docs/reference.html (System functions
> section).
> When 'user' is qualified with a table alias, it becomes a column
> identifier.
> {quote}
> The drill documentation @ https://drill.apache.org/docs/reserved-keywords/
> merely says to use backticks (``), not to do any table aliasing. For those
> who have columns named 'user', this may be misleading...
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
