fix(sql): fix order by position resolution with window functions over CTEs by bluestreak01 · Pull Request #6716 · questdb/questdb

bluestreak01 · 2026-01-27T19:05:25Z

Summary

Fixed a bug where ORDER BY <position> failed with "Invalid column" error when used with a CTE containing aggregation and a window function expression

Problem

The following query failed with SqlException: [176] Invalid column: rank:

with d as (select symbol, price, count() from trades)
select *
      , 100 * (rank() over(partition by symbol order by price)) as rank1
from d
where symbol = 'EURUSD'
order by 4;

Root Cause

In SqlOptimiser.rewriteOrderByPosition(), when resolving positional ORDER BY (e.g., order by 4), the code determines which model's columns to use for position-to-name resolution. The condition at line 6908 only set baseOuter when a VIRTUAL model's direct nested model was GROUP_BY:

if (nested != null && nested.getSelectModelType() == SELECT_MODEL_GROUP_BY) {
    baseOuter = base;
}

With window functions, the model structure is:

outerVirtualModel (VIRTUAL) - columns: [symbol, price, count, rank1]
    └── windowModel (WINDOW) - columns: [symbol, price, count, rank]
            └── groupByModel (GROUP_BY)

Since VIRTUAL's nested is WINDOW (not GROUP_BY), baseOuter was not set, causing the wrong model's columns (windowModel with rank) to be used instead of the correct model (outerVirtualModel with rank1).

Fix

Extended the condition to also check for SELECT_MODEL_WINDOW:

if (nested != null && (nested.getSelectModelType() == SELECT_MODEL_GROUP_BY
        || nested.getSelectModelType() == SELECT_MODEL_WINDOW)) {
    baseOuter = base;
}

Test plan

Added testWithWindowFunctionRankAndOrderByAlias - tests ORDER BY with explicit alias
Added testWithWindowFunctionRankAndOrderByPosition - tests ORDER BY with position (the original failing case)

🤖 Generated with Claude Code

…by position

coderabbitai · 2026-01-27T19:05:32Z

Walkthrough

Modified SqlOptimiser to expand the condition for identifying outer models during ORDER BY rewriting. Previously only GROUP_BY was recognized as outer context; now WINDOW models are also treated as such. Added two test methods to verify window function ranking behavior with ORDER BY using both alias and positional references.

Changes

Cohort / File(s)	Summary
Order-by model context logic `core/src/main/java/io/questdb/griffin/SqlOptimiser.java`	Modified `rewriteOrderByPosition` to treat WINDOW nested model types as outer context alongside GROUP_BY when determining outer vs. inner models for ORDER BY token resolution
Window function ordering tests `core/src/test/java/io/questdb/test/griffin/SqlParserTest.java`	Added `testWithWindowFunctionRankAndOrderByAlias` and `testWithWindowFunctionRankAndOrderByPosition` test methods to verify rank() window function ordering behavior with alias and positional ORDER BY references

Estimated code review effort

🎯 3 (Moderate) | ⏱️ ~20 minutes

Possibly related PRs

feat(sql): support nested window functions with deduplication optimization #6643: Modifies SqlOptimiser's handling of window-related query models for ORDER BY and outer/inner model selection.
fix(sql): order-by ignored in sub-queries of aggregation #6414: Modifies SqlOptimiser's ORDER BY handling to preserve ordering across query boundaries with GROUP_BY context.
fix(sql): resolve 'Invalid column' error in WINDOW JOIN with aliased columns #6701: Adjusts SqlOptimiser's window-related name and alias resolution in ORDER BY processing.

Suggested labels

Bug, SQL

Suggested reviewers

puzpuzpuz
RaphDal

🚥 Pre-merge checks | ✅ 2 | ❌ 1

❌ Failed checks (1 warning)

Check name	Status	Explanation	Resolution
Docstring Coverage	⚠️ Warning	Docstring coverage is 20.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 clearly and specifically describes the main fix: resolving an issue with ORDER BY position resolution when window functions are used over CTEs.
Description check	✅ Passed	The description is comprehensive and directly related to the changeset. It explains the bug, root cause, fix, and test plan clearly.

_{✏️ 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.}

glasstiger · 2026-01-27T19:39:29Z

[PR Coverage check]

😍 pass : 2 / 2 (100.00%)

file detail

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

bluestreak01 · 2026-01-27T19:44:46Z

@CodeRabbit pls review

coderabbitai · 2026-01-27T19:45:03Z

@bluestreak01: I'll review the changes in this PR.

🧠 Learnings used

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.

fix(sql): fix "invalid column" when ordering window function columns …

364d008

…by position

javier approved these changes Jan 27, 2026

View reviewed changes

bluestreak01 merged commit 79e31f6 into master Jan 27, 2026
43 checks passed

bluestreak01 deleted the vi_fix_win_alias branch January 27, 2026 20:08

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

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

fix(sql): fix order by position resolution with window functions over CTEs#6716
bluestreak01 merged 1 commit intomasterfrom
vi_fix_win_alias

bluestreak01 commented Jan 27, 2026

Uh oh!

coderabbitai bot commented Jan 27, 2026 •

edited

Loading

Uh oh!

glasstiger commented Jan 27, 2026

Uh oh!

bluestreak01 commented Jan 27, 2026

Uh oh!

coderabbitai bot commented Jan 27, 2026

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

Conversation

bluestreak01 commented Jan 27, 2026

Summary

Problem

Root Cause

Fix

Test plan

Uh oh!

coderabbitai bot commented Jan 27, 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!

glasstiger commented Jan 27, 2026

[PR Coverage check]

file detail

Uh oh!

bluestreak01 commented Jan 27, 2026

Uh oh!

coderabbitai bot commented Jan 27, 2026

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

coderabbitai bot commented Jan 27, 2026 •

edited

Loading