Re: Optimizer Bug?

"Alin Dan" <alindan_at_hotmail.com> a écrit dans le message de news:8bfd9158.0401210937.1f62716d_at_posting.google.com...
> 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

May be because constant for NCHAR is N'001' and not '001'.

If not, post the results of your queries.

Regards
Michel Cadot Received on Wed Jan 21 2004 - 12:45:40 CST