1use crate::error::{Failure, Result};
28use crate::sql::parse::{parse, ParseLimits};
29use crate::sql::strip::strip;
30use sqlparser::ast::{Expr, LimitClause, Query, Statement, Value};
31
32pub fn with_limit(
44 sql: &str,
45 limit: usize,
46 offset: Option<usize>,
47 limits: ParseLimits,
48) -> Result<String> {
49 if limit == 0 {
50 return Err(
51 Failure::validation("rewrite.zero_limit", "the row ceiling is zero")
52 .with_hint("ask for at least one row"),
53 );
54 }
55
56 let statements = parse(sql, limits)?;
57 let query = single_query(&statements)?;
58 refuse_unappendable_clauses(query)?;
59
60 match query.limit_clause.as_ref() {
61 None => Ok(append_clause(sql, limit, offset)),
62 Some(existing) => reuse_existing_clause(sql, existing, limit, offset),
63 }
64}
65
66fn single_query(statements: &[Statement]) -> Result<&Query> {
68 let [Statement::Query(query)] = statements else {
69 return Err(Failure::validation(
70 "rewrite.not_a_single_query",
71 "a row ceiling can only be imposed on a single query",
72 )
73 .with_hint("send one statement that returns rows"));
74 };
75 Ok(query)
76}
77
78fn refuse_unappendable_clauses(query: &Query) -> Result<()> {
83 let blocker = if query.fetch.is_some() {
87 Some(("a fetch clause", "fetch"))
88 } else if !query.locks.is_empty() {
89 Some(("a row locking clause", "for update"))
90 } else if query.for_clause.is_some() {
91 Some(("a for clause", "for xml"))
92 } else if query.settings.is_some() {
93 Some(("a settings clause", "settings"))
94 } else if query.format_clause.is_some() {
95 Some(("a format clause", "format"))
96 } else if !query.pipe_operators.is_empty() {
97 Some(("a pipe operator", "|>"))
98 } else {
99 None
100 };
101
102 match blocker {
103 None => Ok(()),
104 Some((clause, keyword)) => Err(Failure::validation(
105 "rewrite.tail_clause_present",
106 format!("the query ends in {clause}, so a row ceiling cannot be appended"),
107 )
108 .with_detail("detail", keyword)
109 .with_hint("remove that clause, or ask for the rows without a ceiling")),
110 }
111}
112
113fn reuse_existing_clause(
115 sql: &str,
116 existing: &LimitClause,
117 limit: usize,
118 offset: Option<usize>,
119) -> Result<String> {
120 let already = existing_row_count(existing);
121
122 match (already, offset) {
123 (Some(rows), None) if rows <= limit => Ok(sql.to_owned()),
126 _ => Err(Failure::validation(
127 "rewrite.limit_already_present",
128 "the query already carries a row clause of its own",
129 )
130 .with_hint(
131 "remove the row clause from the query, or ask for a ceiling no smaller than it",
132 )),
133 }
134}
135
136fn existing_row_count(clause: &LimitClause) -> Option<usize> {
141 let LimitClause::LimitOffset {
142 limit: Some(limit),
143 offset: None,
144 limit_by,
145 } = clause
146 else {
147 return None;
148 };
149 if !limit_by.is_empty() {
150 return None;
151 }
152 let Expr::Value(value) = limit else {
153 return None;
154 };
155 match &value.value {
156 Value::Number(digits, _) => digits.parse::<usize>().ok(),
157 _ => None,
158 }
159}
160
161fn append_clause(sql: &str, limit: usize, offset: Option<usize>) -> String {
166 use std::fmt::Write as _;
167
168 let mut text = trim_trailing_separator(sql).to_owned();
171 if let Some(start) = offset {
172 let _ = write!(text, "\nLIMIT {limit} OFFSET {start}");
173 } else {
174 let _ = write!(text, "\nLIMIT {limit}");
175 }
176 text
177}
178
179fn trim_trailing_separator(sql: &str) -> &str {
187 let blanked = strip(sql);
188 let text = blanked.text();
189 match text.rfind(|character: char| !character.is_whitespace()) {
190 Some(index) if text.as_bytes().get(index) == Some(&b';') => &sql[..index],
191 _ => sql,
192 }
193}
194
195#[cfg(test)]
196mod tests {
197 use super::*;
198
199 fn rewritten(sql: &str, limit: usize, offset: Option<usize>) -> String {
200 with_limit(sql, limit, offset, ParseLimits::default()).expect("the query accepts a ceiling")
201 }
202
203 fn refusal(sql: &str, limit: usize, offset: Option<usize>) -> Failure {
204 with_limit(sql, limit, offset, ParseLimits::default())
205 .expect_err("the query must be refused")
206 }
207
208 #[test]
209 fn a_query_without_a_row_clause_receives_one() {
210 assert_eq!(
211 rewritten("SELECT a FROM t", 10, None),
212 "SELECT a FROM t\nLIMIT 10"
213 );
214 }
215
216 #[test]
217 fn an_offset_is_appended_with_the_ceiling() {
218 assert_eq!(
219 rewritten("SELECT a FROM t", 10, Some(20)),
220 "SELECT a FROM t\nLIMIT 10 OFFSET 20"
221 );
222 }
223
224 #[test]
226 fn a_query_ending_in_a_line_comment_still_receives_a_usable_ceiling() {
227 for sql in ["SELECT a FROM t -- note", "SELECT a FROM t # note"] {
228 let text = rewritten(sql, 4, None);
229 assert_eq!(text, format!("{sql}\nLIMIT 4"), "input {sql:?}");
230 let parsed = parse(&text, ParseLimits::default()).expect("the result parses");
231 assert_eq!(parsed.len(), 1, "input {sql:?}");
232 }
233 }
234
235 #[test]
238 fn a_query_with_an_unnamed_column_is_not_broken() {
239 let text = rewritten("SELECT a + 1 FROM t", 5, None);
240 assert_eq!(text, "SELECT a + 1 FROM t\nLIMIT 5");
241 assert!(
242 !text.to_uppercase().contains("FROM ("),
243 "the caller's query is never wrapped"
244 );
245 }
246
247 #[test]
249 fn a_query_with_a_repeated_column_name_is_not_broken() {
250 assert_eq!(
251 rewritten("SELECT t.a, u.a FROM t JOIN u ON u.id = t.id", 5, None),
252 "SELECT t.a, u.a FROM t JOIN u ON u.id = t.id\nLIMIT 5"
253 );
254 }
255
256 #[test]
257 fn the_callers_own_bytes_are_left_untouched() {
258 let sql = "SELECT a,\n b\nFROM t";
259 assert_eq!(rewritten(sql, 3, None), format!("{sql}\nLIMIT 3"));
260 }
261
262 #[test]
265 fn a_conditional_comment_survives_the_rewrite() {
266 let sql = "SELECT /*!40001 SQL_NO_CACHE */ a FROM t";
267 assert_eq!(rewritten(sql, 7, None), format!("{sql}\nLIMIT 7"));
268 }
269
270 #[test]
271 fn a_trailing_separator_is_removed_before_the_clause_is_appended() {
272 assert_eq!(
273 rewritten("SELECT a FROM t;", 4, None),
274 "SELECT a FROM t\nLIMIT 4"
275 );
276 assert_eq!(
277 rewritten("SELECT a FROM t ; ", 4, None),
278 "SELECT a FROM t \nLIMIT 4"
279 );
280 }
281
282 #[test]
283 fn a_separator_that_ends_a_literal_is_not_mistaken_for_a_trailing_one() {
284 assert_eq!(
285 rewritten("SELECT 'a;'", 4, None),
286 "SELECT 'a;'\nLIMIT 4",
287 "the last character is inside a literal"
288 );
289 }
290
291 #[test]
292 fn a_separator_that_ends_a_comment_is_not_mistaken_for_a_trailing_one() {
293 assert_eq!(
294 rewritten("SELECT a FROM t /* ; */", 4, None),
295 "SELECT a FROM t /* ; */\nLIMIT 4"
296 );
297 }
298
299 #[test]
300 fn a_query_whose_own_ceiling_is_inside_this_one_is_left_as_written() {
301 assert_eq!(
302 rewritten("SELECT a FROM t LIMIT 5", 10, None),
303 "SELECT a FROM t LIMIT 5"
304 );
305 assert_eq!(
306 rewritten("SELECT a FROM t LIMIT 10", 10, None),
307 "SELECT a FROM t LIMIT 10"
308 );
309 }
310
311 #[test]
312 fn a_query_whose_own_ceiling_is_wider_than_this_one_is_refused() {
313 assert_eq!(
314 refusal("SELECT a FROM t LIMIT 1000", 10, None).code(),
315 "rewrite.limit_already_present"
316 );
317 }
318
319 #[test]
320 fn a_query_with_its_own_row_clause_is_refused_when_an_offset_is_asked_for() {
321 assert_eq!(
322 refusal("SELECT a FROM t LIMIT 5", 10, Some(2)).code(),
323 "rewrite.limit_already_present"
324 );
325 }
326
327 #[test]
328 fn a_query_with_its_own_offset_is_refused() {
329 for sql in [
330 "SELECT a FROM t LIMIT 5 OFFSET 3",
331 "SELECT a FROM t LIMIT 3, 5",
332 ] {
333 assert_eq!(
334 refusal(sql, 10, None).code(),
335 "rewrite.limit_already_present",
336 "input {sql:?}"
337 );
338 }
339 }
340
341 #[test]
342 fn a_query_ending_in_a_row_lock_is_refused_rather_than_rewritten() {
343 assert_eq!(
344 refusal("SELECT a FROM t FOR UPDATE", 10, None).code(),
345 "rewrite.tail_clause_present"
346 );
347 }
348
349 #[test]
350 fn a_statement_that_does_not_return_rows_is_refused() {
351 for sql in [
352 "DELETE FROM t",
353 "CREATE TABLE t (a INT)",
354 "SET autocommit = 1",
355 ] {
356 assert_eq!(
357 refusal(sql, 10, None).code(),
358 "rewrite.not_a_single_query",
359 "input {sql:?}"
360 );
361 }
362 }
363
364 #[test]
365 fn more_than_one_statement_is_refused() {
366 assert_eq!(
367 refusal("SELECT 1; SELECT 2", 10, None).code(),
368 "rewrite.not_a_single_query"
369 );
370 }
371
372 #[test]
373 fn a_zero_ceiling_is_refused() {
374 assert_eq!(
375 refusal("SELECT a FROM t", 0, None).code(),
376 "rewrite.zero_limit"
377 );
378 }
379
380 #[test]
381 fn the_result_is_still_a_single_readable_query() {
382 let text = rewritten("SELECT a FROM t WHERE b = 'x;y'", 2, Some(1));
383 let parsed = parse(&text, ParseLimits::default()).expect("the result parses");
384 assert_eq!(parsed.len(), 1);
385 assert!(text.contains("'x;y'"), "the literal survives, got {text:?}");
386 }
387
388 #[test]
389 fn a_union_receives_the_ceiling_at_the_end() {
390 assert_eq!(
391 rewritten("SELECT a FROM t UNION SELECT b FROM u", 6, None),
392 "SELECT a FROM t UNION SELECT b FROM u\nLIMIT 6"
393 );
394 }
395
396 #[test]
397 fn a_query_with_a_common_table_expression_receives_the_ceiling() {
398 assert_eq!(
399 rewritten("WITH c AS (SELECT a FROM t) SELECT a FROM c", 6, None),
400 "WITH c AS (SELECT a FROM t) SELECT a FROM c\nLIMIT 6"
401 );
402 }
403
404 #[test]
406 fn a_ceiling_inside_a_sub_query_does_not_block_the_outer_one() {
407 assert_eq!(
408 rewritten("SELECT a FROM (SELECT a FROM t LIMIT 3) AS s", 6, None),
409 "SELECT a FROM (SELECT a FROM t LIMIT 3) AS s\nLIMIT 6"
410 );
411 }
412}