can't get warning 01003 in nested select in update

[有你就不孤单 <87...@qq.com>]

hi, 

Below is the SQL details in derby ij:

1, list the column salary is null record.
ij(CONNECTION1)> select empno, salary from emp where salary is null;
EMPNO |SALARY
-----------------
001188|NULL
000010|NULL

2 rows selected

2， when run select with function avg lonely, can get the warning.
ij(CONNECTION1)> SELECT AVG ( T2 . SALARY ) FROM APP.EMP T2;
1
----------------
28363.2926
WARNING 01003: Null values were eliminated from the argument of a column functio
n.

1 row selected

3, when the select in an update statement, can't get the warning 01003.
ij(CONNECTION1)> UPDATE APP.EMP T1 SET SALARY = ( SELECT AVG ( T2 . SALARY ) FRO
M APP.EMP T2 ) WHERE WORKDEPT = 'D21' AND SALARY < ( SELECT AVG ( T3 . SALARY )
FROM APP.EMP T3 );
7 rows inserted/updated/deleted
ij(CONNECTION1)>

And I also tried JDBC call derby, also the pure select can get the warning, the update one also can't get the warning message, do you think this is a defect? Thanks for your help :).

Re: can't get warning 01003 in nested select in update

[Rick Hillegas <ri...@gmail.com>]

On 5/16/15 7:25 AM, Bryan Pendleton wrote:
>> 3, when the select in an update statement, can't get the warning 01003.
>> ij(CONNECTION1)> UPDATE APP.EMP T1 SET SALARY = ( SELECT AVG ( T2 . 
>> SALARY ) FRO
>> M APP.EMP T2 ) WHERE WORKDEPT = 'D21' AND SALARY < ( SELECT AVG ( T3 
>> . SALARY )
>> FROM APP.EMP T3 );
>> 7 rows inserted/updated/deleted
>
> The lack of the warning seems incorrect to me. But I'm not sure if this
> behavior is governed by the SQL Standards or not.
>
> thanks,
>
> bryan
>
>
>
Nulls are ignored when computing aggregates, but they should cause the 
statement to raise a warning, as specified in the 2011 SQL Standard, 
part 2, section 10.9 (aggregate function), General Rule 6a. I don't see 
anything in the Standard which absolves the UPDATE statement of its 
responsibility to report a warning encountered during the processing of 
an aggregate. So, I agree. This looks like a bug to me.

Please feel free to log a bug.

Thanks,
-Rick

Re: can't get warning 01003 in nested select in update

[Bryan Pendleton <bp...@gmail.com>]

> 3, when the select in an update statement, can't get the warning 01003.
> ij(CONNECTION1)> UPDATE APP.EMP T1 SET SALARY = ( SELECT AVG ( T2 . SALARY ) FRO
> M APP.EMP T2 ) WHERE WORKDEPT = 'D21' AND SALARY < ( SELECT AVG ( T3 . SALARY )
> FROM APP.EMP T3 );
> 7 rows inserted/updated/deleted

The lack of the warning seems incorrect to me. But I'm not sure if this
behavior is governed by the SQL Standards or not.

thanks,

bryan