SQL Query Rewriting for Outer Join Null Sensitivity
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Rewriting SQL queries involving outer joins and Derived Tables (DTs) with null sensitive expressions (NSEs) can lead to incorrect results due to the inability to determine null values after joins have been made, particularly when views or DTs are folded, limiting the optimizer's join planning options and resulting in poor performance.
Innovation Solution
A method and system for rewriting SQL queries by identifying unmatching rows using non-nullable fields, converting NSEs into SQL case constructs, and performing outer joins, ensuring correct execution of NSEs as null if the row is unmatching, thereby avoiding materialization of views and allowing folding of views or DTs.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If views or Derived Tables are folded (rewritten without reference to the view or DT), then query performance is improved, but incorrect results may be produced when null sensitive expressions are involved in outer joins
Solution Approach 1:
The patent applies preliminary action by introducing a null_sensitivity_flag during the view or DT folding process. This flag is set before the actual query execution to mark NSEs that require special handling. The flagging occurs in advance, allowing the optimizer to proceed with folding while maintaining correctness through the预先 established null sensitivity markers.
Solution Approach 2:
The patent uses an intermediary mechanism by introducing a case expression as a mediator between the folded view/DT and the final query results. The case expression acts as a conditional evaluator that checks the null_sensitivity_flag and appropriately handles NSEs during outer joins, ensuring correct results while allowing the folding optimization to proceed.
2Reliability
If views are materialized into spools to ensure correct results with outer joins, then query correctness is maintained, but join planning options are limited and performance may deteriorate
Solution Approach 1:
The patent extracts the null sensitivity information from the view or DT definition and represents it as a separate null_sensitivity_flag. This extraction allows the optimizer to work with the folded view/DT without materialization, while the extracted flag information guides the correct evaluation of NSEs during query execution, avoiding the performance penalty of spool creation.
3Device complexity
If NSEs are executed during the last step of the query, then expression evaluation is simplified, but there is no way to determine null values for un-matching rows after joins have been made
Solution Approach 1:
The patent applies preliminary action by attaching the null_sensitivity_flag to NSEs before the join operations are performed. This preliminary marking preserves the information needed to determine null values for un-matching rows, while allowing the NSEs to be executed at the last step as originally planned. The flag ensures that null determination capability is not lost despite delayed evaluation.
Data Source
AI summary
The invention relates to a method and database system for rewriting a SQL query having at least one null sensitive expression (NSE) in a select list of the query, the query comprising at least one outer join command to be performed on at least two tables and involving a view or a Derived Table (DT). The method comprising the steps of: identifying at least one unmatching row belonging to the view or DT using non-nullable fields that belong to the outermost table in the view or DT; converting the NSE(s) from the at least one identified rows into a SQL case construct; and performing the at least one outer join. The database system comprising means for rewriting a SQL query having at least one null sensitive expression (NSE) in a select list of the query, the query comprising at least one outer join command to be performed on at least two tables and involving a view or a Derived Table (DT). The system comprises a device configured to identify at least one unmatching row belonging to the view or DT using non-nullable fields that belong to the outermost table in the view or DT; a device configured to convert the NSE(s) from the at least one identified rows into a SQL case construct; and a device configured to perform the at least one outer join.