Link to home
Create AccountLog in
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
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

what if they do:

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

ASKER

I asked that one...0 rows selected. thx.
no. varchar and varchar2 are 2 different things.
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
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.
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.
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.
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.
that helps support that it's likely one of the bugs reported on Oracle Support
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.
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.
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.
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.