HANA and not fully qualified SELECT SINGLE statements - an issue?

[BMEIJS]

As part of an upgrade from Oracle to HANA (ECC), we have checked all (used) custom programs using the Code Inspector using check variant FUNCTIONAL_DB to find possible HANA issues. Now, during functional testing, one specific issue pops up related to a SELECT SINGLE statement with an incompletely specified key. (SELECT SINGLE * FROM KNVV INTO WA_KNVV WHERE KUNNR = I_KUNNR). According to the tester, the row that is selected now is different from the expected one.

I have used the Code Inspector to check for all SELECT SINGLE statements with incomplete specified key and found a lot of them. However, SAP did not add this check to the variant FUNCTIONAL_DB, so I'm wondering if there really is a problem here.

So my questions are:

1. Do all tables still have a primary index? I have performed a database object check for KNVV via SE11 and this says there is still a consistent index KNVV~0 so I presume these indices still exist.
2. In general, if I perform SELECT SINGLE without using fully qualified primary key, will HANA still use the primary index (if for example the first keys are specified and the last key field isn't)
3. Are there situations where a SELECT SINGLE without fully qualified key on a HANA database can return a different row from the table than on an Oracle or other non-HANA database?

I fully understand that the best solution is analyzing and changing these SELECT SINGLE statements, but for the moment I want to know if there actually is a real problem. If this is the case, I am going to add the check on SELECT SINGLE to my next HANA upgrade projects.

[thomasgauweiler]

The existence of an primary key index does not imply that the index is being used by HANA.

When you do a SELECT SINGLE with an incomplete key, the database can deliver any of the matching records. Most databases use the primary key for access but HANA does not.

[BMEIJS]

Thanks Thomas for your swift response.

In the mean time I have done some tests and found this to be the case. This means that for an impact analysis for HANA upgrades, this issue must also be taken into account. Actually, I think that this will have a large impact on the upgrade effort, because a lot of companies now (often without knowing this) are using a lot of these SELECT SINGLE statements that need to be rewritten.

So I think it would be useful to have this check as part of the FUNCTIONAL_DB inspection variant. 

[thomasgauweiler]

Please check if SAP Note 2251947 helps for your case.

[MGXDev]

Hi B. Meijs,

here is a link to my blog and document about the Mandatory Steps to Adapt ABAP code for HANA, where this problematic is discussed in more detail:

http://scn.sap.com/community/abap/hana/blog/2016/01/18/mandatory-steps-to-adapt-abap-code-for-sap-ha...

[BMEIJS]

Thank you Michael,

Just last week I received a link to this document via the Dutch speaking SAP User group (VNSG).

Regards

Ben Meijs

[BMEIJS]

Now that we are live with BW, CRM and ECC on HANA, I can share my HANA-related ABAP-experiences (besides the 'normal' upgrade issues and Unicode Issues we also had to solve):

1. There were only a handfull of issues related to SELECT and missing ORDER BY, direct reading of Pool or Clusters, or native SQL statements.
2. However, we found lots of real issues related to the following statements:

• SELECT SINGLE without fully qualified key
• SELECT. EXIT. ENDSELECT statements
• SELECT CHECK ENDSELECT statements
• SELECT UP TO 1 ROWS statements
• SELECT INTO TABLE statements where sequence of resulting table is important for subsequent processing using LOOP, even without AT NEW/ AT FIRST events.

Example of what can go wrong:

SELECT SINGLE * FROM LIPS INTO WA_LIPS WHERE VBELN = I_VBELN. (I know, you would never program like this )

Pre-HANA: returns first position because data is returned in sequence of primary key

HANA: returns a position, NOT IN SEQUENCE OF PRIMARY KEY

The fact that the Code Inspector doesn't check modifications, ABAP query code and generated SMARTFORMS-functions makes finding issues more difficult.

Using the Runtime Check Monitor (SRTCM) during testphase has helped to find some issues regarding FOR ALL ENTRIES and Sorting Order.

The best tip: test, test and test again. And instruct testers to look for strange occurences that can be related to the issues above. (i.e. they must check if the output is really the same as before etc)

The next step: finding the statements with highest potential to improve performance.