Result of OrderBy query differs from what DB returns

Result of OrderBy query differs from what DB returns

Postby 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.
a.m.sidorenko
 
Posts: 9
Joined: Fri 21 Apr 2017 05:23

Re: Result of OrderBy query differs from what DB returns

Postby 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?
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

Re: Result of OrderBy query differs from what DB returns

Postby 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.
a.m.sidorenko
 
Posts: 9
Joined: Fri 21 Apr 2017 05:23

Re: Result of OrderBy query differs from what DB returns

Postby 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
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

Re: Result of OrderBy query differs from what DB returns

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

OCI mode:
Image

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

Re: Result of OrderBy query differs from what DB returns

Postby 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/server.102/b14237/initparams130.htm.
How to set session parameters via connection string: http://forums.devart.com/viewtopic.php?f=1&t=27970#p120034.
Does this help?
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

Re: Result of OrderBy query differs from what DB returns

Postby 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

Postby 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!
a.m.sidorenko
 
Posts: 9
Joined: Fri 21 Apr 2017 05:23

Re: Result of OrderBy query differs from what DB returns

Postby 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.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle