Query transformation

• US 8,108,415 B2
• Filed: 09/09/2008
• Issued: 01/31/2012
• Est. Priority Date: 05/27/2003
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: International Business Machines Corporation
• Inventors: Styles, Michael E.
• Primary Examiner(s): Pulliam, Christyann
• Application Number:
  US12/283,194
• Publication Number:
  US 20090063441A1
• Time in Patent Office: 1,239 Days
• Field of Search: 707/756, 707759-763
• US Class Current: 707/760
• CPC Class Codes: G06F 16/244 Grouping and aggregation
• CPC Class Codes: G06F 16/24547 Optimisations to support specific applications; Extensibility of optimisers
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: Y10S 707/954 Relational
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access

Abstract

A mechanism is provided for transforming an original database query into a supported database query that can be fully computed by a target database. The original database query comprising a select list including a plurality of expressions, the plurality of expressions having a control break. The plurality of expressions includes an expression that cannot be directly computed by the target database. The mechanism constructs a derived table from the unsupported database query comprising constructing a new select list of the derived table, traversing the plurality of expressions of the select list of the unsupported database query, and adding a GROUP BY expression to the derived table based on the new select list of the derived table. The mechanism constructs the supported database query using the unsupported database query and the derived table.

Claims

1. A method of transforming an unsupported database query into a supported database query that can be fully computed by a target database, the unsupported database query comprising a select list including a plurality of expressions, the plurality of expressions each having a control break, the plurality of expressions including an expression that cannot be directly computed by the target database, the method comprising:

constructing a derived table from the unsupported database query, wherein constructing the derived table comprises;

constructing a new select list of the derived table;

traversing the plurality of expressions of the select list of the unsupported database query and for each of the expressions;

determining a corresponding expression to add to the new select list, wherein determining the corresponding expression to add to the new select list comprises determining an aggregate of the expression and determining a control break of the aggregate;

adding the corresponding expression to the new select list;

adding the control break of the expression to an ordered list of control breaks, wherein the ordered list is ordered from a shortest control break with a fewest number of columns to a longest control break with a highest number of columns; and

when the control break of the aggregate has fewer columns than the longest control break of the order list of control breaks, and the aggregate of the expression is of the form AGG(C₀);

replacing the expression AGG(C₀) OVER (PARTITION BY C₁, C₂, . . . , C_k) with an expression of the form AGG(AGG(C₀)) OVER(PARTITION C₁, C₂, . . . , C_k), where each C is a column, where n is a number of columns in the longest control break, and where k is a number of columns in the other control break; and

adding a GROUP BY expression to the derived table based on the new select list of the derived table;

determining the unsupported database query can be transformed into a supported database query, wherein determining the unsupported database query can be transformed into the supported database query comprises determining all control breaks of the unsupported database query are compatible based on the ordered list; and

constructing the supported database query using the unsupported database query and the derived table.

2. The method as claimed in claim 1, wherein constructing the supported database query comprises:

adding to a select list of the supported database query, expressions of the select list of the unsupported database query with a standard expression referencing the corresponding expression added to the new select list of the derived table; and

replacing a FROM table of the unsupported database query with a reference to the derived table.

3. The method as claimed in claim 1, further comprising:

for each expression, determining the control break of the expression is not already in the ordered list prior to adding the control break of the expression to the ordered list.

4. The method as claimed in claim 1, the method further comprising, when the control break of the aggregate of the expression has no columns, and the aggregate of the expression is of the form AVG(C₀):

replacing the expression AVG(C₀) OVER ( ) with an expression of the form SUM(SUM(C₀)) OVER( )/SUM(COUNT(C₀)) OVER( ), where C₀ is a column.

5. The method as claimed in claim 4, further comprising, when the control break of the aggregate of the expression has one or more columns found in the longest control break in the ordered list, and the aggregate of the expression is of the form AVG(C0):

replacing the expression AVG(C₀) OVER (PARTITION BY C₁, C₂, . . . , C_n)) with an expression of the form SUM(SUM(C₀) OVER (PARTITION BY C₁, C₂, . . . , C_n)) OVER( )/SUM(COUNT(C₀) OVER (PARTITION BY C₁, C₂, . . . , C_n)) OVER( ), where each C is a column and where n is the number of columns in the longest control break.

6. The method as claimed in claim 1, the method further comprising, when the control break of the aggregate is the same as the longest control break of the order list of control breaks, and the aggregate of the expression is of the form AGG(C₀):

replacing the expression AGG(C₀) OVER (PARTITION BY C₁, C₂, . . . , C_n) with an expression of the form AGG(C₀), where each C is a column and where n is the number of columns in the longest control break.

7. The method as claimed in claim 1, where aggregates of the form AGG comprise one of MIN, MAX, SUB, COUNT.

8. The method as claimed in claim 1, wherein determining the unsupported database query can be transformed into a supported database query further comprises:

determining a DISTINCT keyword is present in the select list of the unsupported database query;

determining all aggregates of the expressions are eligible for transformation; and

determining simple column references in the select list of the unsupported database query are found in the longest control break of the unsupported database query, when the simple column references are not part of a speciation of one of the aggregates of the unsupported database query.

9. The method as claimed in claim 8, wherein determining an aggregate is eligible comprises:

determining the aggregate is a report aggregate that has a standard aggregate counterpart.

10. The method as claimed in claim 8, wherein determining all control breaks are compatible comprises:

determining the longest control break is of the form (C₁, C₂, . . . , C_n) and all other control breaks are of the form ( ) or of the form (C₁, C₂, . . . , C_k) where 0<

=k<

=n, where;

each C is a column;

n is the number of columns in the longest control break; and

k is the number of columns in the other control break.

11. A computer program product comprising a computer readable memory having computer readable code embodied therein for use in the execution in a computer of a method of transforming an unsupported database query into a supported database query that can be fully computed by a target database, the unsupported database query comprising a select list including a plurality of expressions, the plurality of expressions each having a control break, the plurality of expressions including an expression that cannot be directly computed by the target database, the computer comprising a processor coupled to the computer storage medium for executing the computer readable code, the method comprising:

constructing a derived table from the unsupported database query, wherein constructing a derived table from the unsupported database query comprises;

constructing a new select list of the derived table;

traversing the plurality of expressions of the select list of the unsupported database query and for each of the expressions;

determining a corresponding expression to add to the new select list, wherein determining the corresponding expression to add to the new select list comprises determining an aggregate of the expression and determining the control break of the aggregate;

adding the corresponding expression to the new select list;

adding the control break of the expression to an ordered list of control breaks, wherein the ordered list is ordered from a shortest control break with a fewest number of columns to a longest control break with a highest number of columns; and

when the control break of the aggregate has fewer columns than the longest control break of the order list of control breaks, and the aggregate of the expression is of the form AGG(C₀);

replacing the expression AGG(C₀) OVER (PARTITION BY C₁, C₂, . . . , C_k) with an expression of the form AGG(AGG(C₀)) OVER (PARTITION C₁, C₂, . . . , C_k), where each C is a column, where n is a number of columns in the longest control break, and where k is a number of columns in the other control break; and

adding a GROUP BY expression to the derived table based on the new select list of the derived table;

determining the unsupported database query can be transformed into a supported database query, wherein determining the unsupported database query can be transformed into a supported database query comprises determining all control breaks of the unsupported database query are compatible based on the ordered list; and

constructing the supported database query using the unsupported database query and the derived table.

12. The computer program product as claimed in claim 11, wherein constructing the supported database query comprises:

adding to a select list of the supported database query, expressions of the select list of the unsupported database query with a standard expression referencing the corresponding expression added to the new select list of the derived table; and

replacing a FROM table of the unsupported database query with a reference to the derived table.

13. The computer program product as claimed in claim 11, the method further comprising:

when the control break of the aggregate of the expression has no columns, and the aggregate of the expression is of the form AVG(C₀), replacing the expression AVG(C₀) OVER ( ) with an expression of the form SUM(SUM(C₀)) OVER( )/SUM(COUNT(C₀)) OVER( ) where C₀ is a column; and

when the control break of the aggregate of the expression has one or more columns found in the longest control break in the ordered list, and the aggregate of the expression is of the form AVG(C0), replacing the expression AVG(C₀) OVER (PARTITION BY C₁, C₂, . . . , C_n)) with an expression of the form SUM(SUM(C₀) OVER (PARTITION BY C₁, C₂, . . . , C_n)) OVER( )SUM(COUNT(C₀) OVER (PARTITION BY C₁, C₂, . . . , C_n))OVER( ), where each C is a column and where n is the number of columns in the longest control break.

14. The computer program product as claimed in claim 11, the method further comprising:

when the control break of the aggregate is the same as a longest control break of the order list of control breaks, and the aggregate of the expression is of the form AGG(C₀), replacing the expression AGG(C₀) OVER (PARTITION BY C₁, C₂, . . . , C_n) with an expression of the form AGG(C₀), where each C is a column and where n is the number of columns in the longest control break.

15. The computer program product as claimed in claim 11, wherein determining the unsupported database query can be transformed into a supported database query further comprises:

determining a DISTINCT keyword is present in the select list of the unsupported database query;

determining all aggregates of the expressions are eligible for transformation; and

determining simple column references in the select list of the unsupported database query are found in the longest control break of the unsupported database query, when the simple column references are not part of a speciation of one of the aggregates of the unsupported database query,

wherein determining an aggregate is eligible comprises determining the aggregate is a report aggregate that has a standard aggregate counterpart; and

wherein determining all control breaks are compatible comprises;

determining the longest control break is of the form (C₁, C₂, . . . , C_n) and all other control breaks are of the form ( ) or of the form (C₁, C₂, . . . , C_k) where 0<

=k<

=n, where;

each C is a column;

n is the number of columns in the longest control break; and

k is the number of columns in the other control break.

16. A computer comprising:

a processor; and

a computer readable memory having computer readable code embodied therein for use in the execution by the processor of a method of transforming an unsupported database query into a supported database query that can be fully computed by a target database, the unsupported database query comprising a select list including a plurality of expressions, the plurality of expressions each having a control break, the plurality of expressions including an expression that cannot be directly computed by the target database, the method comprising;

constructing a derived table from the unsupported database query, wherein constructing a derived table from the unsupported database query comprises;

constructing a new select list of the derived table;

traversing the plurality of expressions of the select list of the unsupported database query and for each of the expressions;

determining a corresponding expression to add to the new select list, wherein determining the corresponding expression to add to the new select list comprises determining an aggregate of the expression and determining the control break of the aggregate;

adding the corresponding expression to the new select list;

adding the control break of the expression to an ordered list of control breaks, wherein the ordered list is ordered from a shortest control break with a fewest number of columns to a longest control break with a highest number of columns;

when the control break of the aggregate has fewer columns than the longest control break of the order list of control breaks, and the aggregate of the expression is of the form AGG(C₀);

replacing the expression AGG(C₀) OVER (PARTITION BY C₁, C₂, . . . , C_k) with an expression of the form AGG(AGG(C₀)) OVER (PARTITION C₁, C₂, . . . , C_k), where each C is a column, where n is a number of columns in the longest control break, and where k is a number of columns in the other control break; and

adding a GROUP BY expression to the derived table based on the new select list of the derived table;

determining the unsupported database query can be transformed into a supported database query, wherein determining the unsupported database query can be transformed into a supported database query comprises determining all control breaks of the unsupported database query are compatible based on the ordered list; and

constructing the supported database query using the unsupported database query and the derived table.

17. The computer as claimed in claim 16, the method further comprising:

when the control break of the aggregate of the expression has no columns, and the aggregate of the expression is of the form AVG(C₀), replacing the expression AVG(C₀) OVER ( ) with an expression of the form SUM(SUM(C₀)) OVER( )/SUM(COUNT(C₀)) OVER( ) where C₀ is a column; and

when the control break of the aggregate of the expression has one or more columns found in the longest control break in the ordered list, and the aggregate of the expression is of the form AVG(C0), replacing the expression AVG(C₀) OVER (PARTITION BY C₁, C₂, . . . , C_n)) with an expression of the form SUM(SUM(C₀) OVER (PARTITION BY C₁, C₂, . . . , C_n)) OVER( )SUM(COUNT(C₀) OVER (PARTITION BY C₁, C₂, . . . , C_n))OVER( ) where each C is a column and where n is the number of columns in the longest control break.

18. The computer as claimed in claim 16, the method further comprising:

when the control break of the aggregate is the same as the longest control break of the order list of control breaks, and the aggregate of the expression is of the form AGG(C₀), replacing the expression AGG(C₀) OVER (PARTITION BY C₁, C₂, . . . , C_n) with an expression of the form AGG(C₀), where each C is a column and where n is the number of columns in the longest control break.

19. The computer as claimed in claim 16, wherein determining that the unsupported database query can be transformed into a supported database query further comprises:

determining a DISTINCT keyword is present in the select list of the unsupported database query;

determining all aggregates of the expressions are eligible for transformation; and

determining simple column references in the select list of the unsupported database query are found in the longest control break of the unsupported database query, when the simple column references are not part of a speciation of one of the aggregates of the unsupported database query,

wherein determining an aggregate is eligible comprises determining that the aggregate is a report aggregate that has a standard aggregate counterpart; and

wherein determining all control breaks are compatible comprises;

determining the longest control break is of the form (C₁, C₂, . . . , C_n) and all other control breaks are of the form ( ) or of the form (C₁, C₂, . . . , C_k) where 0<

=k<

=n, where;

each C is a column;

n is the number of columns in the longest control break; and

k is the number of columns in the other control break.