Hi James,
Thanks for the reply.
I was not able to find the exact corner case of the issue when tried with a
sample table.
Can you please let me know if there is something i am doing wrong?
But in our project we are facing this issue when we try to delete the data
from the below table,
Below are the tables and indexes we have in our project,
CREATE TABLE four_g.s11_lusr_hourly (
subscriber.msisdn VARCHAR,
subscriber.plan VARCHAR,
subscriber.imei VARCHAR,
basestation.e_node_b_id VARCHAR,
session_data.mme_ip VARCHAR,
session_data.sgw_ip VARCHAR,
session_data.user_ip VARCHAR,
session_data.apn VARCHAR,
session_data.rat VARCHAR,
session_data.start_time TIMESTAMP,
session_data.end_time TIMESTAMP,
session_data.teid_uplink VARCHAR,
session_data.teid_downlink VARCHAR,
session_data.ipv4_uplink VARCHAR,
session_data.ipv4_downlink VARCHAR,
report_time TIMESTAMP NOT NULL,
imsi VARCHAR NOT NULL,
default_bearer_id BIGINT NOT NULL,
dedicated_bearer_id BIGINT NOT NULL,
rolled_up_count INTEGER,
basestation.name VARCHAR,
basestation.latitude FLOAT,
basestation.longitude FLOAT,
basestation.rnc_id VARCHAR,
basestation.mobile_operator_name VARCHAR,
basestation.mobile_operator_country VARCHAR,
basestation.sector_id VARCHAR,
subscriber.device_type VARCHAR,
subscriber.device_model VARCHAR,
subscriber.device_vendor VARCHAR,
subscriber.mobile_operator_name VARCHAR,
subscriber.mobile_operator_country VARCHAR,
subscriber.roaming_status VARCHAR
CONSTRAINT s11_lusr_hourly_pk PRIMARY KEY (
report_time,
imsi,
default_bearer_id,
dedicated_bearer_id
)
)
SALT_BUCKETS = 4,
IMMUTABLE_ROWS = true,
COMPRESSION = 'GZ';
Below are the secondary indexes i have,
CREATE INDEX LUSR_HOURLY_INDEX_DASHBOARD ON FOUR_G.S11_LUSR_HOURLY
(report_time, start_time, end_time, sgw_ip, user_ip, e_node_b_id, apn, rat,
device_type, device_model, device_vendor);
CREATE INDEX LUSR_HOURLY_INDEX_DASHBOARD_MAP ON FOUR_G.S11_LUSR_HOURLY
(report_time, start_time, end_time, sgw_ip, user_ip, e_node_b_id, apn, rat,
device_type, device_model, device_vendor) INCLUDE (name, latitude,
longitude, sector_id, imsi);
CREATE INDEX LUSR_HOURLY_INDEX_HISTORY ON FOUR_G.S11_LUSR_HOURLY
(report_time, start_time, end_time, sgw_ip, user_ip, roaming_status)
INCLUDE (apn, rat, e_node_b_id, device_type, device_vendor, device_model);
CREATE INDEX LUSR_HOURLY_INDEX_TRENDS ON FOUR_G.S11_LUSR_HOURLY
(report_time, start_time, end_time, sgw_ip, user_ip, roaming_status, name,
device_type, apn, rat) include (msisdn);
CREATE INDEX LUSR_HOURLY_INDEX_CATEGORY ON FOUR_G.S11_LUSR_HOURLY
(report_time, start_time, end_time, sgw_ip, user_ip, msisdn, e_node_b_id,
device_type) include (device_vendor, device_model);
CREATE INDEX LUSR_HOURLY_INDEX_KPI ON FOUR_G.S11_LUSR_HOURLY (report_time,
start_time, end_time, sgw_ip, user_ip,roaming_status, apn, rat, name,
device_type) include (msisdn, plan, imsi);
Below is the exception i am getting,
Caused by: java.sql.SQLException: ERROR 1027 (42Y86): All columns
referenced in a WHERE clause must be available in every index for a
table with immutable rows. tableName=FOUR_G.S11_LUSR_HOURLY
at
org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:386)
at
org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:145)
at
org.apache.phoenix.compile.DeleteCompiler.compile(DeleteCompiler.java:389)
at
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableDeleteStatement.compilePlan(PhoenixStatement.java:546)
at
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableDeleteStatement.compilePlan(PhoenixStatement.java:534)
at
org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:302)
at
org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:295)
at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
at
org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:293)
at
org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:225)
at
org.apache.phoenix.jdbc.PhoenixPreparedStatement.execute(PhoenixPreparedStatement.java:173)
at
org.apache.phoenix.jdbc.PhoenixStatement.executeBatch(PhoenixStatement.java:1130)
Thanks,
Nanda
On Sun, Feb 21, 2016 at 7:51 AM, James Taylor <[email protected]>
wrote:
> Hi Nanda,
> This error occurs if your table is immutable, you have an index on the
> table, and your WHERE clause is filtering on a column not contained in all
> of the indexes. If that's not the case, would you mind posting a complete
> end-to-end test as it's possible you're hitting a bug.
> Thanks,
> James
>
> On Sat, Feb 20, 2016 at 10:25 AM, Nanda <[email protected]> wrote:
>
>> Hi,
>>
>> I am using Phoenix 4.4 and when executing the below query i get an
>> exception as below,
>> I have indexes on the below "some_table" on column report_time in all the
>> indexes created.
>>
>> Delete from some_table where TO_NUMBER(report_time) <= 1455937500000
>>
>>
>>
>> java.sql.SQLException: ERROR 1027 (42Y86): All columns referenced in a
>> WHERE clause must be available in every index for a table with immutable
>> rows. tableName=some_table
>> at
>> org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:386)
>> at
>> org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:145)
>> at
>> org.apache.phoenix.compile.DeleteCompiler.compile(DeleteCompiler.java:389)
>> at
>> org.apache.phoenix.jdbc.PhoenixStatement$ExecutableDeleteStatement.compilePlan(PhoenixStatement.java:546)
>> at
>> org.apache.phoenix.jdbc.PhoenixStatement$ExecutableDeleteStatement.compilePlan(PhoenixStatement.java:534)
>> at
>> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:302)
>> at
>> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:295)
>> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
>> at
>> org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:293)
>> at
>> org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1247)
>> at sqlline.Commands.execute(Commands.java:822)
>> at sqlline.Commands.sql(Commands.java:732)
>> at sqlline.SqlLine.dispatch(SqlLine.java:808)
>> at sqlline.SqlLine.begin(SqlLine.java:681)
>> at sqlline.SqlLine.start(SqlLine.java:398)
>> at sqlline.SqlLine.main(SqlLine.java:292)
>>
>>
>>
>> Any Idea what that error exactly meant?
>>
>> Thanks,
>> Nanda
>>
>
>
