Why does Count(exp) raise a Warning 'Null value eliminated in aggregate function'

[thomas_duemesnil]

Sometimes the Warning helps me find a Problem when I use SUM or AVG and the existence of null values can lead to a wrong/unexpected result. I have traced a not working Query in my app down to this.

Test Case

BEGIN
    declare local temporary table MyTest( Line integer, Val integer);
    insert into MyTest( Line, Val)
    values ( 1, 1), (2,null), (3,3);
    select * from MyTest;
    select count(Val) from MyTest;
END

In iSql I get the expected Result 2 which is fine. With a request level logging in the Database if find the following Warning which i don't get to see in iSql but broke my Query over ODBC.

=,W,38,109,Null value eliminated in aggregate function

This is for me unexpected because the documentation for COUNT function I can read

Return the number of rows in each group where the value of expression is not NULL

So it is expected that COUNT(expression) is used on Columns that can contain NULL Values.

With ansinull set to On, the evaluation of any aggregate function, except COUNT(*), on an expression that contains at least one NULL value, may generate a warning (SQLSTATE=01003). See Null value eliminated in aggregate function. ansinull option

Is there a switch that I can get these errors/warnings in iSQL ? When I debug a Query i would like to see these warnings.

I have checked select connection_property('ansinull') and it is 'On' in iSql.

I use 16.0.0 Build 2234.

Thomas

[VolkerBarth]

Interestingly, when leaving out the first SELECT statement, dbisqlc does return the warning whereas DBISQL does not (I'm using 12.0.1.4314 with ansinull set to the default "On"). - No, I don't have a clue.

---

This is for me unexpected

The fact that COUNT(AnyExpression) does raise a warning when "ansinull" is set to "On" is expected behaviour, methinks.