10.2.0.4 patch 31 on win32 breaks queries on tables with nested table columns?

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Fri, 12 Feb 2010 02:00:39 -0800 (PST)
Message-ID: <ce778dad-f6e7-4f86-901d-9cae2ddbbc0b_at_q29g2000yqn.googlegroups.com>

Applied patch bundle 31 on top of my 10.2.0.4 test instance on Win32 a couple of weeks ago and just noticed that my bdump started to fill with large trace files from jobs. Quick glance shown that rule manager job started to fail due to ORA-600 [15160] on a seemingly trivial SQL. Ran the query myself and indeed this trivial SQL results in OERI in optimization phase. This is a simple single table select, the only unusual thing is that the table being queried has a nested table column. I reproduced this behavior and the test case below fails consistently for me (and it doesn't for patch levels before bundle 31, so this must be a regression introduced by merge 9118845, in particular the fix for bug 8467123, which seems to be relevant.) Anyone sees this, too, or is it just me? Maybe on other platforms with patch for 8467123 applied as well?

• test case for OERI:15160 in optimizer for min/max
• queries against tables with NT columns create or replace type nt_type is table of VARCHAR2(38);

create table x
(
  a TIMESTAMP(6),
  b VARCHAR2(200),
  c nt_type,
  d VARCHAR2(32),
  e VARCHAR2(32),
  f NUMBER default 3 not null
)
nested table c store as c_nt;

create index ix$x#a on x (a);

• grab the optimizer dump alter session set events='10053 trace name context forever, level 1';
  • this one will throw ORA-600 [15160] in optimization phase
  • when evaluating join orders (as if we need them here...) SELECT MIN(a) FROM x WHERE a IS NOT NULL;

I can't open SRs as of now (my MOS account is browse-only,) so if anyone confirms this - can you open a SR to have this nasty fixed? Seems like at least P2 bug to me and I was unable to find anything similar on MOS so far (but when was the search there relevant anyway...?)

Thanks,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Fri Feb 12 2010 - 04:00:39 CST