Skip to main content

mariadb_cli/sql/
rewrite.rs

1//! Imposition of this tool's own row ceiling on a caller's query.
2//!
3//! # Why the query is not wrapped in a sub-query
4//!
5//! The Rust reference implementation paginates by wrapping the caller's query
6//! as a sub-query and selecting from it. That breaks two legitimate queries:
7//!
8//! * `SELECT a + 1 FROM t` has an unnamed column, and a derived table demands
9//!   that every column be named.
10//! * `SELECT t.a, u.a FROM t JOIN u` has a repeated column name, and a derived
11//!   table demands that every name be distinct.
12//!
13//! Labelling those columns would work, but it changes the names the caller
14//! asked for, and the caller reads results by name. So this module does not
15//! wrap and does not relabel. It reads the syntax tree to prove the query has
16//! no row clause of its own, then appends the ceiling to the original text.
17//!
18//! # Why the text is appended rather than re-rendered
19//!
20//! Rendering the tree back to text would drop a conditional comment, whose body
21//! is executable on this server, and would normalise spacing the caller wrote.
22//! Appending leaves every byte the caller sent untouched except the suffix.
23//!
24//! A query that already carries a clause this module cannot append after is
25//! refused with a named reason rather than silently rewritten.
26
27use crate::error::{Failure, Result};
28use crate::sql::parse::{parse, ParseLimits};
29use crate::sql::strip::strip;
30use sqlparser::ast::{Expr, LimitClause, Query, Statement, Value};
31
32/// Imposes a row ceiling and a starting offset on a caller's query.
33///
34/// When the query already carries a row ceiling of its own that is no larger
35/// than `limit`, and no offset is asked for, the text is returned unchanged:
36/// the caller's own ceiling is already inside this one.
37///
38/// # Errors
39///
40/// Returns a validation failure when the text is not a single query, when
41/// `limit` is zero, or when the query carries a clause this module cannot
42/// append after.
43pub 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
66/// Returns the one query the text holds, or says why it is not one.
67fn 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
78/// Refuses a query whose tail this module cannot append after.
79///
80/// Each of these clauses is written after the row clause on this server, so a
81/// suffix would land in the wrong place and change what the statement means.
82fn refuse_unappendable_clauses(query: &Query) -> Result<()> {
83    // The prose names the clause the way a reader of the message would, and the
84    // keyword beside it is what the catalogue substitutes, because a keyword
85    // reads the same in every language the catalogue carries.
86    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
113/// Decides what to do with a row clause the caller already wrote.
114fn 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        // The caller's own ceiling is already inside this one and no offset is
124        // asked for, so the text stands as written.
125        (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
136/// Reads a plain row count out of a row clause, when it holds one.
137///
138/// A clause whose count is an expression, a placeholder or a grouped count is
139/// not comparable, so `None` is returned and the caller refuses.
140fn 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
161/// Appends the row clause to the caller's own text.
162///
163/// The clause starts on a new line. A statement may legitimately end in a line
164/// comment, and a clause appended on the same line would be swallowed by it.
165fn append_clause(sql: &str, limit: usize, offset: Option<usize>) -> String {
166    use std::fmt::Write as _;
167
168    // Written straight into the statement rather than through a second string
169    // built only to be copied in and dropped.
170    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
179/// Cuts a trailing statement separator, and nothing else.
180///
181/// The separator is located over the blanked text, so a separator that is
182/// really the last character of a literal, of a comment or of a quoted name is
183/// never mistaken for one. Everything before it is returned exactly as the
184/// caller wrote it, including any blanked region, which is why the search runs
185/// over the blanked text but the cut is applied to the original.
186fn 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    /// A clause appended on the same line would land inside the comment.
225    #[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    /// The Rust reference implementation wraps the query in a derived table,
236    /// which a server refuses when a column has no name.
237    #[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    /// The same wrapping breaks when two columns share a name.
248    #[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    /// A conditional comment is executable here, so re-rendering the tree would
263    /// have dropped it.
264    #[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    /// An inner ceiling belongs to a sub-query and is not the query's own.
405    #[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}