[
https://issues.apache.org/jira/browse/PHOENIX-3523?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15747958#comment-15747958
]
Ankit Singhal commented on PHOENIX-3523:
----------------------------------------
[~d.s.karthick], to create case sensitive table in the case sensitive
namespace, you need to create a table like this.
{code}
0: jdbc:phoenix:localhost> create schema "m3";
No rows affected (0.013 seconds)
0: jdbc:phoenix:localhost> create table "m3"."merchants"("primary" varchar
primary key, "merchant.name" varchar, "merchant.feature_country" varchar);
No rows affected (1.266 seconds)
0: jdbc:phoenix:localhost> create index "merchant_feature_country_idx" ON
"m3"."merchants"("merchant.name");
No rows affected (6.304 seconds)
: jdbc:phoenix:localhost> select "primary", "merchant.name",
"merchant.feature_country" from "m3"."merchants" limit 1;
+----------+----------------+---------------------------+
| primary | merchant.name | merchant.feature_country |
+----------+----------------+---------------------------+
+----------+----------------+---------------------------+
No rows selected (0.069 seconds)
0: jdbc:phoenix:localhost> explain select "primary", "merchant.name",
"merchant.feature_country" from "m3"."merchants" limit 1;
+----------------------------------------------------------+
| PLAN |
+----------------------------------------------------------+
| CLIENT 1-CHUNK SERIAL 1-WAY FULL SCAN OVER m3:merchants |
| SERVER 1 ROW LIMIT |
| CLIENT 1 ROW LIMIT |
+----------------------------------------------------------+
3 rows selected (0.016 seconds)
0: jdbc:phoenix:localhost> select * from "m3"."merchant_feature_country_idx"
. . . . . . . . . . . . .> ;
+------------------+-----------+
| 0:merchant.name | :primary |
+------------------+-----------+
+------------------+-----------+
{code}
{code}
hbase(main):005:0> list
TABLE
SYSTEM:CATALOG
SYSTEM:FUNCTION
SYSTEM:SEQUENCE
SYSTEM:STATS
m3:merchant_feature_country_idx
m3:merchants
6 row(s) in 0.0240 seconds
hbase(main):010:0> list_namespace_tables 'm3'
TABLE
merchant_feature_country_idx
merchants
{code}
Actually, bug is that we should not be creating "A.B" in namespace "A", instead
it should go to default namespace with table name "A.B", I'll fix this. Hence
other things will work as it is.
> 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)
