Hi, there I think the problem root cause has been explained explicitely by James, that you may need to remove the double quotes cause
double quotes would got upper case identifiers. BI tool may be leveraged to reach that. Best, Sun. CertusNet From: Yufan Liu Date: 2015-06-17 07:45 To: user Subject: Re: table alias Hi James, I change everything in the query to upper case: select "FACT"."C1" as “C0" from (select COL1 as C1 from T1) as "FACT”, and it returns the same error: "Undefined column family. familyName=FACT.null." But when I change to FACT."C1", it works fine. It seems there is problem with double quote on derived table name in selecting. Can you verify it? 2015-06-16 14:29 GMT-07:00 James Taylor <[email protected]>: Hi Yanlin, That's a legit error as well. Putting double quotes around an identifier makes it case sensitive. Without double quotes, identifiers are normalized by upper casing them. So you don't have a "c1" column, but you do have a "C1" column. Thanks, James On Tue, Jun 16, 2015 at 2:23 PM, yanlin wang <[email protected]> wrote: > 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 >>>>>>>> >>>>>> >>>>> >>> > -- best, Yufan
