ORDER BY in Custom-Code for HANA

[andreas_spix2]

Hi,

we are currently checking our custom-code and the most painful finding is the missig ORDER BY Statement. Before we checked our code we've searched the internet for hints to adjust the abap-code for hana. But in our findigs there were no explicit hints to the ORER BY. The Document "Condiderations for Custom ABAP Code During Migration to SAP HANA" describes the implicit sorting but there is no REDLIGHT for that.

In my opinion this is the most frequently finding in custom-code!

So I have a question for all custom-coders: ow shall we deal with the ORDER BY? Adjust all findings (we have more than 2500 findings!!!)? Adjust only special findings - but what ist "special"? Or no adjustments an we will see what happens in our functional tests?

Regarding for your answers. Thx.

Andi

[joachimrees1]

If you have assumed in the past that the lines returned by a select are always sorted in a specific way (which they are not) and relied on this assumption (what should not have been done), the yes, I think you should adjust all those findings with the appropriate ORDER BY to assure the results will be sorted the way it's expected by the following coding.

[vonglan]

Hi Andreas,

my understanding is as follows:

• Even without HANA, it is not guaranteed in what order the records are returned (without ORDER BY)
• BUT usually this does not lead to problems, because in most cases non-HANA databases return the records in the sort order of the primary key. For Oracle databases, SAP says in note 825653, Nr. 9, that the sort order depends on the key that is chosen for access by Oracle's cost-based optimizer.
• (Thinking more about it, problems will arise if there is an SQL command that used to use the primary key when the code was originally written and tested, and then the Cost-Based Optimizer decides differently after an Oracle upgrade, migration to new hardware, or things like that. We had a few problems resulting from this after migrating to different hardware.)
• Because with HANA, data is stored per column, I assume there is no such thing as a primary key. Therefore, I think with HANA it is very probable that every missing ORDER BY (that is not a false positive) will lead to different behavior of the program.
• I think  it is less effort to clean up the coding, than to think through for each case whether it is a false positive (whether the final result/output of the program will be incorrect due to different sort order).

Best regards,

Edo

[lbreddemann]

Of course SAP HANA provides primary keys.

That's not a concept that is bound to any type of table implementation (row, column, banana store) or index support. It's a semantic quality of a group of columns of a table.

Honestly I have a hard time to believe that there are still programmers out there, who claim to be professionals and even consider to rely on the default sort order without providing an ORDER BY.

This has been well known, documented and advised for many years now. In fact my very own first blog post ever () was just about this.

This is one of the occasions where there is no ambiguity: expecting sorted result sets without ORDER BY is simply wrong. It's not a bug, not a mishap, it's developer incompetence straight out.

[vonglan]

Hi Lars,

thanks for the link to your blog.

No need to bash me, though. I understand the topic (except the HANA bits, apparently). I only wanted to explain why there is a huge amount of code out there that is wrong in this aspect.

I think that the developers who do not use the ORDER BY maybe do so because they are confused because of the discrepancy between the "theory" ("not guaranteed, simply wrong") and the practice ("my user tested this and it works"), and no-one ever told them the details that explain the difference.

(I think you can educate people better - if this is what you want - if you think about where they are coming from.)

Best regards, Edo

[lbreddemann]

Sorry mate, surely wasn't meant as bashing in your direction. Not at all!

I see and partly agree with the point of educating people.

Yes, there are a lot of rather crappy defaults and degrees of freedom (permanent SELECT * ? rubbish! This should automatically extend to the full column list or a selection dialog or... , SELECT without ORDER BY followed by loop constructs? Put the ORDER BY there automatically and take the error rather on too much sorting, which is easy to fix if really avoidable, etc.).

On the other hand is the fact that ABAP developers typically are paid for their work and have quite an influence on the way their customers work. I think it's fair to expect careful work from them.

cheers,

Lars