IBM Support

JR32930: Premature return of SQL811N or SQL801N from a subquery with non-deterministic function in SET clause of an UPDATE statement

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • Premature return of SQL811N or SQL801N from a subquery with
    non-deterministic function in the SET clause of an UPDATE
    statement  because the subquery in the SET clause is evaluated
    before the WHERE predicate.
    
    Example 1: SQL0811N:
    create table a1 (i1 int, i2 int);
    create table a2 (i1 int, i2 int);
    insert into a1 values (1,1), (2,2);
    insert into a2 values (1,1), (2,2);
    
    update a1 set i1 = (select i1 from a2 where 1 = case when (1=1)
    then 1 else cast(raise_error('','' ) as int) end) where i2 = 3;
    DB21034E The command was processed as an SQL statement because
    it was not a valid Command Line Processor command. During SQL
    processing it returned:
    SQL0811N The result of a scalar fullselect, SELECT INTO
    statement, or VALUES
    INTO statement is more than one row. SQLSTATE=21000
    
    Example 2: SQL0801N:
    update a1 set i1 = (select 1/VALUE(0,0) from a2  where 1 = case
    when (1=1) then 1 else cast(raise_error('','' ) as int) end)
    where i2 = 3;
    DB21034E The command was processed as an SQL statement because
    it was not a
    valid Command Line Processor command. During SQL processing it
    returned:
    SQL0801N Division by zero was attempted. SQLSTATE=22012
    

Local fix

Problem summary

  • Users Affected:
    All users prior to V95 FP5
    
    Problem Description:
    Premature return of SQL811N or SQL801N from a subquery with
    non-deterministic function in SET clause of an UPDATE statement
    

Problem conclusion

  • Problem Conclusion:
    First Fixed in DB2 V95 FP5
    

Temporary fix

Comments

APAR Information

  • APAR number

    JR32930

  • Reported component name

    DB2 UDB WSE WIN

  • Reported component ID

    5765F3501

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2009-05-08

  • Closed date

    2009-12-17

  • Last modified date

    2009-12-17

  • APAR is sysrouted FROM one or more of the following:

    JR27091

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    DB2 UDB WSE WIN

  • Fixed component ID

    5765F3501

Applicable component levels

  • R950 PSY

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"950","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
17 December 2009