ORA 9i Bug?

OS: HP Unix 11
Oracle 9.0.2

I think I've found a SQL optimizer bug. We've opend a tar but I wanted to check with the experts here.

The SQL is very simple with 8 columns linking 4 tables with about 4 where-clause conditions. The first selected column is a varchar2(1) with unique values of 'Y','N', and null. The error is "invalid number" (I'm at home, so forgive that I don't have the exact ORA err number) and complains about the varchar2(1) column. It acts like there's a to_number function on that column.

The table is selected via a public synonym and was dropped/recreated but no luck. We tried using the full explicit path to the table ( schema.table.column) and it somehow works. Makes me think it's still related to the public synonym...

Any change to the full SQL also makes the error not occur. So changing the SQL where clause somehow does not trip the error.

The error was found in testing of our client-server app, but is reproducible even within SQL*Plus. We tried running a trace during SQL*Plus execution but the trace session crashed(?); so no trace file. We've used trace many times to diagnose problems so I think the DBAs have some experience in doing it.

Am I missing something?

Cliff Received on Sat Jun 14 2003 - 07:22:54 CDT