[NIFI-5855] Optimize GenerateTableFetch to remove unnecessary ORDER BY clause

• Type: Improvement
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.9.0
• Component/s: Extensions
• Assignee: Matt Burgess
• Reporter: Matt Burgess
• Created: 29/Nov/18 22:15
• Updated: 30/Nov/18 09:56
• Resolved: 30/Nov/18 09:55

Description

Related to NIFI-5727, which for the query that GenerateTableFetch uses to get column/table information removes an unnecessary COUNT( * ) clause when Partition Size = 0, the ORDER BY clause can also be removed. This is because there will be a single generated query that will fetch all rows that match the filters. The ORDER BY is necessary during pagination (when Partition Size > 0) so it can fetch from Row X to Row Y based on the row number, which is only constant if the column is ordered.

This Jira proposes to remove the ORDER BY clause from the generated query if Partition Size is set to 0. Note that this can cause the output of the executed query to not guaranteed to be ordered; it is a possible change in behavior, but we don't document any guarantees about row ordering anyway. If the user needs the rows ordered, they can do it with QueryRecord, or they can add the ORDER BY clause manually via ReplaceText.

Issue Links

• relates to: NIFI-5727 Optimize GenerateTableFetch to remove unnecessary COUNT(*)
• links to: GitHub Pull Request #3191