Copyright © 2024 PawSQL

Definition​

SAT-TC(Satisfiability-Transitive Closure) rewrite rule analyzes a set of predicates and try to determine if

1. there is a contradiction (e.g., c_custkey=1 AND c_custkey=0), or
2. new predicates can be inferred from the set (e.g., c_custkey=1 AND c_custkey=o_custkey implies o_custkey=1.
3. predicates can simplified (e.g. c_custkey <> c_custkey or c_name = 'b' can be simplified to c_name = 'b')

SAT and TC optimizations are two closely related and interacting optimization techniques, often used together.

Copyright © 2024 PawSQL

Problem Definition​

NPE (Null Pointer Exception) issues in SQL refer to cases where aggregate functions like SUM, AVG return NULL when the aggregated column is all NULL, which can then cause null pointer exceptions in subsequent program logic.

select sum(t.b) from (values row(1,null)) as t(a,b);

This can be avoided by using:

SELECT IFNULL(SUM(t.b), 0) from (values row(1,null)) as t(a,b);

Copyright © 2024 PawSQL

Problem Definition​

In earlier versions of MySQL, GROUP BY would default to sorting by the grouping fields even without an ORDER BY clause. This could cause unnecessary file sorting which impacts SQL query performance. Adding ORDER BY NULL forces disabling sorting on the result set.

For example in the following:

SELECT l_orderkey, sum(l_quantity) 
FROM lineitem
GROUP BY l_orderkey;

Copyright © 2024 PawSQL

Definition​

By enabling Query Folding rule, PawSQL merges view references, derived tables and common table expressions to outer query blocks to:

• avoid materializing intermediate result-set
• enable more join order planning
• enable more opportunities for index recommendation

We will use view in following paragraph, but all the descriptions also apply to derived tables and common table expressions.

Copyright © 2024 PawSQL

explain analyze 
select * from orders where o_orderdate < all(select o_orderdate from orders where o_custkey > 100)
;

-> Filter: <not>((orders.O_ORDERDATE >= (select #2)))  (cost=13846.04 rows=133181) (actual time=672.536..672.536 rows=0 loops=1)
    -> Table scan on orders  (cost=13846.04 rows=199762) (actual time=2.460..408.819 rows=201800 loops=1)
    -> Select #2 (subquery in condition; run only once)
        -> Aggregate: min(orders.O_ORDERDATE)  (cost=30281.45 rows=1) (actual time=214.765..214.765 rows=1 loops=1)
            -> Filter: (orders.O_CUSTKEY > 100)  (cost=20293.35 rows=99881) (actual time=0.237..202.460 rows=199252 loops=1)
                -> Covering index range scan on orders using comp_idx over (100 < O_CUSTKEY)  (cost=20293.35 rows=99881) (actual time=0.138..189.628 rows=199252 loops=1)