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
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
//! A blob used in an arithmetic or numeric-function context coerces the way
//! SQLite does: it reads the blob's *bytes as a text string* and applies the
//! ordinary text→number rule. So `x'3132'` (the bytes `0x31 0x32`, i.e. the
//! ASCII text `"12"`) behaves like the number 12 — `abs(x'3132')` is `12.0`,
//! `x'3132' + 0` is `12`, and a blob inside `sum`/`avg`/`total` contributes its
//! parsed value.
//!
//! graphite used to map every blob to `0` in this path (`abs(x'35')` → `0.0`).
//! The fix is a single point — `eval::to_number`'s `Blob` arm — shared by the
//! tree-walker and the VDBE. Affinity application and the strict aggregate
//! integer-vs-real test still leave blobs unconverted (matching SQLite), so a
//! blob keeps BLOB affinity and is non-integer-typed for `sum`.
//!
//! Verified against sqlite3 3.50.4 with the VDBE both on (default) and off.
#![cfg(feature = "std")]
use graphitesql::Connection;
use std::process::Command;
fn one(c: &Connection, sql: &str) -> String {
let r = c.query(sql).unwrap();
r.rows
.iter()
.map(|row| {
row.iter()
.map(|v| format!("{v:?}"))
.collect::<Vec<_>>()
.join(",")
})
.collect::<Vec<_>>()
.join("|")
}
#[test]
fn blob_coerces_as_text_number() {
for &vdbe in &[true, false] {
let c = Connection::open_memory().unwrap();
c.set_use_vdbe(vdbe);
// abs reads the bytes as text "5"/"12"/"-12"/"3.5"/"12345".
assert_eq!(
one(&c, "SELECT abs(x'35')"),
"Real(5.0)",
"abs5 vdbe={vdbe}"
);
assert_eq!(
one(&c, "SELECT abs(x'3132')"),
"Real(12.0)",
"abs12 vdbe={vdbe}"
);
assert_eq!(
one(&c, "SELECT abs(x'2D3132')"),
"Real(12.0)",
"absNeg vdbe={vdbe}"
);
assert_eq!(
one(&c, "SELECT abs(x'332E35')"),
"Real(3.5)",
"absReal vdbe={vdbe}"
);
// An all-zero / non-numeric-prefix blob parses to 0.
assert_eq!(
one(&c, "SELECT abs(x'00')"),
"Real(0.0)",
"absZero vdbe={vdbe}"
);
// Arithmetic: blob operands become their text-number value.
assert_eq!(
one(&c, "SELECT x'3132' + 0"),
"Integer(12)",
"add vdbe={vdbe}"
);
assert_eq!(
one(&c, "SELECT x'3132' + x'3334'"),
"Integer(46)",
"addbb vdbe={vdbe}"
);
assert_eq!(
one(&c, "SELECT x'35' * 2"),
"Integer(10)",
"mul vdbe={vdbe}"
);
assert_eq!(one(&c, "SELECT -x'35'"), "Integer(-5)", "neg vdbe={vdbe}");
assert_eq!(
one(&c, "SELECT typeof(x'3132' + 0)"),
"Text(\"integer\")",
"typeof vdbe={vdbe}"
);
// A leading numeric prefix only (`"1 2"` -> 1), and an empty blob -> 0.
assert_eq!(
one(&c, "SELECT x'31202032' + 0"),
"Integer(1)",
"prefix vdbe={vdbe}"
);
assert_eq!(one(&c, "SELECT x'' + 0"), "Integer(0)", "empty vdbe={vdbe}");
// Aggregates: a blob contributes its parsed value (and, being
// non-integer-typed, keeps the sum REAL).
assert_eq!(
one(
&c,
"SELECT sum(c) FROM (SELECT x'3132' c UNION ALL SELECT 3)"
),
"Real(15.0)",
"sum vdbe={vdbe}"
);
assert_eq!(
one(
&c,
"SELECT avg(c) FROM (SELECT x'3132' c UNION ALL SELECT 4)"
),
"Real(8.0)",
"avg vdbe={vdbe}"
);
// Truthiness also reads the blob as a number.
assert_eq!(
one(&c, "SELECT CASE WHEN x'3132' THEN 'y' ELSE 'n' END"),
"Text(\"y\")",
"truthy vdbe={vdbe}"
);
assert_eq!(
one(&c, "SELECT CASE WHEN x'00' THEN 'y' ELSE 'n' END"),
"Text(\"n\")",
"falsy vdbe={vdbe}"
);
}
}
#[test]
fn matches_sqlite_cli() {
if Command::new("sqlite3").arg("--version").output().is_err() {
eprintln!("sqlite3 CLI not found; skipping");
return;
}
let run = |bin: &str, sql: &str| -> String {
let out = Command::new(bin).arg(":memory:").arg(sql).output().unwrap();
let s = String::from_utf8_lossy(&out.stdout);
let e = String::from_utf8_lossy(&out.stderr);
let et = e.trim();
if !et.is_empty() {
return et.lines().next().unwrap_or("").to_string();
}
s.lines().collect::<Vec<_>>().join("|")
};
let g = env!("CARGO_BIN_EXE_graphitesql");
for sql in [
"SELECT abs(x'35');",
"SELECT abs(x'3132');",
"SELECT abs(x'2D3132');",
"SELECT abs(x'332E35');",
"SELECT abs(x'3132333435');",
"SELECT abs(x'00');",
"SELECT x'3132' + 0;",
"SELECT x'3132' + x'3334';",
"SELECT x'35' * 2;",
"SELECT -x'35';",
"SELECT round(x'332E3134',1);",
"SELECT x'31202032' + 0;",
"SELECT '12'+x'3334';",
"SELECT x'' + 0;",
"SELECT typeof(x'3132'+0);",
"SELECT sum(c) FROM (SELECT x'3132' c UNION ALL SELECT 3);",
"SELECT total(c) FROM (SELECT x'3132' c UNION ALL SELECT 3);",
"SELECT avg(c) FROM (SELECT x'3132' c UNION ALL SELECT 4);",
"SELECT CASE WHEN x'3132' THEN 'y' ELSE 'n' END;",
"SELECT CASE WHEN x'00' THEN 'y' ELSE 'n' END;",
"SELECT x'3132'/x'34';",
"SELECT x'3132'%5;",
] {
assert_eq!(run("sqlite3", sql), run(g, sql), "for {sql}");
}
}