Avatar of privasoft
privasoft
 asked on

Select from table in Oracle returning wrong results when using CAST

I was wondering if someone may be able to provide some insight into an Oracle issue a client of ours is having. I believe the issue may involve character sets, but I am not sure. We have narrowed the problem down to a field in the database called 'COMMENT' which is a VARCHAR2(4000). On my client site, when they do:

SELECT * FROM MYTABLE WHERE COMMENT LIKE '%1234%';
RESULT: 1 row selected

However, if they do:

SELECT * FROM MYTABLE WHERE CAST(COMMENT AS VARCHAR(4000)) LIKE '%1234%';
RESULT: 0 rows selected

If character sets have anything to do with the issue:

On the database server: NLS_NCHAR_CHARACTERSET=AL16UTF16
On the client: NLS_CHARACTERSET=WE8MSWIN1252
Oracle Database

Avatar of undefined
Last Comment
privasoft

8/22/2022 - Mon
Guy Hengel [angelIII / a3]

what if they do:

SELECT * FROM MYTABLE WHERE CAST(COMMENT AS VARCHAR2(4000)) LIKE '%1234%';
privasoft

ASKER
I asked that one...0 rows selected. thx.
Guy Hengel [angelIII / a3]

no. varchar and varchar2 are 2 different things.
Your help has saved me hundreds of hours of internet surfing.
fblack61
privasoft

ASKER
Sorry, will clarify...I asked the client to run the SQL using VARCHAR2 instead of VARCHAR and it came back with 0 rows as well.
ASKER CERTIFIED SOLUTION
Sean Stuber

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
gatorvip

That is bizarre. What is the full version of Oracle?

Since COMMENT is a reserved word in Oracle, there might be a bug when doing cast on a field with that name. Try running it like this:


SELECT * FROM MYTABLE
WHERE "COMMENT" LIKE '%1234%';

SELECT * FROM MYTABLE
WHERE CAST("COMMENT" AS VARCHAR(4000)) LIKE '%1234%';

Also, if the field is already a varchar2, there's no reason to cast it back into varchar2.
privasoft

ASKER
Thank-you, but I made up the tablename...it is really 'PGCOMMENT' - sorry for the red herring.

I will check for CAST errors with Oracle 10g R2. Thanks...sdstuber.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Naveen Kumar

But still i do not understand why you want to use CAST if the field itself is already a VARCHAR2 field ?

am i missing something here.
privasoft

ASKER
To be honest, if I were coding it I wouldn't do it this way, but it's an older product that has worked for years like this with thousands of copies in the field. It's only on one client site that this line of SQL does not work.
Sean Stuber

that helps support that it's likely one of the bugs reported on Oracle Support
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
gatorvip

If it has "worked for years like this" does that mean that something was changed? Or it has always had this issue? An Oracle bug would indicate the latter, not the former.
privasoft

ASKER
Nope...nothing has changed...this code has been this way for 15 years. So, I am thinking this may be an Oracle bug as well.
Naveen Kumar

was there any oracle database upgrade done which couuld have caused this ? if so, from what batabase version to what database version ?

i am not sure whether CAST function was there before 15 years in the older versions of oracle database.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
privasoft

ASKER
Looking into Bug 7154415 –  Using a CAST() operator can give wrong results / corruption. Fixed in 10.2.0.4 patchset 8, the client is running 10.2.0.4 patchset 3. The client is planning to apply 10.2.0.4 patchset 30 soon.