Summary

This issue proposes the implementation of Common Sub-expression Elimination (CSE) as an optimization technique within the SQL query engine. CSE aims to improve query performance by identifying and evaluating redundant expressions only once, and reusing it throughout the query execution. This is particularly beneficial for expressions that are computationally expensive, such as those involving complex function calls, JSON data extraction, or regular expression matching.

Example:

Consider the following SQL query that extracts payload from a Variant column:

SELECT
    get(payload, 'field1') AS field1_value,
    get(payload, 'field2') AS field2_value,
    CASE
        WHEN get(payload, 'field1') = 'some_value' THEN 'Condition Met'
        ELSE 'Condition Not Met'
    END AS condition_status
FROM
    my_table
WHERE
    lower(get(payload, 'field1')) LIKE '%term1%';
    AND lower(get(payload, 'field2')) LIKE '%term2%';

In this example, the expression get(payload, 'field1') is evaluated three times, we can extract this expression as a dervied column and evaluate only once.

Benefits:

• Reduced query execution time, especially for queries with complex and redundant expressions.
• Reduced resource consumption (CPU, memory).
• Automatic optimization without requiring user intervention.