null value eliminated in set function
SQLSTATE 01003 is issued when an aggregate function (e.g., SUM, AVG, COUNT) silently ignores NULL values in its input set. This matches standard SQL behaviour but is flagged as a warning to alert the caller.
- 1Running an aggregate function such as SUM or AVG over a column that contains NULL values
Aggregating a column with NULLs.
SELECT AVG(salary) FROM employees; -- some salary values are NULL
expected output
WARNING: null value eliminated in set function
Fix 1
Use COALESCE to substitute NULLs before aggregating
WHEN When NULLs should be treated as zero or a default value in the aggregate.
SELECT AVG(COALESCE(salary, 0)) FROM employees;
Why this works
COALESCE replaces NULLs before the aggregate sees them, preventing silent exclusion.
Fix 2
Filter NULLs explicitly with WHERE
WHEN When NULLs represent unknown data that should be excluded intentionally.
SELECT AVG(salary) FROM employees WHERE salary IS NOT NULL;
Why this works
Making the exclusion explicit documents intent and suppresses the warning.
Class 01 — Warning
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev