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

Ordered GROUP BY not occurring with leading PK equality expression

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 4.8.0
    • Labels:
      None

      Description

      The following query:

      SELECT SUM(DUP_COUNT) FROM (
          SELECT COUNT(1) As DUP_COUNT
          FROM DATACLOUD.DATA_ASSESSMENT_RECORD 
         WHERE JOURNEY_ID='07ixx000000004J' AND 
                       DATASOURCE=0 AND MATCH_STATUS <= 1 and 
                       ORGANIZATION_ID='07ixx000000004J' 
          GROUP BY MATCH_STATUS, EXTERNAL_DATASOURCE_KEY 
          HAVING COUNT(1) > 1);
      

      Should use an ORDERED DISTINCT, but doesn't.

      This is the DDL:

      CREATE TABLE DATACLOUD.DATA_ASSESSMENT_RECORD (ORGANIZATION_ID char(15) not null, 
      JOURNEY_ID char(15) not null, 
      DATASOURCE SMALLINT not null, 
      MATCH_STATUS TINYINT not null, 
      EXTERNAL_DATASOURCE_KEY varchar(30), 
      ENTITY_ID char(15) not null, 
      CONSTRAINT PK PRIMARY KEY (
          ORGANIZATION_ID, 
          JOURNEY_ID, 
          DATASOURCE, 
          MATCH_STATUS,
          EXTERNAL_DATASOURCE_KEY,
          ENTITY_ID))
      

      The optimization does occur if you include the PK columns in the GROUP BY like this:

      SELECT SUM(DUP_COUNT) FROM (
          SELECT COUNT(1) As DUP_COUNT
          FROM DATACLOUD.DATA_ASSESSMENT_RECORD 
         WHERE JOURNEY_ID='07ixx000000004J' AND 
                       DATASOURCE=0 AND MATCH_STATUS <= 1 and 
                       ORGANIZATION_ID='07ixx000000004J' 
          GROUP BY ORGANIZATION_ID, JOURNEY_ID, DATASOURCE, MATCH_STATUS, EXTERNAL_DATASOURCE_KEY 
          HAVING COUNT(1) > 1);
      

        Attachments

        1. PHOENIX-2758_wip.patch
          6 kB
          James Taylor
        2. PHOENIX-2758.patch
          20 kB
          James Taylor

          Activity

            People

            • Assignee:
              jamestaylor James Taylor
              Reporter:
              jamestaylor James Taylor
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: