SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Conversion Functions are interfering with 'or' operators??

User_M8EXOJan 5 2012 — edited Jan 5 2012

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

the_slk

'20120105'

Looks like CHAR. Is in this case conversion necessary?

Tubby

893730 wrote:
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.

Assuming the m.stop_dt column is actually a date (maybe a bad assumption) you're likely getting screwed by the fact that you aren't explicitly converting (you're relying on Oracle to convert the string to a date so a comparison can be done)

 substr(to_char('20120105'),1,8));

to a date as you should be.

Is that supposed to be jan-05-2012, or it is supposed to be dec-20-0105, or ... something else.

the_slk

Can you add:
- DESCRIBE mailing_list;
- SELECT DUMP ('20120105', 1017) FROM DUAL

User_M8EXO

I used ('20120105') for the sample query to demonstrate the actual issue, in the real query it is SYSDATE being returned which I need to convert to character format of 'YYYYMMDD'. We also tried using the cast statement to varchar2 and the issue with the 'or' still occurred.

Tubby

893730 wrote:
I used ('20120105') for the sample query to demonstrate the actual issue, in the real query it is SYSDATE being returned which I need to convert to character format of 'YYYYMMDD'. We also tried using the cast statement to varchar2 and the issue with the 'or' still occurred.

I see i was off in my original reply, i didn't realize you were using the same column in both predicates ... so the column in the table is indeed a char.

A couple of possibilities for you to look in to.
1) you're running on an unpatched version of the database, it's possible you're hitting a bug. Any chance you can patch to the latest release?
2) you say the actual SQL is more complex and you've dumbed it down. It's possible you're hitting a bug with query transformation, have you tried running a 10053 trace to see what the optimizer is transforming your query in to? It's possible you're losing a predicate along the way or something like that.

Is it possible for you to put together a test case that reproduces the issue? I attempted on an 11.2.0.1 instance and i was unable to. Since you've dumbed down the example it's entirely possible you've left something out of the explanation that would be crucial to reproducing your issue. Can you provide the actual objects and definitions involved here to us?

User_M8EXO

1. This install is only 2 months old and were fully patched then. I will check with my DBA to see if any patches are now missing
2. Both my boss and I believe we hit a bug in Oracle. Both the original code and the dumb down code work fine in Sybase but not in Oracle (only change is we use Sybase's character convert function)
3. Yes, I can put together a test case. I will add to this tread when I get it done.

Thanks

Tubby

893730 wrote:
1. This install is only 2 months old and were fully patched then. I will check with my DBA to see if any patches are now missing

I don't think so.

http://structureddata.org/2010/09/10/11-2-0-2-patch-set-for-oracle-database-server/

So 11.2.0.2 has been out since September 2010, whereas you've stated you're on 11.2.0.1 (in your initial post).

And 11.2.0.3 was made available late in 2011
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

893730 wrote:
2. Both my boss and I believe we hit a bug in Oracle. Both the original code and the dumb down code work fine in Sybase but not in Oracle (only change is we use Sybase's character convert function)

Possible, but it's also possible there's a bug in Sybase that's leading you to draw conclusions about the results you should be getting in Oracle :)

893730 wrote:
3. Yes, I can put together a test case. I will add to this tread when I get it done.

That will greatly expedite the assistance (actually helpful assistance anyways) we'll be able to provide you.

Cheers,

Anton Scheffer

If this returns the correct records

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);

then this should do too

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 > to_char(sysdate,'yyyymmdd' )

872364

what is the datatype for stopdate field,if it is char type then you dont need to use to_char function and if it is date then u cant simply compare stop_date='00000000' u need to use to_date function please specify the datatype for the field

User_M8EXO

Below is everything you need to see the issue. Also, I just contacted my DBA and found out we are not on the latest version. Here is our installation:

Oracle Database 11gR2 Enterprise Edition (Release 11.2.0.1.0, 64-bit).
The server it’s running on is RedHat Enterprise Linux 6, Kernel 2.6.32-71.el6.x86_64 #1 SMP.

How to recreate the issue

CREATE TABLE ADVCONV.MAILING_LIST_TEST
(
ID_NUMBER VARCHAR2(10 BYTE) DEFAULT ' ' NOT NULL,
STOP_DT VARCHAR2(8 BYTE) DEFAULT '00000000' NOT NULL,
MAIL_LIST_TYPE_CODE VARCHAR2(3 BYTE) DEFAULT ' ' NOT NULL
);

insert into MAILING_LIST_TEST VALUES
('0000122680','00000000','FXM');
insert into MAILING_LIST_TEST VALUES
('0035542311','00000000','FXM');
insert into MAILING_LIST_TEST VALUES
('0035550847','00000000','FXM');
insert into MAILING_LIST_TEST VALUES
('0035575276','00000000','FXM');
insert into MAILING_LIST_TEST VALUES
('0035576628','20130101','FXM');
insert into MAILING_LIST_TEST VALUES
('0035576629','20121120','FXM');

select m.id_number id_number,
m.mail_list_type_code mail_l_t_code,
stop_dt
from mailing_list_test m
where (m.stop_dt = '00000000') or (m.stop_dt > substr(to_char('20120105'),1,8));
--Above statement only returns two records but should be 6

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

--Above statement only returns two records but should be 6

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

--Above statement returns 6 records as expected

Tubby

Thanks for the test case. Unfortunately, i don't have an 11.2.0.1 instance floating around (i thought i did), when i run that on my 11.2.0.2 instance i don't see the results you are seeing.

All queries return 6 rows for me ...

TUBBY_ORCL?select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

5 rows selected.

Elapsed: 00:00:00.09
TUBBY_ORCL?
TUBBY_ORCL?select m.id_number id_number,
  2  m.mail_list_type_code mail_l_t_code,
  3  stop_dt
  4  from mailing_list_test m
  5  where (m.stop_dt = '00000000') or (m.stop_dt > substr(to_char('20120105 '),1,8));

ID_NUMBER  MAI STOP_DT
---------- --- --------
0000122680 FXM 00000000
0035542311 FXM 00000000
0035550847 FXM 00000000
0035575276 FXM 00000000
0035576628 FXM 20130101
0035576629 FXM 20121120

6 rows selected.

Elapsed: 00:00:00.06
TUBBY_ORCL?--Above statement only returns two records but should be 6
TUBBY_ORCL?
TUBBY_ORCL?select m.id_number id_number,
  2  m.mail_list_type_code mail_l_t_code,
  3  stop_dt
  4  from mailing_list_test m
  5  where (m.stop_dt = '00000000') or (m.stop_dt > substr(to_char(SYSDATE, 'YYYYMMDD'),1,8));

ID_NUMBER  MAI STOP_DT
---------- --- --------
0000122680 FXM 00000000
0035542311 FXM 00000000
0035550847 FXM 00000000
0035575276 FXM 00000000
0035576628 FXM 20130101
0035576629 FXM 20121120

6 rows selected.

Elapsed: 00:00:00.04
TUBBY_ORCL?
TUBBY_ORCL?--Above statement only returns two records but should be 6
TUBBY_ORCL?
TUBBY_ORCL?select m.id_number id_number,
  2  m.mail_list_type_code mail_l_t_code,
  3  stop_dt
  4  from mailing_list_test m
  5  where (m.stop_dt = '00000000') or m.stop_dt > substr('20120105',1,8);

ID_NUMBER  MAI STOP_DT
---------- --- --------
0000122680 FXM 00000000
0035542311 FXM 00000000
0035550847 FXM 00000000
0035575276 FXM 00000000
0035576628 FXM 20130101
0035576629 FXM 20121120

6 rows selected.

Elapsed: 00:00:00.06
TUBBY_ORCL?

So i would assume it's a bug (hopefully someone else with an 11.2.0.1 instance can confirm that), any chance you can upgrade your database to 11.2.0.2 (or 11.2.0.3 even)? It's typically a good idea to keep your patch set reasonably current, i'd suggest moving to 11.2.0.2 if nothing else.

If that's not an option for whatever reason, you have a nice simple test case you should be able to raise a support ticket with Oracle and get any possible workarounds.

Edited by: Tubby on Jan 5, 2012 1:54 PM

riedelme

893730 wrote:
select m.id_number id_number,
m.mail_list_type_code mail_l_t_code,
stop_dt
from mailing_list_test m
where (m.stop_dt = '00000000') or (m.stop_dt > substr(to_char('20120105'),1,8));
--Above statement only returns two records but should be 6

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

--Above statement only returns two records but should be 6

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

--Above statement returns 6 records as expected

Something odd is happening during the conversions.

Why are you wapping TO_CHAR() around a string in the first example?
What is your value for SYSDATE (are you on the other side of the date line)?
Shouldn't the substr() in the example that works be unnecessary?

Frank Kulash

Hi,

That looks like a bug in your version. All three queries return all 6 rows in Oracle 11.1.0.6.0, and in 10.2.0.3.0, and in 10.1.0.2.0.

Always use the right data type. For date information, the right data type is DATE, or, if you really need fractions of a second, TIMESTAMP, but definitely not VARCHAR2.

The first argument to TO_CHAR should be a DATE, so

to_char('20120105')

is wrong.

Edited by: Frank Kulash on Jan 5, 2012 5:20 PM

I just tried

WHERE   m.stop_dt   IN ( '00000000'
		       , TO_CHAR(SYSDATE, 'YYYYMMDD')
		       )

in Oracle 11.2.0.1.0, and got the opposite wrong results, that is, it selected the 4 rows with stop_dt='00000000', and not the 2 rows that are greater than '20120105'.

Solomon Yakobson

Looks like a bug on 11g:

SQL> select * from v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> set autotrace on
SQL>                  select  m.id_number id_number, 
  2        m.mail_list_type_code mail_l_t_code,
  3        stop_dt 
  4  from  mailing_list_test m
  5  where (m.stop_dt = '00000000') or (m.stop_dt >'20120105');

ID_NUMBER  MAI STOP_DT
---------- --- --------
0000122680 FXM 00000000
0035542311 FXM 00000000
0035550847 FXM 00000000
0035575276 FXM 00000000
0035576628 FXM 20130101
0035576629 FXM 20121120

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 795448539

---------------------------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |     6 |    96 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| MAILING_LIST_TEST |     6 |    96 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("M"."STOP_DT">'20120105' OR "M"."STOP_DT"='00000000')

As soon as we convert string to char (which obviously makes not much sense but nevertheless is allowed and simply results in same string):

SQL>                  select  m.id_number id_number, 
  2        m.mail_list_type_code mail_l_t_code,
  3        stop_dt 
  4  from  mailing_list_test m
  5  where (m.stop_dt = '00000000') or (m.stop_dt > to_char('20120105'));

ID_NUMBER  MAI STOP_DT
---------- --- --------
0035576628 FXM 20130101
0035576629 FXM 20121120


Execution Plan
----------------------------------------------------------
Plan hash value: 795448539

---------------------------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |     2 |    32 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| MAILING_LIST_TEST |     2 |    32 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("M"."STOP_DT">'20120105')

As you can see, optimizer is smart enough to get rid of to_char but somehow loses m.stop_dt = '00000000'.

Not on 10.2.0.4.0 is works fine:

SQL> select * from v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL>                  select  m.id_number id_number, 
  2        m.mail_list_type_code mail_l_t_code,
  3        stop_dt 
  4  from  mailing_list_test m
  5  where (m.stop_dt = '00000000') or (m.stop_dt > to_char('20120105'));

ID_NUMBER  MAI STOP_DT
---------- --- --------
0000122680 FXM 00000000
0035542311 FXM 00000000
0035550847 FXM 00000000
0035575276 FXM 00000000
0035576628 FXM 20130101
0035576629 FXM 20121120

6 rows selected.

SQL>

SY.
SY.

User_M8EXO

I have talked to my DBA and we will look into an upgrade. I have already implemented a workaround, but in the future we may need to use 'or' statements with conversion functions so I posted the issue. Thanks Tubby.

Also, If someone out there has the same version as us, 11.2.0.2.0, or wants to run my posted test in another version I would love to see if you have the same issue.

Tubby

893730 wrote:
I have talked to my DBA and we will look into an upgrade. I have already implemented a workaround, but in the future we may need to use 'or' statements with conversion functions so I posted the issue. Thanks Tubby.

Also, If someone out there has the same version as us, 11.2.0.2.0, or wants to run my posted test in another version I would love to see if you have the same issue.

No problem, i really would recommend the patch. As i mentioned earlier, that patch set has been out for over a year now (the 11.2.0.2 patch). As with any patch though, you'd need to test it out in a proper (test) environment. As with most all software, some bugs will be fixed and others will be introduced so you can't just assume it's a magic bullet.

Just so you know, i also ran the test case you supplied on an XE instance (10.2.0.1) and got the same results as i did in 11.2.0.2 (no issues).

User_M8EXO

Thanks tor everyone who did run the code and for proving it is a bug in our specific version. Now I have the ammo to get our database upgraded.

1 - 17

Locked Post

New comments cannot be posted to this locked post.

Locked on Feb 2 2012

Added on Jan 5 2012

#11g, #issue

17 comments

246 views

SQL & PL/SQL

Conversion Functions are interfering with 'or' operators??

Comments

Post Details