vibesql_executor/
explain.rs

1//! EXPLAIN statement execution
2//!
3//! This module provides the ExplainExecutor for analyzing query execution plans.
4//! It shows information about:
5//! - Table scans vs index scans
6//! - Skip-scan optimization (non-prefix index usage)
7//! - Join types and order
8//! - Filter pushdown information
9//! - Estimated row counts (when statistics are available)
10//!
11//! Supports two output formats:
12//! - PostgreSQL-style text output (default for EXPLAIN)
13//! - SQLite-style EXPLAIN QUERY PLAN output (for TCL test compatibility)
14
15use std::fmt::Write;
16
17use std::collections::HashSet;
18
19use vibesql_ast::pretty_print::ToSql;
20use vibesql_ast::{ExplainFormat, ExplainStmt, Expression, SelectItem, SelectStmt, Statement};
21use vibesql_storage::Database;
22
23use crate::{
24    errors::ExecutorError, optimizer::index_planner::IndexPlanner,
25    select::scan::index_scan::cost_based_index_selection,
26};
27
28/// SQLite-style scan type for EQP output
29#[derive(Debug, Clone, PartialEq)]
30pub enum ScanType {
31    /// Sequential scan (SCAN table)
32    Scan,
33    /// Index search (SEARCH table USING INDEX ...)
34    Search,
35    /// Covering index scan (SEARCH table USING COVERING INDEX ...)
36    CoveringIndex,
37    /// Integer primary key lookup (SEARCH table USING INTEGER PRIMARY KEY ...)
38    IntegerPrimaryKey,
39}
40
41/// Represents index predicate information for SQLite EQP output
42#[derive(Debug, Clone)]
43pub struct IndexPredicate {
44    /// Column name
45    pub column: String,
46    /// Predicate type: "=", ">", "<", ">=", "<=", etc.
47    pub predicate_type: String,
48}
49
50/// Represents a single node in the query execution plan
51#[derive(Debug, Clone)]
52pub struct PlanNode {
53    /// Type of operation (e.g., "Seq Scan", "Index Scan", "Hash Join")
54    pub operation: String,
55    /// Target object (e.g., table name, index name)
56    pub object: Option<String>,
57    /// Additional details about this operation
58    pub details: Vec<String>,
59    /// Estimated rows (if statistics available)
60    pub estimated_rows: Option<f64>,
61    /// Child nodes in the plan tree
62    pub children: Vec<PlanNode>,
63    /// SQLite-style scan type (for EQP output)
64    pub scan_type: Option<ScanType>,
65    /// Index name used (for SEARCH operations)
66    pub index_name: Option<String>,
67    /// Index predicates for SQLite EQP format (e.g., "w=?", "x>? AND x<?")
68    pub index_predicates: Vec<IndexPredicate>,
69    /// Whether this query requires a temp B-tree for ORDER BY
70    pub needs_temp_btree_for_order_by: bool,
71    /// Set operation type for compound queries (UNION, INTERSECT, EXCEPT)
72    pub set_operation_type: Option<String>,
73    /// Whether this is a compound query root
74    pub is_compound_query: bool,
75}
76
77impl PlanNode {
78    fn new(operation: &str) -> Self {
79        PlanNode {
80            operation: operation.to_string(),
81            object: None,
82            details: Vec::new(),
83            estimated_rows: None,
84            children: Vec::new(),
85            scan_type: None,
86            index_name: None,
87            index_predicates: Vec::new(),
88            needs_temp_btree_for_order_by: false,
89            set_operation_type: None,
90            is_compound_query: false,
91        }
92    }
93
94    fn with_object(mut self, object: &str) -> Self {
95        self.object = Some(object.to_string());
96        self
97    }
98
99    fn with_detail(mut self, detail: String) -> Self {
100        self.details.push(detail);
101        self
102    }
103
104    fn with_estimated_rows(mut self, rows: f64) -> Self {
105        self.estimated_rows = Some(rows);
106        self
107    }
108
109    fn with_scan_type(mut self, scan_type: ScanType) -> Self {
110        self.scan_type = Some(scan_type);
111        self
112    }
113
114    fn with_index_name(mut self, index_name: &str) -> Self {
115        self.index_name = Some(index_name.to_string());
116        self
117    }
118
119    fn with_index_predicate(mut self, column: &str, predicate_type: &str) -> Self {
120        self.index_predicates.push(IndexPredicate {
121            column: column.to_string(),
122            predicate_type: predicate_type.to_string(),
123        });
124        self
125    }
126
127    fn add_child(&mut self, child: PlanNode) {
128        self.children.push(child);
129    }
130}
131
132/// Result of EXPLAIN operation
133#[derive(Debug)]
134pub struct ExplainResult {
135    /// Root node of the execution plan
136    pub plan: PlanNode,
137    /// Output format
138    pub format: ExplainFormat,
139}
140
141impl ExplainResult {
142    /// Format the plan as text output (PostgreSQL-style)
143    pub fn to_text(&self) -> String {
144        let mut output = String::new();
145        format_node_text(&self.plan, 0, &mut output);
146        output
147    }
148
149    /// Format the plan as JSON output
150    pub fn to_json(&self) -> String {
151        format_node_json(&self.plan)
152    }
153
154    /// Format the plan as SQLite-compatible EXPLAIN QUERY PLAN output
155    ///
156    /// Produces output like:
157    /// ```text
158    /// QUERY PLAN
159    /// |--SCAN t1 USING INDEX i1
160    /// `--USE TEMP B-TREE FOR LAST TERM OF ORDER BY
161    /// ```
162    ///
163    /// For compound queries:
164    /// ```text
165    /// QUERY PLAN
166    /// `--COMPOUND QUERY
167    ///    |--LEFT-MOST SUBQUERY
168    ///    |  `--SCAN t1
169    ///    `--UNION ALL
170    ///       `--SCAN t2
171    /// ```
172    ///
173    /// The "QUERY PLAN" header is included to match SQLite's EQP format used in
174    /// TCL tests with full format comparison (do_eqp_test with QUERY PLAN prefix).
175    pub fn to_sqlite_eqp(&self) -> String {
176        let mut output = String::new();
177
178        // Add "QUERY PLAN" header to match SQLite's EQP format
179        writeln!(output, "QUERY PLAN").unwrap();
180
181        // Check if this is a compound query
182        if self.plan.is_compound_query {
183            format_compound_query_eqp(&self.plan, "", true, &mut output);
184        } else {
185            // Collect all EQP entries (scan nodes + temp b-tree if needed)
186            let entries = collect_eqp_entries(&self.plan);
187
188            for (i, entry) in entries.iter().enumerate() {
189                let is_last = i == entries.len() - 1;
190                let prefix = if is_last { "`--" } else { "|--" };
191                writeln!(output, "{}{}", prefix, entry).unwrap();
192            }
193        }
194
195        output
196    }
197
198    /// Format the plan as SQLite-compatible EXPLAIN output (VM bytecode style)
199    ///
200    /// This produces output mimicking SQLite's VDBE bytecode format:
201    /// ```text
202    /// addr  opcode         p1    p2    p3    p4             p5  comment
203    /// ----  -------------  ----  ----  ----  -------------  --  -------------
204    /// 0     Init           0     8     0                    0   Start at 8
205    /// 1     OpenRead       0     2     0     2              0   root=2 iDb=0; t1
206    /// ...
207    /// ```
208    ///
209    /// Note: This is a synthetic representation since VibeSQL doesn't use SQLite's VM.
210    /// The opcodes are generated to approximate what SQLite would produce.
211    pub fn to_sqlite_vm(&self) -> SqliteVmOutput {
212        let mut instructions = Vec::new();
213        let mut addr = 0;
214
215        // Collect scan nodes to determine table access patterns
216        let scan_nodes = collect_scan_nodes(&self.plan);
217
218        // Generate Init instruction (always first)
219        // p2 will be updated to point to Transaction after we know total instructions
220        let init_addr = addr;
221        instructions.push(VmInstruction {
222            addr,
223            opcode: "Init".to_string(),
224            p1: 0,
225            p2: 0, // Will be patched later
226            p3: 0,
227            p4: String::new(),
228            p5: 0,
229            comment: "Start at ?".to_string(),
230        });
231        addr += 1;
232
233        // Generate OpenRead for each table
234        let mut cursor = 0;
235        let mut table_cursors = std::collections::HashMap::new();
236
237        for node in &scan_nodes {
238            if let Some(ref table_name) = node.object {
239                if !table_cursors.contains_key(table_name) {
240                    let root_page = 2 + cursor; // Synthetic root page
241                    instructions.push(VmInstruction {
242                        addr,
243                        opcode: "OpenRead".to_string(),
244                        p1: cursor,
245                        p2: root_page,
246                        p3: 0,
247                        p4: "2".to_string(), // Number of columns (synthetic)
248                        p5: 0,
249                        comment: format!("root={} iDb=0; {}", root_page, table_name),
250                    });
251                    table_cursors.insert(table_name.clone(), cursor);
252                    cursor += 1;
253                    addr += 1;
254                }
255            }
256        }
257
258        // Generate seek/scan instructions based on scan type
259        let result_row_addr = addr + scan_nodes.len() * 2 + 1;
260        let halt_addr = result_row_addr + 1;
261
262        for node in &scan_nodes {
263            if let Some(ref table_name) = node.object {
264                let cursor_id = *table_cursors.get(table_name).unwrap_or(&0);
265
266                match node.scan_type.as_ref() {
267                    Some(ScanType::Search) | Some(ScanType::CoveringIndex) => {
268                        // Index seek
269                        instructions.push(VmInstruction {
270                            addr,
271                            opcode: "SeekGE".to_string(),
272                            p1: cursor_id,
273                            p2: halt_addr as i32,
274                            p3: 1,
275                            p4: String::new(),
276                            p5: 0,
277                            comment: format!("key=r[1]; {}", table_name),
278                        });
279                        addr += 1;
280                    }
281                    Some(ScanType::IntegerPrimaryKey) => {
282                        // Primary key lookup
283                        instructions.push(VmInstruction {
284                            addr,
285                            opcode: "SeekRowid".to_string(),
286                            p1: cursor_id,
287                            p2: halt_addr as i32,
288                            p3: 1,
289                            p4: String::new(),
290                            p5: 0,
291                            comment: format!("pk; {}", table_name),
292                        });
293                        addr += 1;
294                    }
295                    Some(ScanType::Scan) | None => {
296                        // Full table scan - use Rewind
297                        instructions.push(VmInstruction {
298                            addr,
299                            opcode: "Rewind".to_string(),
300                            p1: cursor_id,
301                            p2: halt_addr as i32,
302                            p3: 0,
303                            p4: String::new(),
304                            p5: 0,
305                            comment: table_name.clone(),
306                        });
307                        addr += 1;
308                    }
309                }
310            }
311        }
312
313        // Generate Column and ResultRow instructions
314        let mut register = 1;
315        for (i, node) in scan_nodes.iter().enumerate() {
316            if let Some(ref table_name) = node.object {
317                let cursor_id = *table_cursors.get(table_name).unwrap_or(&0);
318                // Column instruction for each output column
319                instructions.push(VmInstruction {
320                    addr,
321                    opcode: "Column".to_string(),
322                    p1: cursor_id,
323                    p2: i as i32, // Column index
324                    p3: register,
325                    p4: String::new(),
326                    p5: 0,
327                    comment: format!("r[{}]=cursor {} column {}", register, cursor_id, i),
328                });
329                register += 1;
330                addr += 1;
331            }
332        }
333
334        // ResultRow instruction
335        instructions.push(VmInstruction {
336            addr,
337            opcode: "ResultRow".to_string(),
338            p1: 1,
339            p2: register - 1,
340            p3: 0,
341            p4: String::new(),
342            p5: 0,
343            comment: format!("output=r[1..{}]", register - 1),
344        });
345        addr += 1;
346
347        // Next/Goto for each scan
348        for node in &scan_nodes {
349            if let Some(ref table_name) = node.object {
350                let cursor_id = *table_cursors.get(table_name).unwrap_or(&0);
351                let seek_addr = scan_nodes
352                    .iter()
353                    .position(|n| n.object.as_ref() == Some(table_name))
354                    .map(|pos| 1 + table_cursors.len() + pos)
355                    .unwrap_or(1);
356
357                instructions.push(VmInstruction {
358                    addr,
359                    opcode: "Next".to_string(),
360                    p1: cursor_id,
361                    p2: seek_addr as i32,
362                    p3: 0,
363                    p4: String::new(),
364                    p5: 0,
365                    comment: table_name.clone(),
366                });
367                addr += 1;
368            }
369        }
370
371        // Halt instruction
372        instructions.push(VmInstruction {
373            addr,
374            opcode: "Halt".to_string(),
375            p1: 0,
376            p2: 0,
377            p3: 0,
378            p4: String::new(),
379            p5: 0,
380            comment: String::new(),
381        });
382        addr += 1;
383
384        // Transaction instruction (usually near the end in SQLite)
385        let transaction_addr = addr;
386        instructions.push(VmInstruction {
387            addr,
388            opcode: "Transaction".to_string(),
389            p1: 0,
390            p2: 0,
391            p3: 1,
392            p4: "0".to_string(),
393            p5: 1,
394            comment: "usesStmtJournal=0".to_string(),
395        });
396        addr += 1;
397
398        // Goto to first instruction after Init
399        instructions.push(VmInstruction {
400            addr,
401            opcode: "Goto".to_string(),
402            p1: 0,
403            p2: 1,
404            p3: 0,
405            p4: String::new(),
406            p5: 0,
407            comment: String::new(),
408        });
409
410        // Patch Init instruction to jump to Transaction
411        if let Some(init) = instructions.get_mut(init_addr) {
412            init.p2 = transaction_addr as i32;
413            init.comment = format!("Start at {}", transaction_addr);
414        }
415
416        SqliteVmOutput { instructions }
417    }
418}
419
420/// Represents a single SQLite VM instruction for EXPLAIN output
421#[derive(Debug, Clone)]
422pub struct VmInstruction {
423    /// Instruction address (sequential)
424    pub addr: usize,
425    /// Opcode name (e.g., "OpenRead", "SeekGE", "Column")
426    pub opcode: String,
427    /// First integer parameter
428    pub p1: i32,
429    /// Second integer parameter
430    pub p2: i32,
431    /// Third integer parameter
432    pub p3: i32,
433    /// String parameter
434    pub p4: String,
435    /// Fifth integer parameter (flags)
436    pub p5: i32,
437    /// Human-readable comment
438    pub comment: String,
439}
440
441/// SQLite VM EXPLAIN output
442#[derive(Debug)]
443pub struct SqliteVmOutput {
444    /// List of VM instructions
445    pub instructions: Vec<VmInstruction>,
446}
447
448impl SqliteVmOutput {
449    /// Get the column names for SQLite EXPLAIN output
450    pub fn column_names() -> Vec<&'static str> {
451        vec!["addr", "opcode", "p1", "p2", "p3", "p4", "p5", "comment"]
452    }
453
454    /// Convert to rows for display
455    pub fn to_rows(&self) -> Vec<Vec<String>> {
456        self.instructions
457            .iter()
458            .map(|inst| {
459                vec![
460                    inst.addr.to_string(),
461                    inst.opcode.clone(),
462                    inst.p1.to_string(),
463                    inst.p2.to_string(),
464                    inst.p3.to_string(),
465                    inst.p4.clone(),
466                    inst.p5.to_string(),
467                    inst.comment.clone(),
468                ]
469            })
470            .collect()
471    }
472}
473
474/// Collect all scan/search nodes from the plan tree for SQLite EQP output
475fn collect_scan_nodes(node: &PlanNode) -> Vec<&PlanNode> {
476    let mut nodes = Vec::new();
477
478    // Check if this is a scan/search node (has scan_type set)
479    if node.scan_type.is_some() {
480        nodes.push(node);
481    }
482
483    // Recursively collect from children
484    for child in &node.children {
485        nodes.extend(collect_scan_nodes(child));
486    }
487
488    nodes
489}
490
491/// Collect all EQP entries from the plan tree, including TEMP B-TREE entries
492fn collect_eqp_entries(node: &PlanNode) -> Vec<String> {
493    let mut entries = Vec::new();
494
495    // Collect scan nodes first
496    let scan_nodes = collect_scan_nodes(node);
497    for scan_node in &scan_nodes {
498        entries.push(format_sqlite_eqp_node(scan_node));
499    }
500
501    // Add TEMP B-TREE entry if needed for ORDER BY
502    if node.needs_temp_btree_for_order_by {
503        entries.push("USE TEMP B-TREE FOR ORDER BY".to_string());
504    }
505
506    // Check children for temp b-tree needs as well
507    for child in &node.children {
508        if child.needs_temp_btree_for_order_by && !node.needs_temp_btree_for_order_by {
509            entries.push("USE TEMP B-TREE FOR ORDER BY".to_string());
510            break;
511        }
512    }
513
514    entries
515}
516
517/// Format a compound query in SQLite EQP style
518fn format_compound_query_eqp(node: &PlanNode, indent: &str, is_last: bool, output: &mut String) {
519    let connector = if is_last { "`--" } else { "|--" };
520    let child_indent = if is_last {
521        format!("{}   ", indent)
522    } else {
523        format!("{}|  ", indent)
524    };
525
526    // Write COMPOUND QUERY header
527    writeln!(output, "{}{}COMPOUND QUERY", indent, connector).unwrap();
528
529    // Write compound query children
530    let child_count = node.children.len();
531    for (i, child) in node.children.iter().enumerate() {
532        let is_child_last = i == child_count - 1;
533        let child_connector = if is_child_last { "`--" } else { "|--" };
534        let grandchild_indent = if is_child_last {
535            format!("{}   ", child_indent)
536        } else {
537            format!("{}|  ", child_indent)
538        };
539
540        if i == 0 {
541            // First subquery
542            writeln!(output, "{}{}LEFT-MOST SUBQUERY", child_indent, child_connector).unwrap();
543            // Write scan nodes for this subquery
544            let scan_nodes = collect_scan_nodes(child);
545            for (j, scan_node) in scan_nodes.iter().enumerate() {
546                let is_scan_last = j == scan_nodes.len() - 1;
547                let scan_connector = if is_scan_last { "`--" } else { "|--" };
548                writeln!(output, "{}{}{}", grandchild_indent, scan_connector, format_sqlite_eqp_node(scan_node)).unwrap();
549            }
550        } else {
551            // Subsequent parts with set operation label
552            let set_op = child.set_operation_type.as_deref().unwrap_or("UNION ALL");
553            writeln!(output, "{}{}{}", child_indent, child_connector, set_op).unwrap();
554            // Write scan nodes for this subquery
555            let scan_nodes = collect_scan_nodes(child);
556            for (j, scan_node) in scan_nodes.iter().enumerate() {
557                let is_scan_last = j == scan_nodes.len() - 1;
558                let scan_connector = if is_scan_last { "`--" } else { "|--" };
559                writeln!(output, "{}{}{}", grandchild_indent, scan_connector, format_sqlite_eqp_node(scan_node)).unwrap();
560            }
561        }
562    }
563}
564
565/// Format a single node in SQLite EQP style
566fn format_sqlite_eqp_node(node: &PlanNode) -> String {
567    // Handle constant row scan (no FROM clause)
568    if node.operation == "SCAN CONSTANT ROW" {
569        return "SCAN CONSTANT ROW".to_string();
570    }
571
572    let table_name = node.object.as_deref().unwrap_or("?");
573
574    match node.scan_type.as_ref() {
575        Some(ScanType::Scan) => {
576            // Check if we have an index for ordering (SCAN table USING INDEX idx)
577            if let Some(ref index_name) = node.index_name {
578                format!("SCAN {} USING INDEX {}", table_name, index_name)
579            } else {
580                format!("SCAN {}", table_name)
581            }
582        }
583        Some(ScanType::Search) | Some(ScanType::CoveringIndex) => {
584            let index_name = node.index_name.as_deref().unwrap_or("?");
585            let covering = if matches!(node.scan_type, Some(ScanType::CoveringIndex)) {
586                "COVERING "
587            } else {
588                ""
589            };
590
591            if node.index_predicates.is_empty() {
592                format!("SEARCH {} USING {}INDEX {}", table_name, covering, index_name)
593            } else {
594                let predicates: Vec<String> = node
595                    .index_predicates
596                    .iter()
597                    .map(|p| format!("{}{}?", p.column, p.predicate_type))
598                    .collect();
599                format!(
600                    "SEARCH {} USING {}INDEX {} ({})",
601                    table_name,
602                    covering,
603                    index_name,
604                    predicates.join(" AND ")
605                )
606            }
607        }
608        Some(ScanType::IntegerPrimaryKey) => {
609            if node.index_predicates.is_empty() {
610                format!("SEARCH {} USING INTEGER PRIMARY KEY (rowid=?)", table_name)
611            } else {
612                let predicates: Vec<String> = node
613                    .index_predicates
614                    .iter()
615                    .map(|p| format!("rowid{}?", p.predicate_type))
616                    .collect();
617                format!(
618                    "SEARCH {} USING INTEGER PRIMARY KEY ({})",
619                    table_name,
620                    predicates.join(" AND ")
621                )
622            }
623        }
624        None => {
625            // Fallback for nodes without scan_type (e.g., joins)
626            node.operation.clone()
627        }
628    }
629}
630
631fn format_node_text(node: &PlanNode, depth: usize, output: &mut String) {
632    let indent = "  ".repeat(depth);
633    let arrow = if depth > 0 { "-> " } else { "" };
634
635    // Format the main operation line
636    let mut line = format!("{}{}{}", indent, arrow, node.operation);
637
638    if let Some(ref obj) = node.object {
639        write!(line, " on {}", obj).unwrap();
640    }
641
642    if let Some(rows) = node.estimated_rows {
643        write!(line, "  (rows={:.0})", rows).unwrap();
644    }
645
646    writeln!(output, "{}", line).unwrap();
647
648    // Format details
649    for detail in &node.details {
650        writeln!(output, "{}      {}", indent, detail).unwrap();
651    }
652
653    // Format children
654    for child in &node.children {
655        format_node_text(child, depth + 1, output);
656    }
657}
658
659fn format_node_json(node: &PlanNode) -> String {
660    let mut parts = vec![format!("\"operation\": \"{}\"", node.operation)];
661
662    if let Some(ref obj) = node.object {
663        parts.push(format!("\"object\": \"{}\"", obj));
664    }
665
666    if !node.details.is_empty() {
667        let details: Vec<String> = node.details.iter().map(|d| format!("\"{}\"", d)).collect();
668        parts.push(format!("\"details\": [{}]", details.join(", ")));
669    }
670
671    if let Some(rows) = node.estimated_rows {
672        parts.push(format!("\"estimated_rows\": {:.0}", rows));
673    }
674
675    if !node.children.is_empty() {
676        let children: Vec<String> = node.children.iter().map(format_node_json).collect();
677        parts.push(format!("\"children\": [{}]", children.join(", ")));
678    }
679
680    format!("{{{}}}", parts.join(", "))
681}
682
683/// Executor for EXPLAIN statements
684pub struct ExplainExecutor;
685
686impl ExplainExecutor {
687    /// Execute an EXPLAIN statement
688    pub fn execute(
689        stmt: &ExplainStmt,
690        database: &Database,
691    ) -> Result<ExplainResult, ExecutorError> {
692        let plan = match stmt.statement.as_ref() {
693            Statement::Select(select_stmt) => Self::explain_select(select_stmt, database)?,
694            Statement::Insert(_) => {
695                PlanNode::new("Insert").with_detail("Inserts rows into target table".to_string())
696            }
697            Statement::Update(_) => PlanNode::new("Update")
698                .with_detail("Updates rows matching WHERE clause".to_string()),
699            Statement::Delete(_) => PlanNode::new("Delete")
700                .with_detail("Deletes rows matching WHERE clause".to_string()),
701            _ => {
702                return Err(ExecutorError::Other(
703                    "EXPLAIN only supports SELECT, INSERT, UPDATE, DELETE statements".to_string(),
704                ));
705            }
706        };
707
708        Ok(ExplainResult { plan, format: stmt.format.clone() })
709    }
710
711    /// Generate execution plan for a SELECT statement
712    fn explain_select(stmt: &SelectStmt, database: &Database) -> Result<PlanNode, ExecutorError> {
713        // Check if this is a compound query (UNION, INTERSECT, EXCEPT)
714        if stmt.set_operation.is_some() {
715            return Self::explain_compound_select(stmt, database);
716        }
717
718        let mut root = PlanNode::new("Select");
719
720        // Extract columns needed by the SELECT list for covering index detection
721        let needed_columns = extract_select_columns(&stmt.select_list);
722
723        // Analyze FROM clause
724        if let Some(ref from_clause) = stmt.from {
725            let scan_node = Self::explain_from_clause(
726                from_clause,
727                &stmt.where_clause,
728                &stmt.order_by,
729                &needed_columns,
730                database,
731            )?;
732            root.add_child(scan_node);
733        } else {
734            // No FROM clause - this is a constant expression scan
735            let mut constant_node = PlanNode::new("Constant Row");
736            constant_node.scan_type = Some(ScanType::Scan);
737            constant_node.operation = "SCAN CONSTANT ROW".to_string();
738            root.add_child(constant_node);
739        }
740
741        // Check if we need a temp B-tree for ORDER BY
742        // This happens when ORDER BY cannot be satisfied by an index
743        if let Some(ref order_by) = stmt.order_by {
744            let needs_temp = Self::needs_temp_btree_for_order_by(
745                stmt.from.as_ref(),
746                order_by,
747                database,
748            );
749            root.needs_temp_btree_for_order_by = needs_temp;
750        }
751
752        // Add WHERE clause info
753        if stmt.where_clause.is_some() {
754            root.details.push("Filter: <where clause>".to_string());
755        }
756
757        // Add GROUP BY info
758        if stmt.group_by.is_some() {
759            root.details.push("Group: <group by clause>".to_string());
760        }
761
762        // Add ORDER BY info
763        if stmt.order_by.is_some() {
764            root.details.push("Sort: <order by clause>".to_string());
765        }
766
767        // Add LIMIT info
768        if let Some(ref limit) = stmt.limit {
769            root.details.push(format!("Limit: {}", limit.to_sql()));
770        }
771
772        Ok(root)
773    }
774
775    /// Generate execution plan for a compound SELECT (UNION, INTERSECT, EXCEPT)
776    fn explain_compound_select(stmt: &SelectStmt, database: &Database) -> Result<PlanNode, ExecutorError> {
777        let mut root = PlanNode::new("CompoundQuery");
778        root.is_compound_query = true;
779
780        // Add left-most subquery
781        let left_stmt = SelectStmt {
782            with_clause: stmt.with_clause.clone(),
783            distinct: stmt.distinct,
784            select_list: stmt.select_list.clone(),
785            into_table: stmt.into_table.clone(),
786            into_variables: stmt.into_variables.clone(),
787            from: stmt.from.clone(),
788            where_clause: stmt.where_clause.clone(),
789            group_by: stmt.group_by.clone(),
790            having: stmt.having.clone(),
791            order_by: None, // ORDER BY applies to the compound result
792            limit: None,
793            offset: None,
794            set_operation: None,
795            values: stmt.values.clone(),
796        };
797        let left_plan = Self::explain_select(&left_stmt, database)?;
798        root.add_child(left_plan);
799
800        // Add subsequent set operations
801        let mut current_set_op = stmt.set_operation.as_ref();
802        while let Some(set_op) = current_set_op {
803            let op_label = match (&set_op.op, set_op.all) {
804                (vibesql_ast::SetOperator::Union, true) => "UNION ALL",
805                (vibesql_ast::SetOperator::Union, false) => "UNION",
806                (vibesql_ast::SetOperator::Intersect, true) => "INTERSECT ALL",
807                (vibesql_ast::SetOperator::Intersect, false) => "INTERSECT",
808                (vibesql_ast::SetOperator::Except, true) => "EXCEPT ALL",
809                (vibesql_ast::SetOperator::Except, false) => "EXCEPT",
810            };
811
812            // Create plan for right side of set operation
813            let right_stmt = SelectStmt {
814                with_clause: set_op.right.with_clause.clone(),
815                distinct: set_op.right.distinct,
816                select_list: set_op.right.select_list.clone(),
817                into_table: set_op.right.into_table.clone(),
818                into_variables: set_op.right.into_variables.clone(),
819                from: set_op.right.from.clone(),
820                where_clause: set_op.right.where_clause.clone(),
821                group_by: set_op.right.group_by.clone(),
822                having: set_op.right.having.clone(),
823                order_by: None,
824                limit: None,
825                offset: None,
826                set_operation: None,
827                values: set_op.right.values.clone(),
828            };
829            let mut right_plan = Self::explain_select(&right_stmt, database)?;
830            right_plan.set_operation_type = Some(op_label.to_string());
831            root.add_child(right_plan);
832
833            // Continue to nested set operations
834            current_set_op = set_op.right.set_operation.as_ref();
835        }
836
837        Ok(root)
838    }
839
840    /// Check if ORDER BY requires a temp B-tree (sorting pass)
841    fn needs_temp_btree_for_order_by(
842        from: Option<&vibesql_ast::FromClause>,
843        order_by: &[vibesql_ast::OrderByItem],
844        database: &Database,
845    ) -> bool {
846        // If no FROM clause, this is a constant expression - no sorting needed
847        // (e.g., "SELECT 5 ORDER BY 1" just returns one row)
848        let Some(from_clause) = from else {
849            return false;
850        };
851
852        // Get the table name
853        let table_name = match from_clause {
854            vibesql_ast::FromClause::Table { name, .. } => name.as_str(),
855            _ => return true, // Joins/subqueries need temp B-tree
856        };
857
858        // Check if we have an index that can satisfy the ORDER BY
859        let order_by_vec: Vec<vibesql_ast::OrderByItem> = order_by.to_vec();
860        let index_info = cost_based_index_selection(
861            table_name,
862            None, // No WHERE clause for this check
863            Some(&order_by_vec),
864            database,
865        );
866
867        if index_info.is_none() {
868            return true; // No index can satisfy ORDER BY
869        }
870
871        // Check if index fully covers all ORDER BY columns
872        if let Some((_index_name, sorted_cols)) = index_info {
873            if let Some(ref cols) = sorted_cols {
874                // If we have a partial match (fewer columns than ORDER BY), we need temp B-tree
875                if cols.len() < order_by.len() {
876                    return true;
877                }
878            } else {
879                // No sorted columns means we need temp B-tree
880                return true;
881            }
882        }
883
884        false
885    }
886
887    /// Generate plan node for FROM clause
888    fn explain_from_clause(
889        from: &vibesql_ast::FromClause,
890        where_clause: &Option<vibesql_ast::Expression>,
891        order_by: &Option<Vec<vibesql_ast::OrderByItem>>,
892        needed_columns: &HashSet<String>,
893        database: &Database,
894    ) -> Result<PlanNode, ExecutorError> {
895        match from {
896            vibesql_ast::FromClause::Table { name, alias, .. } => {
897                Self::explain_table_scan(
898                    name,
899                    alias.as_deref(),
900                    where_clause,
901                    order_by,
902                    needed_columns,
903                    database,
904                )
905            }
906            vibesql_ast::FromClause::Join {
907                left,
908                right,
909                join_type,
910                condition,
911                using_columns,
912                natural,
913                ..
914            } => {
915                let join_name = match join_type {
916                    vibesql_ast::JoinType::Inner => "Inner Join",
917                    vibesql_ast::JoinType::LeftOuter => "Left Outer Join",
918                    vibesql_ast::JoinType::RightOuter => "Right Outer Join",
919                    vibesql_ast::JoinType::FullOuter => "Full Outer Join",
920                    vibesql_ast::JoinType::Cross => "Cross Join",
921                    vibesql_ast::JoinType::Semi => "Semi Join",
922                    vibesql_ast::JoinType::Anti => "Anti Join",
923                };
924
925                let mut join_node = PlanNode::new(join_name);
926
927                if *natural {
928                    join_node.details.push("NATURAL join".to_string());
929                }
930
931                if condition.is_some() {
932                    join_node.details.push("Join condition: <on clause>".to_string());
933                }
934
935                if let Some(cols) = using_columns {
936                    join_node.details.push(format!("USING ({})", cols.join(", ")));
937                }
938
939                // Add left child
940                let left_child =
941                    Self::explain_from_clause(left, where_clause, order_by, needed_columns, database)?;
942                join_node.add_child(left_child);
943
944                // Add right child (no WHERE pushdown for right side in simple case)
945                let empty_cols = HashSet::new();
946                let right_child =
947                    Self::explain_from_clause(right, &None, &None, &empty_cols, database)?;
948                join_node.add_child(right_child);
949
950                Ok(join_node)
951            }
952            vibesql_ast::FromClause::Subquery { query, alias, .. } => {
953                let mut subquery_node = PlanNode::new("Subquery");
954                subquery_node.object = Some(format!("AS {}", alias));
955
956                let child = Self::explain_select(query, database)?;
957                subquery_node.add_child(child);
958
959                Ok(subquery_node)
960            }
961            vibesql_ast::FromClause::Values { rows, alias, column_aliases } => {
962                let mut values_node = PlanNode::new("Values");
963                values_node.object = Some(format!("AS {}", alias));
964
965                values_node.details.push(format!("{} row(s)", rows.len()));
966
967                if let Some(aliases) = column_aliases {
968                    values_node.details.push(format!("Columns: {}", aliases.join(", ")));
969                }
970
971                Ok(values_node)
972            }
973        }
974    }
975
976    /// Generate plan node for table scan (sequential or index)
977    fn explain_table_scan(
978        table_name: &str,
979        alias: Option<&str>,
980        where_clause: &Option<vibesql_ast::Expression>,
981        order_by: &Option<Vec<vibesql_ast::OrderByItem>>,
982        needed_columns: &HashSet<String>,
983        database: &Database,
984    ) -> Result<PlanNode, ExecutorError> {
985        // First check for regular index scan
986        let index_info = cost_based_index_selection(
987            table_name,
988            where_clause.as_ref(),
989            order_by.as_ref().map(|v| v.as_slice()),
990            database,
991        );
992
993        // If no regular index scan, check for skip-scan optimization
994        let skip_scan_plan = if index_info.is_none() {
995            if let Some(where_expr) = where_clause {
996                let planner = IndexPlanner::new(database);
997                planner.plan_skip_scan(table_name, where_expr)
998            } else {
999                None
1000            }
1001        } else {
1002            None
1003        };
1004
1005        // Check if we're using a primary key lookup
1006        let is_pk_lookup = Self::is_primary_key_lookup(table_name, where_clause, database);
1007
1008        let mut node = if let Some(skip_plan) = skip_scan_plan {
1009            // Skip-scan detected - display skip-scan specific information
1010            let skip_info = skip_plan.skip_scan_info.as_ref().unwrap();
1011
1012            let mut skip_node = PlanNode::new("Skip Scan")
1013                .with_object(table_name)
1014                .with_scan_type(ScanType::Search)
1015                .with_index_name(&skip_plan.index_name);
1016            skip_node.details.push(format!("USING INDEX {} ", skip_plan.index_name));
1017            skip_node.details.push(format!(
1018                "Skip columns: {} (cardinality: {})",
1019                skip_info.prefix_columns.join(", "),
1020                skip_info.prefix_cardinality
1021            ));
1022            skip_node.details.push(format!("Filter column: {}", skip_info.filter_column));
1023            skip_node.details.push(format!("Estimated cost: {:.2}", skip_info.estimated_cost));
1024
1025            // Add filter column predicate for SQLite EQP
1026            skip_node = skip_node.with_index_predicate(&skip_info.filter_column, "=");
1027
1028            skip_node
1029        } else if let Some((index_name, sorted_cols)) = index_info {
1030            // Check if this is a covering index (all needed columns are in the index)
1031            // A covering index requires:
1032            // 1. All SELECT columns are in the index
1033            // 2. All WHERE clause columns are also in the index (or evaluable from index)
1034            //
1035            // NOTE: If needed_columns is empty, it means SELECT * was used (wildcard),
1036            // which requires ALL table columns - this can never be a covering index
1037            // unless the index literally contains all columns.
1038            let is_covering = if needed_columns.is_empty() {
1039                // Wildcard case: need all table columns, cannot be a covering index
1040                false
1041            } else {
1042                let mut all_needed_columns = needed_columns.clone();
1043                if let Some(where_expr) = where_clause {
1044                    collect_column_refs(where_expr, &mut all_needed_columns);
1045                }
1046                is_covering_index(&index_name, &all_needed_columns, database)
1047            };
1048
1049            // Check if we have any predicates on the index (determines SCAN vs SEARCH)
1050            let has_index_predicates = if let Some(where_expr) = where_clause {
1051                let predicates = extract_index_predicates(where_expr, &index_name, database);
1052                !predicates.is_empty()
1053            } else {
1054                false
1055            };
1056
1057            // Determine scan type:
1058            // - If we have predicates → SEARCH (or COVERING SEARCH)
1059            // - If no predicates but using index for ordering → SCAN USING INDEX
1060            let scan_type = if is_pk_lookup {
1061                ScanType::IntegerPrimaryKey
1062            } else if has_index_predicates {
1063                if is_covering {
1064                    ScanType::CoveringIndex
1065                } else {
1066                    ScanType::Search
1067                }
1068            } else {
1069                // No predicates - this is a SCAN using index for ordering
1070                ScanType::Scan
1071            };
1072
1073            let mut idx_node = PlanNode::new("Index Scan")
1074                .with_object(table_name)
1075                .with_scan_type(scan_type.clone())
1076                .with_index_name(&index_name);
1077            idx_node.details.push(format!("USING INDEX {} ", index_name));
1078
1079            // Extract predicates from WHERE clause for SQLite EQP format
1080            if let Some(where_expr) = where_clause {
1081                let predicates = extract_index_predicates(where_expr, &index_name, database);
1082                for (col, op) in predicates {
1083                    idx_node = idx_node.with_index_predicate(&col, &op);
1084                }
1085            }
1086
1087            if let Some(cols) = sorted_cols {
1088                let col_strs: Vec<String> = cols
1089                    .iter()
1090                    .map(|(col, dir)| {
1091                        format!(
1092                            "{} {}",
1093                            col,
1094                            match dir {
1095                                vibesql_ast::OrderDirection::Asc => "ASC",
1096                                vibesql_ast::OrderDirection::Desc => "DESC",
1097                            }
1098                        )
1099                    })
1100                    .collect();
1101                idx_node.details.push(format!("Sorted by: {}", col_strs.join(", ")));
1102            }
1103
1104            idx_node
1105        } else {
1106            PlanNode::new("Seq Scan").with_object(table_name).with_scan_type(ScanType::Scan)
1107        };
1108
1109        // Add alias if present
1110        if let Some(a) = alias {
1111            node.details.push(format!("Alias: {}", a));
1112        }
1113
1114        // Add row estimate if table exists
1115        if let Some(table) = database.get_table(table_name) {
1116            let row_count = table.row_count();
1117            node = node.with_estimated_rows(row_count as f64);
1118        }
1119
1120        Ok(node)
1121    }
1122
1123    /// Check if this is a primary key lookup (INTEGER PRIMARY KEY in SQLite)
1124    fn is_primary_key_lookup(
1125        table_name: &str,
1126        where_clause: &Option<Expression>,
1127        database: &Database,
1128    ) -> bool {
1129        let Some(table) = database.get_table(table_name) else {
1130            return false;
1131        };
1132
1133        let Some(pk_columns) = table.schema.primary_key.as_ref() else {
1134            return false;
1135        };
1136
1137        // Only consider single-column integer primary keys
1138        if pk_columns.len() != 1 {
1139            return false;
1140        }
1141
1142        let pk_col = &pk_columns[0];
1143        let pk_col_lower = pk_col.to_lowercase();
1144
1145        // Check if WHERE clause references the primary key column
1146        if let Some(where_expr) = where_clause {
1147            return expression_references_column(where_expr, &pk_col_lower);
1148        }
1149
1150        false
1151    }
1152}
1153
1154/// Check if an expression references a specific column (case-insensitive)
1155fn expression_references_column(expr: &Expression, column: &str) -> bool {
1156    match expr {
1157        Expression::ColumnRef(col_id) => col_id.column_canonical().to_lowercase() == column,
1158        Expression::BinaryOp { left, right, .. } => {
1159            expression_references_column(left, column)
1160                || expression_references_column(right, column)
1161        }
1162        Expression::Conjunction(exprs) | Expression::Disjunction(exprs) => {
1163            exprs.iter().any(|e| expression_references_column(e, column))
1164        }
1165        Expression::UnaryOp { expr: inner, .. } => expression_references_column(inner, column),
1166        Expression::IsNull { expr: inner, .. } => expression_references_column(inner, column),
1167        Expression::Between { expr, low, high, .. } => {
1168            expression_references_column(expr, column)
1169                || expression_references_column(low, column)
1170                || expression_references_column(high, column)
1171        }
1172        Expression::InList { expr, values, .. } => {
1173            expression_references_column(expr, column)
1174                || values.iter().any(|e| expression_references_column(e, column))
1175        }
1176        _ => false,
1177    }
1178}
1179
1180/// Extract column references from a SELECT list for covering index detection
1181fn extract_select_columns(select_list: &[SelectItem]) -> HashSet<String> {
1182    let mut columns = HashSet::new();
1183    for item in select_list {
1184        match item {
1185            SelectItem::Wildcard { .. } => {
1186                // Wildcard means all columns needed - can't be a covering index
1187                // Return empty set with a special marker
1188                return HashSet::new();
1189            }
1190            SelectItem::QualifiedWildcard { .. } => {
1191                // table.* also means all columns from that table
1192                return HashSet::new();
1193            }
1194            SelectItem::Expression { expr, .. } => {
1195                collect_column_refs(expr, &mut columns);
1196            }
1197        }
1198    }
1199    columns
1200}
1201
1202/// Recursively collect column references from an expression
1203fn collect_column_refs(expr: &Expression, columns: &mut HashSet<String>) {
1204    match expr {
1205        Expression::ColumnRef(col_id) => {
1206            columns.insert(col_id.column_canonical().to_lowercase());
1207        }
1208        Expression::BinaryOp { left, right, .. } => {
1209            collect_column_refs(left, columns);
1210            collect_column_refs(right, columns);
1211        }
1212        Expression::UnaryOp { expr: inner, .. } => {
1213            collect_column_refs(inner, columns);
1214        }
1215        Expression::Conjunction(exprs) | Expression::Disjunction(exprs) => {
1216            for e in exprs {
1217                collect_column_refs(e, columns);
1218            }
1219        }
1220        Expression::Function { args, .. } | Expression::AggregateFunction { args, .. } => {
1221            for arg in args {
1222                collect_column_refs(arg, columns);
1223            }
1224        }
1225        Expression::IsNull { expr: inner, .. } => {
1226            collect_column_refs(inner, columns);
1227        }
1228        Expression::Between { expr, low, high, .. } => {
1229            collect_column_refs(expr, columns);
1230            collect_column_refs(low, columns);
1231            collect_column_refs(high, columns);
1232        }
1233        Expression::InList { expr, values, .. } => {
1234            collect_column_refs(expr, columns);
1235            for v in values {
1236                collect_column_refs(v, columns);
1237            }
1238        }
1239        Expression::Case { operand, when_clauses, else_result, .. } => {
1240            if let Some(op) = operand {
1241                collect_column_refs(op, columns);
1242            }
1243            for case_when in when_clauses {
1244                for cond in &case_when.conditions {
1245                    collect_column_refs(cond, columns);
1246                }
1247                collect_column_refs(&case_when.result, columns);
1248            }
1249            if let Some(else_res) = else_result {
1250                collect_column_refs(else_res, columns);
1251            }
1252        }
1253        Expression::Cast { expr: inner, .. } => {
1254            collect_column_refs(inner, columns);
1255        }
1256        Expression::ScalarSubquery(_)
1257        | Expression::In { .. }
1258        | Expression::Literal(_)
1259        | Expression::Placeholder(_)
1260        | Expression::NumberedPlaceholder(_)
1261        | Expression::NamedPlaceholder(_)
1262        | Expression::Wildcard => {}
1263        _ => {}  // Handle any other variants
1264    }
1265}
1266
1267/// Check if all needed columns are covered by an index (covering index)
1268fn is_covering_index(
1269    index_name: &str,
1270    needed_columns: &HashSet<String>,
1271    database: &Database,
1272) -> bool {
1273    // If we need all columns (wildcard was used), not a covering index
1274    if needed_columns.is_empty() {
1275        return false;
1276    }
1277
1278    let Some(index) = database.get_index(index_name) else {
1279        return false;
1280    };
1281
1282    // Get index columns - only include column indexes, not expression indexes
1283    // Expression indexes cannot directly cover columns for covering index detection
1284    let index_columns: HashSet<String> = index
1285        .columns
1286        .iter()
1287        .filter_map(|c| c.column_name())
1288        .map(|name| name.to_lowercase())
1289        .collect();
1290
1291    // Check if all needed columns are in the index
1292    needed_columns.iter().all(|col| index_columns.contains(col))
1293}
1294
1295/// Extract index predicates from a WHERE clause for SQLite EQP format
1296///
1297/// Returns a list of (column_name, operator) tuples for predicates that can
1298/// use the index. The operator is one of "=", ">", "<", ">=", "<=".
1299/// Predicates are sorted by their position in the index column order.
1300/// For expression indexes, the expression name is used (e.g., "lower(name)").
1301fn extract_index_predicates(
1302    expr: &Expression,
1303    index_name: &str,
1304    database: &Database,
1305) -> Vec<(String, String)> {
1306    use vibesql_ast::pretty_print::ToSql;
1307
1308    let mut predicates = Vec::new();
1309
1310    // Get the index columns - for column indexes, use the column name
1311    // For expression indexes, use the expression as a string for display
1312    let index_columns: Vec<String> = database
1313        .get_index(index_name)
1314        .map(|idx| {
1315            idx.columns
1316                .iter()
1317                .map(|c| {
1318                    if let Some(name) = c.column_name() {
1319                        name.to_lowercase()
1320                    } else if let Some(expr) = c.get_expression() {
1321                        // For expression indexes, use the expression string
1322                        expr.to_sql().to_lowercase()
1323                    } else {
1324                        String::new()
1325                    }
1326                })
1327                .collect()
1328        })
1329        .unwrap_or_default();
1330
1331    extract_predicates_recursive(expr, &index_columns, &mut predicates);
1332
1333    // Sort predicates by their position in the index column order
1334    predicates.sort_by_key(|(col, _)| {
1335        let col_lower = col.to_lowercase();
1336        index_columns.iter().position(|c| c == &col_lower).unwrap_or(usize::MAX)
1337    });
1338
1339    predicates
1340}
1341
1342fn extract_predicates_recursive(
1343    expr: &Expression,
1344    index_columns: &[String],
1345    predicates: &mut Vec<(String, String)>,
1346) {
1347    use vibesql_ast::pretty_print::ToSql;
1348    use vibesql_ast::BinaryOperator;
1349
1350    /// Check if an expression matches any index column (column ref or expression index)
1351    fn expr_matches_index(expr: &Expression, index_columns: &[String]) -> Option<String> {
1352        match expr {
1353            Expression::ColumnRef(col_id) => {
1354                let col_name = col_id.column_canonical().to_lowercase();
1355                if index_columns.iter().any(|c| c == &col_name) {
1356                    Some(col_id.column_canonical().to_string())
1357                } else {
1358                    None
1359                }
1360            }
1361            // Check if the expression matches an expression index
1362            Expression::Function { .. } | Expression::BinaryOp { .. } => {
1363                let expr_str = expr.to_sql().to_lowercase();
1364                if index_columns.iter().any(|c| c == &expr_str) {
1365                    Some(expr.to_sql())
1366                } else {
1367                    None
1368                }
1369            }
1370            _ => None,
1371        }
1372    }
1373
1374    match expr {
1375        Expression::BinaryOp { op, left, right } => {
1376            // Check if this is a comparison operator
1377            let op_str = match op {
1378                BinaryOperator::Equal => Some("="),
1379                BinaryOperator::NotEqual => None, // Don't use for index
1380                BinaryOperator::LessThan => Some("<"),
1381                BinaryOperator::LessThanOrEqual => Some("<="),
1382                BinaryOperator::GreaterThan => Some(">"),
1383                BinaryOperator::GreaterThanOrEqual => Some(">="),
1384                BinaryOperator::And => {
1385                    // Recurse for AND (but we typically have Conjunction)
1386                    extract_predicates_recursive(left, index_columns, predicates);
1387                    extract_predicates_recursive(right, index_columns, predicates);
1388                    return;
1389                }
1390                _ => None,
1391            };
1392
1393            if let Some(op_str) = op_str {
1394                // Check if left side matches an index column or expression index
1395                if let Some(expr_name) = expr_matches_index(left, index_columns) {
1396                    predicates.push((expr_name, op_str.to_string()));
1397                }
1398                // Check if right side matches (for reversed comparisons)
1399                else if let Some(expr_name) = expr_matches_index(right, index_columns) {
1400                    // Reverse the operator for column on right side
1401                    let reversed_op = match op {
1402                        BinaryOperator::Equal => "=",
1403                        BinaryOperator::LessThan => ">",
1404                        BinaryOperator::LessThanOrEqual => ">=",
1405                        BinaryOperator::GreaterThan => "<",
1406                        BinaryOperator::GreaterThanOrEqual => "<=",
1407                        _ => return,
1408                    };
1409                    predicates.push((expr_name, reversed_op.to_string()));
1410                }
1411            }
1412        }
1413        Expression::Conjunction(exprs) => {
1414            for e in exprs {
1415                extract_predicates_recursive(e, index_columns, predicates);
1416            }
1417        }
1418        Expression::Between { expr, negated: false, .. } => {
1419            // BETWEEN is equivalent to >= AND <=
1420            if let Expression::ColumnRef(col_id) = expr.as_ref() {
1421                let col_name = col_id.column_canonical().to_lowercase();
1422                if index_columns.iter().any(|c| c == &col_name) {
1423                    let col = col_id.column_canonical().to_string();
1424                    predicates.push((col.clone(), ">=".to_string()));
1425                    predicates.push((col, "<=".to_string()));
1426                }
1427            }
1428        }
1429        Expression::InList { expr, negated: false, .. } => {
1430            // IN list treated as equality
1431            if let Expression::ColumnRef(col_id) = expr.as_ref() {
1432                let col_name = col_id.column_canonical().to_lowercase();
1433                if index_columns.iter().any(|c| c == &col_name) {
1434                    predicates.push((col_id.column_canonical().to_string(), "=".to_string()));
1435                }
1436            }
1437        }
1438        // IS (NULL-safe equals): negated=true means "IS NOT DISTINCT FROM" = "IS"
1439        // Displayed as = in EQP output
1440        Expression::IsDistinctFrom { left, right, negated: true } => {
1441            // Check if left side is a column reference that matches index columns
1442            if let Expression::ColumnRef(col_id) = left.as_ref() {
1443                let col_name = col_id.column_canonical().to_lowercase();
1444                if index_columns.iter().any(|c| c == &col_name) {
1445                    predicates.push((col_id.column_canonical().to_string(), "=".to_string()));
1446                }
1447            }
1448            // Check if right side is a column reference
1449            else if let Expression::ColumnRef(col_id) = right.as_ref() {
1450                let col_name = col_id.column_canonical().to_lowercase();
1451                if index_columns.iter().any(|c| c == &col_name) {
1452                    predicates.push((col_id.column_canonical().to_string(), "=".to_string()));
1453                }
1454            }
1455        }
1456        _ => {}
1457    }
1458}
vibesql_executor/explain.rs

vibesql_executor/
explain.rs
