[MDEV-23908] Implement SELECT ... OFFSET ... FETCH ... Created: 2020-10-07  Updated: 2022-08-27  Resolved: 2021-04-21

Status: Closed
Project: MariaDB Server
Component/s: Optimizer, Parser
Fix Version/s: 10.6.0

Type: Task Priority: Critical
Reporter: Ian Gilfillan Assignee: Vicențiu Ciorbaru
Resolution: Fixed Votes: 0
Labels: Compatibility, SQL

Issue Links:
Problem/Incident
causes MDEV-29376 The return rows has error when a stat... Open
Relates
relates to MCOL-4645 Support basic SELECT..OFFSET..FETCH s... Closed
relates to MDEV-25430 ROW | ROWS should be a required keywo... Closed
relates to MDEV-25481 Memory leak in Cached_item_str::Cache... Closed
relates to MDEV-21286 bison warnings on ubuntu 20.04 on dep... Closed
relates to MDEV-25441 WITH TIES is not respected with SQL_B... Closed

 Description   

The 2008 SQL standard introduced the following syntax:

OFFSET start { ROW | ROWS }
 
FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }

See https://www.postgresql.org/docs/13/sql-select.html#SQL-LIMIT for a description.

This would also allow does not include the non-standard LIMIT WITH TIES.

 Comments   
Comment by Vicențiu Ciorbaru [ 2021-03-09 ]

The patch is pushed for review here:
https://github.com/MariaDB/server/compare/30dc4287ec3d46bae7593f56383b9f3738e3c4e6..cf2e494bbb9b07f5847fd64cbb9029045c39236f

bb-10.6-refactor-limit

Comment by Ian Gilfillan [ 2021-04-23 ]

This doesn't include LIMIT WITH TIES, as implemented by PostgreSQL, and which would probably be more familiar for MariaDB users to use.

Comment by Vicențiu Ciorbaru [ 2021-04-24 ]

greenman I'll push a follow-up fix, this shouldn't be too hard. It'll make it into 10.6.1

Comment by Sergei Golubchik [ 2021-04-26 ]

I'd rather suggest to stay within the sql standard syntax here.

Comment by Ralf Gebhardt [ 2021-04-26 ]

I agree with Sergei. It should not be difficult to switch to the new Syntax used by the standard if WITH TIES is needed

Generated at Thu Feb 08 09:25:58 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.