Optimizer Bug?

Optimizer BUG? ORACEL standard 9.2.0.1.0 W2K sp3

Hi.

I have a table with 1 NCHAR(3) field, aaa

If I run the query
select * from <table> where aaa='001' I get 2 rows

If I run
select * from <table> where aaa='002' I get 3 rows

Great, now if I run
select * from <table> where aaa IN('001','002') I should get 5 rows, correct?

Wrong, I only get 2 rows.

If I run
select * from <table> where aaa IN('002,'001) I get 3 (as many as are selected by the first item in the IN list)

The problem is similar if I use the 'OR' predicate!!!!

If I force the database to conver my parmaters to NCHAR select * from <table> where aaa IN(TO_NCHAR('001'),TO_NCAHR('002')) I get the 5 rows I expected.

If I change the data type to CHAR(3), it works OK....

If anybody can explain this or point me to a patch/optimizer bug I would appreciate it.

Alin Dan
adan_at_mmm.com Received on Wed Jan 21 2004 - 11:37:55 CST