Fixing SQL Queries That Miscount or Fail: Aggregates, Casts and Diagnosis

1.Aggregates that miscount

A query can return the right rows and still report the wrong totals. All aggregate functions skip NULL values, except COUNT(*). So COUNT(*) and COUNT(column) answer different questions. COUNT(*) counts rows. COUNT(column) counts the non-NULL values in that column.

-- `count(*)` does not skip `NULL` values.
> SELECT count(*) FROM person;
 count(1)
 --------
        7

-- `NULL` values in column `age` are skipped from processing.
> SELECT count(age) FROM person;
 count(age)
 ----------
          5

The same NULL-skipping explains a subtler bug. AVG(age) divides by the 5 known ages, not by all 7 people. Empty input also behaves differently depending on the function. COUNT(*) on an empty set returns 0, but MAX, MIN, SUM, AVG, EVERY, ANY and SOME return NULL when every input is NULL or there are no rows. If a dashboard tile goes blank instead of showing 0, the cause is often a SUM over a filter that matched nothing.

Scalar expressions cause the same kind of surprise. Most functions are *null-intolerant*: concat('John', null) returns NULL, not 'John'. The fix is a function designed for NULL input, such as COALESCE, IFNULL or NVL. COALESCE returns its first non-NULL argument. It returns NULL only when every argument is NULL.

-- Returns the first occurrence of non `NULL` value.
> SELECT coalesce(null, null, 3, null) AS expression_output;
 expression_output
 -----------------
                 3

Sources1

2.GROUP BY errors and conditional aggregates

Grouping mistakes usually show up as named errors, which makes them easy to read once you know the names. An aggregate inside the GROUP BY list, such as GROUP BY sum(x), raises GROUP_BY_AGGREGATE. A column position that does not exist in the SELECT list raises GROUP_BY_POS_OUT_OF_RANGE, and a position that points to an aggregate raises GROUP_BY_POS_AGGREGATE. The fix is to group only by the non-aggregated expressions.

GROUP BY ALL (Databricks Runtime 12.2 LTS and above) builds that list for you. It adds every SELECT-list expression that does not contain an aggregate. It is not guaranteed to produce a valid clause, though. If it can't, Databricks raises UNRESOLVED_ALL_IN_GROUP_BY or MISSING_AGGREGATION.

Another common logic error is filtering with WHERE when only one aggregate should be restricted. WHERE removes rows from every aggregate in the query. A FILTER (WHERE ...) clause attached to a single aggregate restricts only that one, so the other totals in the same SELECT still see every row.

Sources2

3.Casts and overflow: errors versus silent NULLs

Whether a bad type conversion fails loudly or quietly depends on ANSI mode. When spark.sql.ansi.enabled is true, an illegal cast such as string to integer, or an arithmetic overflow, throws an error at runtime. When it is false, cast('a' AS INT) returns NULL, 2147483647 + 1 wraps around to a negative number, and a decimal overflow returns NULL. In other words, the same query can give wrong answers without complaint under one setting and fail under the other.

-- `spark.sql.ansi.enabled=true`
> SELECT CAST('a' AS INT);
  ERROR: [CAST_INVALID_INPUT] The value 'a' of the type "STRING" cannot be cast to "INT" because it is malformed.

> SELECT CAST(2147483648L AS INT);
  ERROR: [CAST_OVERFLOW] The value 2147483648L of the type "BIGINT" cannot be cast to "INT" due to an overflow.

Some casts are invalid in either mode. In the cast table, Date to Numeric is marked N, so CAST(DATE'2020-01-01' AS INT) fails with DATATYPE_MISMATCH under ANSI and returns NULL without it. The error name tells you which fix is needed. CAST_INVALID_INPUT means the data contains values that don't parse. CAST_OVERFLOW means the target type is too narrow. A mismatch means the conversion itself is not allowed. Inserts behave the same way: spark.sql.storeAssignmentPolicy defaults to ANSI, which rejects a mismatched INSERT outright. Under LEGACY, the same INSERT writes NULLs or wrong values instead.

Sources3

4.Finding the faulty step: query profile and Genie Code

When a query is wrong in a way that only shows up at scale, such as an exploding join, the query profile makes the mistake visible. It shows each operator with its time spent and rows processed, so a join that outputs far more rows than it reads stands out. After you fix the query, the profile also lets you check the effect of the change. To open it, you must own the query or have at least CAN MONITOR on the SQL warehouse.

For queries that fail outright, Genie Code can propose the fix. Quick Fix suggests a correction for basic errors that need only a one-line change. Diagnose Error appears in the cell results when an error occurs, and clicking it runs /fix. /fix shows the proposed change in a diff view, where you choose Accept or Reject. Accepting does not run the code, so you can review it before running it.

Sources45

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.COUNT(column) and COUNT(*) return the same number, so they are interchangeable.Why is that wrong?

   Every aggregate except COUNT(*) skips NULLs, so COUNT(column) counts only the non-NULL values in that column.

   Covered in Aggregates that miscount

2. 2.If a CAST is invalid, the query always fails, so a query that runs has clean conversions.Why is that wrong?

   With ANSI mode off, an invalid cast such as cast('a' AS INT) returns NULL, and integer overflow wraps around, with no error raised.

   Covered in Casts and overflow: errors versus silent NULLs

Practise it for real

Reproduce and fix the common join mistakes on the reference employee/department views

1. 1.Create the employee (6 rows) and department (3 rows) temp views from the JOIN reference, then run the INNER JOIN on employee.deptno = department.deptno.

   Why: This gives you a baseline count for matching rows only.

   You should see: 3 rows: Paul, John and Lisa.

2. 2.Change INNER JOIN to LEFT JOIN and run it again.

   Why: This shows how to keep unmatched rows when the question asks for every employee.

   You should see: 6 rows, with NULL deptname for Chloe, Evan and Amy.

3. 3.Remove the ON clause from the join and run it.

   Why: This reproduces the missing-condition bug.

   You should see: 18 rows: every employee paired with every department.

4. 4.Run SELECT * FROM employee JOIN department USING (name);

   Why: This reproduces a USING column that exists in only one table.

   You should see: Error UNRESOLVED_USING_COLUMN_FOR_JOIN; switch to USING (deptno) to fix it.