R V (Customer) asked a question.

June 1, 2020 at 1:29 AM
Openedge 11.7 returns different result for 'Select COUNT(DISTINCT <ColumnName>).. HAVING (COUNT(1)' when compared to Oracle/MSSQL

Hi everyone,

 

I am facing a problem that has left me a little stumped. Any help or insight will be very helpful.

 

A table called Staples in TestV1 schema was loaded in Oracle, MSSQL and Openedge. There is a customer bug stating that a SQL query to extract a table's data in Openedge 11.1 is different to what is returned by Oracle and MSSQL databases. I now copied the table from Oracle and loaded it in Openedge.

 

The following queries yield the exact same result set in both Oracle and Openedge:

SELECT "Order ID" FROM "TestV1"."Staples" "Staples" order by "Order ID";

SELECT distinct("Order ID") FROM "TestV1"."Staples" "Staples" order by "Order ID";

SELECT "Order ID", count(*) FROM "TestV1"."Staples" "Staples" group by "Order ID" order by "Order ID";

SELECT "Staples"."Order ID", COUNT("Staples"."Order ID") AS "ctd_Order_ID_ok" FROM "TestV1"."Staples" "Staples" GROUP BY "Staples"."Order ID" order by 1;

 

 

But, I see that the result is not the same for this query:

In Oracle:

SELECT COUNT(DISTINCT "Staples"."Order ID") AS "ctd_Order_ID_ok" FROM "TestV1"."Staples" "Staples" HAVING (COUNT(1) > 0);

OUTPUT - 13653

 

(MSSQL also returns 13653)

 

In Openedge:

SELECT COUNT(DISTINCT "Staples"."Order ID") AS "ctd_Order_ID_ok" FROM "TestV1"."Staples" "Staples" HAVING (COUNT(1) > 0);

OUTPUT - 14256

 

 

This is my Openedge environment:

DB Version - 11.7

OS Version - Windows Server 2016

Table definition in Oracle (Oracle-Staples-DDL.txt) and Openedge (Openedge-Staples-DDL.txt) attached. No index/Contraints/Triggers/Dependencies on the table in both.

 

Data file (extract from Oracle) used to load Openedge table seems to be too large to attach here.

 

Am I missing to notice something here Or is this a bug?

 

I posted this on progresstalk.com last week, it still hasn't even been made public. This is blocking our build process, any help is greatly appreciated.

 

Thank You!

RV

  • 11 answers
  • 432 views

    • R V (Customer)

      Great, Thank you. I initially got an email saying that the post is not made public and waiting for approval. But don't recall seeing a notification saying that it has been, so wasn't sure.

  • Mohammed Sayeed Akthar (Progress Software)

    Hi RV,

     

    We will try out this query on Oracle and OE and shall let you know our observations on the difference in results. It would be great if we could get partial data (say, top 10000??) if not full. One easier way to do this is to, creating a table using below statement.

     

    Create table "pub.Staples_Temp" AS select top 10000 * from TestV1.Staples;

     

    And then, you can get us the .d file for pub.Stapes_Temp using Data Dictionary dump...

     

    Will that be possible?

     

     

    Thanks,

    Akthar.

    • R V (Customer)

      Hi Akthar

       

      This is where I am at. I am obviously missing some basics here, learning openedge as I go right now.

       

      SQLExplorer>Create table "pub.Staples_Temp" AS select top 10000 * from TestV1.Staples;

      === SQL Exception 1 ===

      SQLState=HY000

      ErrorCode=-210016

      [DataDirect][OpenEdge JDBC Driver][OpenEdge] Lock table is full. (7870)

       

      C:\OpenEdge\WRK>proutil TestV1 -C increaseto -L 16384

      OpenEdge Release 11.7.4 as of Wed Oct 10 19:02:14 EDT 2018

      Increase Params increasing lock table size (-L) from 8195 to 16384. (13979)

      C:\OpenEdge\WRK>

       

      c:\data>sqlexp -db TestV1 -S 9999 -user test -password password

      OpenEdge Release 11.7.4 as of Wed Oct 10 19:02:14 EDT 2018

      Connecting user "test" to URL "jdbc:datadirect:openedge://localhost:9999;databaseName=TestV1"... (8920)

      SQLExplorer>

      SQLExplorer>Create table "pub.Staples_Temp" AS select top 10000 * from TestV1.Staples;

      SQLExplorer>commit;

      SQLExplorer>select count(*) from "pub.Staples_Temp";

            count(*)

      --------------------

              10000

      SQLExplorer>exit

      c:\data>

       

      C:\OpenEdge\WRK>proutil TestV1 -C dump pub.Staples_Temp "C:\Users\admin.server\Desktop\"

      OpenEdge Release 11.7.4 as of Wed Oct 10 19:02:14 EDT 2018

      ERROR - Unable to find a cursor for a table number lookup. (8788)

      ERROR - No table with given table name found in the database. (8797)

      Binary Dump failed. (6253)

      C:\OpenEdge\WRK>

       

      I do see pub.STaples_Temp in TestV1 when I query PUB."_File". I am trying to understand what I am missing here, but wanted to run it by you as well in the interest of finding quicker resolution.

       

      Thanks

      RV

       

      Thanks

      RV

       

       

       

  • Mohammed Sayeed Akthar (Progress Software)

    Hi RV,

     

    With the given partial data, I am not able to reproduce the issue. That means, I see same output on OpenEdge and Oracle. I used Oracle 12c and OpenEdge 11.7.4.

    Here are the results from my side using data of size 10K rows:

     

    Oracle 12c:

    SQL> SELECT COUNT(DISTINCT (ordid)) AS "ctd_Order_ID_ok" FROM staples;

     

    ctd_Order_ID_ok

    ---------------

          2655

     

    OpenEdge (11.7.4):

    SQLExplorer>SELECT COUNT(DISTINCT "Staples"."Ordid") AS "ctd_Order_ID_ok" FROM pub.staples;

       ctd_Order_ID_ok

    --------------------

            2655

     

    Following are the possibilities that I see:

     

    1. Maybe partial data is not enough to reproduce the issue?
    2. Possibility of difference in data in OE compared to Oracle/MSS? I am sure, you must have checked this out....but worth confirming again.
      1. select count ("order id") from TestV1.Staples; --- OpenEdge
      2. select count ("order id") from Staples; --- Oracle

     

    If we need the full data to reproduce the issue, then you might want to contact Progres Tech Support to know how you can share the full data (if possible to share).

     

    Thanks,

    Akthar.

     

    • R V (Customer)

      Hi Akthar,

       

      I get the same count in both Oracle and OpenEdge:

       

      SQLExplorer>SELECT count("Order ID") FROM "TestV1"."Staples" ;

         count(Order ID)

      --------------------

              54860

      SQLExplorer>

       

       

      Oracle:

      SQL> SELECT count("Order ID") FROM "TestV1"."Staples" ;

       count(Order ID)

      -------------------------

      54860

       

      Thanks

      RV

    • R V (Customer)

      Hi Akthar

      I called the Progress Tech Support number and the technician mentioned something about uploading the data for a support ticket (using Case #) to the FTP site, but I obviously do not have a case # yet. Also, when she tried to open a new support case for me to just get information on how to upload data, it said that the maintenance expired for my company. We run Community Edition in our Dev environment and I do not believe we have a purchased contract for support. So, I was asked to check back with you to see if you can help me to upload the data.

       

      FYI., I exported the data from Oracle (as a csv file) and loaded it into OpenEdge. That file is ~5MB. I believe I will have to sent that your way some how so you can try to repro the issue.

       

      Could you please help?

       

      Thanks

      RV

    • R V (Customer)

      Hi Akthar

       

      A quick update on this - There is a case # open now, hopefully I should be able to get the file over in a few hours.

       

      Thanks

      RV

    • R V (Customer)

      Hi Akthar

       

      csv file (extract from oracle 12c) that was loaded into OpenEdge 11.7 has been uploaded to the trueinc and I believe it will be passed on to you.

       

      Thanks

      RV

10 of 11