[
https://issues.apache.org/jira/browse/PHOENIX-3523?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15747199#comment-15747199
]
Karthick Duraisamy Soundararaj edited comment on PHOENIX-3523 at 12/14/16 4:16
AM:
-----------------------------------------------------------------------------------
Thanks [~jamestaylor] and [[email protected]]
was (Author: d.s.karthick):
Thanks [~jamestaylor] and [~ankit.singhal]
> Secondary index on case sensitive table breaks all queries
> ----------------------------------------------------------
>
> Key: PHOENIX-3523
> URL: https://issues.apache.org/jira/browse/PHOENIX-3523
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.8.1, 4.8.2, 4.8.3
> Reporter: Karthick Duraisamy Soundararaj
>
> Phoenix creates the HBase table for case sensitive Phoenix table under
> "default" namespace rather than creating it under the namespace that the
> table belongs to. Please see the following for illustration of the problem.
> h1. Attempt to create/query secondary index on HBase
> ----
> {panel:title=Step 1: Map an existing case sensitive table on HBase to Phoenix}
> 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.
> {code}
> 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",
> "merchant.feature_country" from "m3.merchants" limit 1;
> +---------------------------------------+-------------------+---------------------------+
> | primary | merchant.name |
> merchant.feature_country |
> +---------------------------------------+-------------------+---------------------------+
> | 00001860-00259060b612 | XXXXX | US |
> +---------------------------------------+-------------------+---------------------------+
> {code}
> {panel}
> {panel:title=Step 2: Create a secondary index on case sensitive table}
> I created a secondary index on "m3.merchants" for "merchant.name". The moment
> I do this, "m3.merchants" table is not usable anymore.
> {code}
> 0: jdbc:phoenix:dev.snc1> create index "merchant_feature_country_idx" ON
> "m3.merchants"("merchant.name");
> 1,660,274 rows affected (36.341 seconds)
> 0: jdbc:phoenix:dev.snc1> select "primary", "merchant.name",
> "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)
> {code}
> If you are wondering why this is happening, it's because the HBase table for
> secondary index gets created under {{default}} namespace as against the
> {{m3}} namespace like shown below
> {code}
> 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
> {code}
> {panel}
> h1. Attempt to force namespace into a namespace
> ----
> I tried the following to force the hbase index table to be located under "m3"
> namespace by doing the following
> {code}
> create index "m3.merchant_feature_country_idx" ON
> "m3.merchants"("merchant.feature_country");
> {code}
> I could see the index table under {{m3}} namespace on HBase
> {code}
> 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
> {code}
> But, when I tried to query the {{m3.merchants}} table, I get the following
> exception
> {code}
> 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",
> "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)
> {code}
> {panel}
> h1. What I suspect is the real problem
> ----
> I am suspecting a bug somewhere in the phoenix code that creates the index
> tables in the {{default}} namespace as against the {{m3}} namespace.
> h1. My Environemnt
> ----
> Phoenix version : 4.8.1
> HBase version : 1.1.2
> Please note that I have included {{4.8.2}} & {{4.8.3}} because I have not
> come across any related tickets in the realase for {{4.8.2}} or an open
> ticket to be addressed on {{4.8.3}}.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
