graphitesql 0.1.3

A pure, safe, no_std Rust re-implementation of SQLite, compatible with the SQLite 3 file format.
Documentation 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97

//! Differential test for `HAVING` on a non-grouped aggregate query on the VDBE.
//! A bare-aggregate `SELECT` with a `HAVING` used to bail to the tree-walker;
//! the aggregate compiler now filters its single output row when the HAVING's
//! aggregate(s) also appear in the projection (bound to their finalized slots).
//! A HAVING aggregate that is NOT in the projection still defers (a "misuse of
//! aggregate" during compile), which the tree-walker evaluates. Either way the
//! result must match the real `sqlite3` CLI.

#![cfg(feature = "std")]

use graphitesql::{Connection, Value};
use std::process::Command;

fn sqlite3(setup: &[&str], query: &str) -> String {
    let mut script = String::new();
    for s in setup {
        script.push_str(s);
        script.push_str(";\n");
    }
    script.push_str(query);
    script.push(';');
    let o = Command::new("sqlite3")
        .arg(":memory:")
        .arg(&script)
        .output()
        .unwrap();
    String::from_utf8_lossy(&o.stdout).trim_end().to_string()
}

fn render(result: &graphitesql::QueryResult) -> String {
    result
        .rows
        .iter()
        .map(|row| {
            row.iter()
                .map(|v| match v {
                    Value::Null => String::new(),
                    Value::Integer(i) => i.to_string(),
                    Value::Text(s) => String::from(s.as_str()),
                    Value::Real(r) => graphitesql::exec::eval::format_real(*r),
                    Value::Blob(b) => b.iter().map(|x| format!("{x:02x}")).collect(),
                })
                .collect::<Vec<_>>()
                .join("|")
        })
        .collect::<Vec<_>>()
        .join("\n")
}

fn check(setup: &[&str], queries: &[&str]) {
    let mut g = Connection::open_memory().unwrap();
    for s in setup {
        g.execute(s).unwrap();
    }
    for q in queries {
        let want = sqlite3(setup, q);
        let got = render(&g.query(q).unwrap());
        assert_eq!(got, want, "bare-aggregate HAVING diverged: {q}");
    }
}

#[test]
fn bare_aggregate_having_matches_sqlite3() {
    if Command::new("sqlite3").arg("--version").output().is_err() {
        eprintln!("sqlite3 not found; skipping");
        return;
    }

    let setup = [
        "CREATE TABLE t(a INTEGER, b TEXT)",
        "INSERT INTO t VALUES(1,'x'),(2,'y'),(3,'z')",
    ];
    check(
        &setup,
        &[
            "SELECT count(*) FROM t HAVING count(*) > 5", // filtered out -> no row
            "SELECT count(*) FROM t HAVING count(*) > 2", // 3
            "SELECT sum(a) FROM t HAVING sum(a) >= 6",    // 6
            "SELECT max(a) FROM t HAVING max(a) > 2",     // 3
            "SELECT count(*), sum(a) FROM t HAVING count(*) >= 3", // multi-agg projection
            "SELECT count(*) FROM t HAVING count(*) BETWEEN 1 AND 3", // 3
            // HAVING aggregate not in the projection -> defers, still correct
            "SELECT count(*) FROM t HAVING sum(a) > 100", // no row
            "SELECT count(*) FROM t HAVING sum(a) = 6",   // 3
        ],
    );

    // Empty table: the aggregate row exists (count 0) and HAVING filters it.
    check(
        &["CREATE TABLE e(a INTEGER)"],
        &[
            "SELECT count(*) FROM e HAVING count(*) > 0", // no row
            "SELECT count(*) FROM e HAVING count(*) = 0", // 0
            "SELECT sum(a) FROM e HAVING count(*) = 0",   // NULL row
        ],
    );
}