| Wrong result for query with like and % [message #561406] |
Sat, 21 July 2012 12:25  |
 |
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 #561413 is a reply to message #561406] |
Sat, 21 July 2012 16:02  |
John Watson
Messages: 3181
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 |
 |
Michel Cadot
Messages: 54718
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 #561428 is a reply to message #561420] |
Sun, 22 July 2012 11:04 |
Solomon Yakobson
Messages: 1443
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 |
|
|
|
] 
Members
Search
Help
Register
Login
Home
