Hi James, I have tried the queries you guys are using above (select fact.c1 from (select k as k1, col1 as c1 from t1) as fact), it works. But in the result set, it displays the original column name (col1) instead of alias name (c1). Is that expected behavior?
2015-06-16 13:39 GMT-07:00 James Taylor <[email protected]>: > Hi Yanlin, > The first error is legit: you're aliasing col1 as c1 in the inner > query but then trying to select it as col1 in the outer query. > > The second error is a known limitation of derived tables (PHOENIX-2041). > > Thanks, > James > > On Tue, Jun 16, 2015 at 11:48 AM, yanlin wang <[email protected]> > wrote: > > Hi James, > > > > I figured the error i got was not the phoenix version issue and here is > the test case you can reproduce it: > > > > create table t1 (k varchar primary key, col1 varchar); > > > > select fact.col1 from (select k as k1, col1 as c1 from t1) as fact; > > > > Error: ERROR 1001 (42I01): Undefined column family. familyName=FACT.null > > SQLState: 42I01 > > ErrorCode: 1001 > > > > It seems phoenix does not like the combination of table and column > aliases. > > > > The other test case also failed: > > > > select fact.col1 from (select * from t1) as fact; > > > > Error: ERROR 1001 (42I01): Undefined column family. familyName=FACT.null > > SQLState: 42I01 > > ErrorCode: 1001 > > > > > > Thx > > Yanlin > > > >> On Jun 16, 2015, at 10:57 AM, yanlin wang <[email protected]> wrote: > >> > >> Hi James, > >> > >> Thanks for the info. I am using cloudera distribution > CLABS_PHOENIX-4.3.0-1.clabs_phoenix1.0.0.p0.78 that can be the issue. I > will try to play with other versions. > >> > >> > >> Thx > >> Yanlin > >> > >> > >>> On Jun 16, 2015, at 9:34 AM, James Taylor <[email protected]> > wrote: > >>> > >>> Hi Yanlin, > >>> What version of Phoenix are you using? I tried the following in > >>> sqlline, and it worked fine: > >>> > >>> 0: jdbc:phoenix:localhost> create table t1 (k varchar primary key, > >>> col1 varchar); > >>> No rows affected (10.29 seconds) > >>> 0: jdbc:phoenix:localhost> select fact.col1 from (select col1 from t1) > as fact; > >>> +------------------------------------------+ > >>> | COL1 | > >>> +------------------------------------------+ > >>> +------------------------------------------+ > >>> No rows selected (0.051 seconds) > >>> 0: jdbc:phoenix:localhost> upsert into t1 values('a','b'); > >>> 1 row affected (0.04 seconds) > >>> 0: jdbc:phoenix:localhost> select fact.col1 from (select col1 from t1) > as fact; > >>> +------------------------------------------+ > >>> | COL1 | > >>> +------------------------------------------+ > >>> | b | > >>> +------------------------------------------+ > >>> 1 row selected (0.031 seconds) > >>> > >>> > >>> On Mon, Jun 15, 2015 at 6:07 PM, yanlin wang <[email protected]> > wrote: > >>>> Hi, > >>>> > >>>> I am trying to setup phoenix working with some BI solution. The issue > i have is that given a tool generated query like this -> select fact.col1 > from (select col1 from t1) as fact, phoenix will confuse the table alias > with column family. Any suggestion? > >>>> > >>>> Thx > >>>> Yanlin > >>>> > >> > > > -- best, Yufan
