Why was this EXCEPTION raised for SQLCODE 109 Null value eliminated in aggregate?

[Breck_Carter]

When run under SQL Anywhere 12.0.1.3298, the following code raised the following exception: SQLCODE = 109, SQLSTATE = 01003, ERRORMSG() = Null value eliminated in aggregate function.

This appears to be an isolated incident.

There are two problems with this:

First, SQLCODE 109 isn't an error, it's a warning, and it shouldn't raise an exception.

Second, none of the code that could have raised the exception contains a query, let alone an aggregate function.

Here's the full diagnostic message:

2012-07-07 10:15:45.084 Full Build ----- 1000001799 211a4g1(211eh2) SQLCODE = 109, SQLSTATE = 01003, 
   ERRORMSG() = Null value eliminated in aggregate function [---]

The substring "211a4g1" indicates the exception was raised somewhere between these two assignment statements:

SET @diagnostic_location = '211a4g1';
...
SET @diagnostic_location = '211a4g2';

The substring "(211eh2)" means the EXCEPTION handler is the one at the bottom of the following code. These substrings are unique across all the code in the database.

(PLEASE IGNORE the fact that indenting has been ignored for random lines.)

CREATE PROCEDURE rroad_monitor_alerts (
...
         BEGIN -- ignore all exceptions

-- Note: The datediff_msec_between_target_and_local value is used to adjust the target rroad_group_2_property_pivot.TransactionStartTime to Foxhound time.

SELECT MAX ( DATEDIFF ( SECOND, 
                                    IF rroad_group_2_property_pivot.TransactionStartTime IN ( '', '1900-01-01', '0000-01-00 00:00:00.000000' )
                                       THEN @sample_recorded_at
                                       ELSE rroad_f_dateadd_msec ( rroad_sample_set.datediff_msec_between_target_and_local,  
                                                                   rroad_group_2_property_pivot.TransactionStartTime )
                                    ENDIF, 
                                    @sample_recorded_at ) ) 
                      * 1000 
              INTO @alert28_max_transaction_time
              FROM rroad_group_2_property_pivot
                      INNER JOIN rroad_sample_set
                              ON rroad_sample_set.sample_set_number = rroad_group_2_property_pivot.sample_set_number
             WHERE rroad_sample_set.sampling_id                   = @sampling_id
               AND rroad_sample_set.sample_set_number             = @sample_set_number 
               AND rroad_group_2_property_pivot.sampling_id       = @sampling_id
               AND rroad_group_2_property_pivot.sample_set_number = @sample_set_number 
               AND rroad_group_2_property_pivot.TransactionStartTime NOT IN ( '', '1900-01-01', '0000-01-00 00:00:00.000000' );

EXCEPTION WHEN OTHERS THEN

SET @alert28_max_transaction_time = 0; -- ignore "Cannot convert '...' to a timestamp"

END; -- ignore all exceptions

----------------------------------
         -- Alert #28 increment / decrement

SET @diagnostic_location = '211a4g1';

IF @alert28_max_transaction_time >= @alert28_threshold_max_transaction_time THEN

-- Increment the counter by 1.

SET @alert28_actual_max_transaction_time_threshold_reached_sample_counter = @alert28_actual_max_transaction_time_threshold_reached_sample_counter + 1;

ELSE

IF @alert28_actual_max_transaction_time_threshold_reached_sample_counter > @alert28_threshold_max_transaction_time_sample_count THEN

-- Pull the counter back down to the threshold so decrementing to zero can begin.

SET @alert28_actual_max_transaction_time_threshold_reached_sample_counter = @alert28_threshold_max_transaction_time_sample_count;

ELSE

-- Decrement the counter by 2, so it sinks to zero faster than it rose to the threshold.

SET @alert28_actual_max_transaction_time_threshold_reached_sample_counter = GREATER ( 0, @alert28_actual_max_transaction_time_threshold_reached_sample_counter - 2 );

END IF;

END IF;

----------------------------------
         -- Alert #29 increment / decrement

SET @diagnostic_location = '211a4g2';
...
EXCEPTION

WHEN OTHERS THEN

SELECT SQLCODE, SQLSTATE, ERRORMSG() 
        INTO @sqlcode, @sqlstate, @errormsg;

CASE

WHEN ( @sqlcode = 0 AND @sqlstate = '00000' ) THEN
...
         WHEN @suspect_sample_set_number IS NOT NULL
          AND @sqlcode IN ( -1280,        -- Underflow when converting '%1'
                            -638,         -- Right truncation of string data    
                            -628,         -- Division by zero
                            -158,         -- Value %1 out of range for destination
                            -157 ) THEN   -- Cannot convert %1 to a %2
...
         ELSE

-----------------------------------------------------------------------
            -- Report and continue.

CALL rroad_exception ( STRING ( 
               @diagnostic_location, '(211eh2)', 
               ' SQLCODE = ', @sqlcode,  
               ', SQLSTATE = ', @sqlstate,  
               ', ERRORMSG() = ', @errormsg ) );

END CASE;
...
2012-07-07 10:15:45.084 Full Build ----- 1000001799 211a4g1(211eh2) SQLCODE = 109, SQLSTATE = 01003, 
   ERRORMSG() = Null value eliminated in aggregate function [---]

[MCMartin]

Maybe @alert28_actual_max_transaction_time_threshold_reached_sample_counter is NULL when reaching the Greater(...) function?

[Breck_Carter]

SET is not a query, and GREATER() is not an aggregate function.

[VolkerBarth]

Hmm, I don't think GREATER would raise a warning - it's not an AGGREGATE function, and GREATER(1, NULL) returns NULL as expected...