SQL Generation for Assert Update Delete Operations

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Massively parallel processing systems face complexity and scalability limitations due to the need for custom communications protocols between control nodes and compute nodes in data warehouse solutions, which restricts the use of off-the-shelf relational database systems and complicates the execution of optimized query plans containing operations without direct analogues in Structured Query Language (SQL).

Innovation Solution

A method to generate semantically equivalent SQL statements from optimized query plans, using constructs like CASE expressions for assert operators and decoding update and delete operations to ensure accurate execution on compute nodes, allowing the use of industry-standard languages and protocols for communication.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Adaptability or versatility

If a custom communications protocol is developed between control node and compute nodes, then the system can execute optimized query plans with operations like assert, update and delete, but the device complexity increases significantly and off-the-shelf relational database systems cannot be used

Engineering Contradiction:

Improvecapability to execute optimized query plansVSAvoidcommunications protocol complexity

Core Design Contradiction:

Adaptability or versatilityVSDevice complexity

Solution Approach 1:

The patent introduces an intermediary translation layer that converts optimized query plan operations (assert, update, delete) into equivalent SQL statements. This mediator component translates the custom protocol operations into standard SQL that off-the-shelf database systems can execute, thereby reducing protocol complexity while maintaining execution capability.

Inventive Principle:

Principle #24Intermediary (Mediator)

Solution Approach 2:

The patent creates semantic copies of query plan operations by generating equivalent SQL statements that replicate the behavior of assert, update and delete operations. Instead of requiring native support for these operations in the database system, the invention copies their functionality through SQL equivalents that produce the same execution results.

Inventive Principle:

Principle #26Copying

2Device complexity

If off-the-shelf relational database systems are used on compute nodes, then device complexity is reduced and scalability is improved, but the system loses the ability to execute certain query plan operations without direct SQL analogues

Engineering Contradiction:

Improvecommunications protocol complexityVSAvoidcapability to execute query plan operations

Core Design Contradiction:

Device complexityVSAdaptability or versatility

Solution Approach 1:

The patent transforms the parameter representation of query plan operations by converting them into SQL-compatible parameters and syntax. Operations like assert become CASE expressions, updates become INSERT/UPDATE statements, and deletes become DELETE statements, allowing standard database systems to execute these operations through parameter transformation.

Inventive Principle:

Principle #35Parameter changes

3Ease of operation

If SQL is used for communication between control node and compute nodes, then ease of operation is improved and off-the-shelf systems can be used, but generating semantically equivalent SQL statements from query plans is difficult

Engineering Contradiction:

Improveuse of industry standard languagesVSAvoidSQL generation complexity

Core Design Contradiction:

Ease of operationVSDevice complexity

Solution Approach 1:

The patent segments the SQL generation process into distinct translation rules for different query plan operations. Each operation type (assert, update, delete, select) has its own translation template and rule set, making the generation process systematic and manageable rather than requiring complex holistic transformation.

Inventive Principle:

Principle #1Segmentation

4Reliability

If custom protocol is used to maintain semantic equivalence under failure conditions, then reliability is improved, but ease of manufacture worsens due to inability to use off-the-shelf systems

Engineering Contradiction:

Improvesemantic equivalence under error conditionsVSAvoidsystem implementation ease

Core Design Contradiction:

ReliabilityVSEase of manufacture

Solution Approach 1:

The patent enables self-service by allowing standard relational database systems to execute translated SQL statements without requiring custom protocol support. The translation layer handles the complexity of maintaining semantic equivalence, while the database systems themselves serve their standard function, improving ease of manufacture through use of off-the-shelf components.

Inventive Principle:

Principle #25Self-service

Data Source

PatentUS9317554B2SQL generation for assert, update and delete relational trees

Publication Date: 2016.04.19 MICROSOFT TECHNOLOGY LICENSING LLC

AI summary

Presented is a system and method for evaluating relational database queries in a distributed system. An optimized query plan is received by a control node. The query plan is decoded to a SQL statement that is semantically equivalent to the query plan, even though the query plan contains elements that have no direct analog in SQL. The decoded SQL is transmitted to a compute node for execution.