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