Fix: Support Alias Fields in MIN, MAX, FIRST, LAST, and TAKE Aggregations by ahkcs · Pull Request #4621 · opensearch-project/sql

ahkcs · 2025-10-21T21:18:28Z

Description

MIN, MAX, FIRST, LAST, and TAKE aggregations returned null or empty results when used with alias fields (e.g., @timestamp aliasing created_at) because they used .fetchSource() which cannot access alias fields.

Changes:

  - Aggregation builders: Changed from .fetchSource() to .fetchField() to use OpenSearch's fields API
  - Response parsers: Enhanced TopHitsParser and ArgMaxMinParser to extract values from hit.getFields()

  # Before: Returns null
  source=index | stats MIN(@timestamp), MAX(@timestamp)

  # After: Returns correct min/max values
  {"MIN(@timestamp)": "2024-01-01T10:00:00.000Z", "MAX(@timestamp)": "2024-01-03T10:00:00.000Z"}

Related Issues

Resolve [BUG] Multiple Aggregation Functions (MIN, MAX, FIRST, LAST, TAKE) Return Null/Empty Values With Alias Fields in PPL #4595

ahkcs · 2025-10-21T23:22:30Z

@ritvibhatt Please take a look at the changes in ArgMaxMinParser to see if makes sense to you

Swiddis

overall lgtm

Swiddis · 2025-10-22T21:48:24Z

opensearch/src/main/java/org/opensearch/sql/opensearch/response/agg/SearchHitUtils.java

+   * @param hit the SearchHit to extract value from
+   * @return the extracted value, or null if no value found
+   */
+  public static Object extractValue(SearchHit hit) {


suggestion: Return Optional<Object> for type safety

dai-chen · 2025-10-22T22:43:55Z

docs/user/ppl/cmd/stats.rst

    | cnt | take(email, 5)                             | age_span | gender |
    |-----+--------------------------------------------+----------+--------|
-    | 1   | []                                         | 25       | F      |
+    | 1   | [null]                                     | 25       | F      |


Is this expected? As discussed, shall we keep previous semantic of each function?

Changed to avoid breaking change

integ-test/src/test/resources/expectedOutput/calcite/explain_earliest_latest.json

opensearch/src/main/java/org/opensearch/sql/opensearch/response/agg/TopHitsParser.java

…ions Signed-off-by: Kai Huang <ahkcs@amazon.com>

Signed-off-by: Kai Huang <ahkcs@amazon.com>

dai-chen · 2025-10-24T22:04:52Z

opensearch/src/main/java/org/opensearch/sql/opensearch/request/AggregateAnalyzer.java

        } else {
          yield Pair.of(
              AggregationBuilders.topHits(aggFieldName)
-                  .fetchSource(helper.inferNamedField(args.getFirst()).getRootName(), null)


I think we should prefer fetch fields API. This will break for certain field types, e.g., text, nested. Please double check.

Updated to use fetch fields API

Signed-off-by: Kai Huang <ahkcs@amazon.com>

opensearch-trigger-bot · 2025-10-24T22:59:20Z

The backport to 2.19-dev failed:

The process '/usr/bin/git' failed with exit code 128

To backport manually, run these commands in your terminal:

# Navigate to the root of your repository
cd $(git rev-parse --show-toplevel)
# Fetch latest updates from GitHub
git fetch
# Create a new working tree
git worktree add ../.worktrees/sql/backport-2.19-dev 2.19-dev
# Navigate to the new working tree
pushd ../.worktrees/sql/backport-2.19-dev
# Create a new branch
git switch --create backport/backport-4621-to-2.19-dev
# Cherry-pick the merged commit of this pull request and resolve the conflicts
git cherry-pick -x --mainline 1 c6a3a70ac0d0e3ae3aa5eaefba88dab000bc70d5
# Push it to GitHub
git push --set-upstream origin backport/backport-4621-to-2.19-dev
# Go back to the original working tree
popd
# Delete the working tree
git worktree remove ../.worktrees/sql/backport-2.19-dev

Then, create a pull request where the base branch is 2.19-dev and the compare/head branch is backport/backport-4621-to-2.19-dev.

…ions (opensearch-project#4621) (cherry picked from commit c6a3a70) Signed-off-by: Kai Huang <ahkcs@amazon.com>

…T, and TAKE Aggregations (#4669) Fix: Support Alias Fields in MIN, MAX, FIRST, LAST, and TAKE Aggregations (#4621)

* default-main: (34 commits) Enhance dynamic source clause to support only metadata filters (opensearch-project#4554) Make nested alias type support referring to outer context (opensearch-project#4673) Update big5 ppl queries and check plans (opensearch-project#4668) Support push down sort after limit (opensearch-project#4657) Use table scan rowType in filter pushdown could fix rename issue (opensearch-project#4670) Fix: Support Alias Fields in MIN, MAX, FIRST, LAST, and TAKE Aggregations (opensearch-project#4621) Fix bin nested fields issue (opensearch-project#4606) Add `per_minute`, `per_hour`, `per_day` function support (opensearch-project#4531) Pushdown sort aggregate metrics (opensearch-project#4603) Followup: Change ComparableLinkedHashMap to compare Key than Value (opensearch-project#4648) Mitigate the CI failure caused by 500 Internal Server Error (opensearch-project#4646) Allow renaming group-by fields to existing field names (opensearch-project#4586) Publish internal modules separately for downstream reuse (opensearch-project#4484) Revert "Update grammar files and developer guide (opensearch-project#4301)" (opensearch-project#4643) Support Automatic Type Conversion for REX/SPATH/PARSE Command Extractions (opensearch-project#4599) Replace all dots in fields of table scan's PhysType (opensearch-project#4633) Return comparable LinkedHashMap in `valueForCalcite()` of ExprTupleValue (opensearch-project#4629) Refactor JsonExtractAllFunctionIT and MapConcatFunctionIT (opensearch-project#4623) Pushdown case function in aggregations as range queries (opensearch-project#4400) Update GEOIP function to support IP types as input (opensearch-project#4613) ... # Conflicts: # docs/user/ppl/functions/conversion.rst

* default-main: (34 commits) Enhance dynamic source clause to support only metadata filters (opensearch-project#4554) Make nested alias type support referring to outer context (opensearch-project#4673) Update big5 ppl queries and check plans (opensearch-project#4668) Support push down sort after limit (opensearch-project#4657) Use table scan rowType in filter pushdown could fix rename issue (opensearch-project#4670) Fix: Support Alias Fields in MIN, MAX, FIRST, LAST, and TAKE Aggregations (opensearch-project#4621) Fix bin nested fields issue (opensearch-project#4606) Add `per_minute`, `per_hour`, `per_day` function support (opensearch-project#4531) Pushdown sort aggregate metrics (opensearch-project#4603) Followup: Change ComparableLinkedHashMap to compare Key than Value (opensearch-project#4648) Mitigate the CI failure caused by 500 Internal Server Error (opensearch-project#4646) Allow renaming group-by fields to existing field names (opensearch-project#4586) Publish internal modules separately for downstream reuse (opensearch-project#4484) Revert "Update grammar files and developer guide (opensearch-project#4301)" (opensearch-project#4643) Support Automatic Type Conversion for REX/SPATH/PARSE Command Extractions (opensearch-project#4599) Replace all dots in fields of table scan's PhysType (opensearch-project#4633) Return comparable LinkedHashMap in `valueForCalcite()` of ExprTupleValue (opensearch-project#4629) Refactor JsonExtractAllFunctionIT and MapConcatFunctionIT (opensearch-project#4623) Pushdown case function in aggregations as range queries (opensearch-project#4400) Update GEOIP function to support IP types as input (opensearch-project#4613) ... Signed-off-by: Asif Bashar <asif.bashar@gmail.com>

…ions (opensearch-project#4621)

Co-authored-by: github-actions[bot] <github-actions[bot]@users.noreply.github.com> Co-authored-by: Simeon Widdis <sawiddis@amazon.com> Co-authored-by: Manasvini B S <manasvis@amazon.com> Co-authored-by: opensearch-ci-bot <opensearch-infra@amazon.com> Co-authored-by: Louis Chu <clingzhi@amazon.com> Co-authored-by: Chen Dai <daichen@amazon.com> Co-authored-by: Mebsina <cnoramut@gmail.com> Co-authored-by: Yuanchun Shen <yuanchu@amazon.com> Co-authored-by: opensearch-trigger-bot[bot] <98922864+opensearch-trigger-bot[bot]@users.noreply.github.com> Co-authored-by: Kai Huang <105710027+ahkcs@users.noreply.github.com> Co-authored-by: Peng Huo <penghuo@gmail.com> Co-authored-by: Alexey Temnikov <alexey.temnikov@improving.com> Co-authored-by: Riley Jerger <214163063+RileyJergerAmazon@users.noreply.github.com> Co-authored-by: Tomoyuki MORITA <moritato@amazon.com> Co-authored-by: Lantao Jin <ltjin@amazon.com> Co-authored-by: Songkan Tang <songkant@amazon.com> Co-authored-by: qianheng <qianheng@amazon.com> Co-authored-by: Simeon Widdis <sawiddis@gmail.com> Co-authored-by: Xinyuan Lu <xinyual@amazon.com> Co-authored-by: Jialiang Liang <jiallian@amazon.com> Co-authored-by: Peter Zhu <zhujiaxi@amazon.com> Co-authored-by: Vinay Krishna Pudyodu <vinkrish.neo@gmail.com> Co-authored-by: expani <anijainc@amazon.com> Co-authored-by: expani1729 <110471048+expani@users.noreply.github.com> Co-authored-by: Vamsi Manohar <reddyvam@amazon.com> Co-authored-by: ritvibhatt <53196324+ritvibhatt@users.noreply.github.com> Co-authored-by: Xinyu Hao <75524174+ishaoxy@users.noreply.github.com> Co-authored-by: Marc Handalian <marc.handalian@gmail.com> Co-authored-by: Marc Handalian <handalm@amazon.com> Fix join type ambiguous issue when specify the join type with sql-like join criteria (opensearch-project#4474) Fix issue 4441 (opensearch-project#4449) Fix missing keywordsCanBeId (opensearch-project#4491) Fix the bug of explicit makeNullLiteral for UDT fields (opensearch-project#4475) Fix mapping after aggregation push down (opensearch-project#4500) Fix percentile bug (opensearch-project#4539) Fix JsonExtractAllFunctionIT failure (opensearch-project#4556) Fix sort push down into agg after project already pushed (opensearch-project#4546) Fix push down failure for min/max on derived field (opensearch-project#4572) Fix compile issue in main (opensearch-project#4608) Fix filter parsing failure on date fields with non-default format (opensearch-project#4616) Fix bin nested fields issue (opensearch-project#4606) Fix: Support Alias Fields in MIN, MAX, FIRST, LAST, and TAKE Aggregations (opensearch-project#4621) fix rename issue (opensearch-project#4670) Fixes for `Multisearch` and `Append` command (opensearch-project#4512) Fix asc/desc keyword behavior for sort command (opensearch-project#4651) Fix] Fix unexpected shift of extraction for `rex` with nested capture groups in named groups (opensearch-project#4641) Fix CVE-2025-48924 (opensearch-project#4665) Fix sub-fields accessing of generated structs (opensearch-project#4683) Fix] Incorrect Field Index Mapping in AVG to SUM/COUNT Conversion (opensearch-project#15)

ahkcs marked this pull request as ready for review October 21, 2025 23:21

dai-chen added PPL Piped processing language backport 2.19-dev bug Something isn't working labels Oct 22, 2025

Swiddis previously approved these changes Oct 22, 2025

View reviewed changes

dai-chen reviewed Oct 22, 2025

View reviewed changes

ahkcs dismissed Swiddis’s stale review via 369714d October 23, 2025 19:30

ahkcs force-pushed the fix/4595 branch from 4561cc5 to 369714d Compare October 23, 2025 19:30

Fix: Support Alias Fields in MIN, MAX, FIRST, LAST, and TAKE Aggregat…

9b6b123

…ions Signed-off-by: Kai Huang <ahkcs@amazon.com>

ahkcs force-pushed the fix/4595 branch from 369714d to 9b6b123 Compare October 23, 2025 23:16

fix CI

cab2b96

Signed-off-by: Kai Huang <ahkcs@amazon.com>

Swiddis previously approved these changes Oct 24, 2025

View reviewed changes

dai-chen reviewed Oct 24, 2025

View reviewed changes

Update to use fetchField

3c88ef2

Signed-off-by: Kai Huang <ahkcs@amazon.com>

ahkcs dismissed Swiddis’s stale review via 3c88ef2 October 24, 2025 22:23

dai-chen approved these changes Oct 24, 2025

View reviewed changes

Swiddis approved these changes Oct 24, 2025

View reviewed changes

Swiddis merged commit c6a3a70 into opensearch-project:main Oct 24, 2025
32 of 33 checks passed

opensearch-trigger-bot bot added the backport-failed label Oct 24, 2025

ahkcs added a commit to ahkcs/sql that referenced this pull request Oct 27, 2025

Fix: Support Alias Fields in MIN, MAX, FIRST, LAST, and TAKE Aggregat…

54f6e3e

…ions (opensearch-project#4621) (cherry picked from commit c6a3a70) Signed-off-by: Kai Huang <ahkcs@amazon.com>

ahkcs mentioned this pull request Oct 27, 2025

[Backport 2.19-dev] Fix: Support Alias Fields in MIN, MAX, FIRST, LAST, and TAKE Aggregations #4669

Merged

LantaoJin added the backport-manually Filed a PR to backport manually. label Oct 27, 2025

RyanL1997 pushed a commit that referenced this pull request Oct 27, 2025

[Backport 2.19-dev] Fix: Support Alias Fields in MIN, MAX, FIRST, LAS…

b3863fc

…T, and TAKE Aggregations (#4669) Fix: Support Alias Fields in MIN, MAX, FIRST, LAST, and TAKE Aggregations (#4621)

expani pushed a commit to vinaykpud/sql that referenced this pull request Nov 4, 2025

Fix: Support Alias Fields in MIN, MAX, FIRST, LAST, and TAKE Aggregat…

c0d4e56

…ions (opensearch-project#4621)

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Fix: Support Alias Fields in MIN, MAX, FIRST, LAST, and TAKE Aggregations#4621

Fix: Support Alias Fields in MIN, MAX, FIRST, LAST, and TAKE Aggregations#4621
Swiddis merged 3 commits intoopensearch-project:mainfrom
ahkcs:fix/4595

ahkcs commented Oct 21, 2025 •

edited

Loading

Uh oh!

ahkcs commented Oct 21, 2025

Uh oh!

Swiddis left a comment

Uh oh!

Swiddis Oct 22, 2025

Uh oh!

dai-chen Oct 22, 2025 •

edited

Loading

Uh oh!

ahkcs Oct 23, 2025

Uh oh!

Uh oh!

Uh oh!

dai-chen Oct 24, 2025

Uh oh!

ahkcs Oct 24, 2025

Uh oh!

Uh oh!

opensearch-trigger-bot bot commented Oct 24, 2025

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

4 participants

Conversation

ahkcs commented Oct 21, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Description

Related Issues

Uh oh!

ahkcs commented Oct 21, 2025

Uh oh!

Swiddis left a comment

Choose a reason for hiding this comment

Uh oh!

Swiddis Oct 22, 2025

Choose a reason for hiding this comment

Uh oh!

dai-chen Oct 22, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

ahkcs Oct 23, 2025

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

dai-chen Oct 24, 2025

Choose a reason for hiding this comment

Uh oh!

ahkcs Oct 24, 2025

Choose a reason for hiding this comment

Uh oh!

Uh oh!

opensearch-trigger-bot bot commented Oct 24, 2025

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

4 participants

ahkcs commented Oct 21, 2025 •

edited

Loading

dai-chen Oct 22, 2025 •

edited

Loading