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
