question

braxx avatar image
0 Votes"
braxx asked MelissaMa-msft answered

query results sorted randomly with each execution

Need your advice guys!

I am doing a migration of SQL queries from Oracle to Sql Server and observed a weird behavior of one of them. Each execution of the below query may give me results sorted different way and the wrong calculation of BAR_CURR_CODE column. While the same query run in Oracle always returns the same, stable results.

 SELECT
     PROD_ID,
     UOM_ID,
     FIRST_VALUE(BAR_CURR_CODE) over(partition BY prod_id order by
                                   case 
                                 when UOM_ID in ('IT') then 1
                                 when UOM_ID in ('MP') then 2
                                 when UOM_ID in ('BP') then 3
                                 when UOM_ID in ('CS') then 4
                                 when UOM_ID in ('SW') then 5
                                 else 99 end
                                 ) AS BAR_CURR_CODE
     , BAR_CURR_CODE AS org_BAR_CURR_CODE
 FROM
     [BSR_STG_Q].[Cdl_Prod_Uom_Fct]
 WHERE
     curr_ind  = 'Y'
     and BAR_CURR_CODE is not null
     and prod_Id = '00267461'

Here is the proof. First execution in SQL Server. BAR_CURR_CODE is different than in second execution.
81941-case1.png


Second execution in SQL Server. Made a couple of seconds after first one. Data were not updated during this time.

81934-case2.png

Here is the example of the same query from Oracle. The result is always the same with each execution

81942-case-ora1.png

I do not understand why the behaviour is different in SQL Server and Oracle and how can I fix it.
TIA

Bartek


sql-server-transact-sql
case1.png (32.0 KiB)
case2.png (32.2 KiB)
case-ora1.png (32.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

StefanHoffmann avatar image
2 Votes"
StefanHoffmann answered StefanHoffmann edited

You're operating on the wrong assumptions. Per definition of ANSI SQL, order of a result set is only defined and created by an explicit ORDER BY clause on the outer SELECT statement or expression of a window function.

Result sets may often have the same order even without ORDER BY statement due to internal implementation details. But there is NO guarantee for this. Thus you've been only lucky.

You're ordering by a CASE expression which returns 99 as constant value. This order is arbitrary per definition as it does not cover a candidate key. Thus add a "failback" order criteria and you're safe:

 SELECT PROD_ID ,
        UOM_ID ,
        FIRST_VALUE(BAR_CURR_CODE) OVER ( PARTITION BY prod_id
                                          ORDER BY CASE WHEN UOM_ID IN ( 'IT' ) THEN 1
                                                        WHEN UOM_ID IN ( 'MP' ) THEN 2
                                                        WHEN UOM_ID IN ( 'BP' ) THEN 3
                                                        WHEN UOM_ID IN ( 'CS' ) THEN 4
                                                        WHEN UOM_ID IN ( 'SW' ) THEN 5
                                                        ELSE 99
                                                   END ASC ,
                                                   UOM_ID ASC ) AS BAR_CURR_CODE ,
        BAR_CURR_CODE AS org_BAR_CURR_CODE
 FROM   BSR_STG_Q.Cdl_Prod_Uom_Fct
 WHERE  curr_ind = 'Y'
        AND NOT BAR_CURR_CODE IS NULL
        AND prod_Id = '00267461';


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered

Hi @braxx,

The syntaxsql of FIRST_VALUE function is as below:

FIRST_VALUE ( [scalar_expression ] ) [ IGNORE NULLS | RESPECT NULLS ]
OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )

The order_by_clause determines the logical order in which the operation is performed.

In your query, you only defined the situations when UOM_ID is 'IT' ,'MP' and so on.
But you did not define the specific order of 'B1' ,'B2' and 'E1' and all of these three UOM_ID would be defined as 99. Then the SQL could not identify the order of these.

You could add one more column in order part as suggested by Stefan.

In addition, you could also define these three UOM_IDs like below:

 SELECT
 PROD_ID,
 UOM_ID,
 FIRST_VALUE(BAR_CURR_CODE) over(partition BY prod_id order by
             case 
             when UOM_ID in ('IT') then 1
             when UOM_ID in ('MP') then 2
             when UOM_ID in ('BP') then 3
             when UOM_ID in ('CS') then 4
             when UOM_ID in ('SW') then 5
             when UOM_ID in ('B1') then 6
             when UOM_ID in ('B2') then 7
             when UOM_ID in ('E1') then 8
             else 99 end
             ) AS BAR_CURR_CODE
 , BAR_CURR_CODE AS org_BAR_CURR_CODE
 FROM
 [BSR_STG_Q].[Cdl_Prod_Uom_Fct]
 WHERE
 curr_ind  = 'Y'
 and BAR_CURR_CODE is not null
 and prod_Id = '00267461'

Finally, you could also consider to add 'rows between unbounded preceding and current row' or 'rows between unbounded preceding and unbounded following' after order part in case of any error appears.

Best regards
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

braxx avatar image
0 Votes"
braxx answered

Thank you both for your contribution. Apperciate it.

As I am doing a migration only from Oracle do SQL Server, I would like to avoid adding a new logic like

              when UOM_ID in ('B1') then 6
              when UOM_ID in ('B2') then 7
              when UOM_ID in ('E1') then 8


as it may behave differently in other cases not mentioned here.

What I noticed and what I supposed may be related to the problem is that the Oracle table has indexes created while SQL Server one not.

82382-capture-indexes.png


PK indexes may sort a data differet way I suppose. I would like to create the analogical indexes in Sql Server to verify it is causing the issue. The doubts I have here is how to recreate it properly:

Does Unique Clustered index can be used to recreate a "Normal" type index in Oracle?
How to recreate the "BITMAP" type index in sql server?



capture-indexes.png (13.8 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
1 Vote"
OlafHelper-2800 answered

How to recreate the "BITMAP" type index in sql server?

SQL Server don't have indexes of type BITMAP like Oracle.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered braxx commented

I would like to create the analogical indexes in Sql Server to verify it is causing the issue.

The issue is caused by the fact that the query is non-deterministic and the varying results you have received from SQL Server are all correct from how the query is written.

If you want the query to be deterministic, and be guaranteed to return a certain results, you will need to add WHEN lines like you have above:

               when UOM_ID in ('B1') then 6
               when UOM_ID in ('B2') then 7
               when UOM_ID in ('E1') then 8

If you do not, the result you get is just by chance. This applies both to Oracle and to SQL Server. There is no way around this.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @braxx

Thanks for your update.

We recommend that you could post CREATE TABLE statements for your table together with INSERT statements with sample data, the creation of non-clustered index ,enough to illustrate all angles of the problem. We also need to see the expected result of the sample after executing your query.

In SQL Server, there are clustered index and non-cluseted index and there are not Normal index or Bitmap index like in Oracle. But it has a feature called Bitmap Filters, which are different but similar. You could have a deep read on it and find out whether it is what you are looking for.

Best regards
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.