Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2005
    Posts
    6

    Question Unanswered: Sorting / Filtering bug?

    Hello

    I do a simple query that gives me strange results on Oracle 9 / Windows:

    Code:
    SQL> select obj_key from myTable where obj_key >=  'A22222222' order by 1;
    
    OBJ_KEY
    ------------------------------
    A222222A
    A2222222A
    A22222222
    C123456CD
    . . .
    Why are 2 lines before the A22222222?
    The column OBJ_KEY is defined as CHAR(30)

    Does somebody knows what's going on?
    Thanks for any hint or clue,
    Hoggar (confused)
    Last edited by Hoggar74; 04-24-07 at 05:58.

  2. #2
    Join Date
    Jul 2006
    Posts
    49
    Probably need more information. You should always post your patchset level. Also, when encountering something that is obviously a bug you should either open a SAR on Oracle MetaLink or download the latest patchset from Oracle MetaLink to a dev server and see if that resolves the problem. The cut off date for bug correction support in 9.2.x is June 2007, so get those bugs to Oracle!

    There are known bugs in 9i and 10g related to functional indexes causing the order by clause to give wrong results. In particular when the order by clause references a CHAR column. Some are fixed in 9205, some in 9208.

    If you were running that in a CTAS operation then you might be hitting a known bug, fixed in 9206.

    Not sure if that was your exact and entire query, so I'll mention another known bug, which causes the order by clause to be ignored when queries are executed in parallel. Fixed in 9205.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    It is doing exactly what you told it to do. Lets look at the results

    A222222A
    A2222222A
    A22222222
    C123456CD

    The space character collates before the A or 2 character. Since you have made your field a char(30) (using char is normally a bad idea), so "A222222A <spaces...>" sorts before "A2222222A <spaces...> ".
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    The way data are sorted depends on the local settings.

    It is driven by the NLS_SORT parameter, which is derived from the NLS_LANGUAGE parameter. You should specify NLS_LANGUAGE in the NLS_LANG environment variable, but you can also set it at the database, instance, session and statement level.

    Code:
    rbaraer@Ora10g> alter session set NLS_LANGUAGE = 'FRENCH';
    
    Session modifi&#233;e.
    
    rbaraer@Ora10g> SELECT OBJ_KEY
      2  FROM
    (
      3    4      SELECT RPAD('A222222A', 30) OBJ_KEY
      5      FROM DUAL
      6      UNION ALL
      7      SELECT RPAD('A2222222A', 30) OBJ_KEY
      8      FROM DUAL
      9      UNION ALL
     10      SELECT RPAD('A22222222', 30) OBJ_KEY
     11      FROM DUAL
     12      UNION ALL
     13      SELECT RPAD('C123456CD', 30) OBJ_KEY
     14      FROM DUAL
     15  )
     16  WHERE OBJ_KEY >= 'A22222222'
     17  ORDER BY OBJ_KEY;
    
    OBJ_KEY
    ------------------------------
    A222222A
    A2222222A
    A22222222
    C123456CD
    
    rbaraer@Ora10g>  alter session set NLS_LANGUAGE = 'AMERICAN';
    
    Session altered.
    
    rbaraer@Ora10g> SELECT OBJ_KEY
      2  FROM
      3  (
      4      SELECT RPAD('A222222A', 30) OBJ_KEY
      5      FROM DUAL
      6      UNION ALL
      7      SELECT RPAD('A2222222A', 30) OBJ_KEY
      8      FROM DUAL
      9      UNION ALL
     10      SELECT RPAD('A22222222', 30) OBJ_KEY
     11      FROM DUAL
     12      UNION ALL
     13      SELECT RPAD('C123456CD', 30) OBJ_KEY
     14      FROM DUAL
     15  )
     16  WHERE OBJ_KEY >= 'A22222222'
     17  ORDER BY OBJ_KEY;
    
    OBJ_KEY
    ------------------------------
    A22222222
    A2222222A
    A222222A
    C123456CD
    
    rbaraer@Ora10g>
    I used RPAD to simulate the CHAR(30) datatype, which pads strings with blanks. See the different sorts ?

    HTH & Regards,

    rbaraer
    Last edited by RBARAER; 04-24-07 at 10:09.
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  5. #5
    Join Date
    Jul 2006
    Posts
    49
    Like I said, more information would have been helpful for reasons rbaraer indicated. Anyways, I get the same results as rbaraer on my 10.2.0.3 database.
    -Mark

  6. #6
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by RBARAER
    The way data are sorted depends on the local settings.
    But the evaluation of
    OBJ_KEY >= 'A22222222'
    does not, apparently.

  7. #7
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by ivon
    But the evaluation of
    OBJ_KEY >= 'A22222222'
    does not, apparently.
    You are right, NLS_SORT is just a matter of how to display data, which is a client-related matter. Of course which data is retrieved does NOT depend on client NLS settings .

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •