Andy Pernsteiner created DRILL-3435:
---------------------------------------
Summary: 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
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:
/usr/bin/sqlline -u jdbc:drill: -n root
then:
select user from `profiles/2aa32e9e-bdae-8949-8461-c14dafe63ee0.sys.drill` ;
+-------+
| user |
+-------+
| root |
+-------+
But the actual file in question has the 'user' as a different user:
cat 2aa32e9e-bdae-8949-8461-c14dafe63ee0.sys.drill|egrep -o 'user\":\"[a-z]+\"'
user":"apernsteiner"
The workaround is to alias the table (t) and prefix the 'user' column in the
resultset w/ the table alias :
0: jdbc:drill:> select t.`user` from
`profiles/2aa32e9e-bdae-8949-8461-c14dafe63ee0.sys.drill` t ;
+---------+
| user |
+---------+
| apernsteiner |
+---------+
@jinfeng gave the following explanation on the user@ list:
<explanation>
'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.
</explanation>
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)
