1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
//! Iterator-based execution strategy for simple queries
//!
//! This module implements lazy iteration to avoid materializing intermediate results.
//! The pipeline: scan → filter → skip → take → collect → project
//! WHERE filtering, OFFSET, and LIMIT are fully lazy, providing memory efficiency
//! and early termination.
use super::{builder::SelectExecutor, validation::validate_where_clause_subqueries};
use crate::{
errors::ExecutorError,
optimizer::optimize_where_clause,
pipeline::ExecutionContext,
select::{
iterator::{FilterIterator, RowIterator, TableScanIterator},
join::FromResult,
projection::project_row_combined,
projection_simd::try_batch_project_simd,
window::{expression_has_window_function, has_window_functions},
},
};
impl SelectExecutor<'_> {
/// Determine if we can use iterator-based execution for this query
///
/// Iterator execution is beneficial for queries that don't require full materialization.
/// We must materialize for: ORDER BY, DISTINCT, and window functions.
pub(in crate::select::executor) fn can_use_iterator_execution(
stmt: &vibesql_ast::SelectStmt,
) -> bool {
// Can't use iterators if we have ORDER BY (requires sorting all rows)
if stmt.order_by.is_some() {
return false;
}
// Can't use iterators if we have DISTINCT (requires deduplication of all rows)
if stmt.distinct {
return false;
}
// Can't use iterators if we have window functions (requires full window frames)
if has_window_functions(&stmt.select_list) {
return false;
}
// Can't use iterators if ORDER BY has window functions
if let Some(order_by) = &stmt.order_by {
if order_by.iter().any(|item| expression_has_window_function(&item.expr)) {
return false;
}
}
// All checks passed - we can use iterator execution!
true
}
/// Execute SELECT using iterator-based execution (for simple queries)
///
/// This method uses lazy iteration to avoid materializing intermediate results.
/// The pipeline: scan → filter → skip → take → collect → project
/// WHERE filtering, OFFSET, and LIMIT are fully lazy, providing memory efficiency
/// and early termination. Projection happens after materialization due to its
/// complexity (wildcard expansion, expression evaluation, etc.).
pub(in crate::select::executor) fn execute_with_iterators(
&self,
stmt: &vibesql_ast::SelectStmt,
from_result: FromResult,
cte_results: &std::collections::HashMap<String, crate::select::CteResult>,
) -> Result<Vec<vibesql_storage::Row>, ExecutorError> {
let schema = from_result.schema.clone();
let _sorted_by = from_result.sorted_by.clone();
let rows = from_result.into_rows();
// Create evaluator using consolidated ExecutionContext
// Handles: outer context (subqueries), procedural context, CTE context
let cte_ctx = if !cte_results.is_empty() { Some(cte_results) } else { self.cte_context };
let mut ctx = ExecutionContext::new(&schema, self.database);
if let (Some(outer_row), Some(outer_schema)) = (self.outer_row, self.outer_schema) {
ctx = ctx.with_outer_context(outer_row, outer_schema);
} else if let Some(proc_ctx) = self.procedural_context {
ctx = ctx.with_procedural_context(proc_ctx);
} else if let Some(trigger_ctx) = self.trigger_context {
// Issue #5082: thread trigger context into the synthetic SELECT used by
// UPDATE…FROM in trigger bodies so OLD/NEW pseudo-vars resolve.
ctx = ctx.with_trigger_context(trigger_ctx);
}
if let Some(cte_ctx) = cte_ctx {
ctx = ctx.with_cte_context(cte_ctx);
}
// Issue #4930: Pass outer_rows for outer-correlated aggregates
// Only set when we actually have outer_rows from a parent subquery context.
// Avoids expensive clone of rows when not needed (most queries).
if let Some(outer_rows) = self.outer_rows {
ctx = ctx.with_outer_rows(outer_rows);
}
let evaluator = ctx.create_evaluator();
// Resolve SELECT aliases in WHERE clause (SQLite extension)
// This allows queries like: SELECT f1-22 AS x FROM t1 WHERE x > 0
// IMPORTANT: Use schema-aware resolution to avoid incorrectly substituting
// table column names with aggregate aliases (SQLite behavior)
// PERFORMANCE: Skip alias resolution if no aliases exist (common case in OLTP)
let resolved_where = if crate::select::order::select_list_has_aliases(&stmt.select_list) {
stmt.where_clause.as_ref().map(|where_expr| {
crate::select::order::resolve_where_aliases_with_schema(
where_expr,
&stmt.select_list,
&schema,
)
})
} else {
stmt.where_clause.clone()
};
// Validate WHERE clause subqueries upfront (before row iteration)
// This ensures schema validation happens even for empty result sets
// Issue #3562: Pass CTE context so CTEs can be resolved in IN subqueries
if let Some(ref where_expr) = resolved_where {
validate_where_clause_subqueries(where_expr, self.database, cte_ctx)?;
}
// Stage 1: Table scan
let mut iterator: Box<dyn RowIterator> =
Box::new(TableScanIterator::new(schema.clone(), rows));
// Stage 2: WHERE filter (if present)
if let Some(ref where_expr) = resolved_where {
// Optimize WHERE clause
let where_optimization = optimize_where_clause(Some(where_expr), &evaluator)?;
match where_optimization {
crate::optimizer::WhereOptimization::AlwaysFalse => {
// WHERE FALSE - return empty result immediately
return Ok(Vec::new());
}
crate::optimizer::WhereOptimization::AlwaysTrue => {
// WHERE TRUE - no filtering needed, keep current iterator
}
crate::optimizer::WhereOptimization::Optimized(expr) => {
// Apply optimized WHERE clause - use the evaluator that has outer context if
// present
iterator = Box::new(FilterIterator::new(
iterator,
expr,
evaluator.clone_for_new_expression(),
));
}
crate::optimizer::WhereOptimization::Unchanged(Some(expr)) => {
// Apply original WHERE clause - use the evaluator that has outer context if
// present
iterator = Box::new(FilterIterator::new(
iterator,
expr.clone(),
evaluator.clone_for_new_expression(),
));
}
crate::optimizer::WhereOptimization::Unchanged(None) => {
// No WHERE clause - keep current iterator
}
}
}
// Stage 3 & 4: OFFSET and LIMIT (skip if set operation - applied after UNION)
// For set operations (UNION, INTERSECT, EXCEPT), LIMIT/OFFSET must be applied
// after combining results, not to individual subqueries
let iterator: Box<dyn Iterator<Item = _>> = if stmt.set_operation.is_some() {
// Skip OFFSET/LIMIT for set operations - will be applied at top level
iterator
} else {
// Apply OFFSET (skip rows lazily)
let iter: Box<dyn Iterator<Item = _>> = if let Some(ref offset_expr) = stmt.offset {
let offset_usize = crate::select::helpers::evaluate_limit_offset_expr(
offset_expr,
self.database,
"OFFSET",
)?;
Box::new(iterator.skip(offset_usize))
} else {
iterator
};
// Apply LIMIT (take only needed rows)
if let Some(ref limit_expr) = stmt.limit {
let limit_usize = crate::select::helpers::evaluate_limit_offset_expr(
limit_expr,
self.database,
"LIMIT",
)?;
Box::new(iter.take(limit_usize))
} else {
iter
}
};
// Stage 5: Materialize filtered results
// Use pooled buffer to reduce allocation overhead
let mut filtered_rows = self.database.query_buffer_pool().get_row_buffer(128);
for row_result in iterator {
// Check timeout during iteration
self.check_timeout()?;
filtered_rows.push(row_result?);
}
// SQLite compatibility: Do NOT apply implicit value-based sorting (issue #4537)
// SQLite returns rows in storage order (insertion order / rowid order) when no ORDER BY
// is specified. Implicit sorting by value breaks this behavior and causes TCL test failures.
//
// Previously we sorted for "deterministic results", but SQLite's deterministic behavior
// is based on physical storage order, not value-based sorting.
// Stage 6: Project columns (handles wildcards, expressions, etc.)
// Try batch SIMD projection first for large datasets
let final_rows = if let Some(projected) = try_batch_project_simd(
&filtered_rows,
&stmt.select_list,
&evaluator,
&schema,
&None, // No window functions in iterator path
self.database.query_buffer_pool(),
)? {
// SIMD batch projection succeeded
projected
} else {
// Fall back to row-by-row projection
// Use pooled buffer to reduce allocation overhead
let mut rows = self.database.query_buffer_pool().get_row_buffer(filtered_rows.len());
for row in &filtered_rows {
// Clear CSE cache before projecting each row
evaluator.clear_cse_cache();
let projected_row = project_row_combined(
row,
&stmt.select_list,
&evaluator,
&schema,
&None, // No window functions in iterator path
self.database.query_buffer_pool(),
)?;
rows.push(projected_row);
}
rows
};
// Clear CSE cache at end of query to prevent cross-query pollution
// Cache can persist within a single query for performance, but must be
// cleared between different SQL statements to avoid stale values
evaluator.clear_cse_cache();
// Return intermediate buffer to pool, then return final result
self.database.query_buffer_pool().return_row_buffer(filtered_rows);
Ok(final_rows)
}
}