Skip to main content

mariadb_cli/exec/
plan.rs

1//! What a statement would do, asked two different ways.
2//!
3//! # The server's plan, which never runs the statement
4//!
5//! [`explain`] asks the server how it would answer, and the statement itself is
6//! never executed. Asking for the plan of a statement that removes rows removes
7//! nothing, which is what makes the plan safe to ask for on any statement.
8//!
9//! # The measured plan, which does run it
10//!
11//! [`measure`] asks the server for the same plan with real numbers instead of
12//! estimated ones, and the server produces those numbers by executing the
13//! statement. That makes it a change in every sense that matters: it is
14//! confirmed like one, it obeys the ceiling like one, and it is refused without
15//! confirmation like one. Treating it as a plan because it is spelled like one
16//! would let a removal through under the name of an investigation.
17//!
18//! # This tool's own prediction
19//!
20//! [`estimate`] answers the same question without asking the server anything:
21//! it reads the statement's own restriction and derives the count that carries
22//! it. That prediction is what the ceiling on affected rows is checked against
23//! before the change is sent, and what a simulation reports back.
24
25use super::prepare::Gated;
26use crate::error::{Failure, Result};
27use crate::output::envelope::Gap;
28use crate::sql::classify::Classification;
29use sqlparser::ast::{Expr, FromTable, Statement, TableFactor, TableObject};
30
31/// The table one change acts on.
32#[derive(Debug, Clone, PartialEq, Eq)]
33pub struct Target {
34    /// The database holding it, when the statement named one.
35    pub database: Option<String>,
36    /// The table itself.
37    pub table: String,
38}
39
40/// How many rows a change is expected to touch, decided before it is sent.
41#[derive(Debug, Clone, PartialEq, Eq)]
42pub enum Estimate {
43    /// The statement itself says how many rows it carries.
44    Known(u64),
45    /// A count derived from the statement's own restriction, to be run first.
46    Derived(String),
47    /// No count can be derived, and the reason is named rather than assumed.
48    Unavailable(&'static str),
49}
50
51/// The table a change acts on, when the statement names exactly one.
52#[must_use]
53pub fn target_of(statement: &Statement) -> Option<Target> {
54    match statement {
55        Statement::Delete(delete) => {
56            let (FromTable::WithFromKeyword(tables) | FromTable::WithoutKeyword(tables)) =
57                &delete.from;
58            match tables.as_slice() {
59                [only] if only.joins.is_empty() => factor(&only.relation),
60                _ => None,
61            }
62        }
63        Statement::Update(update) if update.table.joins.is_empty() => {
64            factor(&update.table.relation)
65        }
66        Statement::Insert(insert) => match &insert.table {
67            TableObject::TableName(name) => Some(split_name(&name.to_string())),
68            // A destination that is produced rather than stored names no table
69            // whose rows a ceiling could be checked against.
70            _ => None,
71        },
72        _ => None,
73    }
74}
75
76/// Reads a target out of one table reference, when it is a plain named table.
77fn factor(relation: &TableFactor) -> Option<Target> {
78    match relation {
79        TableFactor::Table { name, .. } => Some(split_name(&name.to_string())),
80        _ => None,
81    }
82}
83
84/// Splits a possibly qualified name into its database and its table.
85fn split_name(rendered: &str) -> Target {
86    let cleaned = rendered.replace('`', "");
87    cleaned.rsplit_once('.').map_or_else(
88        || Target {
89            database: None,
90            table: cleaned.clone(),
91        },
92        |(database, table)| Target {
93            database: Some(database.to_owned()),
94            table: table.to_owned(),
95        },
96    )
97}
98
99/// The restriction a change carries, when it carries one.
100#[must_use]
101pub fn restriction_of(statement: &Statement) -> Option<&Expr> {
102    match statement {
103        Statement::Delete(delete) => delete.selection.as_ref(),
104        Statement::Update(update) => update.selection.as_ref(),
105        _ => None,
106    }
107}
108
109/// Predicts how many rows a change would touch, from the statement alone.
110#[must_use]
111pub fn estimate(statement: &Statement) -> Estimate {
112    match statement {
113        Statement::Delete(_) | Statement::Update(_) => derive_count(statement),
114        Statement::Insert(insert) => insert_rows(insert),
115        _ => Estimate::Unavailable("this statement shape carries no derivable row count"),
116    }
117}
118
119/// Builds the count that carries the same restriction as the change.
120///
121/// The restriction travels unchanged, so the count counts exactly the rows the
122/// change would reach and never a wider set.
123fn derive_count(statement: &Statement) -> Estimate {
124    let Some(target) = target_of(statement) else {
125        return Estimate::Unavailable("the change names more than one table");
126    };
127    let Some(restriction) = restriction_of(statement) else {
128        return Estimate::Unavailable("the change carries no restriction");
129    };
130    let quoted = match &target.database {
131        Some(database) => crate::sql::ident::quote_qualified(&[database, &target.table]),
132        None => crate::sql::ident::quote(&target.table),
133    };
134    quoted.map_or(
135        Estimate::Unavailable("the target name cannot be quoted safely"),
136        |name| Estimate::Derived(format!("SELECT COUNT(*) FROM {name} WHERE {restriction}")),
137    )
138}
139
140/// Counts the rows an insert carries in its own text, when it carries them.
141fn insert_rows(insert: &sqlparser::ast::Insert) -> Estimate {
142    if !insert.assignments.is_empty() {
143        return Estimate::Known(1);
144    }
145    let Some(source) = insert.source.as_ref() else {
146        return Estimate::Unavailable("the insert names no source of rows");
147    };
148    match source.body.as_ref() {
149        sqlparser::ast::SetExpr::Values(values) => Estimate::Known(values.rows.len() as u64),
150        _ => Estimate::Unavailable("the insert takes its rows from a query"),
151    }
152}
153
154/// The values that belong to the derived count, taken from the change's own.
155///
156/// A change binds the values of its assignments before the values of its
157/// restriction, and the derived count carries only the restriction. Taking the
158/// trailing values is therefore exactly the restriction's own, in order.
159#[must_use]
160pub fn count_values<'a>(count_sql: &str, values: &'a [String]) -> &'a [String] {
161    let markers = count_sql.chars().filter(|c| *c == '?').count();
162    &values[values.len().saturating_sub(markers)..]
163}
164
165/// The gap left when the ceiling could not be applied at all.
166///
167/// A ceiling silently skipped is worse than one that was never asked for, so
168/// the answer says which rows it could not count and why.
169#[must_use]
170pub fn undecidable_gap(reason: &str) -> Gap {
171    Gap::new(
172        "affected_row_ceiling",
173        "not_derivable",
174        format!("the ceiling was not applied because {reason}"),
175    )
176}
177
178/// Builds the request for the server's own plan of a statement.
179///
180/// The statement is wrapped rather than rewritten, so every byte the caller
181/// wrote reaches the server unchanged.
182///
183/// # Errors
184///
185/// Returns a validation failure when the caller's text already asks for a plan
186/// or for a measurement, because wrapping it again would ask a different
187/// question than the one the caller wrote.
188pub fn explain(gated: &Gated, trace: bool) -> Result<String> {
189    refuse_nested_plan(gated)?;
190    let form = if trace { "FORMAT=JSON " } else { "" };
191    Ok(format!("EXPLAIN {form}{}", gated.sql()))
192}
193
194/// Builds the request for the plan with measured rather than estimated numbers.
195///
196/// # Errors
197///
198/// Returns a validation failure when the caller's text already asks for a plan
199/// or for a measurement.
200pub fn measure(gated: &Gated) -> Result<String> {
201    refuse_nested_plan(gated)?;
202    Ok(format!("ANALYZE {}", gated.sql()))
203}
204
205/// Refuses a text that already asks for a plan or a measurement.
206///
207/// # Errors
208///
209/// Returns a validation failure naming what the text already asks for.
210fn refuse_nested_plan(gated: &Gated) -> Result<()> {
211    if !matches!(gated.kind(), "explain" | "describe" | "analyze") {
212        return Ok(());
213    }
214    Err(Failure::validation(
215        "sql.already_a_plan",
216        "the statement already asks for a plan or a measurement",
217    )
218    .with_hint("pass the statement itself, without a leading plan or measurement word"))
219}
220
221/// The request for the plan of a statement running on another connection.
222///
223/// The identifier travels as a bound value, so a caller who took it from a
224/// listing cannot turn it into anything else.
225#[must_use]
226pub fn running(connection_id: u64) -> (String, Vec<String>) {
227    (
228        "SHOW EXPLAIN FOR ?".to_owned(),
229        vec![connection_id.to_string()],
230    )
231}
232
233/// The gap declared when the server offers no reasoning for its choice.
234#[must_use]
235pub fn absent_trace() -> Gap {
236    Gap::new(
237        "optimiser_trace",
238        "unsupported",
239        "this server does not offer the reasoning behind its choice of plan",
240    )
241}
242
243/// Refuses a statement asked for a plan that this tool cannot plan.
244///
245/// # Errors
246///
247/// Returns a validation failure when the statement has no shape a plan applies
248/// to.
249pub fn refuse_unplannable(gated: &Gated) -> Result<()> {
250    if matches!(
251        gated.classification(),
252        Classification::Read | Classification::Change
253    ) {
254        return Ok(());
255    }
256    Err(Failure::validation(
257        "sql.not_plannable",
258        format!(
259            "the server plans statements that read or change rows, and this is a {}",
260            gated.classification().as_str()
261        ),
262    )
263    .with_detail("detail", gated.classification().as_str())
264    .with_hint("ask for the plan of a statement that reads or changes rows"))
265}
266
267#[cfg(test)]
268mod tests {
269    use super::*;
270    use crate::cli::globals::RowCountMode;
271    use crate::exec::{prepare, Policy};
272    use crate::sql::ParseLimits;
273    use std::time::Duration;
274
275    fn policy() -> Policy {
276        Policy::new(
277            ParseLimits::default(),
278            100,
279            50,
280            RowCountMode::Changed,
281            ";".to_owned(),
282            Duration::from_secs(30),
283        )
284    }
285
286    fn gated(sql: &str) -> Gated {
287        prepare::gate(sql, &[], &policy()).expect("admitted")
288    }
289
290    fn first(sql: &str) -> Statement {
291        crate::sql::parse(sql, ParseLimits::default()).expect("parses")[0].clone()
292    }
293
294    /// The ceiling is checked from a count carrying the statement's own
295    /// restriction, so it counts the rows the change would reach and no others.
296    #[test]
297    fn the_derived_count_carries_the_same_restriction_as_the_change() {
298        let Estimate::Derived(sql) = estimate(&first("DELETE FROM pedidos WHERE pago = 0")) else {
299            panic!("a restricted removal must derive a count");
300        };
301        assert_eq!(sql, "SELECT COUNT(*) FROM `pedidos` WHERE pago = 0");
302
303        let Estimate::Derived(sql) = estimate(&first(
304            "UPDATE pedidos SET pago = 1 WHERE id > 7 AND total < 100",
305        )) else {
306            panic!("a restricted update must derive a count");
307        };
308        assert!(sql.starts_with("SELECT COUNT(*) FROM `pedidos` WHERE "));
309        assert!(
310            sql.contains("id > 7"),
311            "the restriction survives whole: {sql}"
312        );
313        assert!(sql.contains("total < 100"), "every part survives: {sql}");
314    }
315
316    #[test]
317    fn a_qualified_target_keeps_its_database_in_the_derived_count() {
318        let Estimate::Derived(sql) = estimate(&first("DELETE FROM loja.pedidos WHERE id = 1"))
319        else {
320            panic!("must derive");
321        };
322        assert_eq!(sql, "SELECT COUNT(*) FROM `loja`.`pedidos` WHERE id = 1");
323    }
324
325    /// A change binds its assignments before its restriction, so the derived
326    /// count must take the trailing values and not the leading ones.
327    #[test]
328    fn the_derived_count_binds_only_the_values_of_the_restriction() {
329        let values = vec!["novo".to_owned(), "7".to_owned()];
330        assert_eq!(
331            count_values("SELECT COUNT(*) FROM `t` WHERE id = ?", &values),
332            &["7".to_owned()]
333        );
334        assert_eq!(
335            count_values("SELECT COUNT(*) FROM `t` WHERE a = ? AND b = ?", &values),
336            values.as_slice()
337        );
338        assert!(count_values("SELECT COUNT(*) FROM `t` WHERE a = 1", &values).is_empty());
339    }
340
341    #[test]
342    fn an_insert_of_written_rows_needs_no_round_trip_to_be_counted() {
343        assert_eq!(
344            estimate(&first("INSERT INTO t (a) VALUES (1), (2), (3)")),
345            Estimate::Known(3)
346        );
347        assert_eq!(
348            estimate(&first("INSERT INTO t SET a = 1")),
349            Estimate::Known(1)
350        );
351    }
352
353    /// A count that cannot be derived is declared, never assumed to be zero.
354    #[test]
355    fn a_count_that_cannot_be_derived_says_so_instead_of_guessing() {
356        assert!(matches!(
357            estimate(&first("INSERT INTO t (a) SELECT a FROM u")),
358            Estimate::Unavailable(_)
359        ));
360        assert!(matches!(
361            estimate(&first("DELETE FROM t")),
362            Estimate::Unavailable(_)
363        ));
364        assert_eq!(undecidable_gap("no restriction").cause, "not_derivable");
365    }
366
367    #[test]
368    fn the_target_table_is_read_from_the_tree_for_every_change_shape() {
369        assert_eq!(
370            target_of(&first("DELETE FROM loja.pedidos WHERE a = 1")),
371            Some(Target {
372                database: Some("loja".to_owned()),
373                table: "pedidos".to_owned()
374            })
375        );
376        assert_eq!(
377            target_of(&first("UPDATE pedidos SET a = 1")).map(|t| t.table),
378            Some("pedidos".to_owned())
379        );
380        assert_eq!(
381            target_of(&first("INSERT INTO pedidos (a) VALUES (1)")).map(|t| t.table),
382            Some("pedidos".to_owned())
383        );
384        assert_eq!(target_of(&first("SELECT 1")), None, "a read has no target");
385        assert_eq!(
386            target_of(&first("UPDATE t JOIN u ON u.id = t.id SET t.a = 1")),
387            None,
388            "a change over several tables names no single target"
389        );
390    }
391
392    /// Asking for a plan must never execute the statement, so the request is
393    /// built around the caller's text and the text itself is untouched.
394    #[test]
395    fn the_plan_of_a_destructive_statement_wraps_it_and_changes_nothing() {
396        let request = explain(&gated("DELETE FROM pedidos WHERE id = 7"), false).expect("plans");
397        assert_eq!(request, "EXPLAIN DELETE FROM pedidos WHERE id = 7");
398        assert!(
399            request.starts_with("EXPLAIN "),
400            "the statement is only ever wrapped"
401        );
402    }
403
404    #[test]
405    fn the_reasoning_behind_the_choice_is_asked_for_only_when_requested() {
406        assert_eq!(
407            explain(&gated("SELECT a FROM t"), false).expect("plans"),
408            "EXPLAIN SELECT a FROM t"
409        );
410        assert_eq!(
411            explain(&gated("SELECT a FROM t"), true).expect("plans"),
412            "EXPLAIN FORMAT=JSON SELECT a FROM t"
413        );
414    }
415
416    /// A measurement runs the statement, so it is built from the word that
417    /// says so and is treated as a change everywhere else.
418    #[test]
419    fn a_measurement_is_built_from_the_word_that_runs_the_statement() {
420        assert_eq!(
421            measure(&gated("SELECT a FROM t")).expect("measures"),
422            "ANALYZE SELECT a FROM t"
423        );
424    }
425
426    #[test]
427    fn a_text_that_already_asks_for_a_plan_is_refused_rather_than_wrapped_twice() {
428        for sql in ["EXPLAIN SELECT a FROM t", "DESCRIBE t"] {
429            assert_eq!(
430                explain(&gated(sql), false).expect_err("must refuse").code(),
431                "sql.already_a_plan",
432                "input {sql:?}"
433            );
434        }
435        assert_eq!(
436            measure(&gated("EXPLAIN SELECT 1"))
437                .expect_err("must refuse")
438                .code(),
439            "sql.already_a_plan"
440        );
441    }
442
443    #[test]
444    fn a_statement_the_server_does_not_plan_is_refused_with_its_own_kind_named() {
445        let failure = refuse_unplannable(&gated("SET autocommit = 1")).expect_err("must refuse");
446        assert_eq!(failure.code(), "sql.not_plannable");
447        assert!(failure.message().contains("administration"));
448    }
449
450    #[test]
451    fn a_read_and_a_change_are_both_plannable() {
452        assert!(refuse_unplannable(&gated("SELECT 1")).is_ok());
453        assert!(refuse_unplannable(&gated("DELETE FROM t WHERE a = 1")).is_ok());
454    }
455
456    /// A connection identifier is a value the caller took from a listing, so it
457    /// travels bound and never spliced into the request.
458    #[test]
459    fn the_identifier_of_a_running_statement_travels_as_a_bound_value() {
460        let (sql, values) = running(4711);
461        assert_eq!(sql, "SHOW EXPLAIN FOR ?");
462        assert_eq!(values, vec!["4711".to_owned()]);
463        assert!(!sql.contains("4711"), "the value is never in the text");
464    }
465
466    #[test]
467    fn a_server_without_the_reasoning_declares_the_gap_rather_than_failing() {
468        let gap = absent_trace();
469        assert_eq!(gap.object, "optimiser_trace");
470        assert_eq!(gap.cause, "unsupported");
471    }
472}