fix(sql): resolve 'Invalid column' error in WINDOW JOIN with aliased columns by bluestreak01 · Pull Request #6701 · questdb/questdb

bluestreak01 · 2026-01-24T14:01:20Z

Summary

Fixed a bug where WINDOW JOIN queries using aliased columns in expressions with aggregates would fail with Invalid column: <column> error at position 0
The issue occurred when the same column was both aliased (e.g., t.price AS fill_price) and used in an expression with aggregates (e.g., (t.price - sum(...)))

Problem

When processing expressions containing aggregates in WINDOW JOIN context, the SqlOptimiser.replaceIfAggregateOrLiteral() method failed to correctly resolve column references because:

It looked up the column in translatingModel to get its alias
Then tried to verify this alias exists in groupByModel using groupByModel.getColumnNameToAliasMap().get(translatingAlias)
For window joins, both models are the same (windowJoinModel), and the lookup failed because the map stores token -> alias, not alias -> something

This caused the column to be re-added with a bare name instead of using the existing alias, leading to the "Invalid column" error at code generation time.

Fix

Pass windowJoinModel as the translating model to emitAggregatesAndLiterals when isWindowJoin is true
Add a special case in replaceIfAggregateOrLiteral for when translatingModel == groupByModel (window join case) to directly return the found alias

Test plan

Added reproducer test in SqlParserTest.testWindowJoin() - verifies correct query model is generated
Added end-to-end test in WindowJoinTest.testWindowJoinSelfJoinWithAggregatesInSelectAndWhere() - verifies query executes correctly
All existing WindowJoinTest tests pass (184 tests)

Example query that was failing

SELECT 
    t.timestamp,
    t.price AS fill_price,
    sum(w.price * w.quantity) / sum(w.quantity) AS vwap_5m,
    (t.price - sum(w.price * w.quantity) / sum(w.quantity)) 
        / (sum(w.price * w.quantity) / sum(w.quantity)) * 10000 AS slippage_bps
FROM fx_trades t
WINDOW JOIN fx_trades w 
    ON (t.symbol = w.symbol)
    RANGE BETWEEN 5 minutes PRECEDING AND 1 microseconds PRECEDING
    EXCLUDE PREVAILING
WHERE t.symbol = 'EURUSD'
ORDER BY t.timestamp
LIMIT 100

🤖 Generated with Claude Code

coderabbitai · 2026-01-24T14:01:29Z

Walkthrough

Changes to SqlOptimiser modify alias resolution and aggregate/literal emission logic for window join scenarios. When a literal references a column, the code now short-circuits for certain window join cases or preserves group-by key tracking. Additionally, the translatingModel parameter for emitAggregatesAndLiterals is conditionally set based on whether a window join is present. Test cases are added for self-join window join scenarios with aggregates.

Changes

Cohort / File(s)	Summary
Core Query Optimizer Logic `core/src/main/java/io/questdb/griffin/SqlOptimiser.java`	Modified `replaceIfAggregateOrLiteral` to short-circuit literal handling when translatingAlias equals groupByModel, otherwise compute and track alias from groupByModel. Updated `rewriteSelect0HandleOperation` to pass `isWindowJoin ? windowJoinModel : translatingModel` to `emitAggregatesAndLiterals`.
SQL Parser Tests `core/src/test/java/io/questdb/test/griffin/SqlParserTest.java`	Added test case for self-join window join scenario with aliased columns used in multiple expressions, covering alias handling and expression propagation in window-join contexts.
Window Join Tests `core/src/test/java/io/questdb/test/griffin/WindowJoinTest.java`	Added `testWindowJoinSelfJoinWithAggregatesInSelectAndWhere` test method covering self-join window join with aggregates in select clause and where clause, including table setup, data insertion, and query validation.

Estimated code review effort

🎯 3 (Moderate) | ⏱️ ~35 minutes

Possibly related PRs

feat(sql): support nested window functions with deduplication optimization #6643: Modifies SqlOptimiser's window-related expression rewriting and alias resolution logic.
fix(sql): breaking change 💥- invalid column error returned from GROUP BY on joined tables #6275: Changes SqlOptimiser's alias and group-by handling for aggregates/literals in window-join contexts.
feat(sql): implement arithmetic with window functions #6626: Adds windowContext/WindowColumn support in ExpressionNode/ExpressionParser, directly relevant to window-join model routing in aggregate/literal replacement.

Suggested labels

Bug, SQL

Suggested reviewers

puzpuzpuz
mtopolnik
jerrinot

🚥 Pre-merge checks | ✅ 2 | ❌ 1

❌ Failed checks (1 warning)

Check name	Status	Explanation	Resolution
Docstring Coverage	⚠️ Warning	Docstring coverage is 0.00% which is insufficient. The required threshold is 80.00%.	Write docstrings for the functions missing them to satisfy the coverage threshold.

✅ Passed checks (2 passed)

Check name	Status	Explanation
Title check	✅ Passed	The title accurately summarizes the main fix: resolving an 'Invalid column' error in WINDOW JOIN queries with aliased columns, which is the core change in the PR.
Description check	✅ Passed	The description is directly related to the changeset, providing clear explanation of the bug, root cause, fix implementation, and test coverage.

_{✏️ Tip: You can configure your own custom pre-merge checks in the settings.}

✨ Finishing touches

📝 Generate docstrings

Warning

There were issues while running some tools. Please review the errors and either fix the tool's configuration or disable the tool if it's a critical failure.

🔧 ast-grep (0.40.5)

core/src/main/java/io/questdb/griffin/SqlOptimiser.java

core/src/test/java/io/questdb/test/griffin/SqlParserTest.java

Thanks for using CodeRabbit! It's free for OSS, and your support helps us grow. If you like it, consider giving us a shout-out.

❤️ Share

_{Comment @coderabbitai help to get the list of available commands and usage tips.}

bluestreak01 · 2026-01-24T14:02:27Z

@CodeRabbit pls review, find bugs

coderabbitai · 2026-01-24T14:03:12Z

Oops, something went wrong! Please try again later. 🐰 💔

bluestreak01 · 2026-01-24T14:15:39Z

@CodeRabbit whenever you can, review my bugs

coderabbitai · 2026-01-24T14:15:54Z

@bluestreak01: I'll review the pull request and look for potential bugs. Starting the review now.

🧠 Learnings used

Learnt from: bluestreak01
Repo: questdb/questdb PR: 6673
File: core/src/test/java/io/questdb/test/griffin/WindowJoinTest.java:2616-2618
Timestamp: 2026-01-19T21:43:19.228Z
Learning: In test `testWindowJoinFailsWhenSlaveDoesNotSupportTimeFrames` in `core/src/test/java/io/questdb/test/griffin/WindowJoinTest.java`, the use of `AND` between timestamp equality conditions (e.g., `ts = 'X' AND ts = 'Y'`) is intentional to prevent intrinsic interval creation. This ensures the window join fails as expected when the slave doesn't support time frames, which is necessary after PR `#6673` added OR timestamp intrinsics recognition.

Learnt from: bluestreak01
Repo: questdb/questdb PR: 6643
File: core/src/main/java/io/questdb/griffin/SqlOptimiser.java:8671-8687
Timestamp: 2026-01-20T00:59:19.630Z
Learning: QuestDB’s parser rejects pure window functions (e.g., row_number(), rank()) used without an OVER clause; such invocations are invalid and fail early, so validators don’t need to catch pure-name window functions in window specs.

✅ Actions performed

Review triggered.

Note: CodeRabbit is an incremental review system and does not re-review already reviewed commits. This command is applicable only when automatic reviews are paused.

questdb-butler · 2026-01-24T14:24:58Z

⚠️ Enterprise CI Failed

The enterprise test suite failed for this PR.

Build: View Details
Tested Commit: f08359e49443d3e1f9916917d0a9e6b6181539a5

Please investigate the failure before merging.

glasstiger · 2026-01-24T14:32:32Z

[PR Coverage check]

😍 pass : 13 / 13 (100.00%)

file detail

	path	covered line	new line	coverage
🔵	io/questdb/griffin/SqlOptimiser.java	13	13	100.00%

bluestreak01 · 2026-01-24T15:21:46Z

@CodeRabbit what is the story?

coderabbitai · 2026-01-24T15:23:30Z

Tip

For best results, initiate chat on the files or code changes.

🧩 Analysis chain

@bluestreak01: Let me examine the changes in detail to provide you with a thorough review and identify any potential bugs.

🏁 Script executed:

#!/bin/bash
# Get the diff for the PR to examine the actual changes
gh pr diff 6701 --repo questdb/questdb

Length of output: 10965

🏁 Script executed:

#!/bin/bash
# Check the enterprise CI failure details if available
gh pr checks 6701 --repo questdb/questdb

Length of output: 8502

🏁 Script executed:

#!/bin/bash
# Look at the specific changes in SqlOptimiser.java around the replaceIfAggregateOrLiteral method
gh pr diff 6701 --repo questdb/questdb -- core/src/main/java/io/questdb/griffin/SqlOptimiser.java

Length of output: 36

@bluestreak01: Here's my comprehensive review of the changes:

✅ Implementation Analysis

The fix correctly addresses the root cause you described. The logic is sound:

Window join special case: When translatingModel == groupByModel (identity check), it directly returns nextLiteral(translatingAlias), which is correct since for window joins both models are the same windowJoinModel instance.
Restructured logic: The new nested structure properly handles the flow:
- If translatingAlias is null → falls through to add as key (same as before)
- If translatingAlias exists and it's a window join → early return with the alias
- If translatingAlias exists but not a window join → looks up existingAlias and proceeds with original logic

⚠️ Potential Issues Found

1. Enterprise CI Failure

The enterprise test suite failed. This is concerning and should be investigated before merging:

Build: https://dev.azure.com/questdb/questdb-enterprise/_build/results?buildId=210670
All regular CI tests passed, suggesting the failure might be enterprise-specific

Recommendation: Check the enterprise CI logs to see if the failure is related to this change or a flaky test.

2. Logic flow edge case

When translatingAlias != null but translatingModel != groupByModel and existingAlias == null, the code now falls through to add the column as a key. This is the same behavior as before, but I want to confirm this is intentional for the window join fix.

3. Test coverage observation

The test testWindowJoinSelfJoinWithAggregatesInSelectAndWhere has hardcoded UUIDs in the expected output:

2025-01-01T00:00:00.000000Z\t0010cde8-12ce-40ee-8010-a928bb8b9650\tEURUSD...

Since UUIDs are generated with rnd_uuid4(), these are non-deterministic. This test might be flaky unless there's seed initialization I'm not seeing in the diff.

📋 Code Quality Notes

Positive:

✅ 100% code coverage (13/13 lines)
✅ Comprehensive test cases covering both query model validation and end-to-end execution
✅ Clear comments explaining the window join special case
✅ The reference equality check (==) is appropriate here for object identity

Suggestions:

The comment mentions "groupByNodes/groupByAliases lists are not used for window joins since GROUP BY is disallowed with WINDOW JOIN" - consider adding an assertion to verify this invariant if not already present elsewhere

🔍 Next Steps

Investigate the Enterprise CI failure - this is blocking
Verify the UUID test won't be flaky
Consider adding a test case where translatingAlias != null but existingAlias == null for non-window joins to ensure backward compatibility

Would you like me to investigate the Enterprise CI failure logs or check the test for potential flakiness?

🧠 Learnings used

Learnt from: bluestreak01
Repo: questdb/questdb PR: 6673
File: core/src/test/java/io/questdb/test/griffin/WindowJoinTest.java:2616-2618
Timestamp: 2026-01-19T21:43:19.228Z
Learning: In test `testWindowJoinFailsWhenSlaveDoesNotSupportTimeFrames` in `core/src/test/java/io/questdb/test/griffin/WindowJoinTest.java`, the use of `AND` between timestamp equality conditions (e.g., `ts = 'X' AND ts = 'Y'`) is intentional to prevent intrinsic interval creation. This ensures the window join fails as expected when the slave doesn't support time frames, which is necessary after PR `#6673` added OR timestamp intrinsics recognition.

Learnt from: bluestreak01
Repo: questdb/questdb PR: 6643
File: core/src/main/java/io/questdb/griffin/SqlOptimiser.java:8671-8687
Timestamp: 2026-01-20T00:59:19.630Z
Learning: QuestDB’s parser rejects pure window functions (e.g., row_number(), rank()) used without an OVER clause; such invocations are invalid and fail early, so validators don’t need to catch pure-name window functions in window specs.

wip

aaff55b

cleanup

f08359e

bluestreak01 merged commit e5cd8dc into master Jan 24, 2026
42 of 43 checks passed

bluestreak01 deleted the vi_fix_win_bug branch January 24, 2026 16:22

coderabbitai bot mentioned this pull request Jan 27, 2026

fix(sql): fix order by position resolution with window functions over CTEs #6716

Merged

2 tasks

coderabbitai bot mentioned this pull request Feb 13, 2026

fix(sql): fix alias hiding original column name in CASE with window function #6775

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

fix(sql): resolve 'Invalid column' error in WINDOW JOIN with aliased columns#6701

fix(sql): resolve 'Invalid column' error in WINDOW JOIN with aliased columns#6701
bluestreak01 merged 2 commits intomasterfrom
vi_fix_win_bug

bluestreak01 commented Jan 24, 2026

Uh oh!

coderabbitai bot commented Jan 24, 2026 •

edited

Loading

Uh oh!

bluestreak01 commented Jan 24, 2026

Uh oh!

coderabbitai bot commented Jan 24, 2026

Uh oh!

bluestreak01 commented Jan 24, 2026

Uh oh!

coderabbitai bot commented Jan 24, 2026

Uh oh!

questdb-butler commented Jan 24, 2026

Uh oh!

glasstiger commented Jan 24, 2026

Uh oh!

bluestreak01 commented Jan 24, 2026

Uh oh!

coderabbitai bot commented Jan 24, 2026

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

Conversation

bluestreak01 commented Jan 24, 2026

Summary

Problem

Fix

Test plan

Example query that was failing

Uh oh!

coderabbitai bot commented Jan 24, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Walkthrough

Changes

Estimated code review effort

Possibly related PRs

Suggested labels

Suggested reviewers

Uh oh!

bluestreak01 commented Jan 24, 2026

Uh oh!

coderabbitai bot commented Jan 24, 2026

Uh oh!

bluestreak01 commented Jan 24, 2026

Uh oh!

coderabbitai bot commented Jan 24, 2026

Uh oh!

questdb-butler commented Jan 24, 2026

⚠️ Enterprise CI Failed

Uh oh!

glasstiger commented Jan 24, 2026

[PR Coverage check]

file detail

Uh oh!

bluestreak01 commented Jan 24, 2026

Uh oh!

coderabbitai bot commented Jan 24, 2026

✅ Implementation Analysis

⚠️ Potential Issues Found

1. Enterprise CI Failure

2. Logic flow edge case

3. Test coverage observation

📋 Code Quality Notes

🔍 Next Steps

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

coderabbitai bot commented Jan 24, 2026 •

edited

Loading