phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Karthick Duraisamy Soundararaj (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (PHOENIX-3523) Secondary index on case sensitive table breaks all queries
Date Wed, 07 Dec 2016 23:24:58 GMT

     [ https://issues.apache.org/jira/browse/PHOENIX-3523?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Karthick Duraisamy Soundararaj updated PHOENIX-3523:
----------------------------------------------------
    Description: 
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 



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




> 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 



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message