Lack of support for the ANSI SQL syntax FETCH FIRST N ROWS WITH TIES

[linghengqian]

Expected Behavior or Use Case

• Add support for ANSI SQL syntax FETCH FIRST N ROWS WITH TIES. The actual SQL might look like the following.

CREATE TABLE memory.default.table_with_array AS SELECT 1 id, ARRAY[1, 42, 2, 42, 4, 42] my_array

SELECT nationkey, element 
FROM tpch.tiny.nation 
JOIN memory.default.table_with_array twa ON nationkey = twa.id 
CROSS JOIN UNNEST(my_array) a(element) 
ORDER BY element OFFSET 1 FETCH FIRST 3 ROWS WITH TIES

• This was mentioned at Add mysql-style LIMIT support to Presto #12210 (comment) but the original issue has been closed. Now executing this SQL will report the following error.

line 1:196: mismatched input 'WITH'. Expecting: 'ONLY'
com.facebook.presto.sql.parser.ParsingException: line 1:196: mismatched input 'WITH'. Expecting: 'ONLY'
	at com.facebook.presto.sql.parser.ErrorHandler.syntaxError(ErrorHandler.java:109)
	at org.antlr.v4.runtime.ProxyErrorListener.syntaxError(ProxyErrorListener.java:41)
	at org.antlr.v4.runtime.Parser.notifyErrorListeners(Parser.java:544)
	at org.antlr.v4.runtime.DefaultErrorStrategy.reportInputMismatch(DefaultErrorStrategy.java:327)
	at org.antlr.v4.runtime.DefaultErrorStrategy.reportError(DefaultErrorStrategy.java:139)
	at com.facebook.presto.sql.parser.SqlBaseParser.queryNoWith(SqlBaseParser.java:5965)
	at com.facebook.presto.sql.parser.SqlBaseParser.query(SqlBaseParser.java:4643)
	at com.facebook.presto.sql.parser.SqlBaseParser.statement(SqlBaseParser.java:2189)
	at com.facebook.presto.sql.parser.SqlBaseParser.singleStatement(SqlBaseParser.java:271)
	at com.facebook.presto.sql.parser.SqlParser.invokeParser(SqlParser.java:173)
	at com.facebook.presto.sql.parser.SqlParser.createStatement(SqlParser.java:108)
	at com.facebook.presto.sql.analyzer.BuiltInQueryPreparer.prepareQuery(BuiltInQueryPreparer.java:70)
	at com.facebook.presto.sql.analyzer.BuiltInQueryPreparer.prepareQuery(BuiltInQueryPreparer.java:56)
	at com.facebook.presto.dispatcher.DispatchManager.createQueryInternal(DispatchManager.java:290)
	at com.facebook.presto.dispatcher.DispatchManager.lambda$createQuery$0(DispatchManager.java:254)
	at com.facebook.airlift.concurrent.BoundedExecutor.drainQueue(BoundedExecutor.java:78)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: org.antlr.v4.runtime.InputMismatchException
	at com.facebook.presto.sql.parser.SqlParser$2.recoverInline(SqlParser.java:147)
	at org.antlr.v4.runtime.Parser.match(Parser.java:206)
	at com.facebook.presto.sql.parser.SqlBaseParser.queryNoWith(SqlBaseParser.java:5947)
	... 13 more

Presto Component, Service, or Connector

• Presto Service.

Possible Implementation

• I lack the expertise in this area, so I'm afraid there's nothing I can do.

Example Screenshots (if appropriate):

• I don't think a screenshot is necessary.

Context

• I found this issue from Change the Docker Image used by the Presto module to prestodb/presto testcontainers/testcontainers-java#8946 and [Feature]: Presto Module uses the outdated Docker Image of ghcr.io/trinodb/presto and needs to be updated to prestodb/presto testcontainers/testcontainers-java#8657 side, testcontainers-java exists a small test set to test ANSI SQL, so I currently have to rewrite the original SQL as follows.

SELECT nationkey, element 
FROM tpch.tiny.nation 
JOIN memory.default.table_with_array twa ON nationkey = twa.id 
CROSS JOIN UNNEST(my_array) a(element) 
ORDER BY element OFFSET 1 FETCH FIRST 3 ROWS ONLY 

• On the other hand, https://prestodb.io/docs/current/release/release-0.280.html mentions that FETCH FIRST N ROWS ONLY is supported in Presto 0.280, so it seems that supporting FETCH FIRST N ROWS WITH TIES is a natural thing.
• Earlier research actually happened on the Improve GraalVM Reachability Metadata and corresponding nativeTest related unit tests apache/shardingsphere#29052 side, where I personally wrote unit tests for the Presto JDBC Driver under the GraalVM Native Image.