difference between order by and between operators

From: Amihay Gonen <AmihayG_at_ectel.com>
Date: Sun, 8 May 2005 18:53:56 +0300
Message-ID: <A18EEF7322197F4A8586D0B013C7B8F605878083@ectexil1.ectel.com>

Hi ,
we have notice differences between the operator of order by and between.  

It seems that nls_sort effect only the order by sort operator , but not the between.  

Is there a nls setting which effect the between operator in a similar way ? What is the default nls setting for the between ? Does it an expect behavior ?  

Here is an example which show the problem:  

DROP TABLE TEST;
CREATE TABLE TEST ( entity VARCHAR2(4));

INSERT INTO TEST VALUES('a');
INSERT INTO TEST VALUES('0');
INSERT INTO TEST VALUES('A');
INSERT INTO TEST VALUES('1');

COMMIT; CREATE OR REPLACE VIEW yy AS
select min(entity) min_entity,MAX(ENTITY) max_entity from (SELECT ENTITY from TEST order by ENTITY ) group by floor ( ROWNUM/2 );

ALTER SESSION SET nls_sort = BINARY;

SELECT ENTITY from TEST order by ENTITY ;

select * FROM yy;

ALTER SESSION SET nls_sort = 'German';

SELECT ENTITY from TEST order by ENTITY ;

select * FROM yy;

SELECT * FROM TEST,yy WHERE entity BETWEEN min_entity AND max_entity ;

ALTER SESSION SET nls_sort = BINARY;

SELECT * FROM TEST,yy WHERE entity BETWEEN min_entity AND max_entity ;

The output :  

Connected to Oracle9i Enterprise Edition Release 9.2.0.4.0 Connected as ps

SQL> Session altered ( binary sort)

ENTITY

---

0
1
A
a

MIN_ENTITY MAX_ENTITY
---------- ----------

0          0
1          A
a          a

Session altered ( german sort)

ENTITY

---

a
A
0
1

MIN_ENTITY MAX_ENTITY
---------- ----------

a          a
0          A
1          1

 

ENTITY MIN_ENTITY MAX_ENTITY
------ ---------- ----------

a      a          a
0      0          A
A      0          A
1      0          A                       --------> notice that entity 1
apper twice
1      1          1                      ---------> this is a bug . 



                                           -------> because if the between

was working like the order by , that 1 0 A - wouldn't be .        

Session altered (binary sort)

ENTITY MIN_ENTITY MAX_ENTITY
------ ---------- ----------

0      0          0
A      1          A
1      1          A
a      a          a

SQL>   Amihay Gonen
DBA,
972-3-9268280

Be there ... -> http://golan/Forum%20DBA/default.aspx <http://golan/Forum%20DBA/default.aspx>  

--
http://www.freelists.org/webpage/oracle-l

Received on Sun May 08 2005 - 11:55:21 CDT