[PHOENIX-2758] Ordered GROUP BY not occurring with leading PK equality expression

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 4.8.0
• Assignee: James R. Taylor
• Reporter: James R. Taylor
• Created: 10/Mar/16 07:26
• Updated: 11/Aug/16 08:27
• Resolved: 02/Apr/16 07:10

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);