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