Home » SQL & PL/SQL » SQL & PL/SQL » Difference in SQL 'order by' and MIN, MAX, LISTAGG results (oracle 12c, client 11G sous windows)
Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666885] Thu, 30 November 2017 07:44 Go to next message
moracles
Messages: 16
Registered: July 2017
Junior Member
Hi,
could anyone help me to understand, why the 'order by' result is not the same in my test here :

select x  from (select 'AA' x from dual union  select 'A_' x from dual) order by x ASC;
produces expected result: 'A_' before 'AA'

while
select 
  min(x) "min_x"
, max(x) "max_x"  
, listagg(x, ' # ') WITHIN GROUP (ORDER BY x ASC) "liste_x" 
from (select x from (select 'AA' x from dual union  select 'A_' x from dual)) s  ;

produces unexpected result : min_x='AA', max_x='A_' and liste_x='AA # A_'

Regards

[Updated on: Thu, 30 November 2017 07:47]

Report message to a moderator

Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666886 is a reply to message #666885] Thu, 30 November 2017 07:49 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
orclx> select x  from (select 'AA' x from dual union  select 'A_' x from dual) order by x ASC;

X
--
AA
A_

orclx>
orclx>
orclx>
orclx> select
  2    min(x) "min_x"
  3  , max(x) "max_x"
  4  , listagg(x, ' # ') WITHIN GROUP (ORDER BY x ASC) "liste_x"
  5  from (select x from (select 'AA' x from dual union  select 'A_' x from dual)) s  ;

mi ma
-- --
liste_x
--------------------------------------------------------------------------------------------------------------
AA A_
AA # A_


orclx>
Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666888 is a reply to message #666886] Thu, 30 November 2017 08:13 Go to previous messageGo to next message
moracles
Messages: 16
Registered: July 2017
Junior Member
Hi,
why is it different for me :
SQL> COLUMN x FORMAT A10
SQL> COLUMN min_x FORMAT A10
SQL> COLUMN max_x FORMAT A10
SQL> COLUMN liste_x FORMAT A10
SQL> 
SQL> select x  from (select 'AA' x from dual union  select 'A_' x from dual) order by x ASC;

X                                                                               
----------                                                                      
A_                                                                              
AA                                                                              

SQL> --
SQL> select
  2    min(x) "min_x"
  3  , max(x) "max_x"
  4  , listagg(x, ' # ') WITHIN GROUP (ORDER BY x ASC) "liste_x"
  5  from (select x from (select 'AA' x from dual union  select 'A_' x from dual)) s  ;

min_x      max_x      liste_x                                                   
---------- ---------- ----------                                                
AA         A_         AA # A_                                                   

SQL> spool off

Does it depend on any database parameter , (NLS...) ?

Regards

[Updated on: Thu, 30 November 2017 08:17]

Report message to a moderator

Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666892 is a reply to message #666888] Thu, 30 November 2017 08:56 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Maybe. what version of Oracle?

I have tried on 11.2.0.3, 12.1.0.2 and 12.2.0.1 and all produce the same results as John.
Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666893 is a reply to message #666892] Thu, 30 November 2017 09:00 Go to previous messageGo to next message
moracles
Messages: 16
Registered: July 2017
Junior Member
I'm working with :
Quote:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
"CORE 12.1.0.2.0 Production" 0
TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0
Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666894 is a reply to message #666893] Thu, 30 November 2017 09:11 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Do you have access to any DBs on a different version to try it out?
Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666896 is a reply to message #666893] Thu, 30 November 2017 09:19 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
All my version are enterprise edition.
Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666897 is a reply to message #666896] Thu, 30 November 2017 09:31 Go to previous messageGo to next message
moracles
Messages: 16
Registered: July 2017
Junior Member
No, I don't have access to another oracle database.
Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666898 is a reply to message #666897] Thu, 30 November 2017 09:31 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Show us the results of:
show parameter nls
Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666899 is a reply to message #666898] Thu, 30 November 2017 09:57 Go to previous messageGo to next message
moracles
Messages: 16
Registered: July 2017
Junior Member
NAME                                               TYPE        VALUE                                                                                                
-------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------- 
nls_calendar                                       string                                                                                                           
nls_comp                                           string      BINARY                                                                                               
nls_currency                                       string      €                                                                                                    
nls_date_format                                    string      DD/MM/RR                                                                                             
nls_date_language                                  string                                                                                                           
nls_dual_currency                                  string                                                                                                           
nls_iso_currency                                   string      FRANCE                                                                                               
nls_language                                       string      FRENCH                                                                                               
nls_length_semantics                               string      BYTE                                                                                                 
nls_nchar_conv_excp                                string      FALSE                                                                                                
nls_numeric_characters                             string      ,                                                                                                    
nls_sort                                           string      FRENCH                                                                                               
nls_territory                                      string      FRANCE                                                                                               
nls_time_format                                    string                                                                                                           
nls_timestamp_format                               string      DD/MM/RR HH24:MI:SSXFF                                                                               
nls_timestamp_tz_format                            string      DD/MM/RR HH24:MI:SSXFF TZR                                                                           
nls_time_tz_format                                 string                                                                                                           

[Updated on: Thu, 30 November 2017 10:01]

Report message to a moderator

Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666900 is a reply to message #666899] Thu, 30 November 2017 10:13 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
What happens if you set nls_sort to BINARY?
Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666901 is a reply to message #666900] Thu, 30 November 2017 10:18 Go to previous messageGo to next message
moracles
Messages: 16
Registered: July 2017
Junior Member
Indeed, it has changed, but isn't it the opposite of expected ('A_' should be before 'AA' ) ?

SQL> alter session set nls_sort=BINARY;

Session altered.


SQL> select x  from (select 'AA' x from dual union  select 'A_' x from dual) order by x ASC;

X
----------
AA
A_

SQL> select
  2    min(x) "min_x"
  3  , max(x) "max_x"
  4  , listagg(x, ' # ') WITHIN GROUP (ORDER BY x ASC) "liste_x"
  5  from (select x from (select 'AA' x from dual union  select 'A_' x from dual)) s  ;

min_x      max_x      liste_x
---------- ---------- ----------
AA         A_         AA # A_

[Updated on: Thu, 30 November 2017 10:24]

Report message to a moderator

Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666902 is a reply to message #666901] Thu, 30 November 2017 10:42 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
_ comes after A in the standard ascii character set, so that's the correct order for a binary sort.
Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666903 is a reply to message #666902] Thu, 30 November 2017 10:44 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
So it looks like your nls_sort setting was affecting the order by but not the aggregates.
I would assume that's a bug
Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666904 is a reply to message #666903] Thu, 30 November 2017 10:53 Go to previous messageGo to next message
moracles
Messages: 16
Registered: July 2017
Junior Member
Thanks a lot every body.
I will work with BINARY sort when running PLSQL procedure.

Best regards

[Updated on: Thu, 30 November 2017 10:53]

Report message to a moderator

Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666906 is a reply to message #666903] Thu, 30 November 2017 14:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Database SQL Language Reference
Sorting Query Results

Quote:
The mechanism by which Oracle Database sorts character values for the ORDER BY clause, also known as the collation, is specified by the NLS_SORT session parameter.

So, the following result is correct for French:
SQL> alter session set nls_sort=french;

Session altered.

SQL> select x  from (select 'AA' x from dual union  select 'A_' x from dual) order by x ASC;
X
--
A_
AA

2 rows selected.

Now, the first reason that LISTAGG does not use this parameter is explained in Database Globalization Support Guide, Chapter 5 Linguistic Sorting and Matching, Section Performing Linguistic Comparisons about NLS_COMP:

Quote:
BINARY: All SQL collations and comparisons are based on the binary values of the string characters, regardless of the value set to NLS_SORT.

Now even if you set it to LINGUISTIC as advised ("When NLS_COMP is set to LINGUISTIC, SQL operations perform a linguistic comparison based on the value of NLS_SORT."):
SQL>  alter session set nls_comp=linguistic;

Session altered.

SQL> select
  2    min(x) "min_x"
  3  , max(x) "max_x"
  4  , listagg(x, ' # ') WITHIN GROUP (ORDER BY x ASC) "liste_x"
  5  from (select x from (select 'AA' x from dual union  select 'A_' x from dual)) s  ;
mi ma
-- --
liste_x
-----------------------------------------------------------------------------------------
A_ AA
AA # A_

The reason is just below the former quote in "Table 5-3 Linguistic Comparison Behavior with NLS_COMP Settings" which lists when this parameter is taken into account, and LISTAGG with its "WITHIN GROUP (ORDER BY)" clause is not there.
Is this a bug? Well, for Oracle, it works as documented.

Now, the question is why MIN and MAX in OP's output give the expected value given the NLS_SORT and NLS_COMP values he posted, I didn't get them in the same way but in the expected given the former table:
SQL> alter session set nls_sort=french nls_comp=binary;

Session altered.

SQL> select
  2    min(x) "min_x"
  3  , max(x) "max_x"
  4  from (select x from (select 'AA' x from dual union  select 'A_' x from dual)) s  ;
mi ma
-- --
AA A_

1 row selected.
(Note: I did this in 11.2.0.4.170418)


Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666907 is a reply to message #666906] Thu, 30 November 2017 14:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Got the same thing (and OP's output) in 12.1 and 12.2:
SQL> @v

Oracle version: 12.1.0.2.160719

SQL> alter session set nls_sort=french nls_comp=binary;

Session altered.

SQL> select
  2    min(x) "min_x"
  3    , max(x) "max_x"
  4  from (select x from (select 'AA' x from dual union  select 'A_' x from dual)) s  ;
mi ma
-- --
AA A_

1 row selected.
SQL> @v

Oracle version: 12.2.0.1.170718

SQL> alter session set nls_sort=french nls_comp=binary;

Session altered.

SQL> select
  2    min(x) "min_x"
  3    , max(x) "max_x"
  4  from (select x from (select 'AA' x from dual union  select 'A_' x from dual)) s  ;
mi ma
-- --
AA A_

1 row selected.
Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666908 is a reply to message #666907] Thu, 30 November 2017 14:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And the same thing from a 11g client to a 12c database:
>sqlplus michel/michel@mikc

SQL*Plus: Release 11.2.0.4.0 Production on Jeu. Nov. 30 21:53:42 2017

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> @v

Oracle version: 12.1.0.2.160719

SQL> alter session set nls_sort=french nls_comp=binary;

Session altered.

SQL> select
  2    min(x) "min_x"
  3  , max(x) "max_x"
  4  from (select x from (select 'AA' x from dual union  select 'A_' x from dual)) s  ;
mi ma
-- --
AA A_

1 row selected.
Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666912 is a reply to message #666908] Fri, 01 December 2017 02:31 Go to previous messageGo to next message
moracles
Messages: 16
Registered: July 2017
Junior Member
Michel,
my default NLS are : NLS_SORT=FRENCH and NLS_COMP=BINARY.

In #666906 you say : Quote:
"Table 5-3 Linguistic Comparison Behavior with NLS_COMP Settings" which lists when this parameter is taken into account, and LISTAGG with its "WITHIN GROUP (ORDER BY)" clause is not there
The LISTAGG function is not explicitly in the table "5-3", but it's an analytic function with "OVER(ORDER BY)" isn't it ?

So, for me, according to the "Table 5-3 Linguistic Comparison Behavior with NLS_COMP Settings" ->"Analytic Function Clauses" -> "OVER(ORDER BY)"
The result of LISTAGG ("Honors NLS_SORT"), should be "A_ # AA", regardless of NLS_COMP value.

What about my solution, which consists of working with NLS_SORT=BINARY ?

Regards



Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666916 is a reply to message #666912] Fri, 01 December 2017 02:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
my default NLS are : NLS_SORT=FRENCH and NLS_COMP=BINARY.
So you should have the same results than those I posted.
Note: he would be easier to see what you have if you post it.

Quote:
The LISTAGG function is not explicitly in the table "5-3", but it's an analytic function with "OVER(ORDER BY)" isn't it ?
No, LISTAGG has no "OVER(ORDRE BY)" clause.

Quote:
What about my solution, which consists of working with NLS_SORT=BINARY ?
It will give you a binary sort, it is good if this is what you want, it is not if you want a linguistic sort.
So only you can define what you want.
But anyway LISTAGG will give a binary sort within each row of the result.
Or you could write your own LISTAGG function, search on the board for STRAGG you will find several alternate codes for it.

Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666918 is a reply to message #666916] Fri, 01 December 2017 03:16 Go to previous messageGo to next message
moracles
Messages: 16
Registered: July 2017
Junior Member
Michel,

I've posted all details :
#666885 : Quote:
listagg(x, ' # ') WITHIN GROUP (ORDER BY x ASC)
, but you're right it's not exactly the same as OVER(ORDER BY)

#666899 : NLS parameter's values.

All I need, is to obtain the same result from 'select ... order by' and LISTAGG in PLSQL procedure.

Thanks a lot for your prticipation


Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666928 is a reply to message #666885] Fri, 01 December 2017 09:06 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
So you want LISTAGG to use French ordering, right? Then orader by NLSSORT:

SQL> show parameter nls_sort

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_sort                             string
SQL> show parameter nls_comp

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_comp                             string      BINARY
SQL> select  min(x) "min_x",
  2          max(x) "max_x",
  3          listagg(x,' # ') WITHIN GROUP (ORDER BY x ASC) "liste_x"
  4    from  (
  5           select 'AA' x from dual union all
  6           select 'A_' x from dual
  7          )
  8  /

mi ma liste_x
-- -- ---------------
AA A_ AA # A_

SQL> select  min(x) "min_x",
  2          max(x) "max_x",
  3          listagg(x,' # ') WITHIN GROUP (ORDER BY NLSSORT(x,'NLS_SORT=FRENCH')) "liste_x"
  4    from  (
  5           select 'AA' x from dual union all
  6           select 'A_' x from dual
  7          )
  8  /

mi ma liste_x
-- -- ---------------
AA A_ A_ # AA

SQL> 

SY.
Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666929 is a reply to message #666928] Fri, 01 December 2017 09:21 Go to previous messageGo to next message
moracles
Messages: 16
Registered: July 2017
Junior Member
Thanks Salomon,

but it's already fixed.
I unsubscribe.

P.S.
min_x=AA and max_x=A_ is wrong result
Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666930 is a reply to message #666929] Fri, 01 December 2017 10:11 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
My reply was to clarify Michel's "LISTAGG will give a binary sort within each row of the result. Or you could write your own LISTAGG function". In regards to min/max, yes you need NLS_COMP=LINGUISTIC which comes at performance cost.

SY.
Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666931 is a reply to message #666929] Fri, 01 December 2017 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
P.S.
min_x=AA and max_x=A_ is wrong result
It is correct as BINARY is used.


Quote:
All I need, is to obtain the same result from 'select ... order by' and LISTAGG in PLSQL procedure.

Will your client accept the following result?
SQL> alter session set nls_sort=binary;

Session altered.

SQL> select x from t order by x;
X
-----
 A
 E
 I
 U
 Z
 a
 e
 i
 u
 z
 é
 é
 è
 è
 î
 î
 û

Solomon's solution would be a correct one (given your standard settings for NLS_COMP and NLS_SORT) but there are numerous bugs when using NLSSORT and NLS_SORT with analytic functions from error ORA-04030 to wrong results, have a look in MOS. Before using it, I'd ask Oracle to guarantee that all known bugs in this matter are fixed for the versions you use and will use.

[Updated on: Fri, 01 December 2017 10:28]

Report message to a moderator

Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666932 is a reply to message #666931] Fri, 01 December 2017 10:27 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
you're right it's not exactly the same as OVER(ORDER BY)

The devil is in the details. Smile

Previous Topic: regexp_replace() function help
Next Topic: SQL Query
Goto Forum:
  


Current Time: Fri Mar 29 09:06:11 CDT 2024