drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Andy Pernsteiner (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DRILL-3435) Some reserved-keywords require table aliasing
Date Wed, 01 Jul 2015 17:06:11 GMT

    [ https://issues.apache.org/jira/browse/DRILL-3435?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14610646#comment-14610646
] 

Andy Pernsteiner commented on DRILL-3435:
-----------------------------------------

Maybe I'm missing something here, but this is a relatively specific case, and in fact, my
queries against other columns in this JSON file do NOT require any table aliasing, because
this is not a nested JSON doc (at least, not the fields I'm going after)  EG:

{code}
: jdbc:drill:> select id from profiles limit 3;
+-----------------------------------------------------------------+
|                               id                                |
+-----------------------------------------------------------------+
| {"part1":"3069735710943609483","part2":"-1130875713383276112"}  |
| {"part1":"3069511572705759702","part2":"-8164107516750810817"}  |
| {"part1":"3057076082656734793","part2":"-4260511657052163769"}  |
+-----------------------------------------------------------------+
3 rows selected (2.839 seconds)

{code}

So that works, however running w/ `user`  does not:


{code}

0: jdbc:drill:> select `user` from profiles limit 3;
+---------------+
|     user      |
+---------------+
| apernsteiner  |
| apernsteiner  |
| apernsteiner  |
+---------------+
3 rows selected (2.141 seconds)

{code}

Also, I'm not sure what you mean by your statement : 

{quote}
..'user' is recognized as a function in this case, and the query fails..
{quote}

What do you mean by the query fails?  It doesn't actually fail, it returns data which would
be confusing to someone who ran a query against another column in this file/table, where they
got the actual data, and not the result of a special function.



> 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)

Mime
View raw message