Support spath with dynamic fields by ykmr1224 · Pull Request #5058 · opensearch-project/sql · GitHub
Skip to content

Support spath with dynamic fields#5058

Merged
ykmr1224 merged 28 commits into
opensearch-project:mainfrom
ykmr1224:spath/dynamic-fields-limited
Jan 26, 2026
Merged

Support spath with dynamic fields#5058
ykmr1224 merged 28 commits into
opensearch-project:mainfrom
ykmr1224:spath/dynamic-fields-limited

Conversation

@ykmr1224

@ykmr1224 ykmr1224 commented Jan 20, 2026

Copy link
Copy Markdown
Collaborator

Description

  • Extend spath command to support dynamic fields, where it no longer requires field selection.
    • It has limitations as it does not fully support field ordering of dynamic fields.
  • This change is for step2-1 in [RFC] Support Schema-on-Read in PPL #4984
  • The fields not explicitly referred in the query would be stored in _MAP(dynamic fields), and expanded when result is organized.
  • It does not support partial wildcard like prefix*, *suffix, and raise error in case those are used.
  • When result contains dynamic fields, OpenSearchExecutionEngine puts dynamic fields at the end of the fields when organizing result. (This is temporal behavior until field ordering is implemented)
    • To align this behavior, it raises error when fields command specify * in the middle of field list.
  • Following commands will be adopted in later PR (command with multiple input requires change) : appendcol, multisearch, lookup

Related Issues

#4984

Check List

  • New functionality includes testing.
  • New functionality has been documented.
  • New functionality has javadoc added.
  • New functionality has a user manual doc added.
  • New PPL command checklist all confirmed.
  • API changes companion pull request created.
  • Commits are signed per the DCO using --signoff or -s.
  • Public documentation issue/PR created.

By submitting this pull request, I confirm that my contribution is made under the terms of the Apache 2.0 license.
For more information on following Developer Certificate of Origin and signing off your commits, please check here.

@ykmr1224 ykmr1224 self-assigned this Jan 20, 2026
@ykmr1224 ykmr1224 added enhancement New feature or request PPL Piped processing language calcite calcite migration releated labels Jan 20, 2026
@coderabbitai

coderabbitai Bot commented Jan 20, 2026

Copy link
Copy Markdown
Contributor

@ykmr1224 ykmr1224 force-pushed the spath/dynamic-fields-limited branch from d1a62c4 to 3defb88 Compare January 20, 2026 18:28
Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

@dai-chen dai-chen left a comment

Copy link
Copy Markdown
Collaborator

Choose a reason for hiding this comment

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

Just one concern: I feel it's unclear what future PPL command developers need to understand or implement when adding commands that interact with dynamic fields, since current _MAP implementation seems leaks across multiple layers (field resolution, RelNode visitor, join handling, result post-processing, etc). We can consider improving document or consolidating the logic later if this is valid concern. Thanks!

@ykmr1224 ykmr1224 merged commit 633d760 into opensearch-project:main Jan 26, 2026
37 checks passed
@opensearch-trigger-bot

Copy link
Copy Markdown
Contributor

ykmr1224 added a commit to ykmr1224/sql that referenced this pull request Jan 26, 2026
* Support spath with dynamic fields

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Add explain test and sql conversion test

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Address comments

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix FieldResolutionResultTest

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Address comment

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix explain

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix join logic to adopt dynamic fields

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix join logic

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix spath.md

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Support fillnull and replace

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Update spath.md

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix test failure

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix doc and error for fillnull

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Accept wildcard only at the end of field list

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Minor fix

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Adopt append command to spath

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix join to allow spath in only one input

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix unit test failure

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix join inputs logic

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix test failure

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Move helper methods

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Add _MAP description in the docs

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Extract more from CalciteRelNodeVisitor

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Refactor IT and address comments

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix FieldResolutionResult

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix DynamicFieldsHelper

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Refactor DynamicFieldsHelper

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix DynamicFieldsHelper

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

---------

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>
(cherry picked from commit 633d760)
Signed-off-by: Tomoyuki Morita <moritato@amazon.com>
asifabashar pushed a commit to asifabashar/sql that referenced this pull request Jan 26, 2026
* Support spath with dynamic fields

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Add explain test and sql conversion test

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Address comments

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix FieldResolutionResultTest

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Address comment

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix explain

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix join logic to adopt dynamic fields

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix join logic

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix spath.md

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Support fillnull and replace

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Update spath.md

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix test failure

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix doc and error for fillnull

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Accept wildcard only at the end of field list

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Minor fix

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Adopt append command to spath

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix join to allow spath in only one input

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix unit test failure

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix join inputs logic

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix test failure

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Move helper methods

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Add _MAP description in the docs

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Extract more from CalciteRelNodeVisitor

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Refactor IT and address comments

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix FieldResolutionResult

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix DynamicFieldsHelper

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Refactor DynamicFieldsHelper

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix DynamicFieldsHelper

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

---------

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>
@LantaoJin LantaoJin added the backport-manually Filed a PR to backport manually. label Jan 27, 2026
@coderabbitai coderabbitai Bot mentioned this pull request Jan 27, 2026
LantaoJin pushed a commit that referenced this pull request Jan 27, 2026
* Support spath with dynamic fields (#5058)

* Support spath with dynamic fields

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Add explain test and sql conversion test

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Address comments

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix FieldResolutionResultTest

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Address comment

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix explain

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix join logic to adopt dynamic fields

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix join logic

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix spath.md

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Support fillnull and replace

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Update spath.md

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix test failure

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix doc and error for fillnull

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Accept wildcard only at the end of field list

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Minor fix

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Adopt append command to spath

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix join to allow spath in only one input

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix unit test failure

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix join inputs logic

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix test failure

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Move helper methods

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Add _MAP description in the docs

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Extract more from CalciteRelNodeVisitor

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Refactor IT and address comments

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix FieldResolutionResult

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix DynamicFieldsHelper

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Refactor DynamicFieldsHelper

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix DynamicFieldsHelper

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

---------

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>
(cherry picked from commit 633d760)
Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix for Java 11

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

---------

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>
asifabashar pushed a commit to asifabashar/sql that referenced this pull request Jan 27, 2026
* Support spath with dynamic fields

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Add explain test and sql conversion test

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Address comments

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix FieldResolutionResultTest

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Address comment

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix explain

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix join logic to adopt dynamic fields

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix join logic

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix spath.md

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Support fillnull and replace

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Update spath.md

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix test failure

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix doc and error for fillnull

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Accept wildcard only at the end of field list

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Minor fix

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Adopt append command to spath

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix join to allow spath in only one input

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix unit test failure

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix join inputs logic

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix test failure

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Move helper methods

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Add _MAP description in the docs

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Extract more from CalciteRelNodeVisitor

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Refactor IT and address comments

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix FieldResolutionResult

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix DynamicFieldsHelper

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Refactor DynamicFieldsHelper

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

* Fix DynamicFieldsHelper

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>

---------

Signed-off-by: Tomoyuki Morita <moritato@amazon.com>
This was referenced Feb 4, 2026
dai-chen added a commit to dai-chen/sql-1 that referenced this pull request Feb 11, 2026
This reverts commit 633d760.

Signed-off-by: Chen Dai <daichen@amazon.com>
@dai-chen dai-chen mentioned this pull request Feb 11, 2026
8 tasks
dai-chen added a commit to dai-chen/sql-1 that referenced this pull request Feb 11, 2026
This reverts commit 633d760.

Signed-off-by: Chen Dai <daichen@amazon.com>
dai-chen added a commit that referenced this pull request Feb 11, 2026
* Revert "Adopt appendcol, appendpipe, multisearch to spath (#5075)"

This reverts commit 7630db8.

Signed-off-by: Chen Dai <daichen@amazon.com>

* Revert "Support spath with dynamic fields (#5058)"

This reverts commit 633d760.

Signed-off-by: Chen Dai <daichen@amazon.com>

* Revert "Implement spath command with field resolution (#5028)"

This reverts commit 65baa2a.

Signed-off-by: Chen Dai <daichen@amazon.com>

* Fix failed IT testSpathWithMvCombine

Signed-off-by: Chen Dai <daichen@amazon.com>

---------

Signed-off-by: Chen Dai <daichen@amazon.com>
LantaoJin pushed a commit to LantaoJin/search-plugins-sql that referenced this pull request Feb 12, 2026
* Revert "Adopt appendcol, appendpipe, multisearch to spath (opensearch-project#5075)"

This reverts commit 7630db8.

Signed-off-by: Chen Dai <daichen@amazon.com>

* Revert "Support spath with dynamic fields (opensearch-project#5058)"

This reverts commit 633d760.

Signed-off-by: Chen Dai <daichen@amazon.com>

* Revert "Implement spath command with field resolution (opensearch-project#5028)"

This reverts commit 65baa2a.

Signed-off-by: Chen Dai <daichen@amazon.com>

* Fix failed IT testSpathWithMvCombine

Signed-off-by: Chen Dai <daichen@amazon.com>

---------

Signed-off-by: Chen Dai <daichen@amazon.com>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

backport 2.19-dev backport-failed backport-manually Filed a PR to backport manually. calcite calcite migration releated enhancement New feature or request PPL Piped processing language

Projects

None yet

Development

Successfully merging this pull request may close these issues.

4 participants