IBM Support

IZ51789: SQL0901N MAY BE ISSUED EXECUTING A QUERY CONTAINING PREDICATES ON MAX OR MIN FUNCTIONS AGGREGATED ON NESTED CASE EXPRESSIONS

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • SQLCODE SQL0901N may be issued while executing a query that
    satisfies the following conditions:
    - There is an equality predicate (EQPRD) in the query
    - One side of EQPRD is either a constant, host-variable or
    special register (CONST)
    - The other side of EQPRD is a column derived from the output of
    a MAX or MIN aggregate function
    - The MAX or MIN function aggregates the output of a CASE
    expression (CASEEXP)
    - CASEEXP has only 1 WHEN clause and either an ELSE NULL or no
    ELSE clause
    - The WHEN clause is also an EQPRD (WHENPRD)
    - One side WHENPRD is CONST
    - The other side WHENPRD is non-CONST
    - The THEN clause is a CASE expression (NESTCASE), i.e., nested
    inside CASEEXP
    - The THEN and ELSE clause(s) in NESTCASE do not contain
    expressions
    
    An example scenario:
    
    -- DDL
    CREATE TABLE T1 (C1 CHAR(3), C2 VARCHAR(1));
    INSERT INTO T1 VALUES ('ABC', 'A');
    INSERT INTO T1 VALUES ('BCD', 'B');
    
    -- Query
    SELECT CASE_COL_1,
           CASE_COL_2
    FROM
    (SELECT MAX(CASE WHEN C1 = 'ABC'
                    THEN (CASE WHEN C2 = ' '
                               THEN '-'
                               ELSE C2
                          END)
                END) AS CASE_COL_1,
            MIN(CASE WHEN C1 = 'BCD'
                    THEN (CASE WHEN C2 = ' '
                               THEN '-'
                               ELSE C2
                          END)
                END) AS CASE_COL_2
     FROM   T1
    ) T
    WHERE CASE_COL_1 = 'A';
    
    -- With just one such column, the -901 will be:
    SQL0901N  The SQL statement failed because of a non-severe
    system error.
    Subsequent SQL statements can be processed.  (Reason "no cast
    fcn".)
    SQLSTATE=58004
    
    -- Otherwise, it will be:
    SQL0901N The SQL statement failed because of a non-severe system
    error.
    Subsequent SQL statements can be processed. (Reason "sqlridummy:
    invalid opcode
    8 at offset <nnnn:n>".) SQLSTATE=58004
    

Local fix

  • Rewrite EQPRD to a semantically equivalent
    non-relational-operator form. For the query above, this may be
    done like so:
    
    SELECT CASE_COL_1,
           CASE_COL_2
    FROM
    (SELECT MAX(CASE WHEN C1 = 'ABC'
                    THEN (CASE WHEN C2 = ' '
                               THEN '-'
                               ELSE C2
                          END)
                END) AS CASE_COL_1,
            MIN(CASE WHEN C1 = 'BCD'
                    THEN (CASE WHEN C2 = ' '
                               THEN '-'
                               ELSE C2
                          END)
                END) AS CASE_COL_2
     FROM   T1
    ) T
    WHERE CASE_COL_1 IN ('A', 'A');
    

Problem summary

  • SQL0901N may be issued executing a query containing predicates
    on MAX or MIN functions aggregated on nested CASE expressions
    

Problem conclusion

  • First fixed in DB2 UDB Version 9, FixPak 8
    

Temporary fix

Comments

APAR Information

  • APAR number

    IZ51789

  • Reported component name

    DB2 UDB ESE SOL

  • Reported component ID

    5765F4102

  • Reported release

    910

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2009-05-24

  • Closed date

    2009-10-06

  • Last modified date

    2009-10-06

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

    IZ51788

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

    IC62631

Fix information

  • Fixed component name

    DB2 UDB ESE SOL

  • Fixed component ID

    5765F4102

Applicable component levels

  • R910 PSN

       UP

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"910"}]

Document Information

Modified date:
03 October 2021