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

asked 24 Mar '16, 10:43

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
2.5k213458
accept rate: 16%

1

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.

(24 Mar '16, 10:57) Volker Barth
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×190
×5

question asked: 24 Mar '16, 10:43

question was seen: 252 times

last updated: 24 Mar '16, 11:04