Yes, please open a Jira. Bob
On Tue, Jun 30, 2015 at 2:17 PM, Andy Pernsteiner <[email protected] > wrote: > 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 > > >
