Vishal desai’s Oracle Blog

March 27, 2012

Oracle CHAR data type comparisons

Filed under: Oracle Database, SQL, Troubleshooting — vishaldesai @ 12:54 am

I received below test case from my pl/sql developer.

dev@oradb > drop table test_3;

Table dropped.

dev@oradb > create table test_3  (col1 char(2));

Table created.
 
dev@oradb > insert into test_3 values('  ');

1 row created.

dev@oradb > insert into test_3 values('~ ');

1 row created.

dev@oradb > insert into test_3 values('~');

1 row created.

dev@oradb > insert into test_3 values(' ~');

1 row created.

dev@oradb > commit;

Commit complete.

dev@oradb > select * from test_3;

CO
--

~
~
 ~


4 rows selected.

Developer wanted to find record with column value = ‘~’ and he used below query:

Test 1: --I would expect no records since it is a char field
dev@oradb > select * from test_3 where col1='~';

CO
--
~
~

2 rows selected.

Developer was expecting that above query will return 0 rows as col1 is defined as char(2) so ‘~’ will be stored as ‘~ ‘ and ‘~’ <> ‘~ ‘ but query returned 2 rows  instead of 0 rows.

As per Oracle documentation http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements002.htm:

With blank-padded semantics, if the two values have different lengths, then Oracle first adds blanks to the end of the shorter one so their lengths are equal. Oracle then compares the values character by character up to the first character that differs. Oracle uses blank-padded comparison semantics only when both values in the comparison are either expressions of datatype CHAR, NCHAR, text literals, or values returned by the USER function.

So now we know how CHAR data types with different lengths are compared how do we know what is the data type of ‘~’ on right side of equality operator? It’s char but lets prove it.

dba@oradb >  create table datatype_test as select '~' as db from dual;

Table created.

dba@oradb > desc  datatype_test
 Name                                            Null?    Type
 ----------------------------------------------- -------- --------------------------------
 DB                                                       CHAR(1)

dba@oradb > select col1,ora_hash(col1),ora_hash('~'),ora_hash('~ ') as "ora_hash('~ ')" from test_3;

CO ORA_HASH(COL1) ORA_HASH('~') ora_hash('~ ')
-- -------------- ------------- --------------
        190699439     216329699     4123278633
~      4123278633     216329699     4123278633
~      4123278633     216329699     4123278633
 ~      999748150     216329699     4123278633
 
4 rows selected.

From above its clear that data type of ‘~’ on right side of equality operator is CHAR and hence select query return 2 records (record 2 and record 3) instead of 0 rows. Visual comparison will look something like following:

Expected Comparison Actual Comparison
‘  ‘ ‘~’ ‘  ‘ ‘~ ‘
‘~ ‘ ‘~’ ‘~ ‘ ‘~ ‘
‘~ ’ ‘~’ ‘~ ‘ ‘~ ‘
‘ ~’ ‘~’ ‘ ~’ ‘~ ‘

Hash value returned by ora_hash function also matches as shown above.

Developer tried to use different work around which produced different results and I used same queries to prove why similar workarounds are producing different results.

Test 2: --above query works then why not this query? all im doing is replacing a ~ with nvl(trim('  '),'~')

dev@oradb > select * from test_3 where col1 =nvl(trim('  '),'~');

no rows selected

dba@oradb > select col1,ora_hash(col1),ora_hash(nvl(trim('  '),'~')) as "ora_hash(nvl(trim('  '),'~'))" from test_3;

CO ORA_HASH(COL1) ora_hash(nvl(trim('  '),'~'))
-- -------------- -----------------------------
        190699439                     216329699
~      4123278633                     216329699
~      4123278633                     216329699
 ~      999748150                     216329699

dba@oradb > create table datatype_test as select nvl(trim('  '),'~') as db from dual;

Table created.

dba@oradb > desc  datatype_test
 Name                                            Null?    Type
 ----------------------------------------------- -------- --------------------------------
 DB                                                       VARCHAR2(1)

Data type of nvl(trim(‘  ‘),’~’) is VARCHAR2 so Oracle will not pad blanks. Hash values returned are also different hence query returned 0 records.

Test 3: --works user defined function

dev@oradb > CREATE OR REPLACE FUNCTION test_fn (v_col1 VARCHAR2) RETURN VARCHAR2
  2  AS
  3   col1 CHAR(2);
  4  BEGIN
  5  col1:=  nvl(trim('  '),'~');
  6    RETURN col1;
  7  END;
  8  /

Function created.

dev@oradb > select * from test_3 where col1 =test_fn('  ');

CO
--
~
~

2 rows selected.

dba@oradb > create table datatype_test as select test_fn('  ') as dt from dual;

Table created.

dba@oradb > desc  datatype_test
 Name                                            Null?    Type
 ----------------------------------------------- -------- --------------------------------
 DT                                                       VARCHAR2(4000)

dba@oradb >  select length(dt) from datatype_test;

LENGTH(DT)
----------
         2

dba@oradb > select dt from  datatype_test;

DT
---------------------------------------------------------------
~

dba@oradb > select col1,ora_hash(col1),ora_hash(test_fn('  ')) as "ora_hash(test_fn('  '))" from test_3;

CO ORA_HASH(COL1) ora_hash(test_fn('  '))
-- -------------- -----------------------
        190699439               216329699
~      4123278633              4123278633
~      4123278633              4123278633
 ~      999748150               216329699

Data type of return variable is VARCHAR but col1 is defined as CHAR in function hence during assignment col1 will store ‘~’ as ‘~ ‘ (return value) which can also be confirmed by length function. Hash values returned for record 2 and record 3 match hence query returns 2 records.

Test 4: case also works

dev@oradb > select * from test_3 where col1 = case when trim('  ') is null then '~'   else '1' end;

CO
--
~
~

2 rows selected.

dba@oradb > select col1,ora_hash(col1),ora_hash(case when trim('  ') is null then '~'   else '1' end) as "ora_hash_on_case" from test_3;

CO ORA_HASH(COL1) ora_hash_on_case
-- -------------- ----------------
        190699439        216329699
~      4123278633        216329699
~      4123278633        216329699
 ~      999748150        216329699

 
dba@oradb > create table datatype_test as select case when trim('  ') is null then '~'   else '1' end as dt from dual;

Table created.


dba@oradb > desc  datatype_test
 Name                                            Null?    Type
 ----------------------------------------------- -------- --------------------------------
 DT                                                       CHAR(1)

Hash value doesn’t match but as the data type of return variable of CASE is CHAR oracle will pad blank and hence return 2 records.

Developer finally asked is there any way to select only record 3 using where condition? Once the data is inserted I don’t think there is way to differentiate between record 2 (‘~ ‘) and record 3 (‘~’).

Below is the data block dump of record 2 and record 3:

tab 0, row 1, @0x3f8c
tl: 6 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 2]  7e 20
tab 0, row 2, @0x3f86
tl: 6 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 2]  7e 20


.

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: