[PHOENIX-5287] Incorrect results for COUNT(1) on a table with GLOBAL INDEX

• Type: Bug
• Status: Resolved
• Priority: Blocker
• Resolution: Duplicate
• Affects Version/s: 5.0.0

Environment

Environment and data information:

• Column structure of TABLE_A is fully identical to TABLE_B
• TABLE_A has a GLOBAL INDEX
• TABLE_B has a LOCAL INDEX

• Flags: Important
• Assignee: Unassigned
• Reporter: Vlad Krava
• Created: 17/May/19 21:08
• Updated: 15/Sep/20 18:53
• Resolved: 15/Sep/20 18:53

Description

COUNT(*) and COUNT(1) commands display incorrect(outdated) statistics for table with GLOBAL index.

Example:

• Export TABLE_A to SCV file (SELECT * FROM POMG.TABLE_A)
• Import CSV file to TABLE_B
• COUNT operation on 'TABLE_A' was constantly returning with an amount of 218623 (for 2 days without any data modifications!!!) :
  • 0: jdbc:phoenix:> select count(1) from POMG.TABLE_A;
    • RESULT: 218623
• Newly exported table from CSV file (TABLE_B) showed different (higher amount of records)
  • 0: jdbc:phoenix:> select count(1) from POMG.TABLE_B;
    • RESULT: 218683
• COUNT in Hbase is returning the bigger value than COUNT comparing to Phoenix table ( 218683 vs 218623)
• Phoenix Statistics for this table was updated few times for the past few testing days
• I took few attends to define data misalignments by executing diff for primary keys:
  • select key_1 from POMG.TABLE_A where key_1 not in (select key_1 from POMG.TABLE_B) - 0 records selected (Doesn't make sense considering a fact that TABLE_A larger than TABLE_B and key_1 is unique PRIMARY KEY)
  • select key_1 from POMG.TABLE_B where key_1 not in (select key_1 from POMG.TABLE_A)  - 23 records selected (Doesn't make sense considering a fact that TABLE_A larger than TABLE_B and key_1 is unique PRIMARY KEY)

Workaround:

• After executing ALTER INDEX with REBUILD flag COUNT statistics for TABLE_A become identical to TABLE_B
• Diff selects didn't show any differences between  POMG.TABLE_A and    POMG.TABLE_B

 

 

Issue Links

• is fixed by: PHOENIX-5211 Consistent Immutable Global Indexes for Non-Transactional Tables
• is fixed by: PHOENIX-6067 (5.x) Global Secondary Index Parity with 4.x