Result of OrderBy query differs from what DB returns

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
a.m.sidorenko
Posts: 9
Joined: Fri 21 Apr 2017 05:23

Result of OrderBy query differs from what DB returns

Post by a.m.sidorenko » Fri 21 Apr 2017 06:15

Hi.
Faced with a problem related to wrong result sorting after adding .OrderBy() extension method in code.
Assume we have following table named TEST_TABLE:

Code: Select all

  TITLE        
 ---------- 
  col_1     
  Patric    
  11_alloy  
  Zambia    
  zulu   
Now we want to get all records ordered by TITLE column.
Querying the DB directly:

Code: Select all

SELECT * from TEST_TABLE ORDER BY TITLE ASC;

  TITLE    
 ---------- 
  col_1     
  Patric    
  Zambia    
  zulu      
  11_alloy 


Querying the DB from code:

Code: Select all

db.TestRecords.OrderBy(x => x.Title).ToList();

SELECT "Extent1".TITLE
FROM TEST_TABLE "Extent1"
ORDER BY "Extent1".TITLE ASC

  TITLE    
 ---------- 
  11_alloy  
  Patric    
  Zambia    
  col_1     
  zulu
As you can see the generated query is similar but the order of elements in result differs. It looks like there is additional sorting of results after querying them from the database. It's unacceptable to us.
So I want to ask: are we doing something wrong or is it a bug in dotConnect for Oracle?
We are using dotConnect 9.2.222.0 in Direct Mode.

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Re: Result of OrderBy query differs from what DB returns

Post by Shalex » Mon 24 Apr 2017 18:31

a.m.sidorenko wrote:Querying the DB directly:

Code: Select all

SELECT * from TEST_TABLE ORDER BY TITLE ASC;

  TITLE    
 ---------- 
  col_1     
  Patric    
  Zambia    
  zulu      
  11_alloy 
We cannot reproduce such an ordering via SQLPlus shipped with Oracle Client 12.1 (64 bit) in our environment.

Please give us the following information:
1. What tool (name, version, capacity x86 or x64) you mean saying "querying the DB directly"?
2. Specify DDL of your TITLE column.
3. Are you querying exactly the same database in both cases?
4. Try switching to the OCI mode (via Oracle Client) of dotConnect for Oracle. Does this change the ordering?

a.m.sidorenko
Posts: 9
Joined: Fri 21 Apr 2017 05:23

Re: Result of OrderBy query differs from what DB returns

Post by a.m.sidorenko » Tue 25 Apr 2017 09:33

1. JetBrains DataGrip 2017.1, Oracle PL/SQL Developer 11.0.6.1796
2.

Code: Select all

create table TEST_TABLE
(
	TITLE VARCHAR2(50)
)
3. Yes
4. Yes, here are results using OCI mode with Oracle Instant Client 12.1.0.2 x64

Code: Select all

  TITLE    
---------- 
  col_1     
  Patric    
  Zambia    
  zulu      
  11_alloy
So, only in a Direct mode we see different ordering.

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Re: Result of OrderBy query differs from what DB returns

Post by Shalex » Wed 26 Apr 2017 17:15

Please enable dbMonitor and specify the exact SQL generated by your EF query in both cases:
1) in OCI mode (via Oracle Client)
2) in Direct mode

a.m.sidorenko
Posts: 9
Joined: Fri 21 Apr 2017 05:23

Re: Result of OrderBy query differs from what DB returns

Post by a.m.sidorenko » Fri 28 Apr 2017 06:22

OCI mode:
Image

Direct mode:
Image

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Re: Result of OrderBy query differs from what DB returns

Post by Shalex » Mon 01 May 2017 11:02

1. Please run the following code with your Oracle Server and specify the output:

Code: Select all

using System;
namespace forum_35300
{
    class Program
    {
        static void Main(string[] args)
        {
            string direct = "direct=true; uid=your_uid_here; pwd=your_pwd_here; host=your_host_here; service name=your_service_name_here;";

            string oci =   @"direct=false; uid=your_uid_here; pwd=your_pwd_here; data source=
(DESCRIPTION =
(ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = your_host_here)(PORT = 1521))
)
(CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = your_service_name_here)
)
);";

            var monitor = new Devart.Data.Oracle.OracleMonitor() { IsActive = true };

            TestOrdering(direct);
            TestOrdering(oci);

            Console.ReadKey();
        }

        private static void TestOrdering(string connString)
        {
            Console.WriteLine(Environment.NewLine);
            Console.WriteLine("=================");
            using (var conn = new Devart.Data.Oracle.OracleConnection())
            {
                conn.ConnectionString = connString;
                conn.Open();
                Console.WriteLine("Direct=" + conn.Direct);

                var cmd = conn.CreateCommand();
                try
                {
                    cmd.CommandText = "drop table forum_35300";
                    cmd.ExecuteNonQuery();
                }
                catch { }

                var script = new Devart.Data.Oracle.OracleScript();
                script.Connection = conn;
                script.ScriptText = @"
create table forum_35300 (
    title varchar(50)
);
insert into forum_35300 values ('Patric');
insert into forum_35300 values ('col_1');
insert into forum_35300 values ('Zambia');
insert into forum_35300 values ('zulu');
insert into forum_35300 values ('11_alloy');";
                script.Execute();

                var cmd2 = conn.CreateCommand();
                cmd2.CommandText = "select title from forum_35300 order by title asc";
                var reader = cmd2.ExecuteReader();
                while (reader.Read())
                {
                    Console.WriteLine(reader.GetString(0));
                }
            }
        }
    }
}
2. Specify the output of this query (execute it via some database management tool) to describe your Oracle Server settings:

Code: Select all

select * from nls_database_parameters
3. Please play with NLS_SORT parameter of the session.
Documentation: https://docs.oracle.com/cd/B19306_01/se ... ams130.htm.
How to set session parameters via connection string: viewtopic.php?f=1&t=27970#p120034.
Does this help?

a.m.sidorenko
Posts: 9
Joined: Fri 21 Apr 2017 05:23

Re: Result of OrderBy query differs from what DB returns

Post by a.m.sidorenko » Tue 02 May 2017 05:43

1.

Code: Select all

=================
Direct=True
11_alloy
Patric
Zambia
col_1
zulu


=================
Direct=False
col_1
Patric
Zambia
zulu
11_alloy
2.

Code: Select all

+-------------------------+------------------------------+
| NLS_RDBMS_VERSION       | 12.1.0.2.0                   |
| NLS_NCHAR_CONV_EXCP     | FALSE                        |
| NLS_LENGTH_SEMANTICS    | BYTE                         |
| NLS_COMP                | BINARY                       |
| NLS_DUAL_CURRENCY       | $                            |
| NLS_TIMESTAMP_TZ_FORMAT | DD-MON-RR HH.MI.SSXFF AM TZR |
| NLS_TIME_TZ_FORMAT      | HH.MI.SSXFF AM TZR           |
| NLS_TIMESTAMP_FORMAT    | DD-MON-RR HH.MI.SSXFF AM     |
| NLS_TIME_FORMAT         | HH.MI.SSXFF AM               |
| NLS_SORT                | BINARY                       |
| NLS_DATE_LANGUAGE       | AMERICAN                     |
| NLS_DATE_FORMAT         | DD-MON-RR                    |
| NLS_CALENDAR            | GREGORIAN                    |
| NLS_NUMERIC_CHARACTERS  | .,                           |
| NLS_NCHAR_CHARACTERSET  | AL16UTF16                    |
| NLS_CHARACTERSET        | CL8MSWIN1251                 |
| NLS_ISO_CURRENCY        | AMERICA                      |
| NLS_CURRENCY            | $                            |
| NLS_TERRITORY           | AMERICA                      |
| NLS_LANGUAGE            | AMERICAN                     |
+-------------------------+------------------------------+
3. I'll play with NLS_SORT and will report later.

a.m.sidorenko
Posts: 9
Joined: Fri 21 Apr 2017 05:23

Re: Result of OrderBy query differs from what DB returns

Post by a.m.sidorenko » Tue 02 May 2017 05:55

Well, adding
Run Once Command=\"BEGIN EXECUTE IMMEDIATE \'ALTER SESSION SET NLS_SORT=RUSSIAN\'; END;\"
to the connection string did the trick, thank you!

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Re: Result of OrderBy query differs from what DB returns

Post by Shalex » Tue 02 May 2017 08:10

A different default behavior of OCI (via Oracle Client) and Direct modes can be a result of using its own NLS_LANG setting by Oracle Client. You can check it via registry, e.g.: HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\KEY_OraClient12Home1 > the NLS_LANG value.

Post Reply