raised* On Wed, Dec 7, 2016 at 3:00 PM, KARTHICK Duraisamy Soundararaj < [email protected]> wrote:
> Thanks for the response James. Rised PHOENIX-3523 > <https://issues.apache.org/jira/browse/PHOENIX-3523>. > > On Wed, Dec 7, 2016 at 12:57 PM, James Taylor <[email protected]> > wrote: > >> Thanks for narrowing down the cause, Karthick. Please file a JIRA with >> this information. >> >> James >> >> On Wed, Dec 7, 2016 at 12:45 PM, KARTHICK Duraisamy Soundararaj < >> [email protected]> wrote: >> >>> I am able to create a secondary index on case-insensitive schema.table >>> and everything works as expected. In other words, index table for a >>> non-case sensitive table gets created in the appropriate namespace on >>> HBase. >>> >>> >>> On Tue, Dec 6, 2016 at 4:18 PM, KARTHICK Duraisamy Soundararaj < >>> [email protected]> wrote: >>> >>>> Hi Team, >>>> I am not sure what I am missing but the secondary index >>>> totally breaks all the phoenix queries. Following sequence of actions >>>> illustrates my problem >>>> >>>> 1. On HBase, I have "m3:merchants". It is mapped to "m3.merchants" on >>>> phoenix. As you can see below, I can query the table just fine. >>>> >>>> 0: jdbc:phoenix:dev.snc1> drop index "merchant_feature_country_idx" on >>>> "m3.merchants"; >>>> No rows affected (4.006 seconds) >>>> 0: jdbc:phoenix:dev.snc1> select "primary", "merchant.name >>>> <https://urldefense.proofpoint.com/v2/url?u=http-3A__merchant.name&d=DgMFaQ&c=LNdz7nrxyGFUIUTz2qIULQ&r=ibZuebyjpgOGN8_Vls6nU-h1Je4eG1upPUO9sIpBAqg&m=z4sZBYWqUAJal5N46_o7jV0WCQ8_s-eAFfCIIeUkReY&s=h4Rm0OI0gYjcVUMHyxcuA2hZnMjcDsbz9W04LFmkHgM&e=>", >>>> "merchant.feature_country" from "m3.merchants" limit 1; >>>> +---------------------------------------+-------------------+---------------------------+ >>>> | primary | merchant.name >>>> <https://urldefense.proofpoint.com/v2/url?u=http-3A__merchant.name&d=DgMFaQ&c=LNdz7nrxyGFUIUTz2qIULQ&r=ibZuebyjpgOGN8_Vls6nU-h1Je4eG1upPUO9sIpBAqg&m=z4sZBYWqUAJal5N46_o7jV0WCQ8_s-eAFfCIIeUkReY&s=h4Rm0OI0gYjcVUMHyxcuA2hZnMjcDsbz9W04LFmkHgM&e=> >>>> | merchant.feature_country | >>>> +---------------------------------------+-------------------+---------------------------+ >>>> | 00001860-00259060b612 | XXXXX | US >>>> | >>>> +---------------------------------------+-------------------+---------------------------+ >>>> >>>> >>>> >>>> 2. I create a secondary index on "m3.merchants" for "merchant.name >>>> <https://urldefense.proofpoint.com/v2/url?u=http-3A__merchant.name&d=DgMFaQ&c=LNdz7nrxyGFUIUTz2qIULQ&r=ibZuebyjpgOGN8_Vls6nU-h1Je4eG1upPUO9sIpBAqg&m=z4sZBYWqUAJal5N46_o7jV0WCQ8_s-eAFfCIIeUkReY&s=h4Rm0OI0gYjcVUMHyxcuA2hZnMjcDsbz9W04LFmkHgM&e=>". >>>> The moment I do this, "m3.merchants" table is not usable anymore. >>>> >>>> 0: jdbc:phoenix:dev.snc1> create index "merchant_feature_country_idx" ON >>>> "m3.merchants"("merchant.name >>>> <https://urldefense.proofpoint.com/v2/url?u=http-3A__merchant.name&d=DgMFaQ&c=LNdz7nrxyGFUIUTz2qIULQ&r=ibZuebyjpgOGN8_Vls6nU-h1Je4eG1upPUO9sIpBAqg&m=z4sZBYWqUAJal5N46_o7jV0WCQ8_s-eAFfCIIeUkReY&s=h4Rm0OI0gYjcVUMHyxcuA2hZnMjcDsbz9W04LFmkHgM&e=>"); >>>> 1,660,274 rows affected (36.341 seconds) >>>> >>>> 0: jdbc:phoenix:dev.snc1> select "primary", "merchant.name >>>> <https://urldefense.proofpoint.com/v2/url?u=http-3A__merchant.name&d=DgMFaQ&c=LNdz7nrxyGFUIUTz2qIULQ&r=ibZuebyjpgOGN8_Vls6nU-h1Je4eG1upPUO9sIpBAqg&m=z4sZBYWqUAJal5N46_o7jV0WCQ8_s-eAFfCIIeUkReY&s=h4Rm0OI0gYjcVUMHyxcuA2hZnMjcDsbz9W04LFmkHgM&e=>", >>>> "merchant.feature_country" from "m3.merchants" limit 1; >>>> Error: ERROR 1012 (42M03): Table undefined. >>>> tableName=m3.merchant_feature_country_idx (state=42M03,code=1012) >>>> org.apache.phoenix.schema.TableNotFoundException: ERROR 1012 (42M03): >>>> Table undefined. tableName=m3.merchant_feature_country_idx >>>> at >>>> org.apache.phoenix.compile.FromCompiler$BaseColumnResolver.createTableRef(FromCompiler.java:539) >>>> at >>>> org.apache.phoenix.compile.FromCompiler$SingleTableColumnResolver.<init>(FromCompiler.java:365) >>>> at >>>> org.apache.phoenix.compile.FromCompiler.getResolverForQuery(FromCompiler.java:213) >>>> at >>>> org.apache.phoenix.optimize.QueryOptimizer.addPlan(QueryOptimizer.java:226) >>>> at >>>> org.apache.phoenix.optimize.QueryOptimizer.getApplicablePlans(QueryOptimizer.java:146) >>>> at >>>> org.apache.phoenix.optimize.QueryOptimizer.optimize(QueryOptimizer.java:94) >>>> at >>>> org.apache.phoenix.optimize.QueryOptimizer.optimize(QueryOptimizer.java:80) >>>> at >>>> org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:278) >>>> at >>>> org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:266) >>>> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53) >>>> at >>>> org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:265) >>>> at >>>> org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1446) >>>> at sqlline.Commands.execute(Commands.java:822) >>>> at sqlline.Commands.sql(Commands.java:732) >>>> at sqlline.SqlLine.dispatch(SqlLine.java:807) >>>> at sqlline.SqlLine.begin(SqlLine.java:681) >>>> at sqlline.SqlLine.start(SqlLine.java:398) >>>> at sqlline.SqlLine.main(SqlLine.java:292) >>>> >>>> >>>> >>>> When I look at my HBase tables, I see that the index is created in the >>>> "default" namespace >>>> >>>> hbase(main):006:0> list_namespace_tables "default" >>>> TABLE >>>> merchant_feature_country_idx >>>> 1 row(s) in 0.0100 seconds >>>> >>>> hbase(main):007:0> list_namespace_tables "m3" >>>> TABLE >>>> merchants >>>> 1 row(s) in 0.0080 seconds >>>> >>>> >>>> >>>> 3. I tried the following to force the hbase index table to be located >>>> under "m3" namespace. But I get the following error. >>>> >>>> 0: jdbc:phoenix:dev.snc1> create index "m3.merchant_feature_country_idx" >>>> ON "m3.merchants"("merchant.feature_country"); >>>> 1,660,274 rows affected (32.14 seconds) >>>> 0: jdbc:phoenix:dev.snc1> select "primary", "merchant.name >>>> <https://urldefense.proofpoint.com/v2/url?u=http-3A__merchant.name&d=DgMFaQ&c=LNdz7nrxyGFUIUTz2qIULQ&r=ibZuebyjpgOGN8_Vls6nU-h1Je4eG1upPUO9sIpBAqg&m=z4sZBYWqUAJal5N46_o7jV0WCQ8_s-eAFfCIIeUkReY&s=h4Rm0OI0gYjcVUMHyxcuA2hZnMjcDsbz9W04LFmkHgM&e=>", >>>> "merchant.feature_country" from "m3.merchants" limit 1; >>>> Error: ERROR 1012 (42M03): Table undefined. >>>> tableName=m3.m3.merchant_feature_country_idx (state=42M03,code=1012) >>>> org.apache.phoenix.schema.TableNotFoundException: ERROR 1012 (42M03): >>>> Table undefined. tableName=m3.m3.merchant_feature_country_idx >>>> at >>>> org.apache.phoenix.compile.FromCompiler$BaseColumnResolver.createTableRef(FromCompiler.java:539) >>>> at >>>> org.apache.phoenix.compile.FromCompiler$SingleTableColumnResolver.<init>(FromCompiler.java:365) >>>> at >>>> org.apache.phoenix.compile.FromCompiler.getResolverForQuery(FromCompiler.java:213) >>>> at >>>> org.apache.phoenix.optimize.QueryOptimizer.addPlan(QueryOptimizer.java:226) >>>> at >>>> org.apache.phoenix.optimize.QueryOptimizer.getApplicablePlans(QueryOptimizer.java:146) >>>> at >>>> org.apache.phoenix.optimize.QueryOptimizer.optimize(QueryOptimizer.java:94) >>>> at >>>> org.apache.phoenix.optimize.QueryOptimizer.optimize(QueryOptimizer.java:80) >>>> at >>>> org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:278) >>>> at >>>> org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:266) >>>> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53) >>>> at >>>> org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:265) >>>> at >>>> org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1446) >>>> at sqlline.Commands.execute(Commands.java:822) >>>> at sqlline.Commands.sql(Commands.java:732) >>>> at sqlline.SqlLine.dispatch(SqlLine.java:807) >>>> at sqlline.SqlLine.begin(SqlLine.java:681) >>>> at sqlline.SqlLine.start(SqlLine.java:398) >>>> at sqlline.SqlLine.main(SqlLine.java:292) >>>> >>>> >>>> >>>> Below is the evidence that index table on hbase is located under "m3" >>>> namespace >>>> >>>> hbase(main):008:0> list_namespace_tables "m3" >>>> TABLE >>>> merchant_feature_country_idx >>>> merchants >>>> 2 row(s) in 0.0100 seconds >>>> >>>> hbase(main):009:0> list_namespace_tables "default" >>>> TABLE >>>> 0 row(s) in 0.0030 seconds >>>> >>>> >>>> >>>> Not sure if this is a bug in the namespace mapping implementation or if >>>> I am missing something. I would appreciate if someone could shed some light >>>> on this. >>>> >>>> Environment: >>>> HBase version : 1.1.2 >>>> Phoenix version : 4.8.1 >>>> >>>> Context: >>>> I am new to phoenix and playing around with various phoenix features. >>>> >>>> Thanks, >>>> Karthick >>>> >>>> >>> >> >
