thanks, done ( https://issues.apache.org/jira/browse/DRILL-3435 )
On Tue, Jun 30, 2015 at 6:36 PM, Bob Rumsby <[email protected]> wrote: > 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 > > > > > > -- 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>
