Home » SQL & PL/SQL » SQL & PL/SQL » Wrong result for query with like and % (Oracle, 11.2.0.2.0, Windows Server 2008 English)
Wrong result for query with like and % [message #561406] Sat, 21 July 2012 12:25 Go to next message
krait
Messages: 2
Registered: July 2012
Junior Member
I have a strange problem with query with like and %.
When I run this script:

ALTER SESSION SET NLS_SORT = 'BINARY_CI'; 
ALTER SESSION SET NLS_COMP = 'LINGUISTIC';

-- drop table test1;
CREATE TABLE TEST1(K1 NVARCHAR2(80));
INSERT INTO TEST1 VALUES ('gsdk'); 
INSERT INTO TEST1 VALUES ('ąxyz'); 
INSERT INTO TEST1 VALUES ('ŁFa'); 
INSERT INTO TEST1 VALUES ('ła'); 
INSERT INTO TEST1 VALUES ('Śab'); 
INSERT INTO TEST1 VALUES ('Śrrrb'); 
commit;

select * from TEST1 where k1 like N'Ł%';


I get this:

K1

ŁFa 
ła 
Śab <- WRONG 
Śrrrb <- WRONG 

4 rows selected 


When i change datatype in column to varchar2 this code work correct.
Is this a bug or what ?

The execution plan:

PLAN_TABLE_OUTPUT

SQL_ID d3d64aupz4bb5, child number 2

select * from TEST1 where k1 like N'Ł%' 
Plan hash value: 4122059633

Id	 Operation	 Name	 Rows	 Bytes	 Cost (%CPU)	 Time
0	 SELECT STATEMENT	 	 	 	 2 (100)	 
* 1	 TABLE ACCESS FULL	 TEST1	 1	 82	 2 (0)	 00:00:01

Predicate Information (identified by operation id):

1 - filter((NLSSORT("K1",'nls_sort=''BINARY_CI''')>=HEXTORAW('014200' 
) AND NLSSORT("K1",'nls_sort=''BINARY_CI''')<HEXTORAW('01610100') )) 
Note


- dynamic sampling used for this statement (level=2)


My NLS configuration
NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_SORT BINARY
Re: Wrong result for query with like and % [message #561409 is a reply to message #561406] Sat, 21 July 2012 12:58 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

it is not bug,where did you tried in TOAD or sqlplus?
Re: Wrong result for query with like and % [message #561410 is a reply to message #561409] Sat, 21 July 2012 12:59 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

you can run the sql's in PLSQL Developer tool...you won't get the any issues...
Re: Wrong result for query with like and % [message #561411 is a reply to message #561409] Sat, 21 July 2012 13:51 Go to previous messageGo to next message
Michel Cadot
Messages: 59815
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
it is not bug


Wow, what an imperative affirmation! Base on what?

Regards
Michel
Re: Wrong result for query with like and % [message #561413 is a reply to message #561406] Sat, 21 July 2012 16:02 Go to previous messageGo to next message
John Watson
Messages: 4806
Registered: January 2010
Location: Global Village
Senior Member
I don't know if it will help you, but this is what I get, copy/pasting your code:
c:\users\john\home>sqlplus scott/tiger

SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 21 21:57:20 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options


Session altered.

orcl> ALTER SESSION SET NLS_SORT = 'BINARY_CI';

Session altered.

orcl> ALTER SESSION SET NLS_COMP = 'LINGUISTIC';

Session altered.

orcl> CREATE TABLE TEST1(K1 NVARCHAR2(80));

Table created.

orcl> INSERT INTO TEST1 VALUES ('gsdk');

1 row created.

orcl> INSERT INTO TEST1 VALUES ('axyz');

1 row created.

orcl> INSERT INTO TEST1 VALUES ('LFa');

1 row created.

orcl> INSERT INTO TEST1 VALUES ('la');

1 row created.

orcl> INSERT INTO TEST1 VALUES ('Sab');

1 row created.

orcl> INSERT INTO TEST1 VALUES ('Srrrb');

1 row created.

orcl> commit;

Commit complete.

orcl> select * from TEST1 where k1 like N'L%';

K1
--------------------------------------------------------------------------------
LFa
la

orcl> select * from v$version;

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

orcl>

Re: Wrong result for query with like and % [message #561415 is a reply to message #561413] Sun, 22 July 2012 00:14 Go to previous messageGo to next message
Michel Cadot
Messages: 59815
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
John, it is not a, L, l, S but ą, Ł, ł and Ś as first character, it seems you have a conversion during copy and paste.
Regards
Michel

[Updated on: Sun, 22 July 2012 00:18]

Report message to a moderator

Re: Wrong result for query with like and % [message #561420 is a reply to message #561406] Sun, 22 July 2012 06:13 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2067
Registered: January 2010
Senior Member
krait wrote on Sat, 21 July 2012 13:25
ALTER SESSION SET NLS_SORT = 'BINARY_CI'; 


What happens when you issue:

ALTER SESSION SET NLS_SORT = 'XXX_CI'; --change XXX to your language name


SY.
Re: Wrong result for query with like and % [message #561428 is a reply to message #561420] Sun, 22 July 2012 11:04 Go to previous message
Solomon Yakobson
Messages: 2067
Registered: January 2010
Senior Member
Actually, the whole thing with NVARCHAR2 is one big bug. First I will create two tables TEST1 with NVARCHAR2 and TEST2 with VARCHAR2:

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> create table test1(k1 nvarchar2(20))
  2  /

Table created.

SQL> create table test2(k1 varchar2(20))
  2  /

Table created.

SQL> insert into test1 select ename from emp
  2  /

14 rows created.

SQL> insert into test2 select ename from emp
  2  /

14 rows created.


Now I will test NVARCHAR2 LIKE VARCHAR2:

SQL> explain plan for
  2  select k1 from test1 where k1 like 'K%'
  3  /

Explained.

SQL> @?\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4122059633

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

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter(NLSSORT("K1",'nls_sort=''BINARY_CI''')>=HEXTORAW('006B00')
                AND NLSSORT("K1",'nls_sort=''BINARY_CI''')<HEXTORAW('6C00') )

Note
-----
   - dynamic sampling used for this statement (level=2)

18 rows selected.

SQL> select k1 from test1 where k1 like 'K%'
  2  /

K1
--------------------
SMITH
WARD
MARTIN
SCOTT
KING
TURNER
MILLER

7 rows selected.


What happened? Oracle transformed LIKE into two predicates >= and <. The idea is LIKE 'K%' selects rows where column K1 >= 'K' (starts with 'K') and K1 < 'L' (L is character that follows K). But this method requires taking into account both NLS_SORT and VARCHAR2/NVARCHAR2 to get correct "starts with" code and next character code. It appears Oracle takes into account NLS_SORT (see 'nls_sort=''BINARY_CI''' in explain plan) but what Oracle did with pattern - it correctly converted 'K' into N'K' and calculated nlssort(N'K') = '006B00' but, for some reason, it didn't take VARCHAR2/NVARCHAR2 into account when calculating nlssort for next character and used nlssort('L') = '6C00'. And, as a result, we got ... what we got. Now I change pattern to NVARCHAR2:

SQL> explain plan for
  2  select k1 from test1 where k1 like N'K%'
  3  /

Explained.

SQL> @?\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4122059633

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

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter(NLSSORT("K1",'nls_sort=''BINARY_CI''')>=HEXTORAW('006B00')
                AND NLSSORT("K1",'nls_sort=''BINARY_CI''')<HEXTORAW('006B0100')
)


Note
-----
   - dynamic sampling used for this statement (level=2)

18 rows selected.

SQL> select k1 from test1 where k1 like N'K%'
  2  /

K1
--------------------
KING


Result looks OK, but, for some reason, Oracle incorrectly calculated next character. So our correct result is just data magic:

SQL> update test1
  2     set k1 = unistr('K\0112RUSS\00D5') -- KĒRUSSÕ
  3   where k1 = N'ALLEN'
  4  /

1 row updated.

SQL> select k1 from test1 where k1 like N'K%'
  2  /

K1
--------------------
KING

SQL> 


Now TEST2 (where column K1 is VARCHAR2). First I will test it with VARCHAR2 pattern:

SQL> explain plan for
  2  select k1 from test2 where k1 like 'K%'
  3  /

Explained.

SQL> @?\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 300966803

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

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter(NLSSORT("K1",'nls_sort=''BINARY_CI''')>=HEXTORAW('6B00')
              AND NLSSORT("K1",'nls_sort=''BINARY_CI''')<HEXTORAW('6C00') )

Note
-----
   - dynamic sampling used for this statement (level=2)

18 rows selected.

SQL> select k1 from test2 where k1 like 'K%'
  2  /

K1
--------------------
KING

SQL> 


Everyting looks OK. Oracle correctly used '6B00' and '6C00'. But look what happens when pattern is NVARCHAR2:

SQL> explain plan for
  2  select k1 from test2 where k1 like N'K%'
  3  /

Explained.

SQL> @?\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 300966803

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

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter(NLSSORT("K1",'nls_sort=''BINARY_CI''')<HEXTORAW('006B0100'
              )  AND NLSSORT(SYS_OP_C2C("K1"),'nls_sort=''BINARY_CI''')>=HEXTORA
W('006

              B00') )

Note
-----
   - dynamic sampling used for this statement (level=2)

19 rows selected.

SQL> select k1 from test2 where k1 like N'K%'
  2  /

no rows selected

SQL> 


Again, "starts with" and "less than" are incorrect. Anyway, I suggest you open SR, or at least browse MetaLink for known bugs.

SY.

[Updated on: Sun, 22 July 2012 11:26]

Report message to a moderator

Previous Topic: replace the like operator to increase the performance
Next Topic: Plsql records - as object
Goto Forum:
  


Current Time: Fri Nov 28 03:08:22 CST 2014

Total time taken to generate the page: 0.09921 seconds