Community Articles
Find and share helpful community-sourced technical articles
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.
Labels (1)

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

642 Views
0 Kudos
Don't have an account?
Coming from Hortonworks? Activate your account here
Version history
Revision #:
1 of 1
Last update:
‎06-03-2017 10:19 PM
Updated by:
 
Contributors
Top Kudoed Authors