Hi Jame, Sorry about the bad test case. The actual test case should be this:
select "fact"."c1" as “c0" from (select col1 as c1 from t1) as "fact”; Error: ERROR 1001 (42I01): Undefined column family. familyName=fact.null SQLState: 42I01 ErrorCode: 1001 The BI tool i am using tries to generate SQL with double quote in it. If i remove the double quoted aliases the phoenix works fine. Thx Yanlin > On Jun 16, 2015, at 2:16 PM, James Taylor <[email protected]> wrote: > > Hi Yufan, > The outer query should use the alias name (c1). If not, please file a > JIRA when you have a chance. > Thanks, > James > > On Tue, Jun 16, 2015 at 2:03 PM, yanlin wang <[email protected]> wrote: >> Thanks James. My example is bad … >> >> >>> On Jun 16, 2015, at 1:39 PM, James Taylor <[email protected]> wrote: >>> >>> 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 >>>>>>> >>>>> >>>> >>
