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