IBM Support

IC91301: 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 fixed if next.

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:                                              *
    * UNKNOWN                                                      *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to db2_v101fp3                                       *
    ****************************************************************
    

Problem conclusion

Temporary fix

Comments

APAR Information

  • APAR number

    IC91301

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A10

  • Status

    CLOSED FIN

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-04-03

  • Closed date

    2013-10-28

  • Last modified date

    2013-10-28

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

    IC89152

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

Fix information

Applicable component levels

  • RA10 PSN

       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":"10.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
28 October 2013