mdql_core/
executor.rs

1//! Unified SQL execution — single entry point for CLI, REPL, and web server.
2
3use std::path::Path;
4
5use crate::api::Table;
6use crate::cascade;
7use crate::database::{ViewDef, is_database_dir, load_database_config, save_database_config};
8use crate::errors::{MdqlError, ValidationError};
9use crate::model::Row;
10use crate::query_ast::*;
11use crate::query_engine::{execute_join_query, execute_query};
12use crate::query_parser::{Statement, parse_query};
13use crate::schema::Schema;
14
15#[derive(Debug)]
16pub enum QueryResult {
17    Rows { rows: Vec<Row>, columns: Vec<String> },
18    Message(String),
19}
20
21pub fn execute(path: &Path, sql: &str) -> crate::errors::Result<(QueryResult, Vec<ValidationError>)> {
22    let stmt = parse_query(sql)?;
23    let is_db = is_database_dir(path);
24
25    match stmt {
26        Statement::Select(ref q) => {
27            let has_ctes = !q.ctes.is_empty();
28            let has_subqueries = query_has_subqueries(q);
29            let needs_db = has_ctes || has_subqueries || q.subquery.is_some() || !q.joins.is_empty() || is_db;
30
31            if has_ctes && !is_db {
32                return Err(MdqlError::QueryExecution(
33                    "CTEs (WITH) require a database directory".into(),
34                ));
35            }
36
37            if needs_db {
38                let (_config, mut tables, errors) = crate::loader::load_database(path)?;
39
40                for cte in &q.ctes {
41                    let (rows, cols) = materialize_cte(&cte.query, &tables)?;
42                    let schema = crate::loader::build_view_schema(&cte.name, &cols, &rows);
43                    tables.insert(cte.name.clone(), (schema, rows));
44                }
45
46                let mut q = q.clone();
47                if has_subqueries {
48                    materialize_subqueries(&mut q, &tables)?;
49                }
50
51                let (rows, cols) = if let Some(ref sub) = q.subquery {
52                    let source_table = &sub.table;
53                    let (schema, table_rows) = tables.get(source_table).ok_or_else(|| {
54                        MdqlError::QueryExecution(format!(
55                            "table '{}' not found in database",
56                            source_table
57                        ))
58                    })?;
59                    execute_query(&q, table_rows, schema)?
60                } else if !q.joins.is_empty() {
61                    execute_join_query(&q, &tables)?
62                } else {
63                    let (schema, rows) = tables.get(&q.table).ok_or_else(|| {
64                        MdqlError::QueryExecution(format!(
65                            "table '{}' not found in database",
66                            q.table
67                        ))
68                    })?;
69                    execute_query(&q, rows, schema)?
70                };
71                Ok((QueryResult::Rows { rows, columns: cols }, errors))
72            } else {
73                let (schema, rows, errors) = crate::loader::load_table(path)?;
74                let (rows, cols) = execute_query(q, &rows, &schema)?;
75                Ok((QueryResult::Rows { rows, columns: cols }, errors))
76            }
77        }
78        Statement::CreateView(ref cv) => {
79            if !is_db {
80                return Err(MdqlError::QueryExecution(
81                    "CREATE VIEW requires a database directory".into(),
82                ));
83            }
84            let mut config = load_database_config(path)?;
85
86            let (_config_check, tables, _errors) = crate::loader::load_database(path)?;
87            if tables.contains_key(&cv.view_name) {
88                return Err(MdqlError::QueryExecution(format!(
89                    "Name '{}' already exists as a table or view",
90                    cv.view_name
91                )));
92            }
93
94            if config.views.iter().any(|v| v.name == cv.view_name) {
95                return Err(MdqlError::QueryExecution(format!(
96                    "View '{}' already exists",
97                    cv.view_name
98                )));
99            }
100
101            let query_str = extract_view_query(sql)?;
102
103            let view_def = ViewDef {
104                name: cv.view_name.clone(),
105                query: query_str,
106            };
107
108            let test_result = crate::loader::load_database(path);
109            if let Ok((_cfg, test_tables, _errs)) = test_result {
110                let test_view = ViewDef {
111                    name: view_def.name.clone(),
112                    query: view_def.query.clone(),
113                };
114                if let Err(e) = super::loader::materialize_view(&test_view, &test_tables) {
115                    return Err(MdqlError::QueryExecution(format!(
116                        "View query failed validation: {}",
117                        e
118                    )));
119                }
120            }
121
122            config.views.push(view_def);
123            save_database_config(path, &config)?;
124            Ok((
125                QueryResult::Message(format!("View '{}' created", cv.view_name)),
126                vec![],
127            ))
128        }
129        Statement::DropView(ref dv) => {
130            if !is_db {
131                return Err(MdqlError::QueryExecution(
132                    "DROP VIEW requires a database directory".into(),
133                ));
134            }
135            let mut config = load_database_config(path)?;
136            let len_before = config.views.len();
137            config.views.retain(|v| v.name != dv.view_name);
138            if config.views.len() == len_before {
139                return Err(MdqlError::QueryExecution(format!(
140                    "View '{}' does not exist",
141                    dv.view_name
142                )));
143            }
144            save_database_config(path, &config)?;
145            Ok((
146                QueryResult::Message(format!("View '{}' dropped", dv.view_name)),
147                vec![],
148            ))
149        }
150        Statement::Delete(ref dq) if dq.mode != DeleteMode::Default => {
151            if !is_db {
152                return Err(MdqlError::QueryExecution(
153                    "CASCADE/RESTRICT requires a database directory".into(),
154                ));
155            }
156            let config = load_database_config(path)?;
157            if config.views.iter().any(|v| v.name == dq.table) {
158                return Err(MdqlError::QueryExecution(format!(
159                    "Cannot write to view '{}' — views are read-only",
160                    dq.table
161                )));
162            }
163            let (_cfg, tables, errors) = crate::loader::load_database(path)?;
164            let (_, rows) = tables.get(&dq.table).ok_or_else(|| {
165                MdqlError::QueryExecution(format!("table '{}' not found in database", dq.table))
166            })?;
167            let matched_filenames: Vec<String> = if let Some(ref wc) = dq.where_clause {
168                rows.iter()
169                    .filter(|r| crate::query_engine::evaluate(wc, r))
170                    .filter_map(|r| r.get("path").and_then(|v| v.as_str()).map(|s| s.to_string()))
171                    .collect()
172            } else {
173                rows.iter()
174                    .filter_map(|r| r.get("path").and_then(|v| v.as_str()).map(|s| s.to_string()))
175                    .collect()
176            };
177
178            match dq.mode {
179                DeleteMode::Cascade => {
180                    let plan = cascade::build_cascade_plan(
181                        &dq.table, &matched_filenames, &config, &tables,
182                    );
183                    let msg = cascade::execute_cascade_plan(&plan, path)?;
184                    Ok((QueryResult::Message(msg), errors))
185                }
186                DeleteMode::Restrict => {
187                    let plan = cascade::build_restrict_plan(
188                        &dq.table, &matched_filenames, &config, &tables,
189                    );
190                    if !plan.restrict_violations.is_empty() {
191                        let violations = plan.restrict_violations.join("\n  ");
192                        return Err(MdqlError::QueryExecution(format!(
193                            "RESTRICT: cannot delete — {} dependent references:\n  {}",
194                            plan.restrict_violations.len(),
195                            violations,
196                        )));
197                    }
198                    let table_path = path.join(&dq.table);
199                    let table = Table::new(&table_path)?;
200                    let msg = table.exec_delete_matched(&matched_filenames)?;
201                    Ok((QueryResult::Message(msg), errors))
202                }
203                DeleteMode::Default => unreachable!(),
204            }
205        }
206        ref stmt @ (Statement::Insert(_)
207        | Statement::Update(_)
208        | Statement::Delete(_)
209        | Statement::AlterRename(_)
210        | Statement::AlterDrop(_)
211        | Statement::AlterMerge(_)) => {
212            if is_db {
213                let config = load_database_config(path)?;
214                let target = stmt.table_name();
215                if config.views.iter().any(|v| v.name == target) {
216                    return Err(MdqlError::QueryExecution(format!(
217                        "Cannot write to view '{}' — views are read-only",
218                        target
219                    )));
220                }
221            }
222            let table_path = if is_db {
223                path.join(stmt.table_name())
224            } else {
225                path.to_path_buf()
226            };
227            let mut table = Table::new(&table_path)?;
228            let msg = table.execute_sql(sql)?;
229            Ok((QueryResult::Message(msg), vec![]))
230        }
231    }
232}
233
234pub fn materialize_cte(
235    query: &crate::query_ast::SelectQuery,
236    tables: &std::collections::HashMap<String, (crate::schema::Schema, Vec<Row>)>,
237) -> crate::errors::Result<(Vec<Row>, Vec<String>)> {
238    if let Some(ref sub) = query.subquery {
239        let (_, sub_rows) = tables.get(&sub.table).ok_or_else(|| {
240            MdqlError::QueryExecution(format!("table '{}' not found in database", sub.table))
241        })?;
242        let (sub_rows, _) = execute_query(sub, sub_rows, &tables.get(&sub.table).unwrap().0)?;
243        execute_query(query, &sub_rows, &tables.get(&sub.table).unwrap().0)
244    } else if !query.joins.is_empty() {
245        execute_join_query(query, tables)
246    } else {
247        let (schema, rows) = tables.get(&query.table).ok_or_else(|| {
248            MdqlError::QueryExecution(format!("table '{}' not found in database", query.table))
249        })?;
250        execute_query(query, rows, schema)
251    }
252}
253
254type Tables = std::collections::HashMap<String, (Schema, Vec<Row>)>;
255
256fn query_has_subqueries(q: &SelectQuery) -> bool {
257    if let Some(ref wc) = q.where_clause {
258        if where_has_subquery(wc) { return true; }
259    }
260    if let ColumnList::Named(ref exprs) = q.columns {
261        for se in exprs {
262            match se {
263                SelectExpr::Expr { expr, .. } => {
264                    if expr_has_subquery(expr) { return true; }
265                }
266                SelectExpr::Aggregate { arg_expr: Some(e), .. } => {
267                    if expr_has_subquery(e) { return true; }
268                }
269                _ => {}
270            }
271        }
272    }
273    false
274}
275
276fn where_has_subquery(wc: &WhereClause) -> bool {
277    match wc {
278        WhereClause::BoolOp(bop) => where_has_subquery(&bop.left) || where_has_subquery(&bop.right),
279        WhereClause::Comparison(cmp) => {
280            cmp.left_expr.as_ref().map_or(false, |e| expr_has_subquery(e))
281                || cmp.right_expr.as_ref().map_or(false, |e| expr_has_subquery(e))
282        }
283    }
284}
285
286fn expr_has_subquery(expr: &Expr) -> bool {
287    match expr {
288        Expr::Subquery(_) => true,
289        Expr::BinaryOp { left, right, .. } => expr_has_subquery(left) || expr_has_subquery(right),
290        Expr::UnaryMinus(inner) => expr_has_subquery(inner),
291        Expr::Case { whens, else_expr } => {
292            whens.iter().any(|(c, e)| where_has_subquery(c) || expr_has_subquery(e))
293                || else_expr.as_ref().map_or(false, |e| expr_has_subquery(e))
294        }
295        _ => false,
296    }
297}
298
299pub fn materialize_subqueries(
300    query: &mut SelectQuery,
301    tables: &Tables,
302) -> crate::errors::Result<()> {
303    if let Some(ref mut wc) = query.where_clause {
304        materialize_in_where(wc, tables)?;
305    }
306    if let ColumnList::Named(ref mut exprs) = query.columns {
307        for se in exprs.iter_mut() {
308            match se {
309                SelectExpr::Expr { ref mut expr, .. } => {
310                    materialize_in_expr(expr, tables)?;
311                }
312                SelectExpr::Aggregate { ref mut arg_expr, .. } => {
313                    if let Some(ref mut e) = arg_expr {
314                        materialize_in_expr(e, tables)?;
315                    }
316                }
317                _ => {}
318            }
319        }
320    }
321    Ok(())
322}
323
324fn materialize_in_where(wc: &mut WhereClause, tables: &Tables) -> crate::errors::Result<()> {
325    match wc {
326        WhereClause::BoolOp(ref mut bop) => {
327            materialize_in_where(&mut bop.left, tables)?;
328            materialize_in_where(&mut bop.right, tables)?;
329        }
330        WhereClause::Comparison(ref mut cmp) => {
331            if let Some(ref mut expr) = cmp.left_expr {
332                materialize_in_expr(expr, tables)?;
333            }
334            if let Some(ref mut expr) = cmp.right_expr {
335                if let Expr::Subquery(ref sq) = expr {
336                    let (rows, _cols) = materialize_cte(sq, tables)?;
337                    if cmp.op == CmpOp::In {
338                        let values: Vec<SqlValue> = rows.iter()
339                            .filter_map(|r| r.values().next())
340                            .map(|v| value_to_sql_value(v))
341                            .collect();
342                        cmp.value = Some(SqlValue::List(values.clone()));
343                        cmp.right_expr = None;
344                    } else {
345                        let val = rows.first()
346                            .and_then(|r| r.values().next())
347                            .map(|v| value_to_sql_value(v))
348                            .unwrap_or(SqlValue::Null);
349                        *expr = Expr::Literal(val);
350                    }
351                } else {
352                    materialize_in_expr(expr, tables)?;
353                }
354            }
355        }
356    }
357    Ok(())
358}
359
360fn materialize_in_expr(expr: &mut Expr, tables: &Tables) -> crate::errors::Result<()> {
361    match expr {
362        Expr::Subquery(ref sq) => {
363            let (rows, _cols) = materialize_cte(sq, tables)?;
364            let val = rows.first()
365                .and_then(|r| r.values().next())
366                .map(|v| value_to_sql_value(v))
367                .unwrap_or(SqlValue::Null);
368            *expr = Expr::Literal(val);
369        }
370        Expr::BinaryOp { ref mut left, ref mut right, .. } => {
371            materialize_in_expr(left, tables)?;
372            materialize_in_expr(right, tables)?;
373        }
374        Expr::UnaryMinus(ref mut inner) => {
375            materialize_in_expr(inner, tables)?;
376        }
377        Expr::Case { ref mut whens, ref mut else_expr } => {
378            for (ref mut cond, ref mut result) in whens.iter_mut() {
379                materialize_in_where(cond, tables)?;
380                materialize_in_expr(result, tables)?;
381            }
382            if let Some(ref mut e) = else_expr {
383                materialize_in_expr(e, tables)?;
384            }
385        }
386        _ => {}
387    }
388    Ok(())
389}
390
391fn value_to_sql_value(v: &crate::model::Value) -> SqlValue {
392    use crate::model::Value;
393    match v {
394        Value::String(s) => SqlValue::String(s.clone()),
395        Value::Int(n) => SqlValue::Int(*n),
396        Value::Float(f) => SqlValue::Float(*f),
397        Value::Bool(b) => SqlValue::Int(if *b { 1 } else { 0 }),
398        // Dates/datetimes round-trip through their canonical ISO string, which
399        // the comparison engine coerces back to a date when matched against a
400        // date column. Without this a subquery over a date column (e.g.
401        // `WHERE modified IN (SELECT MAX(modified) ...)`) collapsed to NULL.
402        Value::Date(_) | Value::DateTime(_) => SqlValue::String(v.to_display_string()),
403        Value::Null | Value::List(_) | Value::Dict(_) => SqlValue::Null,
404    }
405}
406
407fn extract_view_query(sql: &str) -> crate::errors::Result<String> {
408    let upper = sql.to_uppercase();
409    let as_keyword = upper.find(" AS ");
410    if let Some(pos) = as_keyword {
411        let after = &sql[pos + 4..];
412        let trimmed = after.trim_start();
413        let trimmed_upper = trimmed.to_uppercase();
414        if trimmed_upper.starts_with("SELECT") {
415            return Ok(trimmed.to_string());
416        }
417    }
418    // Fallback: scan for any whitespace-surrounded AS that precedes SELECT
419    let bytes = upper.as_bytes();
420    let mut i = 0;
421    while i + 4 < bytes.len() {
422        if bytes[i].is_ascii_whitespace()
423            && bytes[i + 1] == b'A'
424            && bytes[i + 2] == b'S'
425            && bytes[i + 3].is_ascii_whitespace()
426        {
427            let after = &sql[i + 3..];
428            let trimmed = after.trim_start();
429            let trimmed_upper = trimmed.to_uppercase();
430            if trimmed_upper.starts_with("SELECT") {
431                return Ok(trimmed.to_string());
432            }
433        }
434        i += 1;
435    }
436    Err(crate::errors::MdqlError::QueryExecution(
437        "CREATE VIEW must contain AS clause followed by SELECT".into(),
438    ))
439}
440
441#[cfg(test)]
442mod tests {
443    use super::*;
444    use crate::model::Value;
445    use std::fs;
446
447    fn make_test_db() -> tempfile::TempDir {
448        let dir = tempfile::tempdir().unwrap();
449
450        // Database-level _mdql.md
451        fs::write(
452            dir.path().join("_mdql.md"),
453            "---\ntype: database\nname: testdb\n---\n",
454        )
455        .unwrap();
456
457        // Table: strategies
458        let strats = dir.path().join("strategies");
459        fs::create_dir(&strats).unwrap();
460        fs::write(
461            strats.join("_mdql.md"),
462            "---\ntype: schema\ntable: strategies\nprimary_key: path\nfrontmatter:\n  title:\n    type: string\n  status:\n    type: string\n---\n",
463        )
464        .unwrap();
465        fs::write(
466            strats.join("alpha.md"),
467            "---\ntitle: Alpha\nstatus: LIVE\n---\n# Alpha\n",
468        )
469        .unwrap();
470        fs::write(
471            strats.join("beta.md"),
472            "---\ntitle: Beta\nstatus: DRAFT\n---\n# Beta\n",
473        )
474        .unwrap();
475
476        dir
477    }
478
479    #[test]
480    fn test_create_and_query_view() {
481        let dir = make_test_db();
482        let (result, _) = execute(
483            dir.path(),
484            "CREATE VIEW live AS SELECT * FROM strategies WHERE status = 'LIVE'",
485        )
486        .unwrap();
487        assert!(matches!(result, QueryResult::Message(ref m) if m.contains("created")));
488
489        let (result, _) = execute(dir.path(), "SELECT * FROM live").unwrap();
490        if let QueryResult::Rows { rows, columns } = result {
491            assert_eq!(rows.len(), 1);
492            assert!(columns.contains(&"title".to_string()));
493        } else {
494            panic!("Expected Rows");
495        }
496    }
497
498    #[test]
499    fn test_drop_view() {
500        let dir = make_test_db();
501        execute(
502            dir.path(),
503            "CREATE VIEW live AS SELECT * FROM strategies WHERE status = 'LIVE'",
504        )
505        .unwrap();
506
507        let (result, _) = execute(dir.path(), "DROP VIEW live").unwrap();
508        assert!(matches!(result, QueryResult::Message(ref m) if m.contains("dropped")));
509
510        let err = execute(dir.path(), "SELECT * FROM live");
511        assert!(err.is_err());
512    }
513
514    #[test]
515    fn test_drop_nonexistent_view() {
516        let dir = make_test_db();
517        let err = execute(dir.path(), "DROP VIEW nonexistent");
518        assert!(err.is_err());
519        assert!(err.unwrap_err().to_string().contains("does not exist"));
520    }
521
522    #[test]
523    fn test_create_view_duplicate_name() {
524        let dir = make_test_db();
525        execute(
526            dir.path(),
527            "CREATE VIEW live AS SELECT * FROM strategies WHERE status = 'LIVE'",
528        )
529        .unwrap();
530
531        let err = execute(
532            dir.path(),
533            "CREATE VIEW live AS SELECT * FROM strategies",
534        );
535        assert!(err.is_err());
536        assert!(err.unwrap_err().to_string().contains("already exists"));
537    }
538
539    #[test]
540    fn test_create_view_conflicts_with_table() {
541        let dir = make_test_db();
542        let err = execute(
543            dir.path(),
544            "CREATE VIEW strategies AS SELECT * FROM strategies",
545        );
546        assert!(err.is_err());
547        assert!(err.unwrap_err().to_string().contains("already exists"));
548    }
549
550    #[test]
551    fn test_write_to_view_rejected() {
552        let dir = make_test_db();
553        execute(
554            dir.path(),
555            "CREATE VIEW live AS SELECT * FROM strategies WHERE status = 'LIVE'",
556        )
557        .unwrap();
558
559        let err = execute(
560            dir.path(),
561            "INSERT INTO live (title, status) VALUES ('Gamma', 'LIVE')",
562        );
563        assert!(err.is_err());
564        assert!(err.unwrap_err().to_string().contains("read-only"));
565    }
566
567    #[test]
568    fn test_create_view_not_database() {
569        let dir = tempfile::tempdir().unwrap();
570        fs::write(
571            dir.path().join("_mdql.md"),
572            "---\ntype: schema\ntable: t\nprimary_key: path\nfrontmatter:\n  x:\n    type: string\n---\n",
573        )
574        .unwrap();
575
576        let err = execute(
577            dir.path(),
578            "CREATE VIEW v AS SELECT * FROM t",
579        );
580        assert!(err.is_err());
581        assert!(err.unwrap_err().to_string().contains("database directory"));
582    }
583
584    #[test]
585    fn test_extract_view_query_basic() {
586        let q = extract_view_query("CREATE VIEW v AS SELECT * FROM t").unwrap();
587        assert!(q.starts_with("SELECT"));
588    }
589
590    #[test]
591    fn test_extract_view_query_with_column_alias() {
592        let q = extract_view_query(
593            "CREATE VIEW v AS SELECT token, SUM(size) as sell_size FROM orders GROUP BY token HAVING sell_size > 0"
594        ).unwrap();
595        assert!(q.starts_with("SELECT"));
596        assert!(q.contains("HAVING"));
597    }
598
599    #[test]
600    fn test_extract_view_query_newline_after_as() {
601        let q = extract_view_query("CREATE VIEW v AS\nSELECT * FROM t").unwrap();
602        assert!(q.starts_with("SELECT"));
603    }
604
605    #[test]
606    fn test_create_view_with_aggregate_arithmetic() {
607        let dir = make_test_db();
608        let result = execute(
609            dir.path(),
610            "CREATE VIEW v AS SELECT status, COUNT(*) - COUNT(*) as zero FROM strategies GROUP BY status",
611        );
612        assert!(result.is_ok());
613    }
614
615    // ── Issue #44: HAVING in CREATE VIEW ──
616
617    #[test]
618    fn test_create_view_with_having() {
619        let dir = make_test_db();
620        // Create a view with HAVING — both statuses have cnt=1, so HAVING cnt > 0 keeps both
621        let (result, _) = execute(
622            dir.path(),
623            "CREATE VIEW popular AS SELECT status, COUNT(*) as cnt FROM strategies GROUP BY status HAVING cnt > 0",
624        )
625        .unwrap();
626        assert!(matches!(result, QueryResult::Message(ref m) if m.contains("created")));
627
628        // Query the view to confirm it works
629        let (result, _) = execute(dir.path(), "SELECT * FROM popular").unwrap();
630        if let QueryResult::Rows { rows, columns } = result {
631            assert!(columns.contains(&"status".to_string()));
632            assert!(columns.contains(&"cnt".to_string()));
633            // Both LIVE and DRAFT have count 1, both > 0
634            assert_eq!(rows.len(), 2);
635        } else {
636            panic!("Expected Rows, got {:?}", result);
637        }
638    }
639
640    #[test]
641    fn test_extract_view_query_tab_after_as() {
642        let q = extract_view_query("CREATE VIEW v AS\tSELECT * FROM t").unwrap();
643        assert!(q.starts_with("SELECT"));
644        assert!(q.contains("FROM t"));
645    }
646
647    fn make_join_db() -> tempfile::TempDir {
648        let dir = tempfile::tempdir().unwrap();
649        fs::write(
650            dir.path().join("_mdql.md"),
651            "---\ntype: database\nname: testdb\n---\n",
652        )
653        .unwrap();
654
655        let strats = dir.path().join("strategies");
656        fs::create_dir(&strats).unwrap();
657        fs::write(
658            strats.join("_mdql.md"),
659            "---\ntype: schema\ntable: strategies\nprimary_key: path\nfrontmatter:\n  title:\n    type: string\n---\n",
660        )
661        .unwrap();
662        fs::write(strats.join("alpha.md"), "---\ntitle: Alpha\n---\n# Alpha\n").unwrap();
663        fs::write(strats.join("beta.md"), "---\ntitle: Beta\n---\n# Beta\n").unwrap();
664        fs::write(strats.join("gamma.md"), "---\ntitle: Gamma\n---\n# Gamma\n").unwrap();
665
666        let bt = dir.path().join("backtests");
667        fs::create_dir(&bt).unwrap();
668        fs::write(
669            bt.join("_mdql.md"),
670            "---\ntype: schema\ntable: backtests\nprimary_key: path\nfrontmatter:\n  strategy:\n    type: string\n  sharpe:\n    type: float\n---\n",
671        )
672        .unwrap();
673        fs::write(bt.join("bt-alpha.md"), "---\nstrategy: alpha.md\nsharpe: 1.5\n---\n# BT Alpha\n").unwrap();
674
675        dir
676    }
677
678    #[test]
679    fn test_inner_join() {
680        let dir = make_join_db();
681        let (result, _) = execute(
682            dir.path(),
683            "SELECT s.title, b.sharpe FROM strategies s JOIN backtests b ON b.strategy = s.path",
684        )
685        .unwrap();
686        if let QueryResult::Rows { rows, .. } = result {
687            assert_eq!(rows.len(), 1);
688            assert_eq!(rows[0].get("s.title").unwrap(), &Value::String("Alpha".into()));
689        } else {
690            panic!("Expected Rows");
691        }
692    }
693
694    #[test]
695    fn test_left_join() {
696        let dir = make_join_db();
697        let (result, _) = execute(
698            dir.path(),
699            "SELECT s.title, b.sharpe FROM strategies s LEFT JOIN backtests b ON b.strategy = s.path",
700        )
701        .unwrap();
702        if let QueryResult::Rows { rows, .. } = result {
703            assert_eq!(rows.len(), 3);
704            let alpha = rows.iter().find(|r| r.get("s.title") == Some(&Value::String("Alpha".into()))).unwrap();
705            assert_eq!(alpha.get("b.sharpe"), Some(&Value::Float(1.5)));
706            let beta = rows.iter().find(|r| r.get("s.title") == Some(&Value::String("Beta".into()))).unwrap();
707            assert_eq!(beta.get("b.sharpe"), Some(&Value::Null));
708        } else {
709            panic!("Expected Rows");
710        }
711    }
712
713    #[test]
714    fn test_left_join_in_view() {
715        let dir = make_join_db();
716        execute(
717            dir.path(),
718            "CREATE VIEW overview AS SELECT s.title, b.sharpe FROM strategies s LEFT JOIN backtests b ON b.strategy = s.path",
719        )
720        .unwrap();
721        let (result, _) = execute(dir.path(), "SELECT * FROM overview").unwrap();
722        if let QueryResult::Rows { rows, .. } = result {
723            assert_eq!(rows.len(), 3);
724        } else {
725            panic!("Expected Rows");
726        }
727    }
728
729    fn make_compound_join_db() -> tempfile::TempDir {
730        let dir = tempfile::tempdir().unwrap();
731        fs::write(
732            dir.path().join("_mdql.md"),
733            "---\ntype: database\nname: testdb\n---\n",
734        )
735        .unwrap();
736
737        let strats = dir.path().join("strategies");
738        fs::create_dir(&strats).unwrap();
739        fs::write(
740            strats.join("_mdql.md"),
741            "---\ntype: schema\ntable: strategies\nprimary_key: path\nfrontmatter:\n  title:\n    type: string\n---\n",
742        )
743        .unwrap();
744        fs::write(strats.join("alpha.md"), "---\ntitle: Alpha\n---\n# Alpha\n").unwrap();
745        fs::write(strats.join("beta.md"), "---\ntitle: Beta\n---\n# Beta\n").unwrap();
746
747        let bt = dir.path().join("backtests");
748        fs::create_dir(&bt).unwrap();
749        fs::write(
750            bt.join("_mdql.md"),
751            "---\ntype: schema\ntable: backtests\nprimary_key: path\nfrontmatter:\n  strategy:\n    type: string\n  mode:\n    type: string\n  sharpe:\n    type: float\n---\n",
752        )
753        .unwrap();
754        fs::write(bt.join("bt-alpha-paper.md"), "---\nstrategy: alpha.md\nmode: PAPER\nsharpe: 1.5\n---\n# BT\n").unwrap();
755        fs::write(bt.join("bt-alpha-live.md"), "---\nstrategy: alpha.md\nmode: LIVE\nsharpe: 1.2\n---\n# BT\n").unwrap();
756        fs::write(bt.join("bt-beta-paper.md"), "---\nstrategy: beta.md\nmode: PAPER\nsharpe: 0.8\n---\n# BT\n").unwrap();
757
758        dir
759    }
760
761    #[test]
762    fn test_join_compound_and() {
763        let dir = make_compound_join_db();
764        let (result, _) = execute(
765            dir.path(),
766            "SELECT s.title, b.sharpe FROM strategies s JOIN backtests b ON b.strategy = s.path AND b.mode = 'PAPER'",
767        )
768        .unwrap();
769        if let QueryResult::Rows { rows, .. } = result {
770            assert_eq!(rows.len(), 2);
771            let alpha = rows.iter().find(|r| r.get("s.title") == Some(&Value::String("Alpha".into()))).unwrap();
772            assert_eq!(alpha.get("b.sharpe"), Some(&Value::Float(1.5)));
773            let beta = rows.iter().find(|r| r.get("s.title") == Some(&Value::String("Beta".into()))).unwrap();
774            assert_eq!(beta.get("b.sharpe"), Some(&Value::Float(0.8)));
775        } else {
776            panic!("Expected Rows");
777        }
778    }
779
780    #[test]
781    fn test_left_join_compound() {
782        let dir = make_compound_join_db();
783        let (result, _) = execute(
784            dir.path(),
785            "SELECT s.title, b.sharpe FROM strategies s LEFT JOIN backtests b ON b.strategy = s.path AND b.mode = 'LIVE'",
786        )
787        .unwrap();
788        if let QueryResult::Rows { rows, .. } = result {
789            assert_eq!(rows.len(), 2);
790            let alpha = rows.iter().find(|r| r.get("s.title") == Some(&Value::String("Alpha".into()))).unwrap();
791            assert_eq!(alpha.get("b.sharpe"), Some(&Value::Float(1.2)));
792            let beta = rows.iter().find(|r| r.get("s.title") == Some(&Value::String("Beta".into()))).unwrap();
793            assert_eq!(beta.get("b.sharpe"), Some(&Value::Null));
794        } else {
795            panic!("Expected Rows");
796        }
797    }
798
799    #[test]
800    fn test_join_compound_or() {
801        // Issue #57: OR in the ON clause.
802        let dir = make_compound_join_db();
803        let (result, _) = execute(
804            dir.path(),
805            "SELECT s.title, b.sharpe FROM strategies s JOIN backtests b ON b.strategy = s.path AND (b.mode = 'PAPER' OR b.mode = 'LIVE')",
806        )
807        .unwrap();
808        if let QueryResult::Rows { rows, .. } = result {
809            // alpha has PAPER + LIVE (2), beta has PAPER (1) => 3 rows.
810            assert_eq!(rows.len(), 3);
811        } else {
812            panic!("Expected Rows");
813        }
814    }
815
816    #[test]
817    fn test_join_compound_with_comparison() {
818        let dir = make_compound_join_db();
819        let (result, _) = execute(
820            dir.path(),
821            "SELECT s.title, b.sharpe FROM strategies s JOIN backtests b ON b.strategy = s.path AND b.sharpe > 1.0",
822        )
823        .unwrap();
824        if let QueryResult::Rows { rows, .. } = result {
825            assert_eq!(rows.len(), 2);
826            assert!(rows.iter().all(|r| {
827                if let Some(Value::Float(s)) = r.get("b.sharpe") { *s > 1.0 } else { false }
828            }));
829        } else {
830            panic!("Expected Rows");
831        }
832    }
833
834    fn make_cascade_db() -> tempfile::TempDir {
835        let dir = tempfile::tempdir().unwrap();
836
837        fs::write(
838            dir.path().join("_mdql.md"),
839            "---\ntype: database\nname: testdb\nforeign_keys:\n  - from: backtests.strategy\n    to: strategies.path\n---\n",
840        )
841        .unwrap();
842
843        let strats = dir.path().join("strategies");
844        fs::create_dir(&strats).unwrap();
845        fs::write(
846            strats.join("_mdql.md"),
847            "---\ntype: schema\ntable: strategies\nprimary_key: path\nfrontmatter:\n  title:\n    type: string\n  status:\n    type: string\n---\n",
848        )
849        .unwrap();
850        fs::write(strats.join("alpha.md"), "---\ntitle: Alpha\nstatus: KILLED\n---\n# Alpha\n").unwrap();
851        fs::write(strats.join("beta.md"), "---\ntitle: Beta\nstatus: LIVE\n---\n# Beta\n").unwrap();
852
853        let bt = dir.path().join("backtests");
854        fs::create_dir(&bt).unwrap();
855        fs::write(
856            bt.join("_mdql.md"),
857            "---\ntype: schema\ntable: backtests\nprimary_key: path\nfrontmatter:\n  strategy:\n    type: string\n  sharpe:\n    type: float\n---\n",
858        )
859        .unwrap();
860        fs::write(bt.join("bt-alpha.md"), "---\nstrategy: alpha.md\nsharpe: 1.5\n---\n# BT Alpha\n").unwrap();
861        fs::write(bt.join("bt-beta.md"), "---\nstrategy: beta.md\nsharpe: 0.8\n---\n# BT Beta\n").unwrap();
862
863        dir
864    }
865
866    #[test]
867    fn test_cascade_delete() {
868        let dir = make_cascade_db();
869        let (result, _) = execute(
870            dir.path(),
871            "DELETE FROM strategies WHERE status = 'KILLED' CASCADE",
872        )
873        .unwrap();
874        if let QueryResult::Message(msg) = result {
875            assert!(msg.contains("DELETE 1"));
876            assert!(msg.contains("cascade"));
877        } else {
878            panic!("Expected Message");
879        }
880        assert!(!dir.path().join("strategies/alpha.md").exists());
881        assert!(!dir.path().join("backtests/bt-alpha.md").exists());
882        assert!(dir.path().join("strategies/beta.md").exists());
883        assert!(dir.path().join("backtests/bt-beta.md").exists());
884    }
885
886    #[test]
887    fn test_restrict_delete_blocks() {
888        let dir = make_cascade_db();
889        let err = execute(
890            dir.path(),
891            "DELETE FROM strategies WHERE status = 'KILLED' RESTRICT",
892        );
893        assert!(err.is_err());
894        let msg = err.unwrap_err().to_string();
895        assert!(msg.contains("RESTRICT"));
896        assert!(dir.path().join("strategies/alpha.md").exists());
897    }
898
899    #[test]
900    fn test_restrict_delete_allows_no_dependents() {
901        let dir = make_cascade_db();
902        fs::remove_file(dir.path().join("backtests/bt-alpha.md")).unwrap();
903
904        let (result, _) = execute(
905            dir.path(),
906            "DELETE FROM strategies WHERE status = 'KILLED' RESTRICT",
907        )
908        .unwrap();
909        if let QueryResult::Message(msg) = result {
910            assert!(msg.contains("DELETE 1"));
911        } else {
912            panic!("Expected Message");
913        }
914        assert!(!dir.path().join("strategies/alpha.md").exists());
915    }
916
917    #[test]
918    fn test_cascade_default_unchanged() {
919        let dir = make_cascade_db();
920        let (result, _) = execute(
921            dir.path(),
922            "DELETE FROM strategies WHERE status = 'KILLED'",
923        )
924        .unwrap();
925        if let QueryResult::Message(msg) = result {
926            assert!(msg.contains("DELETE 1"));
927        } else {
928            panic!("Expected Message");
929        }
930        assert!(!dir.path().join("strategies/alpha.md").exists());
931        assert!(dir.path().join("backtests/bt-alpha.md").exists());
932    }
933
934    // ── CTE (WITH) integration tests ──
935
936    #[test]
937    fn test_cte_basic() {
938        let dir = make_test_db();
939        let (result, _) = execute(
940            dir.path(),
941            "WITH live AS (SELECT * FROM strategies WHERE status = 'LIVE') SELECT * FROM live",
942        )
943        .unwrap();
944        if let QueryResult::Rows { rows, columns } = result {
945            assert_eq!(rows.len(), 1);
946            assert!(columns.contains(&"title".to_string()));
947            assert_eq!(rows[0].get("title"), Some(&Value::String("Alpha".into())));
948        } else {
949            panic!("Expected Rows");
950        }
951    }
952
953    #[test]
954    fn test_cte_with_where_on_cte() {
955        let dir = make_join_db();
956        let (result, _) = execute(
957            dir.path(),
958            "WITH bt AS (SELECT * FROM backtests WHERE sharpe > 1.0) SELECT * FROM bt",
959        )
960        .unwrap();
961        if let QueryResult::Rows { rows, .. } = result {
962            assert_eq!(rows.len(), 1);
963            assert_eq!(rows[0].get("sharpe"), Some(&Value::Float(1.5)));
964        } else {
965            panic!("Expected Rows");
966        }
967    }
968
969    #[test]
970    fn test_cte_multi_with_join() {
971        let dir = make_join_db();
972        let (result, _) = execute(
973            dir.path(),
974            "WITH s AS (SELECT * FROM strategies), b AS (SELECT * FROM backtests) SELECT s.title, b.sharpe FROM s JOIN b ON b.strategy = s.path",
975        )
976        .unwrap();
977        if let QueryResult::Rows { rows, .. } = result {
978            assert_eq!(rows.len(), 1);
979            assert_eq!(rows[0].get("s.title"), Some(&Value::String("Alpha".into())));
980        } else {
981            panic!("Expected Rows");
982        }
983    }
984
985    #[test]
986    fn test_cte_with_aggregation() {
987        let dir = make_test_db();
988        let (result, _) = execute(
989            dir.path(),
990            "WITH counts AS (SELECT status, COUNT(*) AS cnt FROM strategies GROUP BY status) SELECT * FROM counts WHERE cnt > 0",
991        )
992        .unwrap();
993        if let QueryResult::Rows { rows, columns } = result {
994            assert!(columns.contains(&"status".to_string()));
995            assert!(columns.contains(&"cnt".to_string()));
996            assert!(rows.len() >= 1);
997        } else {
998            panic!("Expected Rows");
999        }
1000    }
1001
1002    #[test]
1003    fn test_cte_chained() {
1004        let dir = make_join_db();
1005        let (result, _) = execute(
1006            dir.path(),
1007            "WITH good AS (SELECT * FROM backtests WHERE sharpe > 1.0), matched AS (SELECT s.title, g.sharpe FROM strategies s JOIN good g ON g.strategy = s.path) SELECT * FROM matched",
1008        )
1009        .unwrap();
1010        if let QueryResult::Rows { rows, .. } = result {
1011            assert_eq!(rows.len(), 1);
1012        } else {
1013            panic!("Expected Rows");
1014        }
1015    }
1016
1017    // ── Subquery integration tests ──
1018
1019    #[test]
1020    fn test_where_in_subquery() {
1021        let dir = make_join_db();
1022        let (result, _) = execute(
1023            dir.path(),
1024            "SELECT * FROM strategies WHERE path IN (SELECT strategy FROM backtests)",
1025        )
1026        .unwrap();
1027        if let QueryResult::Rows { rows, .. } = result {
1028            assert_eq!(rows.len(), 1);
1029            assert_eq!(rows[0].get("title"), Some(&Value::String("Alpha".into())));
1030        } else {
1031            panic!("Expected Rows");
1032        }
1033    }
1034
1035    fn make_dated_bt_db() -> tempfile::TempDir {
1036        let dir = tempfile::tempdir().unwrap();
1037        fs::write(
1038            dir.path().join("_mdql.md"),
1039            "---\ntype: database\nname: testdb\n---\n",
1040        )
1041        .unwrap();
1042        let bt = dir.path().join("backtests");
1043        fs::create_dir(&bt).unwrap();
1044        fs::write(
1045            bt.join("_mdql.md"),
1046            "---\ntype: schema\ntable: backtests\nprimary_key: path\nfrontmatter:\n  strategy:\n    type: string\n  result:\n    type: string\n  modified:\n    type: datetime\n---\n",
1047        )
1048        .unwrap();
1049        fs::write(bt.join("b1.md"), "---\nstrategy: alpha.md\nresult: PASS\nmodified: \"2026-01-01T00:00:00\"\n---\n# b1\n").unwrap();
1050        fs::write(bt.join("b2.md"), "---\nstrategy: alpha.md\nresult: INCONCLUSIVE\nmodified: \"2026-05-01T00:00:00\"\n---\n# b2\n").unwrap();
1051        dir
1052    }
1053
1054    #[test]
1055    fn test_where_in_subquery_datetime() {
1056        // Issue #58: a subquery over a datetime column must not collapse to
1057        // NULL. Latest-per-group via IN should return the newest row.
1058        let dir = make_dated_bt_db();
1059        let (result, _) = execute(
1060            dir.path(),
1061            "SELECT result, modified FROM backtests WHERE modified IN (SELECT MAX(modified) FROM backtests GROUP BY strategy)",
1062        )
1063        .unwrap();
1064        if let QueryResult::Rows { rows, .. } = result {
1065            assert_eq!(rows.len(), 1);
1066            assert_eq!(rows[0].get("result"), Some(&Value::String("INCONCLUSIVE".into())));
1067        } else {
1068            panic!("Expected Rows");
1069        }
1070    }
1071
1072    #[test]
1073    fn test_where_scalar_subquery_datetime() {
1074        // Scalar (non-correlated) subquery returning a datetime.
1075        let dir = make_dated_bt_db();
1076        let (result, _) = execute(
1077            dir.path(),
1078            "SELECT result FROM backtests WHERE modified = (SELECT MAX(modified) FROM backtests)",
1079        )
1080        .unwrap();
1081        if let QueryResult::Rows { rows, .. } = result {
1082            assert_eq!(rows.len(), 1);
1083            assert_eq!(rows[0].get("result"), Some(&Value::String("INCONCLUSIVE".into())));
1084        } else {
1085            panic!("Expected Rows");
1086        }
1087    }
1088
1089    fn make_multi_bt_db() -> tempfile::TempDir {
1090        let dir = tempfile::tempdir().unwrap();
1091        fs::write(
1092            dir.path().join("_mdql.md"),
1093            "---\ntype: database\nname: testdb\n---\n",
1094        )
1095        .unwrap();
1096
1097        let strats = dir.path().join("strategies");
1098        fs::create_dir(&strats).unwrap();
1099        fs::write(
1100            strats.join("_mdql.md"),
1101            "---\ntype: schema\ntable: strategies\nprimary_key: path\nfrontmatter:\n  title:\n    type: string\n---\n",
1102        )
1103        .unwrap();
1104        fs::write(strats.join("alpha.md"), "---\ntitle: Alpha\n---\n# Alpha\n").unwrap();
1105        fs::write(strats.join("beta.md"), "---\ntitle: Beta\n---\n# Beta\n").unwrap();
1106
1107        let bt = dir.path().join("backtests");
1108        fs::create_dir(&bt).unwrap();
1109        fs::write(
1110            bt.join("_mdql.md"),
1111            "---\ntype: schema\ntable: backtests\nprimary_key: path\nfrontmatter:\n  strategy:\n    type: string\n  sharpe:\n    type: float\n---\n",
1112        )
1113        .unwrap();
1114        fs::write(bt.join("bt-alpha.md"), "---\nstrategy: alpha.md\nsharpe: 2.0\n---\n# BT\n").unwrap();
1115        fs::write(bt.join("bt-beta.md"), "---\nstrategy: beta.md\nsharpe: 0.5\n---\n# BT\n").unwrap();
1116
1117        dir
1118    }
1119
1120    #[test]
1121    fn test_where_scalar_subquery() {
1122        let dir = make_multi_bt_db();
1123        // AVG(sharpe) = (2.0 + 0.5) / 2 = 1.25 → only bt-alpha (2.0) passes
1124        let (result, _) = execute(
1125            dir.path(),
1126            "SELECT * FROM backtests WHERE sharpe > (SELECT AVG(sharpe) FROM backtests)",
1127        )
1128        .unwrap();
1129        if let QueryResult::Rows { rows, .. } = result {
1130            assert_eq!(rows.len(), 1);
1131            assert_eq!(rows[0].get("sharpe"), Some(&Value::Float(2.0)));
1132        } else {
1133            panic!("Expected Rows");
1134        }
1135    }
1136
1137    #[test]
1138    fn test_select_scalar_subquery() {
1139        let dir = make_join_db();
1140        let (result, _) = execute(
1141            dir.path(),
1142            "SELECT title, (SELECT COUNT(*) FROM backtests) AS bt_count FROM strategies",
1143        )
1144        .unwrap();
1145        if let QueryResult::Rows { rows, columns } = result {
1146            assert!(columns.contains(&"bt_count".to_string()));
1147            for row in &rows {
1148                assert_eq!(row.get("bt_count"), Some(&Value::Int(1)));
1149            }
1150        } else {
1151            panic!("Expected Rows");
1152        }
1153    }
1154}
mdql_core/executor.rs

mdql_core/
executor.rs
