You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "Fly-a-Kite (via GitHub)" <gi...@apache.org> on 2026/05/24 07:38:03 UTC

[I] DataFusion drops grouped row after inner ORDER BY/LIMIT and outer ORDER BY/OFFSET [datafusion]

Fly-a-Kite opened a new issue, #22489:
URL: https://github.com/apache/datafusion/issues/22489

   ### Describe the bug
   
   ## Summary
   
   DataFusion `53.0.0` returns an empty result for a two-row grouped aggregate when an inner ordered `LIMIT` is followed by an outer `ORDER BY ... OFFSET 1`.
   
   This may be related to #22190, but the reproducer differs: it does not use a NULL aggregate sort key or `MIN`/`MAX`. The row loss appears with a non-NULL grouped result when an inner `ORDER BY/LIMIT` is followed by an outer `ORDER BY/OFFSET`.
   
   ## Environment
   
   - `datafusion==53.0.0`
   - `pyarrow==24.0.0`
   - Python `3.12.3`
   
   ## Reproduction
   
   ```python
   #!/usr/bin/env python3
   
   from __future__ import annotations
   
   import datafusion
   import pyarrow as pa
   from datafusion import SessionContext
   
   
   def register(ctx: SessionContext, name: str, rows: list[dict], schema: pa.Schema) -> None:
       batch = pa.RecordBatch.from_pylist(rows, schema=schema)
       ctx.register_record_batches(name, [[batch]])
   
   
   def main() -> None:
       ctx = SessionContext()
   
       register(
           ctx,
           "t0",
           [{"id": 0}, {"id": 1}],
           pa.schema([pa.field("id", pa.int64())]),
       )
       register(
           ctx,
           "t1",
           [{"id": 1, "j": 1}],
           pa.schema([
               pa.field("id", pa.int64()),
               pa.field("j", pa.int64(), nullable=True),
           ]),
       )
   
       base = (
           "SELECT t0.id, COUNT(t0.id) AS count_id, COUNT(DISTINCT j) AS nunique_j "
           "FROM t0 LEFT JOIN t1 ON t0.id = t1.id GROUP BY t0.id"
       )
   
       control_query = (
           f"SELECT * FROM ({base}) q "
           "ORDER BY id DESC NULLS LAST, count_id DESC NULLS LAST, nunique_j ASC NULLS LAST "
           "OFFSET 1"
       )
   
       failing_query = (
           f"SELECT * FROM ("
           f"  SELECT * FROM ({base}) q "
           "   ORDER BY id DESC NULLS LAST, count_id DESC NULLS LAST, nunique_j DESC NULLS LAST "
           "   LIMIT 8"
           ") q2 "
           "ORDER BY id DESC NULLS LAST, count_id DESC NULLS LAST, nunique_j ASC NULLS LAST "
           "OFFSET 1"
       )
   
       print(f"datafusion={getattr(datafusion, '__version__', 'unknown')}")
       print(f"pyarrow={pa.__version__}")
   
       control = ctx.sql(control_query).to_pandas()
       failing = ctx.sql(failing_query).to_pandas()
   
       print("control query result:")
       print(control)
   
       print("query with inner ORDER BY/LIMIT result:")
       print(failing)
   
       assert len(control) == 1 and int(control.iloc[0]["id"]) == 0
       assert len(failing) == 1 and int(failing.iloc[0]["id"]) == 0
   
   
   if __name__ == "__main__":
       main()
   ```
   
   ## Expected behavior
   
   Both queries should return the second grouped row after the final ordering and `OFFSET 1`:
   
   ```text
   id=0, count_id=1, nunique_j=0
   ```
   
   ## Actual behavior
   
   The control query returns the expected row, but the query with the inner `ORDER BY ... LIMIT 8` returns an empty result.
   
   Observed output:
   
   ```text
   control query result:
      id  count_id  nunique_j
   0   0         1          0
   
   query with inner ORDER BY/LIMIT result:
   Empty DataFrame
   Columns: [id, count_id, nunique_j]
   Index: []
   ```
   
   ### To Reproduce
   
   _No response_
   
   ### Expected behavior
   
   _No response_
   
   ### Additional context
   
   _No response_


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] DataFusion drops grouped row after inner ORDER BY/LIMIT and outer ORDER BY/OFFSET [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #22489:
URL: https://github.com/apache/datafusion/issues/22489#issuecomment-4548065099

   Thank you for the report @Fly-a-Kite 
   
   As this is a regression, I have added this to a potential 53 patch release
   - https://github.com/apache/datafusion/issues/22489
   
   Also I think we should fix it before releasing 54 (or maybe as a patch release)


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] DataFusion drops grouped row after inner ORDER BY/LIMIT and outer ORDER BY/OFFSET [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb closed issue #22489: DataFusion drops grouped row after inner ORDER BY/LIMIT and outer ORDER BY/OFFSET
URL: https://github.com/apache/datafusion/issues/22489


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org