thanks for clarifying. It might be worth updating the docs to make it clear that special handling is required for a subset of the reserved words. Is the process to file a JIRA against the docs?
On Tue, Jun 30, 2015 at 5:09 PM, Jinfeng Ni <[email protected]> wrote: > Just checked SQL Standard 2011. Section 6.4 has the following: > > > <general value specification> ::= > <host parameter specification> > > | <SQL parameter reference> > | <dynamic parameter specification> > | <embedded variable specification> > | <current collation specification> > | CURRENT_CATALOG > | CURRENT_DEFAULT_TRANSFORM_GROUP > | CURRENT_PATH > | CURRENT_ROLE > | CURRENT_SCHEMA > | CURRENT_TRANSFORM_GROUP_FOR_TYPE <path-resolved user-defined type > name>* | CURRENT_USER > * | SESSION_USER > | SYSTEM_USER* | USER > * | VALUE > > > > On Tue, Jun 30, 2015 at 2:06 PM, Jinfeng Ni <[email protected]> wrote: > > > '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. That probably explains why you would see the different > results. > > > > In postgres, using 'user' alone would also produce the same behavior on > > Drill. > > > > > > mydb=# select * from dept2; > > dept_id | dept_name > > ---------+----------- > > 1 | ABC > > | EFG > > (2 rows) > > > > > > mydb=# select user, * from dept2; > > current_user | dept_id | dept_name > > --------------+---------+----------- > > postgres | 1 | ABC > > postgres | | EFG > > (2 rows) > > > > > > > > > > On Tue, Jun 30, 2015 at 1:32 PM, Andy Pernsteiner < > > [email protected]> wrote: > > > >> ya that definitely works (as per my note above). I wonder though, is > this > >> case 'special' (where you have to alias the table to pull out this > >> reserved > >> word from a json file)? > >> > >> The drill documentation @ > >> https://drill.apache.org/docs/reserved-keywords/ > >> merely says to use backticks (``), not to do any table aliasing.. > >> > >> > >> > >> On Tue, Jun 30, 2015 at 4:11 PM, Andries Engelbrecht < > >> [email protected]> wrote: > >> > >> > Try using an alias on the profile > >> > > >> > select p.`user` from > >> > > >> > maprfs.profiles.`profiles/2a77fbb8-c1d6-8266-619f-537892b35fe1.sys.drill` p; > >> > > >> > +---------+ > >> > | user | > >> > +---------+ > >> > | cmatta | > >> > +————+ > >> > > >> > > >> > > >> > On Jun 30, 2015, at 1:03 PM, Andy Pernsteiner < > >> [email protected]> > >> > wrote: > >> > > >> > > Ya I tried that: > >> > > > >> > > select `user` from > >> > > `profiles/2aa32e9e-bdae-8949-8461-c14dafe63ee0.sys.drill` ; > >> > > +---------------+ > >> > > | user | > >> > > +---------------+ > >> > > | root | > >> > > +---------------+ > >> > > > >> > > but this works: > >> > > > >> > > select t.`user` from > >> > > `profiles/2aa32e9e-bdae-8949-8461-c14dafe63ee0.sys.drill` t ; > >> > > +---------+ > >> > > | user | > >> > > +---------+ > >> > > | cmatta | > >> > > +---------+ > >> > > > >> > > > >> > > > >> > > On Tue, Jun 30, 2015 at 3:50 PM, Christopher Matta <[email protected] > > > >> > wrote: > >> > > > >> > >> It would seem that user is a protected word that Drill uses to > return > >> > your > >> > >> username. Try specifying user without backticks on any dataset: > >> > >> > >> > >> 0: jdbc:drill:zk=sen11:5181,sen12:5181> select user from > sys.version > >> > >> . . . . . . . . . . . . . . . . . . . > ; > >> > >> +---------+ > >> > >> | user | > >> > >> +---------+ > >> > >> | cmatta | > >> > >> +---------+ > >> > >> 1 row selected (0.093 seconds) > >> > >> 0: jdbc:drill:zk=sen11:5181,sen12:5181> select user from > tweets_view > >> > limit > >> > >> 10; > >> > >> +---------+ > >> > >> | user | > >> > >> +---------+ > >> > >> | cmatta | > >> > >> | cmatta | > >> > >> | cmatta | > >> > >> | cmatta | > >> > >> | cmatta | > >> > >> | cmatta | > >> > >> | cmatta | > >> > >> | cmatta | > >> > >> | cmatta | > >> > >> | cmatta | > >> > >> +---------+ > >> > >> 10 rows selected (1.5 seconds) > >> > >> > >> > >> Re-run your first query with the user keyword surrounded by > >> back-ticks. > >> > >> > >> > >> > >> > >> Chris Matta > >> > >> [email protected] > >> > >> 215-701-3146 > >> > >> > >> > >> On Tue, Jun 30, 2015 at 3:41 PM, Andy Pernsteiner < > >> > >> [email protected] > >> > >>> wrote: > >> > >> > >> > >>> I decided to embark on a short journey into querying drill > profiles > >> to > >> > >> get > >> > >>> interesting statistics from a shared cluster (where I have root > >> priv's) > >> > >>> > >> > >>> What I noticed was that if I attempt to query on the 'user' field > >> > within > >> > >>> the JSON file (which appears as the element), that drill returns > to > >> me > >> > >> the > >> > >>> user who I am currently impersonating...not the user who > originally > >> ran > >> > >> the > >> > >>> query: > >> > >>> > >> > >>> (drill 1.0) > >> > >>> > >> > >>> /usr/bin/sqlline -u jdbc:drill: -n root > >> > >>> > >> > >>> then: > >> > >>> > >> > >>> select user from > >> > >> `profiles/2aa32e9e-bdae-8949-8461-c14dafe63ee0.sys.drill` > >> > >>> ; > >> > >>> +-------+ > >> > >>> | user | > >> > >>> +-------+ > >> > >>> | *root* | > >> > >>> +-------+ > >> > >>> > >> > >>> cat 2aa32e9e-bdae-8949-8461-c14dafe63ee0.sys.drill|egrep -o > >> > >>> 'user\":\"[a-z]+\"' > >> > >>> > >> > >>> user":"apernsteiner" > >> > >>> > >> > >>> > >> > >>> > >> > >>> Now, I stumbled across a workaround, which 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 | > >> > >>> +---------+ > >> > >>> > >> > >>> > >> > >>> Is this expected? I could understand if Drill would only return > >> valid > >> > >> data > >> > >>> if I properly aliased and subscripted to get to the > >> element...however: > >> > >> why > >> > >>> would it return the logged in username in this query if I > neglected > >> to > >> > do > >> > >>> so? > >> > >>> > >> > >>> > >> > >>> > >> > >>> > >> > >>> -- > >> > >>> Andy Pernsteiner > >> > >>> Manager, Field Enablement > >> > >>> ph: 206.228.0737 > >> > >>> > >> > >>> www.mapr.com > >> > >>> > >> > >>> Now Available - Free Hadoop On-Demand Training > >> > >>> < > >> > >>> > >> > >> > >> > > >> > http://www.mapr.com/training?utm_source=Email&utm_medium=Signature&utm_campaign=Free%20available > >> > >>>> > >> > >>> > >> > >> > >> > > > >> > > > >> > > > >> > > -- > >> > > Andy Pernsteiner > >> > > Manager, Field Enablement > >> > > ph: 206.228.0737 > >> > > > >> > > www.mapr.com > >> > > > >> > > Now Available - Free Hadoop On-Demand Training > >> > > < > >> > > >> > http://www.mapr.com/training?utm_source=Email&utm_medium=Signature&utm_campaign=Free%20available > >> > > > >> > > >> > > >> > >> > >> -- > >> Andy Pernsteiner > >> Manager, Field Enablement > >> ph: 206.228.0737 > >> > >> www.mapr.com > >> > >> Now Available - Free Hadoop On-Demand Training > >> < > >> > http://www.mapr.com/training?utm_source=Email&utm_medium=Signature&utm_campaign=Free%20available > >> > > >> > > > > > -- Andy Pernsteiner Manager, Field Enablement ph: 206.228.0737 www.mapr.com Now Available - Free Hadoop On-Demand Training <http://www.mapr.com/training?utm_source=Email&utm_medium=Signature&utm_campaign=Free%20available>
