1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
//! A `FROM` reference naming a **view** now runs on the VDBE. `scan_one` materializes
//! the view exactly as the tree-walker does (running its stored body), then exposes the
//! view's output columns — each column's `(affinity, collation)` comes from `try_view`'s
//! origin resolution, so an outer `WHERE` / `ORDER BY` over a view column coerces just as
//! it would over the body. Projection / WHERE / aggregate / GROUP BY / HAVING / DISTINCT /
//! ORDER BY / LIMIT over the view, a view joined to a base table, and a join- or
//! compound-bodied view all run without falling back.
//!
//! A view column carrying a *non-BINARY* collation flows through to the VDBE's
//! collation-aware compare / GROUP BY / DISTINCT / aggregate paths (like a base-table
//! column), so a `NOCASE` view column runs — see `nocase_view_column_runs_on_vdbe`.
//!
//! A view has no rowid, so a `rowid` reference over the view resolves to nothing and the
//! query defers (like a derived table) — but a view body that *projects* `rowid` under an
//! alias exposes an ordinary column that runs.
//!
//! `query_vdbe` errors on any fallback, so a passing query proves the VDBE handled the
//! view source. Checked against the tree-walker and sqlite3 3.50.4.
#![cfg(feature = "std")]
use graphitesql::{Connection, Value};
use std::process::Command;
const SETUP: &str = "\
CREATE TABLE t(g INTEGER, a TEXT, n INTEGER);\n\
INSERT INTO t VALUES (1,'x',10),(2,'y',20),(3,'z',30);\n\
CREATE TABLE u(g INTEGER, m INTEGER);\n\
INSERT INTO u VALUES (1,100),(2,200),(2,201);\n\
CREATE VIEW vt AS SELECT g, n FROM t;\n\
CREATE VIEW vj AS SELECT t.g AS g, u.m AS m FROM t JOIN u ON t.g=u.g;\n\
CREATE VIEW vc AS SELECT g FROM t UNION SELECT m FROM u;\n\
CREATE VIEW vrowid AS SELECT rowid AS r, g FROM t;\n";
// Each query names a view in its FROM clause. ORDER BY (or a deterministic aggregate)
// pins the row order for a direct comparison.
const QUERIES: &[&str] = &[
// Affinity-sensitive outer WHERE: the view column `g` keeps INTEGER affinity, so a
// text literal coerces numerically and matches.
"SELECT g, n FROM vt WHERE g = '2' ORDER BY g",
// Wildcard over a view expands its columns in declaration order.
"SELECT * FROM vt ORDER BY 1, 2",
// Affinity-sensitive ORDER BY + LIMIT.
"SELECT n FROM vt ORDER BY n DESC LIMIT 2",
// Whole-view aggregate.
"SELECT count(*), sum(n) FROM vt",
// GROUP BY / HAVING over the view.
"SELECT g FROM vt GROUP BY g HAVING count(*) >= 1 ORDER BY g",
// DISTINCT over the view.
"SELECT DISTINCT g FROM vt ORDER BY 1",
// A view joined to a base table (qualified refs disambiguate the shared name `g`).
"SELECT vt.g, u.m FROM vt JOIN u ON vt.g = u.g ORDER BY 1, 2",
// A join-bodied view as the source.
"SELECT g, m FROM vj ORDER BY 1, 2",
// A compound-bodied (UNION) view as the source.
"SELECT g FROM vc ORDER BY 1",
// A view body that projects an aliased rowid is an ordinary column.
"SELECT r, g FROM vrowid ORDER BY r",
];
fn conn() -> Connection {
let mut c = Connection::open_memory().unwrap();
for stmt in SETUP.split(';') {
let s = stmt.trim();
if !s.is_empty() {
c.execute(s).unwrap();
}
}
c
}
fn render(v: &Value) -> String {
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(),
}
}
#[test]
fn view_source_runs_on_vdbe_and_matches_tree_walker() {
let c = conn();
for q in QUERIES {
// `query_vdbe` errors on fallback, so this proves the VDBE scanned the view.
let got = c.query_vdbe(q).unwrap().rows;
let want = c.query(q).unwrap().rows;
assert_eq!(got, want, "VDBE vs tree-walker diverged on {q}");
}
}
#[test]
fn view_source_matches_sqlite3() {
if Command::new("sqlite3").arg("--version").output().is_err() {
eprintln!("sqlite3 not found; skipping");
return;
}
let c = conn();
for q in QUERIES {
let vdbe: Vec<Vec<String>> = c
.query_vdbe(q)
.unwrap()
.rows
.iter()
.map(|r| r.iter().map(render).collect())
.collect();
let out = Command::new("sqlite3")
.arg(":memory:")
.arg("-ascii")
.arg(format!("{SETUP}{q};"))
.output()
.unwrap();
assert!(out.status.success(), "sqlite3 failed on {q}");
let text = String::from_utf8(out.stdout).unwrap();
let want: Vec<Vec<String>> = text
.split('\u{1e}')
.filter(|r| !r.is_empty())
.map(|r| r.split('\u{1f}').map(|f| f.to_string()).collect())
.collect();
assert_eq!(vdbe, want, "VDBE vs sqlite3 diverged on {q}");
}
}
#[test]
fn nocase_view_column_runs_on_vdbe() {
let mut c = conn();
c.execute("CREATE TABLE w(k TEXT COLLATE NOCASE, val INTEGER)")
.unwrap();
c.execute("INSERT INTO w VALUES ('A',1),('a',2),('b',3)")
.unwrap();
c.execute("CREATE VIEW vw AS SELECT k, val FROM w").unwrap();
// A view column carrying a non-BINARY collation now flows through to the VDBE's
// collation-aware compare / GROUP / DISTINCT paths (like a base-table column), so
// these run on the VDBE and honour NOCASE — `k='a'` matches 'A' and 'a', DISTINCT
// folds them together — matching the tree-walker and SQLite.
for q in [
"SELECT k FROM vw WHERE k = 'a' ORDER BY val",
"SELECT DISTINCT k FROM vw ORDER BY k",
"SELECT k, count(*) FROM vw GROUP BY k ORDER BY k",
"SELECT count(DISTINCT k) FROM vw",
] {
let got = c
.query_vdbe(q)
.unwrap_or_else(|e| panic!("expected VDBE to run {q}: {e}"));
assert_eq!(
got.rows,
c.query(q).unwrap().rows,
"VDBE vs tree-walker on {q}"
);
}
}