31 posts tagged with "pawsql"

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)

Copyright © 2024 PawSQL

Definition​

By enabling Projection Pushdown rule, PawSQL eliminates the columns in derived tables, common table expressions and views(after resolving), which are not referenced in outer query blocks, to:

• reduce the IO cost
• reduce the network cost
• enable index recommendation of index-only strategy

This justification is similar to rule [RuleStarInSelectList], but PawSQL supplies a rewrite optimization instead of only warning for star in select list scenario.

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