Offset not working in SAP HANA

Question

I am trying to run a simple query using OFFSET keyword in SAP HANA, but I am getting same data for all Offset values.

 select * from tableName order by BillingDate limit 10 OFFSET 0;

select * from tableName order by BillingDate limit 10 OFFSET 11;

Both of queries are returning the same result. Can someone help me?

Answer 1

We have finally found the answer to this question. Actually, there are two different types of tables on HANA First is a normal physical table which is actually present on HANA, for this type of table offset and the limit query is working as expected. The second type of tables are virtual tables

On these tables, an OFFSET query is not working link here Note: In our case, we were querying a virtual table

Answer 2

I have had problems using OFFSET in some queries as well. I tracked down the problem to what I believe is a bug in Hana's OLAP engine in version 1.0.120.x.

Forcing the execution engine to COLUMN resolved the problem but then we loose the performance benefits of the OLAP engine. In order to force Hana NOT to use the OLAP engine you can append the following SQL clause:

WITH HINT(NO_USE_OLAP_PLAN)

The bug apparently have been fixed in Hana version 1.0.122.x though.

Answer 3

It would be helpful if you can share some info/scripts related to the HANA version & data model.

OFFSET and LIMIT will work in HANA SQL (I am using version HANA 02 SP03) with or without the use of ORDER BY. However, technically it wont make sense to use LIMIT and OFFSET without ORDER BY.

The use of OFFSET without ORDER BY is unreliable since the order could change over time (unless you pair it with an ORDER BY query).

For Example: A sample entity with ProductID as the key.

Let's fill it up with sample data

Scenario 1 : With Order By

Scenario 2 : Without Order By

Scenario 3 : 0 Offset check