IBM Support

IC89152: A PREDICATE USING CONTAINS FUNCTION ON A COLUMN DERIVED FROM A UNION ALL OPERATION MIGHT RETURN SQL0901N

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • You might get error SQL0901N with Reason "column number of
    range" when you execute an SQL statements which has both:
    1) a WHERE clause that uses the CONTAINS function, and:
    2) a FROM clause that includes more than one view, if the DDL
    for both views includes UNION ALL.
    
    For example:
    
    CREATE VIEW view1
    AS
    SELECT ... FROM tabA ...
    UNION ALL
    SELECT ... FROM tabB ...
    ;
    
    CREATE VIEW view2
    AS
    SELECT ... FROM tabC ...
    UNION ALL
    SELECT ... FROM tabD ...
    ;
    
    SELECT
     ...
    FROM
     view1 JOIN view2 ...
    WHERE
     CONTAINS ...
    ;
    
    
    The call stack is as follows:
    
    sqlnq_ftb::num2fcs
    sqlnq_handle_resolved_col_ref
    sqlnr_ftslpd_thr_union
    sqlnr_text_func_prdpd
    sqlnr_optprep
    sqlnr_optprep_action
    sqlnr_comp
    sqlnr_seq
    sqlnr_rcc
    sqlnr_exe
    sqlnn_cmpl
    sqlnn_cmpl
    sqlra_compile_var
    sqlra_find_var
    sqlra_get_var
    sqlrr_prepare
    

Local fix

  • As a workaround, you can amend the SQL statement so that the
    CONTAINS function is no longer in the main WHERE clause but in
    the WHERE clause of subselect and applies directly to the the
    tables in a view instead, like this:
    
    SELECT ...
    FROM
    (
     SELECT * FROM tabA WHERE CONTAINS ...
     UNION ALL
     SELECT * FROM tabB WHERE CONTAINS ...
    ) AS view1
    JOIN view2 ...
    ;
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * Users of DB2 for Linux, UNIX and Windows                     *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * .                                                            *
    ****************************************************************
    

Problem conclusion

  • Problem was first fixed in Version 9.7 Fix Pack 8.
    At a minimum, this fix should be applied on the server.
    

Temporary fix

  • As a workaround, you can amend the SQL statement so that the
    CONTAINS function is no longer in the main WHERE clause but in
    the WHERE clause of the subselect and applies directly to the
    the
    tables in a view instead, like this:
    
    SELECT ...
    FROM
    (
     SELECT * FROM tabA WHERE CONTAINS ...
     UNION ALL
     SELECT * FROM tabB WHERE CONTAINS ...
    ) AS view1
    JOIN view2 ...
    ;
    

Comments

APAR Information

  • APAR number

    IC89152

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-12-17

  • Closed date

    2013-04-15

  • Last modified date

    2013-04-15

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

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

    IC91301

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC89152

Modified date: 15 April 2013