Avoid creating subqueries for OFFSET (might also be new feature in DB2)

[windiana42]

This code even produces buggy subqueries:

import sqlalchemy as sa

url = "db2+ibm_db://db2inst1:password@localhost:50000/testdb"

engine = sa.create_engine(url)

with engine.connect() as conn:
    conn.execute(sa.text('CREATE TABLE IF NOT EXISTS "_tmp02" (id INT, name VARCHAR(100))'))
    conn.commit()
tbl = sa.Table("_tmp02", sa.MetaData(), autoload_with=engine).alias("_raw_tmp02")
tbl2 = tbl.alias("_other_tmp02")

id = tbl.c.id.label("id")
x = sa.func.coalesce(id.isnot(None), 0).label("id2")
query = sa.select(id, x).limit(10)
print("Query:")
print(query.compile(engine, compile_kwargs={"literal_binds": True}))
print()
print("Query with offset:")
query = query.offset(5)
print(query.compile(engine, compile_kwargs={"literal_binds": True}))
print()
print('Problem exists (see IS NOT NULL AS "Z.__db2_2"):')
print('coalesce("_raw_tmp02".id IS NOT NULL AS "Z.__db2_2",  0)' in str(query.compile(engine, compile_kwargs={"literal_binds": True})))

output:

Query:
SELECT "_raw_tmp02".id AS id, coalesce("_raw_tmp02".id IS NOT NULL, 0) AS id2 
FROM "_tmp02" AS "_raw_tmp02" LEFT OUTER JOIN "_tmp02" AS "_other_tmp02" ON "_raw_tmp02".id = "_other_tmp02".id FETCH FIRST 10 ROWS ONLY

Query with offset:
( SELECT  id, "Z.__db2_2", id2  FROM ( SELECT  id, "Z.__db2_2", id2 , ( ROW_NUMBER() OVER() ) AS "Z.__ROWNUM" FROM (  SELECT "_raw_tmp02".id AS id,  coalesce("_raw_tmp02".id IS NOT NULL AS "Z.__db2_2",  0) AS id2  
FROM "_tmp02" AS "_raw_tmp02" LEFT OUTER JOIN "_tmp02" AS "_other_tmp02" ON "_raw_tmp02".id = "_other_tmp02".id  ) AS M ) Z WHERE "Z.__ROWNUM" > 5 AND  "Z.__ROWNUM" <= 15 )

Problem exists (see IS NOT NULL AS "Z.__db2_2"):
True

This query would be ideal:

SELECT "_raw_tmp02".id AS id, coalesce("_raw_tmp02".id IS NOT NULL, 0) AS id2
FROM "_tmp02" AS "_raw_tmp02" LEFT OUTER JOIN "_tmp02" AS "_other_tmp02" ON "_raw_tmp02".id = "_other_tmp02".id
LIMIT 10 OFFSET 5

This might also be a new feature of DB2

[bimalkjha]

@windiana42 Db2 does not support the LIMIT clause in the same way that databases like MySQL or PostgreSQL do. Instead, Db2 uses the FETCH FIRST n ROWS ONLY syntax to limit the number of rows returned by a query.

Example:

SELECT * FROM employees
ORDER BY hire_date
FETCH FIRST 10 ROWS ONLY;

If you're using Db2 for LUW (Linux, Unix, Windows), this syntax is standard.

If you try to run your suggested ideal query on Db2 server, it will give error. Server can not execute an SQL which is not supported by it. Thanks.

[windiana42]

My DB2 container executes the following statements exactly as expected:

drop table if exists "_tmp";
create table "_tmp" (col1 int);
insert into "_tmp" values (1),(2),(3),(4);
select * from "_tmp" order by col1
LIMIT 2 OFFSET 1;

docker-compose.yaml (attention: startup time might be >5min before the DB is reachable):

services:
  ibm_db2:
    platform: linux/x86_64
    image: icr.io/db2_community/db2
    privileged: true
    environment:
      LICENSE: accept
      DB2INSTANCE: db2inst1
      DB2INST1_PASSWORD: password
      DBNAME: testdb
      UPDATEAVAIL: NO
    ports:
      - 50000:50000

I have no problem with replacing LIMIT with FETCH FIRST ROWS ONLY. However, the subquery logic with replacing some column names just doesn't work reliably.

[bimalkjha]

Ok. I verified and seems new version of Db2 support both LIMIT and OFFSET clause in SQL. @bchoudhary6415 Please check what is the issue here. Thanks.

[bchoudhary6415]

@bimalkjha, I will debug and update the code and raise a PR for it.

Thank you!

[bimalkjha]

select * from "_tmp" order by col1 LIMIT 2 OFFSET 1

COL1

      2
      3

2 record(s) selected.

select * from "_tmp" order by col1 LIMIT 2

COL1

      1
      2

2 record(s) selected.

select * from "_tmp" order by col1 OFFSET 1
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "der by
col1 OFFSET 1". Expected tokens may include: "<row_rows>". SQLSTATE=42601

[bchoudhary6415]

select * from "_tmp" order by col1 LIMIT 2 OFFSET 1

COL1

      2
      3

2 record(s) selected.

select * from "_tmp" order by col1 LIMIT 2

COL1

      1
      2

2 record(s) selected.

select * from "_tmp" order by col1 OFFSET 1 SQL0104N An unexpected token "END-OF-STATEMENT" was found following "der by col1 OFFSET 1". Expected tokens may include: "<row_rows>". SQLSTATE=42601

@windiana42 ,
DB2 requires that the OFFSET clause be used together with a FETCH FIRST n ROWS ONLY (or FETCH NEXT n ROWS ONLY) clause. Unlike some databases (e.g., SQLite), DB2 does not support using OFFSET by itself without a corresponding row limit. Therefore, queries that include only OFFSET without a limit will result in a syntax error:

SQL0104N An unexpected token "END-OF-STATEMENT" was found following "der by col1 OFFSET 1". Expected tokens may include: "<row_rows>". SQLSTATE=42601

[bimalkjha]

Fix for this issue got delivered using #167