Skip to main content

NPE Rewrite

Copyright © 2023 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);

Or:

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

Or:

SELECT case when SUM(t.b) is null then 0 else sum(t.b) end from (values row(1,null)) as t(a,b);

Which will return 0 instead of NULL, avoiding the null pointer exception.

Oracle: NVL(); SQL Server and MS Access: ISNULL(); MySQL: IFNULL() or COALESCE();

Rewrite Optimization​

Trigger Conditions​

  • SUM or AVG aggregate function
  • Aggregate parameter can be all NULL, including:
    • Parameter is column, column can be NULL
    • Parameter is expression, expression can be NULL
    • Column not NULL

Database Types and Versions​

  • MySQL
  • openGauss
  • Oracle
  • PostgreSQL
  • KingbaseES
  • MariaDB

About PawSQL​

PawSQL is dedicated to automatic and intelligent database performance optimization. The database types supported include MySQL, PostgreSQL, Oracle, and openGauss; and the products provided by PawSQL include:

  • PawSQL Cloud, an online automated SQL optimization tool that supports SQL auditing, intelligent query rewriting, cost-based index recommendations, suitable for database administrators and data application developers.
  • PawSQL Advisor, an IntelliJ plugin that is suitable for data application developers and can be installed via the IDEA/DataGrip marketplace by searching for "PawSQL Advisor" by name.
  • PawSQL Engine, which is the backend optimization engine of the PawSQL series of products, can be installed and deployed independently, and provides SQL optimization services through http/json interfaces. PawSQL Engine is provided for deployment and installation as a docker image.

Contact Us​

Website: https://docs.pawsql.com

Email: service@pawsql.com

Twitter: https://twitter.com/pawsql