Uploaded image for project: 'Phoenix'
  1. Phoenix
  2. PHOENIX-5287

Incorrect results for COUNT(1) on a table with GLOBAL INDEX

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Blocker
    • Resolution: Duplicate
    • Affects Version/s: 5.0.0
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • 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

      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

       

       

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                vkrava4 Vlad Krava
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: