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
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
//! v7.17.0 Phase 3.3 → P0-41 — LATERAL derived tables.
//!
//! Phase 3.3 (`981812a`) carved this out; v7.17.0 Phase 3.P0-41
//! lands the real implementation. SPG now parses `LATERAL (
//! SELECT … )` in any FROM-list position and the join executor
//! materialises the inner SELECT per outer row, substituting
//! `<outer_alias>.<col>` references against the current join row.
//!
//! v7.17 limitations (separate follow-ups):
//! * The schema probe falls back to a TEXT-typed column shape
//! when the inner SELECT references outer columns at
//! projection time (rare; values are still correct because
//! the per-row substitution path runs the real query).
//! * No `JOIN LATERAL … ON expr` mixed forms (parsed but the
//! v7.17 executor treats `LATERAL` as a CROSS JOIN — the ON
//! clause must move into the inner subquery's WHERE).
//! * No correlated subquery references inside aggregate
//! window arguments (window+LATERAL combination).
use spg_engine::{Engine, QueryResult};
use spg_storage::Value;
fn rows(r: QueryResult) -> Vec<Vec<Value<'static>>> {
match r {
QueryResult::Rows { rows, .. } => rows.into_iter().map(|r| r.values).collect(),
_ => panic!("expected rows"),
}
}
fn setup(e: &mut Engine) {
e.execute("CREATE TABLE users (id INT NOT NULL, name TEXT NOT NULL)")
.unwrap();
e.execute("CREATE TABLE orders (id INT NOT NULL, user_id INT NOT NULL, amount INT NOT NULL)")
.unwrap();
e.execute("INSERT INTO users VALUES (1, 'alice'), (2, 'bob')")
.unwrap();
e.execute("INSERT INTO orders VALUES (1, 1, 100), (2, 1, 200), (3, 2, 50), (4, 2, 80)")
.unwrap();
}
// read01 LATERAL — a FROM-less LATERAL subquery references outer columns
// in its projection. Previously only qualified `alias.col` refs (in a
// correlated inner WHERE) were substituted; a FROM-less `LATERAL (SELECT
// v*2)` left the bare outer column unresolved and errored. Values vs
// live PG 18.4.
#[test]
fn lateral_fromless_projects_outer_column() {
let mut e = Engine::new();
e.execute("CREATE TABLE t (id INT, g INT, v INT)").unwrap();
e.execute("INSERT INTO t VALUES (1, 1, 10), (2, 1, 20), (3, 2, 30)")
.unwrap();
// Comma-join FROM-less LATERAL: x = v*2.
let r = rows(
e.execute("SELECT t.id, x FROM t, LATERAL (SELECT v * 2 AS x) sub WHERE t.id = 1")
.unwrap(),
);
assert_eq!(r, vec![vec![Value::Int(1), Value::Int(20)]]);
// CROSS JOIN LATERAL, bare outer ref.
let r = rows(
e.execute(
"SELECT t.id, x FROM t CROSS JOIN LATERAL (SELECT v + 1 AS x) sub WHERE t.id = 2",
)
.unwrap(),
);
assert_eq!(r, vec![vec![Value::Int(2), Value::Int(21)]]);
// Multiple outer columns across multiple projected columns.
let r = rows(
e.execute(
"SELECT t.id, a, b FROM t, LATERAL (SELECT v * 10 AS a, g + v AS b) sub WHERE t.id = 3",
)
.unwrap(),
);
assert_eq!(
r,
vec![vec![Value::Int(3), Value::Int(300), Value::Int(32)]]
);
// JOIN LATERAL … ON true (the ON-clause mixed form).
let r = rows(
e.execute("SELECT t.id, x FROM t JOIN LATERAL (SELECT v AS x) sub ON true WHERE t.id = 2")
.unwrap(),
);
assert_eq!(r, vec![vec![Value::Int(2), Value::Int(20)]]);
}
#[test]
fn lateral_subquery_correlated_in_where() {
// The canonical LATERAL shape: for each user, fetch one order.
let mut e = Engine::new();
setup(&mut e);
let r = rows(
e.execute(
"SELECT u.name, o.amount \
FROM users u, LATERAL (SELECT amount FROM orders WHERE user_id = u.id ORDER BY amount LIMIT 1) o \
ORDER BY u.id",
)
.unwrap(),
);
// alice: min order amount = 100. bob: min = 50.
assert_eq!(r.len(), 2);
assert_eq!(r[0][0], Value::text("alice"));
assert_eq!(r[0][1], Value::Int(100));
assert_eq!(r[1][0], Value::text("bob"));
assert_eq!(r[1][1], Value::Int(50));
}
#[test]
fn lateral_subquery_returns_multiple_rows_per_outer() {
// For each user, fetch their top-2 orders.
let mut e = Engine::new();
setup(&mut e);
let r = rows(
e.execute(
"SELECT u.name, o.amount \
FROM users u, LATERAL (SELECT amount FROM orders WHERE user_id = u.id ORDER BY amount DESC LIMIT 2) o \
ORDER BY u.id, o.amount DESC",
)
.unwrap(),
);
// alice has 100 + 200 → top 2 = 200, 100.
// bob has 50 + 80 → top 2 = 80, 50.
assert_eq!(r.len(), 4);
assert_eq!(r[0][1], Value::Int(200));
assert_eq!(r[1][1], Value::Int(100));
assert_eq!(r[2][1], Value::Int(80));
assert_eq!(r[3][1], Value::Int(50));
}
#[test]
fn lateral_subquery_with_no_inner_matches_drops_outer() {
// Add a user with no orders; CROSS-shaped LATERAL drops them
// (LEFT JOIN LATERAL would keep — v7.17 doesn't yet support
// the ON clause variant cleanly).
let mut e = Engine::new();
setup(&mut e);
e.execute("INSERT INTO users VALUES (3, 'carol')").unwrap();
let r = rows(
e.execute(
"SELECT u.name, o.amount \
FROM users u, LATERAL (SELECT amount FROM orders WHERE user_id = u.id) o \
ORDER BY u.id",
)
.unwrap(),
);
// carol has no orders → cross-join with empty subquery emits
// zero rows for her.
assert_eq!(r.len(), 4);
for row in &r {
assert_ne!(row[0], Value::text("carol"));
}
}
#[test]
fn correlated_subquery_in_select_workaround() {
// For "per-row aggregate" use cases, a correlated scalar
// subquery in the SELECT list achieves similar semantics.
let mut e = Engine::new();
setup(&mut e);
let r = e.execute(
"SELECT u.name, \
(SELECT max(amount) FROM orders WHERE user_id = u.id) AS top_amount \
FROM users u",
);
match r {
Ok(QueryResult::Rows { rows: out, .. }) => {
assert_eq!(out.len(), 2);
}
Err(e) => {
// The correlated subquery path may also have gaps;
// pin the actual behavior.
eprintln!("correlated subquery workaround: {e:?}");
}
_ => panic!(),
}
}
#[test]
fn regular_join_still_works() {
// Negative regression: ordinary inner joins unaffected.
let mut e = Engine::new();
setup(&mut e);
let r = rows(
e.execute(
"SELECT u.name, o.amount \
FROM users u JOIN orders o ON o.user_id = u.id \
ORDER BY u.id, o.amount",
)
.unwrap(),
);
assert_eq!(r.len(), 4);
}
// v7.37.7 (sentori Epic 4 P1) — `LEFT JOIN LATERAL … ON TRUE` shape
// from the endpoint-probe runtime query. SPG already had head-position
// LATERAL since v7.17.0 P0-41; this case pins the JOIN-position shape
// sentori actually uses.
#[test]
fn left_join_lateral_on_true_matches_per_outer_row() {
let mut e = Engine::new();
// endpoint_check + endpoint_probe — sentori's exact tables.
e.execute(
"CREATE TABLE endpoint_check (
id BIGINT PRIMARY KEY,
project_id BIGINT NOT NULL,
url TEXT NOT NULL,
method TEXT NOT NULL,
paused BOOL NOT NULL DEFAULT FALSE
)",
)
.unwrap();
e.execute(
"CREATE TABLE endpoint_probe (
id BIGINT PRIMARY KEY,
check_id BIGINT NOT NULL,
ts TIMESTAMPTZ NOT NULL
)",
)
.unwrap();
e.execute("INSERT INTO endpoint_check VALUES (1, 100, 'https://a', 'GET', FALSE)")
.unwrap();
e.execute("INSERT INTO endpoint_check VALUES (2, 100, 'https://b', 'GET', FALSE)")
.unwrap();
// Check 1 has two probes; we want the latest. Check 2 has none.
e.execute("INSERT INTO endpoint_probe VALUES (10, 1, '2026-06-01 00:00:00+00')")
.unwrap();
e.execute("INSERT INTO endpoint_probe VALUES (11, 1, '2026-06-15 00:00:00+00')")
.unwrap();
let result = rows(
e.execute(
"SELECT c.id, lp.ts \
FROM endpoint_check c \
LEFT JOIN LATERAL ( \
SELECT ts FROM endpoint_probe \
WHERE check_id = c.id \
ORDER BY ts DESC LIMIT 1 \
) lp ON TRUE \
ORDER BY c.id",
)
.expect("LEFT JOIN LATERAL parses and runs"),
);
// Two outer rows. Check 1 ⇒ latest probe ts = 2026-06-15.
// Check 2 ⇒ no probe, lateral row absent ⇒ ts is NULL.
assert_eq!(result.len(), 2);
assert_eq!(result[0][0], Value::BigInt(1));
assert!(matches!(result[0][1], Value::Timestamp(_)));
assert_eq!(result[1][0], Value::BigInt(2));
assert_eq!(result[1][1], Value::Null);
}