Hi James, If we can't change the query on the BI tool's side, is there anyway we can intercept to modify the query without changing client's driver?
2015-06-16 18:32 GMT-07:00 Fulin Sun <[email protected]>: > 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 <[email protected]> > *Date:* 2015-06-17 07:45 > *To:* user <[email protected]> > *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 > > -- best, Yufan
