GeezerVison

The World Wide Web, restored to 800 pixels of glory.

GeezerVison is showing github.com — Add support for TableFunction [WITH OFFSET|ORDINALITY] by davyboyhayes · Pull Request #2219 · JSQLParser/JSqlParser

Conversation

davyboyhayes commented Apr 7, 2025 •

edited

Loading

Uh oh!

There was an error while loading. Please reload this page.

Copy link

Copy Markdown

Contributor

This PR fixes #2218, by adding a new withClause to a TableFunction, which allows for support of SQL-99's UNNEST(...) WITH ORDINALITY or GoogleSQL's UNNEST(...) WITH OFFSET.

Sorry, something went wrong.

Uh oh!

There was an error while loading. Please reload this page.

All reactions

David Hayes added 3 commits

April 7, 2025 18:48


          Add support for TableFunction [WITH OFFSET|ORDINALITY]

348e82f

This PR fixes JSQLParser#2218, by adding a new `withClause` to a TableFunction, which allows for support of SQL-99's `UNNEST(...) WITH ORDINALITY` or GoogleSQL's `UNNEST(...) WITH OFFSET`.

Currently, this is a feeler PR, as I'm getting a test failure, as the output of the parsing differs in my tests:
```
org.opentest4j.AssertionFailedError: Output from Deparser does not match. ==>
Expected :select*from unnest(array[1,2,3])with offset as t(a,b)
Actual   :select*from unnest(array[1,2,3])as t(a,b)
<Click to see difference>

	at org.junit.jupiter.api.AssertionFailureBuilder.build(AssertionFailureBuilder.java:151)
	at org.junit.jupiter.api.AssertionFailureBuilder.buildAndThrow(AssertionFailureBuilder.java:132)
	at org.junit.jupiter.api.AssertEquals.failNotEqual(AssertEquals.java:197)
	at org.junit.jupiter.api.AssertEquals.assertEquals(AssertEquals.java:182)
	at org.junit.jupiter.api.Assertions.assertEquals(Assertions.java:1156)
	at [email protected]/net.sf.jsqlparser.test.TestUtils.assertStatementCanBeDeparsedAs(TestUtils.java:126)
	at [email protected]/net.sf.jsqlparser.test.TestUtils.assertSqlCanBeParsedAndDeparsed(TestUtils.java:99)
	at [email protected]/net.sf.jsqlparser.test.TestUtils.assertSqlCanBeParsedAndDeparsed(TestUtils.java:85)
	at [email protected]/net.sf.jsqlparser.statement.select.TableFunctionTest.testTableFunctionWithSupportedWithClauses(TableFunctionTest.java:60)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:184)
	at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
	at java.base/java.util.stream.ReferencePipeline$2$1.accept(ReferencePipeline.java:179)
	at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
	at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:184)
	at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
	at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:184)
	at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:184)
	at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
	at java.base/java.util.Spliterators$ArraySpliterator.forEachRemaining(Spliterators.java:1024)
	at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:509)
	at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:499)
	at java.base/java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:151)
	at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:174)
	at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
	at java.base/java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:596)
	at java.base/java.util.stream.ReferencePipeline$7$1.accept(ReferencePipeline.java:276)
	at java.base/java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1708)
	at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:509)
	at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:499)
	at java.base/java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:151)
	at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:174)
	at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
	at java.base/java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:596)
	at java.base/java.util.stream.ReferencePipeline$7$1.accept(ReferencePipeline.java:276)
	at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
	at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
	at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
	at java.base/java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1708)
	at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:509)
	at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:499)
	at java.base/java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:151)
	at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:174)
	at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
	at java.base/java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:596)
	at java.base/java.util.stream.ReferencePipeline$7$1.accept(ReferencePipeline.java:276)
	at java.base/java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1708)
	at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:509)
	at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:499)
	at java.base/java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:151)
	at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:174)
	at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
	at java.base/java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:596)
```


          rename clause -> withClause

78dc700


          Resolving test failure, and modifying the SelectDeParser to add the w…

bf1892e

…ithClause where present

davyboyhayes marked this pull request as ready for review

April 7, 2025 18:08

manticore-projects requested changes

Apr 7, 2025

View reviewed changes

manticore-projects left a comment

Copy link

Copy Markdown

Contributor

There was a problem hiding this comment.

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Good stuff and thank you for your work and contribution.
Please change the token definition and then I will merge.

Sorry, something went wrong.

Uh oh!

There was an error while loading. Please reload this page.

All reactions

Comment thread

src/main/jjtree/net/sf/jsqlparser/parser/JSqlParserCC.jjt Outdated

		\| <K_WHERE:"WHERE">
		\| <K_WINDOW:"WINDOW">
		\| <K_WITH:"WITH">
		\| <K_WITH_OFFSET:"WITH OFFSET">

manticore-projects Apr 7, 2025

Copy link

Copy Markdown

Contributor

There was a problem hiding this comment.

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

We don't need the compound token. Just WITH and ORDINALITY/ The goal must be to define as few tokens as possible.

Sorry, something went wrong.

Uh oh!

There was an error while loading. Please reload this page.

davyboyhayes reacted with thumbs up emoji

All reactions

👍 1 reaction

Comment thread

src/main/jjtree/net/sf/jsqlparser/parser/JSqlParserCC.jjt Outdated

		{
		[ prefix = <K_LATERAL> ]
		function=Function()
		[ withClause = <K_WITH_OFFSET> \| withClause = <K_WITH_ORDINALITY> ]

manticore-projects Apr 7, 2025

Copy link

Copy Markdown

Contributor

There was a problem hiding this comment.

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

[ <K_WITH> ( withClause = <K_OFFSET> | withClause = <K_ORDINALITY> ) ]

Sorry, something went wrong.

Uh oh!

There was an error while loading. Please reload this page.

davyboyhayes reacted with thumbs up emoji

All reactions

👍 1 reaction

davyboyhayes Apr 8, 2025

Copy link

Copy Markdown

Contributor Author

There was a problem hiding this comment.

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

I've tried changing to this, but there is a new test failing:
KeywordsTest.testRelObjectNameWithoutValue(String)

Keyword ORDINALITY

net.sf.jsqlparser.JSQLParserException: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "SELECT" <K_SELECT>
    at line 1, column 1.

Was expecting one of:

    "WITH"


	at [email protected]/net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatement(CCJSqlParserUtil.java:352)
	at [email protected]/net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:125)
	at [email protected]/net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:91)
	at [email protected]/net.sf.jsqlparser.test.TestUtils.assertSqlCanBeParsedAndDeparsed(TestUtils.java:98)
	at [email protected]/net.sf.jsqlparser.test.TestUtils.assertSqlCanBeParsedAndDeparsed(TestUtils.java:85)
	at [email protected]/net.sf.jsqlparser.statement.KeywordsTest.testRelObjectNameWithoutValue(KeywordsTest.java:53)

Sorry, something went wrong.

Uh oh!

There was an error while loading. Please reload this page.

All reactions

davyboyhayes Apr 8, 2025

Copy link

Copy Markdown

Contributor Author

There was a problem hiding this comment.

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Found the issue, I needed to add ORDINALITY to the restricted SQL2016 keywords

Sorry, something went wrong.

Uh oh!

There was an error while loading. Please reload this page.

All reactions

manticore-projects Apr 8, 2025

Copy link

Copy Markdown

Contributor

There was a problem hiding this comment.

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

I've tried changing to this, but there is a new test failing:
KeywordsTest.testRelObjectNameWithoutValue(String)

Please see here for an explanation: https://manticore-projects.com/JSQLParser/contribution.html#manage-reserved-keywords

I don't think, we need to reserve those keywords but only whitelist them properly by running gradle updateKeywords.

Sorry, something went wrong.

Uh oh!

There was an error while loading. Please reload this page.

davyboyhayes reacted with thumbs up emoji

All reactions

👍 1 reaction

davyboyhayes Apr 8, 2025

Copy link

Copy Markdown

Contributor Author

There was a problem hiding this comment.

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

I've made those changes, and pushed up. Thank you

Sorry, something went wrong.

Uh oh!

There was an error while loading. Please reload this page.

All reactions

Comment thread

src/test/java/net/sf/jsqlparser/statement/select/TableFunctionTest.java

		TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr, true);
		}

		@ParameterizedTest

manticore-projects Apr 7, 2025

Copy link

Copy Markdown

Contributor

There was a problem hiding this comment.

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

nice!

Sorry, something went wrong.

Uh oh!

There was an error while loading. Please reload this page.

davyboyhayes reacted with laugh emoji

All reactions

😄 1 reaction


          Addressing PR Review comments. Using existing WITH and OFFSET keyword…

6549bcb

…s, adding ORDINALITY keyword.

davyboyhayes requested a review from manticore-projects

April 8, 2025 08:10

davyboyhayes commented Apr 8, 2025

Copy link

Copy Markdown

Contributor Author

Ready for re-review.

All reactions

Sorry, something went wrong.

Uh oh!

There was an error while loading. Please reload this page.


          Removing ORDINALITY from the reserved keywords list in ParserKeywords…

a066a96

…Utils, and updating pushing the result of `gradle updateKeywords`

manticore-projects approved these changes

Apr 8, 2025

View reviewed changes

manticore-projects commented Apr 8, 2025

Copy link

Copy Markdown

Contributor

Thank you a lot for your work and contribution. Merged.

davyboyhayes reacted with heart emoji

All reactions

❤️ 1 reaction

Sorry, something went wrong.

Uh oh!

There was an error while loading. Please reload this page.

manticore-projects merged commit e5fa004 into JSQLParser:master

Apr 8, 2025

davyboyhayes deleted the fix/2218 branch

April 8, 2025 09:30

minleejae mentioned this pull request

Sep 7, 2026

Cannot parse PGSQL JSONB_ARRAY_ELEMENTS() WITH ORDINALITY ARR() #1511

Closed

Reviewers

manticore-projects

manticore-projects approved these changes

Uh oh!

There was an error while loading. Please reload this page.

Assignees

No one assigned

Labels

None yet

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

[BUG] JSQLParser 5.1 : SQL-99 : unnest(...) with ordinality

Uh oh!

There was an error while loading. Please reload this page.

2 participants

Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.

You are visitor 01402503 — GeezerVison does not store the pages you look up.
Best viewed with Netscape Navigator 4.7 or Internet Explorer 6 at 800×600. No cookies required.

Conversation

davyboyhayes commented Apr 7, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

manticore-projects left a comment

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

davyboyhayes commented Apr 8, 2025

Uh oh!

manticore-projects commented Apr 8, 2025

Uh oh!

Reviewers

Uh oh!

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

davyboyhayes commented Apr 7, 2025 •

edited

Loading

Uh oh!

There was an error while loading. Please reload this page.