//! Differential fuzzing: random-but-valid SQL is run through this engine and //! through the system `sqlite3` CLI, and the results must match exactly. //! //! Any statement that ever failed lives on in `tests/corpus/queries.txt`, which //! is replayed on every run — the corpus only grows. mod common; use common::*; use makepad_sqlite::{Database, Value}; use std::path::{Path, PathBuf}; const FIXTURE: &str = r#" CREATE TABLE t( id INTEGER PRIMARY KEY, name TEXT, kind TEXT, n INTEGER, r REAL, b BLOB ); CREATE INDEX t_by_kind ON t(kind, n); CREATE INDEX t_by_name ON t(name); CREATE TABLE u(id INTEGER PRIMARY KEY, t_id INTEGER, tag TEXT, w INTEGER); CREATE INDEX u_by_t ON u(t_id, tag); CREATE TABLE k(k TEXT PRIMARY KEY, v BLOB NOT NULL, m INTEGER); INSERT INTO t VALUES (1,'alpha','a',10,1.5,x'01'), (2,'beta','b',20,2.5,x'02'), (3,'gamma','a',30,NULL,NULL), (4,'delta',NULL,40,4.5,x'04'), (5,'Epsilon','b',NULL,5.5,x'05'), (6,'zeta','c',60,6.0,x'0607'), (7,'eta','a',-5,-1.25,x''), (8,NULL,'b',0,0.0,x'00'), (9,'theta','c',1000000,1e10,x'ffee'), (10,'iota','a',7,7.75,x'0a'); INSERT INTO u VALUES (1,1,'red',5),(2,1,'blue',6),(3,2,'red',7),(4,6,'green',8), (5,99,'orphan',9),(6,3,NULL,10),(7,3,'red',NULL); INSERT INTO k VALUES ('key-a', x'aa', 1), ('key-b', x'bb', 2), ('key-c', x'cc', NULL), ('zzz', x'00', 4); "#; struct Rng(u64); impl Rng { fn next(&mut self) -> u64 { let mut x = self.0; x ^= x << 13; x ^= x >> 7; x ^= x << 17; self.0 = x; x } fn pick<'a, T>(&mut self, items: &'a [T]) -> &'a T { &items[(self.next() % items.len() as u64) as usize] } fn chance(&mut self, n: u64) -> bool { self.next() % n == 0 } } fn gen_literal(rng: &mut Rng) -> String { match rng.next() % 8 { 0 => "NULL".into(), 1 => format!("{}", (rng.next() % 100) as i64 - 20), 2 => format!("{:.2}", (rng.next() % 1000) as f64 / 10.0), 3 => "'a'".into(), 4 => "'red'".into(), 5 => "x'02'".into(), 6 => "'alpha'".into(), _ => format!("'{}'", ["a", "b", "c", "key-a", "zeta", ""][(rng.next() % 6) as usize]), } } fn gen_predicate(rng: &mut Rng, cols: &[&str]) -> String { let col = *rng.pick(cols); match rng.next() % 10 { 0 => format!("{col} IS NULL"), 1 => format!("{col} IS NOT NULL"), 2 => format!("{col} IN ({}, {})", gen_literal(rng), gen_literal(rng)), 3 => format!("{col} NOT IN ({})", gen_literal(rng)), 4 => format!( "{col} BETWEEN {} AND {}", gen_literal(rng), gen_literal(rng) ), 5 => format!("{col} LIKE '{}%'", ["a", "b", "z", "e"][(rng.next() % 4) as usize]), 6 => format!("NOT {col} = {}", gen_literal(rng)), 7 => format!("COALESCE({col}, {}) = {}", gen_literal(rng), gen_literal(rng)), _ => { let op = *rng.pick(&["=", "<>", "<", "<=", ">", ">="]); format!("{col} {op} {}", gen_literal(rng)) } } } fn gen_query(rng: &mut Rng) -> String { let joined = rng.chance(3); let (table, cols): (&str, Vec<&str>) = if joined { ( "t JOIN u ON u.t_id = t.id", vec!["t.id", "t.name", "t.kind", "t.n", "t.r", "u.tag", "u.w"], ) } else if rng.chance(4) { ("k", vec!["k", "v", "m"]) } else if rng.chance(2) { ("u", vec!["id", "t_id", "tag", "w"]) } else { ("t", vec!["id", "name", "kind", "n", "r", "b"]) }; let mut sql = String::from("SELECT "); if rng.chance(6) { sql.push_str("DISTINCT "); } let group = rng.chance(4); let mut order_cols: Vec = Vec::new(); if group { let g = *rng.pick(&cols); // Aggregate over a column the chosen table actually has. let any = *rng.pick(&cols); let agg = match rng.next() % 5 { 0 => "COUNT(*)".to_string(), 1 => format!("COUNT(DISTINCT {any})"), 2 => "SUM(1)".to_string(), 3 => format!("MIN({any})"), _ => format!("MAX({any})"), }; sql.push_str(&format!("{g}, {agg} FROM {table}")); order_cols.push("1".into()); order_cols.push("2".into()); if !rng.chance(3) { sql.push_str(&format!(" WHERE {}", gen_predicate(rng, &cols))); } sql.push_str(&format!(" GROUP BY {g}")); if rng.chance(3) { sql.push_str(" HAVING COUNT(*) > 1"); } } else { let n = 1 + rng.next() % 3; let mut picked = Vec::new(); for _ in 0..n { picked.push((*rng.pick(&cols)).to_string()); } sql.push_str(&picked.join(", ")); sql.push_str(&format!(" FROM {table}")); let terms = rng.next() % 3; if terms > 0 { let mut parts = Vec::new(); for _ in 0..terms { parts.push(gen_predicate(rng, &cols)); } let joiner = if rng.chance(4) { " OR " } else { " AND " }; sql.push_str(&format!(" WHERE {}", parts.join(joiner))); } for i in 0..picked.len() { order_cols.push(format!("{}", i + 1)); } } // Always order fully, so row order is defined for both engines. sql.push_str(&format!(" ORDER BY {}", order_cols.join(", "))); if rng.chance(3) { sql.push_str(&format!(" LIMIT {}", 1 + rng.next() % 5)); if rng.chance(2) { sql.push_str(&format!(" OFFSET {}", rng.next() % 3)); } } sql } fn rows_from_cli(db: &Path, sql: &str) -> Option>> { let out = std::process::Command::new("sqlite3") .arg(db) .arg("-cmd") .arg(".mode quote") .arg("-cmd") .arg(".separator |") .arg(format!("{sql};")) .output() .ok()?; if !out.status.success() { return None; } Some( String::from_utf8_lossy(&out.stdout) .lines() .filter(|l| !l.trim().is_empty()) .map(|l| l.split('|').map(parse_quoted).collect()) .collect(), ) } fn same(a: &Value, b: &Value) -> bool { a.class() == b.class() && a == b } fn check(db_path: &Path, db: &mut Database, sql: &str) -> Result<(), String> { let ours = match db.query(sql, &[]) { Ok(r) => r, Err(e) => return Err(format!("engine error: {e}")), }; let Some(theirs) = rows_from_cli(db_path, sql) else { return Err("sqlite3 rejected the statement".into()); }; if ours.rows.len() != theirs.len() { return Err(format!( "row count {} vs {}\nours: {:?}\ncli: {:?}", ours.rows.len(), theirs.len(), ours.to_quoted_lines(), theirs .iter() .map(|r| r.iter().map(quote).collect::>().join("|")) .collect::>() )); } for (i, (a, b)) in ours.rows.iter().zip(theirs.iter()).enumerate() { if a.len() != b.len() { return Err(format!("row {i}: {} vs {} columns", a.len(), b.len())); } for (c, (x, y)) in a.iter().zip(b.iter()).enumerate() { if !same(x, y) { return Err(format!( "row {i} column {c}: ours {} vs cli {}", quote(x), quote(y) )); } } } Ok(()) } fn corpus_path() -> PathBuf { Path::new(env!("CARGO_MANIFEST_DIR")).join("tests/corpus/queries.txt") } #[test] fn corpus_replays_clean() { if !have_sqlite3() { return; } let scratch = Scratch::new("corpus"); let path = build_db(&scratch.dir, "fuzz.db", FIXTURE); let mut db = Database::open(&path).unwrap(); let text = std::fs::read_to_string(corpus_path()).expect("corpus file"); let mut checked = 0; for line in text.lines() { let sql = line.trim(); if sql.is_empty() || sql.starts_with('#') { continue; } if let Err(e) = check(&path, &mut db, sql) { panic!("corpus statement failed:\n {sql}\n {e}"); } checked += 1; } assert!(checked > 20, "corpus is suspiciously small: {checked}"); } #[test] fn random_queries_match_sqlite() { if !have_sqlite3() { return; } let scratch = Scratch::new("fuzz"); let path = build_db(&scratch.dir, "fuzz.db", FIXTURE); let mut db = Database::open(&path).unwrap(); // Fixed seed: a failure is always reproducible, and any statement it finds // gets added to the corpus by hand. let mut rng = Rng(0x9e37_79b9_7f4a_7c15); let mut failures = Vec::new(); let rounds = 400; for _ in 0..rounds { let sql = gen_query(&mut rng); if let Err(e) = check(&path, &mut db, &sql) { failures.push(format!("{sql}\n {e}")); if failures.len() > 6 { break; } } } assert!( failures.is_empty(), "{} of {rounds} generated queries disagreed with sqlite3:\n{}", failures.len(), failures.join("\n") ); }