SQL & PL/SQL

Issue with Oracle Database 11g 11.2.0.1.0

In a sql where clause, when we have a data conversion function and this function is associated to another statement with the use of an 'or' operator, the 'or' operator does not function correctly. The associated statement will not return any records even if it returns true. Below are two samples that demonstrate the issue we found.

Sample of Issue:

select m.id_number id_number,
m.mail_list_type_code mail_l_t_code,
stop_dt
from mailing_list m
where (m.stop_dt = '00000000') or (m.stop_dt > substr(to_char('20120105'),1,8));

The above where clause, (m.stop_dt = '00000000'), statement never returns data even though over 500,000 records should be returned. The (m.stop_dt > substr(to_char('20120105'),1,8) statement returns 334 records as expected. We have changed the to_char to the convert function and the same issue occurs. Basically, having a data conversion function on one side of an 'or' statement that returns data prevents the other side of the or statement from returning data even if true.

Sample when conversion function removed:

select m.id_number id_number,
m.mail_list_type_code mail_l_t_code,
stop_dt
from mailing_list m
where (m.stop_dt = '00000000') or m.stop_dt > substr('20120105',1,8);

When we remove the conversion function, 548,286, records are returned as expected.

Does anyone have an idea on what is happening. We found this issue in a lot more complex sql statement which I dumb downed to demonstrate the issue on this forum.

Thanks