IC57243: AF SELECTING FROM A VIEW WHICH CONTAINS A SELECT WITH A SUBQUERY USING SPL UDR

APAR status

• Closed as program error.

Error description

• For clarity, a demonstration of the problem follows:
  DROP DATABASE db1;
  CREATE DATABASE db1;
  CREATE TABLE t1 (t1_c1 int, t1_c2 int, t1_c3 int);
  CREATE TABLE t2 (t2_c1 int, t2_c2 int);
  CREATE TABLE t3 (t3_c1 int);
  CREATE FUNCTION udr1 (param_1 int) RETURNING int
     RETURN 1;
  END FUNCTION;
  CREATE VIEW v2 (v2_c1) AS
  SELECT
     (
     SELECT t1_c3
     FROM OUTER (t2, t1), t3
     WHERE t2_c1 == t1_c3 AND t1_c1 == t3_c1 AND
    udr1(t1_c2) BETWEEN t2_c2 AND 2
     )
  FROM t1;
  SELECT 1 FROM v2 WHERE v2_c1 = 3;
  This will cause an assert failure similar to the following
  stack trace:
  0x1009d4574 oninit :: afstack
  0x1009d3634 oninit :: afhandler
  0x1009d2a24 oninit :: affail_interface
  0x1009d9044 oninit :: mt_ex_throw_sig
  0x100995368 oninit :: afsig_handler
  0xffffffff7dad23c4 /lib/sparcv9/libc.so.1 :: _thr_slot_offset
  0xffffffff7dac6630 /lib/sparcv9/libc.so.1 :: sema_post
  0x100155bc0 oninit :: makeidxfunc
  0x100155810 oninit :: do_makeindex
  0x1002daf20 oninit :: mkrangekey
  0x1002d56b8 oninit :: bldnode
  0x1002d5fe0 oninit :: opbldtree
  0x1002bd158 oninit :: sqoptim
  0x1002c1c90 oninit :: cnttabs
  0x1002c0998 oninit :: opcntab
  0x1002e655c oninit :: makefll
  0x1002be0b4 oninit :: opinit
  0x1002e73dc oninit :: op_opinit
  0x1002bc1f4 oninit :: sqoptim
  0x100437f08 oninit :: bldstructs
  0x100437c2c oninit :: sqcmd
  0x10043777c oninit :: sq_prepare
  0x1004d392c oninit :: sqmain
  0x100a2d840 oninit :: listen_verify
  0x100a2cc2c oninit :: spawn_thread
  0x1009a7ff0 oninit :: startup
  

Local fix

Problem summary

• ****************************************************************
  USERS AFFECTED:
  All users
  ****************************************************************
  PROBLEM DESCRIPTION:
  A query with joins and predicates involving functions may cause
  assertion failure if dynamic index path is chosen by the
  optimizer.
  ****************************************************************
  RECOMMENDATION:
  Upgrade to 11.50.xC2 or above.
  ****************************************************************
  

Problem conclusion

• Problem was first fixed in 11.50.xC2.
  

Temporary fix

Comments

APAR Information

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

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

Fix information

• Fixed component name

  IBM IDS ENTRP E

• Fixed component ID

  5724L2304

Applicable component levels

• RB15 PSN

     UP