SQLite query with TOTAL() returns empty row, throws ConstraintException on read

Question

I have a SQL query which uses the TOTAL() aggregate function:

SELECT
    c.Id,
    c.Name,
    ...
    b.BatchNumber,
    TOTAL(d.OrderedAmount) as TotalOrderedAmount
FROM OrderProducts a
LEFT JOIN WarehouseProducts b ON a.WarehouseProductId = b.Id
...
WHERE a.OrderId = @OrderId
AND (e.Status = @OrderedStatus OR e.Status IS NULL)

^{(Extraneous rows removed for clarity)}

This query returns at least one row even when no results are found, causing every column to be DBNull (I assume). When I try to load the results of this query into a DataTable, I get an ConstraintException because of the null values.

If I substitute the TOTAL() row with 0 as TotalOrderedAmount, the number of rows returned is 0 and everything works fine.

I've tried using WHERE a.Id IS NOT NULL but whatever I try, there always seems to be at least one row returned.

How can I modify this query so that when no products are found, the number of rows returned is 0, even with the aggregate function?

Possible workaround is the extension method in the answer to [this question](http://stackoverflow.com/questions/7580929/mysqldatareader-datatable-fillreader-throws-constraintexception) that would allow you to load the DataTable without the constraint. — davmos, May 04 '13 at 22:49

score 1 · Accepted Answer · answered May 05 '13 at 10:04

That's how aggregate queries in SQL work. The SQLite documentation says about this:

If the SELECT statement is an aggregate query without a GROUP BY clause, then each aggregate expression in the result-set is evaluated once across the entire dataset. Each non-aggregate expression in the result-set is evaluated once for an arbitrarily selected row of the dataset. The same arbitrarily selected row is used for each non-aggregate expression. Or, if the dataset contains zero rows, then each non-aggregate expression is evaluated against a row consisting entirely of NULL values.

The single row of result-set data created by evaluating the aggregate and non-aggregate expressions in the result-set forms the result of an aggregate query without a GROUP BY clause. An aggregate query without a GROUP BY clause always returns exactly one row of data, even if there are zero rows of input data.

To allow an empty result, you have to remove the aggregate function TOTAL from the outermost query. To still get this value (if there is a result record), use a subquery:

SELECT
    c.Id,
    c.Name,
    ...
    b.BatchNumber,
    (SELECT TOTAL(d.OrderedAmount)
     FROM SomeTable d
     WHERE d.x = c.y  -- or however d is related to the other tables
    ) AS TotalOrderedAmount
FROM OrderProducts a
JOIN ...              -- without d here
WHERE ...

SQLite query with TOTAL() returns empty row, throws ConstraintException on read

1 Answers1