Community Articles

Find and share helpful community-sourced technical articles.
Labels (1)
avatar

STEPS TO REPRODUCE:

1. Setup table and local index:

create table mytest (col1 varchar, col2 varchar constraint pk primary key (col1)); 
upsert into mytest values ('123','123'); 
upsert into mytest values ('456','456'); 
create local index myidx on mytest (col2); 

2. Obtain current timestamp and prove rows can be seen:

select cast(current_time() as bigint) from mytest limit 1; 
+--------------------------------------------+ 
| TO_BIGINT(TIME '2017-04-19 09:57:47.867') | 
+--------------------------------------------+ 
| 1492595867867 | 
+--------------------------------------------+ 

sqlline.py "aw25k-3:2181/hbase-secure;currentSCN=1492595867867" 

select * from mytest; 
+-------+-------+ 
| COL1 | COL2 | 
+-------+-------+ 
| 123 | 123 | 
| 456 | 456 | 
+-------+-------+ 

3. Disconnect sqlline and reconnect without SCN. Drop the local index, insert more rows, recreate the index:

sqlline.py 
drop index myidx on mytest; 
upsert into mytest values ('678','678'); 
upsert into mytest values ('456','654'); 
create local index myidx on mytest (col2); 

4. Re-attempt flashback query:

sqlline.py "example-3:2181/hbase-secure;currentSCN=1492595867867" 
select * from mytest; 
+-------+-------+ 
| COL1 | COL2 | 
+-------+-------+ 
+-------+-------+ 
No rows selected (0.18 seconds) 

select * from mytest where col2 = '123' 
+-------+-------+ 
| COL1 | COL2 | 
+-------+-------+ 
+-------+-------+ 
No rows selected (0.18 seconds) 

SELECT /*+NO_INDEX*/ * from mytest; 
select * from mytest; 
+-------+-------+ 
| COL1 | COL2 | 
+-------+-------+ 
| 123 | 123 | 
| 456 | 456 | 
+-------+-------+ 

ROOT CAUSE : This is because we create data for new indexes with the latest timestamp as in data table.Fix includes to create initial index also with the timestamp of the data. (at least for local index).

SOLUTION: Fix is available in next maintenance release of HDP 2.6. Request for a hotfix by a support case if you are on an earlier version.

REFERENCE: Internal BUG-79529

1,360 Views
0 Kudos