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=AL1
On the client: NLS_CHARACTERSET=WE8MSWIN1
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=AL1
On the client: NLS_CHARACTERSET=WE8MSWIN1
ASKER
I asked that one...0 rows selected. thx.
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
no. varchar and varchar2 are 2 different things.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.
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.
am i missing something here.
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.
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.
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.
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.
i am not sure whether CAST function was there before 15 years in the older versions of oracle database.
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.
SELECT * FROM MYTABLE WHERE CAST(COMMENT AS VARCHAR2(4000)) LIKE '%1234%';