Dr. Sum: Incorrect Results when Using OVER Clause and DISTINCT Function

KB#: 000020579
Last Updated: 6/Aug/2020

Affected Products

• All models of Dr. Sum V5.5 (Build=5.5.00.0000 - 5.5.00.0010)
• All models of Dr. Sum V5.1 (Build=5.1.00.0000 - 5.1.00.0050)

Details

When extracting from a view that uses OVER clause, or when issuing a query that uses OVER clause, the results may be incorrect.

Condition

When CREATE VIEW statement or the query issued meets all of the following conditions, the result of the query may be incorrect. In the incorrect result, the number of rows will be insufficient because the records that should be extracted are not outputted.

• OVER clause is included.
• Aggregation function "DISTINCT" is specified in the items to be extracted.

  *The condition will not be met when DISTINCT clause is included in aggregate function only, such as COUNT(DISTINCT col1).

  *This issue will not occur when the query is run on the in-memory server.

Sample SQL that causes the issue:

SELECT DISTINCT col1, COUNT(col2) over (PARTITION BY col3) from table1;

Sample SQL that does not cause the issue:

SELECT col1, COUNT(col2) over (PARTITION BY col3) from table1;
SELECT col1, COUNT(DISTINCT col2) over (PARTITION BY col3) from table1;

How to check the issue

1. You can check whether a view's statement meets the condition as follows:

Ⅰ. Output the definition information of the database from Enterprise Manager.

Ⅱ. Select the database from Database tab and search for "OVER" in the page.

Ⅲ. Check query column of the view that contains OVER clause and confirm if there is the item that DISTINCT clause is specified. 

If the view containing OVER clause does not exist, the view's statement does not meet the condition. 

If no user has permission to set a query that meet the condition in 2, the condition is not satisfied. 

2. For the issued query, you cannot check whether the query meets the condition or not.

*In Datalizer, only custom items can be set to meet the condition.

*In MotionBoard, only custom views and custom items can be set to meet the condition.

Solution

We are preparing the patches for Dr.Sum Ver.5.5 and Ver.5.1 to address the issue.

The schedule for the release has not been determined yet.

Contact us if you've experienced the above issue.

Workaround

Until the patches that address the issue are ready, consider the following workaround.

NOTE: If you use the following workaround, the query will take longer. We recommend that you revert to the original process after applying the patches.

 

Modify SQL to process DISTINCT clause, after processing the query containing OVER clause by using WITH clause or sub-query.

• Sample SQL that causes the issue:

SELECT DISTINCT co11, COUNT(DISTINCT col2) over (PARTITION BY col3) from table1;

• Workaround using WITH clause

WITH SUB(col1, col2) AS (SELECT col1, COUNT(DISTINCT col2) over (PARTITION BY col3) from table1) SELECT DISTINCT * FROM SUB;

• Workaround using sub-query

SELECT DISTINCT * from (SELECT col1, COUNT(DISTINCT col2) over (PARTITION BY col3) AS col2 from table1) SUB;

Delete DISTINCT clause in WITH clause or sub-query and run DISTINCT on the result of the sub-query or WITH clause, unless "DISTINCT item name" is the target of another aggregate function.