use crate::error::{Failure, Result};
use crate::sql::parse::{parse, ParseLimits};
use crate::sql::strip::strip;
use sqlparser::ast::{Expr, LimitClause, Query, Statement, Value};
pub fn with_limit(
sql: &str,
limit: usize,
offset: Option<usize>,
limits: ParseLimits,
) -> Result<String> {
if limit == 0 {
return Err(
Failure::validation("rewrite.zero_limit", "the row ceiling is zero")
.with_hint("ask for at least one row"),
);
}
let statements = parse(sql, limits)?;
let query = single_query(&statements)?;
refuse_unappendable_clauses(query)?;
match query.limit_clause.as_ref() {
None => Ok(append_clause(sql, limit, offset)),
Some(existing) => reuse_existing_clause(sql, existing, limit, offset),
}
}
fn single_query(statements: &[Statement]) -> Result<&Query> {
let [Statement::Query(query)] = statements else {
return Err(Failure::validation(
"rewrite.not_a_single_query",
"a row ceiling can only be imposed on a single query",
)
.with_hint("send one statement that returns rows"));
};
Ok(query)
}
fn refuse_unappendable_clauses(query: &Query) -> Result<()> {
let blocker = if query.fetch.is_some() {
Some(("a fetch clause", "fetch"))
} else if !query.locks.is_empty() {
Some(("a row locking clause", "for update"))
} else if query.for_clause.is_some() {
Some(("a for clause", "for xml"))
} else if query.settings.is_some() {
Some(("a settings clause", "settings"))
} else if query.format_clause.is_some() {
Some(("a format clause", "format"))
} else if !query.pipe_operators.is_empty() {
Some(("a pipe operator", "|>"))
} else {
None
};
match blocker {
None => Ok(()),
Some((clause, keyword)) => Err(Failure::validation(
"rewrite.tail_clause_present",
format!("the query ends in {clause}, so a row ceiling cannot be appended"),
)
.with_detail("detail", keyword)
.with_hint("remove that clause, or ask for the rows without a ceiling")),
}
}
fn reuse_existing_clause(
sql: &str,
existing: &LimitClause,
limit: usize,
offset: Option<usize>,
) -> Result<String> {
let already = existing_row_count(existing);
match (already, offset) {
(Some(rows), None) if rows <= limit => Ok(sql.to_owned()),
_ => Err(Failure::validation(
"rewrite.limit_already_present",
"the query already carries a row clause of its own",
)
.with_hint(
"remove the row clause from the query, or ask for a ceiling no smaller than it",
)),
}
}
fn existing_row_count(clause: &LimitClause) -> Option<usize> {
let LimitClause::LimitOffset {
limit: Some(limit),
offset: None,
limit_by,
} = clause
else {
return None;
};
if !limit_by.is_empty() {
return None;
}
let Expr::Value(value) = limit else {
return None;
};
match &value.value {
Value::Number(digits, _) => digits.parse::<usize>().ok(),
_ => None,
}
}
fn append_clause(sql: &str, limit: usize, offset: Option<usize>) -> String {
use std::fmt::Write as _;
let mut text = trim_trailing_separator(sql).to_owned();
if let Some(start) = offset {
let _ = write!(text, "\nLIMIT {limit} OFFSET {start}");
} else {
let _ = write!(text, "\nLIMIT {limit}");
}
text
}
fn trim_trailing_separator(sql: &str) -> &str {
let blanked = strip(sql);
let text = blanked.text();
match text.rfind(|character: char| !character.is_whitespace()) {
Some(index) if text.as_bytes().get(index) == Some(&b';') => &sql[..index],
_ => sql,
}
}
#[cfg(test)]
mod tests {
use super::*;
fn rewritten(sql: &str, limit: usize, offset: Option<usize>) -> String {
with_limit(sql, limit, offset, ParseLimits::default()).expect("the query accepts a ceiling")
}
fn refusal(sql: &str, limit: usize, offset: Option<usize>) -> Failure {
with_limit(sql, limit, offset, ParseLimits::default())
.expect_err("the query must be refused")
}
#[test]
fn a_query_without_a_row_clause_receives_one() {
assert_eq!(
rewritten("SELECT a FROM t", 10, None),
"SELECT a FROM t\nLIMIT 10"
);
}
#[test]
fn an_offset_is_appended_with_the_ceiling() {
assert_eq!(
rewritten("SELECT a FROM t", 10, Some(20)),
"SELECT a FROM t\nLIMIT 10 OFFSET 20"
);
}
#[test]
fn a_query_ending_in_a_line_comment_still_receives_a_usable_ceiling() {
for sql in ["SELECT a FROM t -- note", "SELECT a FROM t # note"] {
let text = rewritten(sql, 4, None);
assert_eq!(text, format!("{sql}\nLIMIT 4"), "input {sql:?}");
let parsed = parse(&text, ParseLimits::default()).expect("the result parses");
assert_eq!(parsed.len(), 1, "input {sql:?}");
}
}
#[test]
fn a_query_with_an_unnamed_column_is_not_broken() {
let text = rewritten("SELECT a + 1 FROM t", 5, None);
assert_eq!(text, "SELECT a + 1 FROM t\nLIMIT 5");
assert!(
!text.to_uppercase().contains("FROM ("),
"the caller's query is never wrapped"
);
}
#[test]
fn a_query_with_a_repeated_column_name_is_not_broken() {
assert_eq!(
rewritten("SELECT t.a, u.a FROM t JOIN u ON u.id = t.id", 5, None),
"SELECT t.a, u.a FROM t JOIN u ON u.id = t.id\nLIMIT 5"
);
}
#[test]
fn the_callers_own_bytes_are_left_untouched() {
let sql = "SELECT a,\n b\nFROM t";
assert_eq!(rewritten(sql, 3, None), format!("{sql}\nLIMIT 3"));
}
#[test]
fn a_conditional_comment_survives_the_rewrite() {
let sql = "SELECT /*!40001 SQL_NO_CACHE */ a FROM t";
assert_eq!(rewritten(sql, 7, None), format!("{sql}\nLIMIT 7"));
}
#[test]
fn a_trailing_separator_is_removed_before_the_clause_is_appended() {
assert_eq!(
rewritten("SELECT a FROM t;", 4, None),
"SELECT a FROM t\nLIMIT 4"
);
assert_eq!(
rewritten("SELECT a FROM t ; ", 4, None),
"SELECT a FROM t \nLIMIT 4"
);
}
#[test]
fn a_separator_that_ends_a_literal_is_not_mistaken_for_a_trailing_one() {
assert_eq!(
rewritten("SELECT 'a;'", 4, None),
"SELECT 'a;'\nLIMIT 4",
"the last character is inside a literal"
);
}
#[test]
fn a_separator_that_ends_a_comment_is_not_mistaken_for_a_trailing_one() {
assert_eq!(
rewritten("SELECT a FROM t /* ; */", 4, None),
"SELECT a FROM t /* ; */\nLIMIT 4"
);
}
#[test]
fn a_query_whose_own_ceiling_is_inside_this_one_is_left_as_written() {
assert_eq!(
rewritten("SELECT a FROM t LIMIT 5", 10, None),
"SELECT a FROM t LIMIT 5"
);
assert_eq!(
rewritten("SELECT a FROM t LIMIT 10", 10, None),
"SELECT a FROM t LIMIT 10"
);
}
#[test]
fn a_query_whose_own_ceiling_is_wider_than_this_one_is_refused() {
assert_eq!(
refusal("SELECT a FROM t LIMIT 1000", 10, None).code(),
"rewrite.limit_already_present"
);
}
#[test]
fn a_query_with_its_own_row_clause_is_refused_when_an_offset_is_asked_for() {
assert_eq!(
refusal("SELECT a FROM t LIMIT 5", 10, Some(2)).code(),
"rewrite.limit_already_present"
);
}
#[test]
fn a_query_with_its_own_offset_is_refused() {
for sql in [
"SELECT a FROM t LIMIT 5 OFFSET 3",
"SELECT a FROM t LIMIT 3, 5",
] {
assert_eq!(
refusal(sql, 10, None).code(),
"rewrite.limit_already_present",
"input {sql:?}"
);
}
}
#[test]
fn a_query_ending_in_a_row_lock_is_refused_rather_than_rewritten() {
assert_eq!(
refusal("SELECT a FROM t FOR UPDATE", 10, None).code(),
"rewrite.tail_clause_present"
);
}
#[test]
fn a_statement_that_does_not_return_rows_is_refused() {
for sql in [
"DELETE FROM t",
"CREATE TABLE t (a INT)",
"SET autocommit = 1",
] {
assert_eq!(
refusal(sql, 10, None).code(),
"rewrite.not_a_single_query",
"input {sql:?}"
);
}
}
#[test]
fn more_than_one_statement_is_refused() {
assert_eq!(
refusal("SELECT 1; SELECT 2", 10, None).code(),
"rewrite.not_a_single_query"
);
}
#[test]
fn a_zero_ceiling_is_refused() {
assert_eq!(
refusal("SELECT a FROM t", 0, None).code(),
"rewrite.zero_limit"
);
}
#[test]
fn the_result_is_still_a_single_readable_query() {
let text = rewritten("SELECT a FROM t WHERE b = 'x;y'", 2, Some(1));
let parsed = parse(&text, ParseLimits::default()).expect("the result parses");
assert_eq!(parsed.len(), 1);
assert!(text.contains("'x;y'"), "the literal survives, got {text:?}");
}
#[test]
fn a_union_receives_the_ceiling_at_the_end() {
assert_eq!(
rewritten("SELECT a FROM t UNION SELECT b FROM u", 6, None),
"SELECT a FROM t UNION SELECT b FROM u\nLIMIT 6"
);
}
#[test]
fn a_query_with_a_common_table_expression_receives_the_ceiling() {
assert_eq!(
rewritten("WITH c AS (SELECT a FROM t) SELECT a FROM c", 6, None),
"WITH c AS (SELECT a FROM t) SELECT a FROM c\nLIMIT 6"
);
}
#[test]
fn a_ceiling_inside_a_sub_query_does_not_block_the_outer_one() {
assert_eq!(
rewritten("SELECT a FROM (SELECT a FROM t LIMIT 3) AS s", 6, None),
"SELECT a FROM (SELECT a FROM t LIMIT 3) AS s\nLIMIT 6"
);
}
}