qusql_type/

lib.rs

// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
#![cfg_attr(not(test), no_std)]
#![forbid(unsafe_code)]

//! Crate for typing SQL statements.
//!
//! ```
//! use qusql_type::{schema::parse_schemas, type_statement, TypeOptions,
//!     SQLDialect, SQLArguments, StatementType, Issues};
//!
//! let schemas = "
//!     CREATE TABLE `events` (
//!       `id` bigint(20) NOT NULL,
//!       `user` int(11) NOT NULL,
//!       `message` text NOT NULL
//!     );";
//!
//! let mut issues = Issues::new(schemas);
//! let schemas = parse_schemas(schemas,
//!     &mut issues,
//!     &TypeOptions::new().dialect(SQLDialect::MariaDB));
//! assert!(issues.is_ok());
//!
//! let sql = "SELECT `id`, `user`, `message` FROM `events` WHERE `id` = ?";
//! let mut issues = Issues::new(sql);
//! let stmt = type_statement(&schemas, sql, &mut issues,
//!     &TypeOptions::new().dialect(SQLDialect::MariaDB).arguments(SQLArguments::QuestionMark));
//! assert!(issues.is_ok());
//!
//! match stmt {
//!     StatementType::Select{columns, arguments} => {
//!         assert_eq!(columns.len(), 3);
//!         assert_eq!(arguments.len(), 1);
//!     }
//!     _ => panic!("Expected select statement")
//! };
//! ```
//!
//! ```
//! use qusql_type::{
//!     schema::parse_schemas, type_statement, TypeOptions,
//!     SQLDialect, SQLArguments, StatementType, Issues,
//! };
//!
//! let schema_sql = "
//!     CREATE TABLE notes (
//!         id    integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
//!         title text    NOT NULL,
//!         body  text
//!     );";
//!
//! let opts = TypeOptions::new().dialect(SQLDialect::PostgreSQL);
//!
//! let mut issues = Issues::new(schema_sql);
//! let schemas = parse_schemas(schema_sql, &mut issues, &opts);
//! assert!(issues.is_ok());
//!
//! let query = "SELECT id, title, body FROM notes WHERE id = $1";
//! let mut issues = Issues::new(query);
//! let stmt = type_statement(
//!     &schemas, query, &mut issues,
//!     &TypeOptions::new()
//!         .dialect(SQLDialect::PostgreSQL)
//!         .arguments(SQLArguments::Dollar),
//! );
//! assert!(issues.is_ok());
//!
//! match stmt {
//!     StatementType::Select { columns, arguments } => {
//!         // columns[0] -> id   : i32,  not-null
//!         // columns[1] -> title: String, not-null
//!         // columns[2] -> body : Option<String>
//!         // arguments[0] -> i32 (the type of `id`)
//!         println!("{} columns, {} arguments", columns.len(), arguments.len());
//!     }
//!     _ => panic!("expected SELECT"),
//! }
//! ```

extern crate alloc;

use alloc::vec::Vec;
pub use qusql_parse::{ByteToChar, Fragment, Issue, Issues, Level};
use qusql_parse::{ParseOptions, parse_statement};
use schema::Schemas;

mod type_;
mod type_binary_expression;
mod type_call;
mod type_delete;
mod type_expression;
mod type_function;
mod type_insert_replace;
mod type_lock;
mod type_reference;
mod type_select;
mod type_set;
mod type_statement;
mod type_truncate;
mod type_update;
mod typer;

pub mod schema;
pub use type_::{BaseType, FullType, Type};
pub use type_insert_replace::AutoIncrementId;
pub use type_select::SelectTypeColumn;
use typer::Typer;

pub use qusql_parse::{SQLArguments, SQLDialect};

/// Options used when typing sql or parsing a schema
#[derive(Debug, Default, Clone)]
pub struct TypeOptions {
    parse_options: ParseOptions,
    warn_unnamed_column_in_select: bool,
    warn_duplicate_column_in_select: bool,
}

impl TypeOptions {
    /// Produce new default options
    pub fn new() -> Self {
        Default::default()
    }

    /// Change what sql dialect is used
    pub fn dialect(self, dialect: SQLDialect) -> Self {
        Self {
            parse_options: self.parse_options.dialect(dialect),
            ..self
        }
    }

    /// Change how sql arguments are supplied
    pub fn arguments(self, arguments: SQLArguments) -> Self {
        Self {
            parse_options: self.parse_options.arguments(arguments),
            ..self
        }
    }

    /// Should we warn about unquoted identifiers
    pub fn warn_unquoted_identifiers(self, warn_unquoted_identifiers: bool) -> Self {
        Self {
            parse_options: self
                .parse_options
                .warn_unquoted_identifiers(warn_unquoted_identifiers),
            ..self
        }
    }

    /// Should we warn about keywords not in ALL CAPS
    pub fn warn_none_capital_keywords(self, warn_none_capital_keywords: bool) -> Self {
        Self {
            parse_options: self
                .parse_options
                .warn_none_capital_keywords(warn_none_capital_keywords),
            ..self
        }
    }

    /// Should we warn about unnamed columns in selects
    pub fn warn_unnamed_column_in_select(self, warn_unnamed_column_in_select: bool) -> Self {
        Self {
            warn_unnamed_column_in_select,
            ..self
        }
    }

    /// Should we warn about duplicate columns in selects
    pub fn warn_duplicate_column_in_select(self, warn_duplicate_column_in_select: bool) -> Self {
        Self {
            warn_duplicate_column_in_select,
            ..self
        }
    }

    /// Parse _LIST_ as special expression and type as a list of items
    pub fn list_hack(self, list_hack: bool) -> Self {
        Self {
            parse_options: self.parse_options.list_hack(list_hack),
            ..self
        }
    }
}

/// Key of argument
#[derive(Debug, Clone, Hash, PartialEq, Eq)]
pub enum ArgumentKey<'a> {
    /// Index of unnamed argument
    Index(usize),
    /// Name of named argument
    Identifier(&'a str),
}

/// Type information of typed statement
#[derive(Debug, Clone)]
pub enum StatementType<'a> {
    /// The statement was a select statement
    Select {
        /// The types and named of the columns return from the select
        columns: Vec<SelectTypeColumn<'a>>,
        /// The key and type of arguments to the query
        arguments: Vec<(ArgumentKey<'a>, FullType<'a>)>,
    },
    /// The statement is a delete statement
    Delete {
        /// The key and type of arguments to the query
        arguments: Vec<(ArgumentKey<'a>, FullType<'a>)>,
        /// If present, the types and names of the columns returned from the delete
        returning: Option<Vec<SelectTypeColumn<'a>>>,
    },
    /// The statement is an insert statement
    Insert {
        /// The insert happend in a table with a auto increment id row
        yield_autoincrement: AutoIncrementId,
        /// The key and type of arguments to the query
        arguments: Vec<(ArgumentKey<'a>, FullType<'a>)>,
        /// If present, the types and names of the columns returned from the insert
        returning: Option<Vec<SelectTypeColumn<'a>>>,
    },
    /// The statement is a update statement
    Update {
        /// The key and type of arguments to the query
        arguments: Vec<(ArgumentKey<'a>, FullType<'a>)>,
        /// If present, the types and names of the columns returned from the insert
        returning: Option<Vec<SelectTypeColumn<'a>>>,
    },
    /// The statement is a replace statement
    Replace {
        /// The key and type of arguments to the query
        arguments: Vec<(ArgumentKey<'a>, FullType<'a>)>,
        /// If present, the types and names of the columns returned from the replace
        returning: Option<Vec<SelectTypeColumn<'a>>>,
    },
    /// The statement is a truncate statement
    Truncate,
    /// The statement is a call statement
    Call {
        /// The key and type of arguments to the query
        arguments: Vec<(ArgumentKey<'a>, FullType<'a>)>,
    },
    /// The statement is a transaction control statement (BEGIN, COMMIT, END, START TRANSACTION)
    Transaction,
    /// The statement is a set statement
    Set,
    /// The statement is a table lock/unlock statement
    Lock,
    /// The query was not valid, errors are preset in issues
    Invalid,
}

/// Type an sql statement with respect to a given schema
pub fn type_statement<'a>(
    schemas: &'a Schemas<'a>,
    statement: &'a str,
    issues: &mut Issues<'a>,
    options: &TypeOptions,
) -> StatementType<'a> {
    if let Some(stmt) = parse_statement(statement, issues, &options.parse_options) {
        let mut typer = Typer {
            schemas,
            issues,
            reference_types: Vec::new(),
            outer_reference_types: Vec::new(),
            arg_types: Default::default(),
            options,
            with_schemas: Default::default(),
        };
        let t = type_statement::type_statement(&mut typer, &stmt);
        let arguments = typer.arg_types;
        match t {
            type_statement::InnerStatementType::Select(s) => StatementType::Select {
                columns: s.columns,
                arguments,
            },
            type_statement::InnerStatementType::Delete { returning } => StatementType::Delete {
                arguments,
                returning: returning.map(|r| r.columns),
            },
            type_statement::InnerStatementType::Insert {
                auto_increment_id,
                returning,
            } => StatementType::Insert {
                yield_autoincrement: auto_increment_id,
                arguments,
                returning: returning.map(|r| r.columns),
            },
            type_statement::InnerStatementType::Update { returning } => StatementType::Update {
                arguments,
                returning: returning.map(|r| r.columns),
            },
            type_statement::InnerStatementType::Replace { returning } => StatementType::Replace {
                arguments,
                returning: returning.map(|r| r.columns),
            },
            type_statement::InnerStatementType::Truncate => StatementType::Truncate,
            type_statement::InnerStatementType::Call => StatementType::Call { arguments },
            type_statement::InnerStatementType::Transaction => StatementType::Transaction,
            type_statement::InnerStatementType::Set => StatementType::Set,
            type_statement::InnerStatementType::Lock => StatementType::Lock,
            type_statement::InnerStatementType::Invalid => StatementType::Invalid,
        }
    } else {
        StatementType::Invalid
    }
}

#[cfg(test)]
mod tests {
    use alloc::vec::Vec;
    use codespan_reporting::{
        diagnostic::{Diagnostic, Label},
        files::SimpleFiles,
        term::{
            self,
            termcolor::{ColorChoice, StandardStream},
        },
    };
    use qusql_parse::{Identifier, Issue, Issues, Level, SQLArguments, SQLDialect};

    use crate::{
        ArgumentKey, AutoIncrementId, BaseType, FullType, SelectTypeColumn, StatementType, Type,
        TypeOptions, schema::parse_schemas, type_statement,
    };

    struct N<'a>(Option<&'a str>);
    impl<'a> alloc::fmt::Display for N<'a> {
        fn fmt(&self, f: &mut alloc::fmt::Formatter<'_>) -> alloc::fmt::Result {
            if let Some(v) = self.0 {
                v.fmt(f)
            } else {
                f.write_str("None")
            }
        }
    }

    struct N2<'a>(Option<Identifier<'a>>);
    impl<'a> alloc::fmt::Display for N2<'a> {
        fn fmt(&self, f: &mut alloc::fmt::Formatter<'_>) -> alloc::fmt::Result {
            if let Some(v) = &self.0 {
                v.fmt(f)
            } else {
                f.write_str("None")
            }
        }
    }

    fn check_no_errors(name: &str, src: &str, issues: &[Issue], errors: &mut usize) {
        let mut files = SimpleFiles::new();
        let file_id = files.add(name, &src);
        let writer = StandardStream::stderr(ColorChoice::Always);
        let config = codespan_reporting::term::Config::default();
        for issue in issues {
            let mut labels = vec![Label::primary(file_id, issue.span.clone())];
            for fragment in &issue.fragments {
                labels.push(
                    Label::secondary(file_id, fragment.span.clone())
                        .with_message(fragment.message.to_string()),
                );
            }
            let d = match issue.level {
                Level::Error => {
                    *errors += 1;
                    Diagnostic::error()
                }
                Level::Warning => Diagnostic::warning(),
            };
            let d = d
                .with_message(issue.message.to_string())
                .with_labels(labels);
            term::emit_to_write_style(&mut writer.lock(), &config, &files, &d).unwrap();
        }
    }

    fn str_to_type(t: &str) -> FullType<'static> {
        let (t, not_null) = if let Some(t) = t.strip_suffix('!') {
            (t, true)
        } else {
            (t, false)
        };
        let (t, list_hack) = if let Some(v) = t.strip_suffix("[]") {
            (v, true)
        } else {
            (t, false)
        };
        let t = match t {
            "b" => BaseType::Bool.into(),
            "u8" => Type::U8,
            "u16" => Type::U16,
            "u32" => Type::U32,
            "u64" => Type::U64,
            "i8" => Type::I8,
            "i16" => Type::I16,
            "i32" => Type::I32,
            "i64" => Type::I64,
            "f32" => Type::F32,
            "f64" => Type::F64,
            "i" => BaseType::Integer.into(),
            "f" => BaseType::Float.into(),
            "str" => BaseType::String.into(),
            "bytes" => BaseType::Bytes.into(),
            "dt" => BaseType::DateTime.into(),
            "date" => BaseType::Date.into(),
            "ts" => BaseType::TimeStamp.into(),
            "time" => BaseType::Time.into(),
            "json" => Type::JSON,
            "any" => BaseType::Any.into(),
            _ => panic!("Unknown type {t}"),
        };
        let mut t = FullType::new(t, not_null);
        if list_hack {
            t.list_hack = true;
        }
        t
    }

    fn check_arguments(
        name: &str,
        got: &[(ArgumentKey<'_>, FullType<'_>)],
        expected: &str,
        errors: &mut usize,
    ) {
        if expected.is_empty() {
            for (cnt, value) in got.iter().enumerate() {
                println!("{name}: Unexpected argument {cnt} type {value:?}");
                *errors += 1;
            }
            return;
        }
        let mut got2 = Vec::new();
        let inv = FullType::invalid();
        for (k, v) in got {
            match k {
                ArgumentKey::Index(i) => {
                    while got2.len() <= *i {
                        got2.push(&inv);
                    }
                    got2[*i] = v;
                }
                ArgumentKey::Identifier(k) => {
                    println!("{name}: Got named argument {k}");
                    *errors += 1;
                }
            }
        }
        let mut cnt = 0;
        for (i, t) in expected.split(',').enumerate() {
            let t = t.trim();
            let t = str_to_type(t);
            if let Some(v) = got2.get(i) {
                if *v != &t {
                    println!("{name}: Expected type {t} for argument {i} got {v}");
                    *errors += 1;
                }
            } else {
                println!("{name}: Expected type {t} for argument {i} got None");
                *errors += 1;
            }
            cnt += 1;
        }
        while cnt < got.len() {
            println!("{}: Unexpected argument {} type {:?}", name, cnt, got[cnt]);
            cnt += 1;
            *errors += 1;
        }
    }

    fn check_columns(
        name: &str,
        src: &str,
        got: &[SelectTypeColumn<'_>],
        expected: &str,
        errors: &mut usize,
    ) {
        let mut cnt = 0;
        for (i, t) in expected.split(',').enumerate() {
            let t = t.trim();
            let (cname, t) = t.split_once(":").unwrap();
            let t = str_to_type(t);
            let cname = if cname.is_empty() { None } else { Some(cname) };
            if let Some(v) = got.get(i) {
                if v.name.as_deref() != cname || v.type_ != t {
                    let mut files = SimpleFiles::new();
                    let file_id = files.add(name, &src);
                    let writer = StandardStream::stderr(ColorChoice::Always);
                    let config = codespan_reporting::term::Config::default();
                    let d = Diagnostic::error()
                        .with_message(format!(
                            "{}: Expected column {} with name {} of type {} got {} of type {}",
                            name,
                            i,
                            N(cname),
                            t,
                            N2(v.name.clone()),
                            v.type_
                        ))
                        .with_label(Label::primary(file_id, v.span.clone()));

                    term::emit_to_write_style(&mut writer.lock(), &config, &files, &d).unwrap();

                    *errors += 1;
                }
            } else {
                let mut files = SimpleFiles::new();
                let file_id = files.add(name, &src);
                let writer = StandardStream::stderr(ColorChoice::Always);
                let config = codespan_reporting::term::Config::default();
                let d = Diagnostic::error()
                    .with_message(format!(
                        "{}: Expected column {} with name {} of type {} got None",
                        name,
                        i,
                        N(cname),
                        t
                    ))
                    .with_label(Label::primary(file_id, 0..src.len()));
                term::emit_to_write_style(&mut writer.lock(), &config, &files, &d).unwrap();
                *errors += 1;
            }
            cnt += 1;
        }
        while cnt < got.len() {
            println!(
                "{}: Unexpected column {} with name {} of type {}",
                name,
                cnt,
                N2(got[cnt].name.clone()),
                got[cnt].type_
            );
            cnt += 1;
            *errors += 1;
        }
    }

    #[test]
    fn mariadb() {
        let schema_src = "

        DROP TABLE IF EXISTS `t1`;
        CREATE TABLE `t1` (
          `id` int(11) NOT NULL,
          `cbool` tinyint(1) NOT NULL,
          `cu8` tinyint UNSIGNED NOT NULL,
          `cu16` smallint UNSIGNED NOT NULL,
          `cu32` int UNSIGNED NOT NULL,
          `cu64` bigint UNSIGNED NOT NULL,
          `ci8` tinyint,
          `ci16` smallint,
          `ci32` int,
          `ci64` bigint,
          `cbin` binary(16),
          `ctext` varchar(100) NOT NULL,
          `cbytes` blob,
          `cf32` float,
          `cf64` double,
          `cu8_plus_one` tinyint UNSIGNED GENERATED ALWAYS AS (
            `cu8` + 1
           ) STORED,
          `status` varchar(10) GENERATED ALWAYS AS (case when `cu8` <> 0 and `cu16` = 0 then 'a' when
            `cbool` then 'b' when `ci32` = 42 then 'd' when `cu64` = 43 then 'x' when
            `ci64` = 12 then 'y' else 'z' end) VIRTUAL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

        ALTER TABLE `t1`
          MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

        DROP INDEX IF EXISTS `hat` ON `t1`;

        CREATE INDEX `hat2` ON `t1` (`id`, `cf64`);

        CREATE TABLE `t2` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `t1_id` int(11) NOT NULL);

        CREATE TABLE `t3` (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `text` TEXT);

        CREATE TABLE `t4` (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `dt` datetime NOT NULL);

        CREATE TABLE `t5` (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `a` int NOT NULL,
            `b` int,
            `c` int NOT NULL DEFAULT 42);

        CREATE TABLE `t6` (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `d` date NOT NULL,
            `dt` datetime NOT NULL,
            `t` time NOT NULL);

        CREATE PROCEDURE `p1`(IN `a` int, IN `b` varchar(100))
        BEGIN
        END;
        ";

        let options = TypeOptions::new().dialect(SQLDialect::MariaDB);
        let mut issues = Issues::new(schema_src);
        let schema = parse_schemas(schema_src, &mut issues, &options);
        let mut errors = 0;
        check_no_errors("schema", schema_src, issues.get(), &mut errors);

        let options = TypeOptions::new()
            .dialect(SQLDialect::MariaDB)
            .arguments(SQLArguments::QuestionMark);

        {
            let name = "q1";
            let src =
                "SELECT `id`, `cbool`, `cu8`, `cu8_plus_one`, `cu16`, `cu32`, `cu64`, `ci8`, `ci16`, `ci32`, `ci64`,
                `ctext`, `cbytes`, `cf32`, `cf64` FROM `t1` WHERE ci8 IS NOT NULL
                AND `cbool`=? AND `cu8`=? AND `cu16`=? AND `cu32`=? AND `cu64`=?
                AND `ci8`=? AND `ci16`=? AND `ci32`=? AND `ci64`=?
                AND `ctext`=? AND `cbytes`=? AND `cf32`=? AND `cf64`=?";

            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Select { arguments, columns } = q {
                check_arguments(
                    name,
                    &arguments,
                    "b,i,i,i,i,i,i,i,i,str,bytes,f,f",
                    &mut errors,
                );
                check_columns(
                    name,
                    src,
                    &columns,
                    "id:i32!,cbool:b!,cu8:u8!,cu8_plus_one:u8!,cu16:u16!,cu32:u32!,cu64:u64!,
                    ci8:i8!,ci16:i16!,ci32:i32!,ci64:i64!,ctext:str!,cbytes:bytes!,cf32:f32!,cf64:f64!",
                    &mut errors,
                );
            } else {
                println!("{name} should be select");
                errors += 1;
            }
        }

        {
            let name = "q1.1";
            let src =
                "SELECT `id`, `cbool`, `cu8`, `cu16`, `cu32`, `cu64`, `ci8`, `ci16`, `ci32`, `ci64`,
                `ctext`, `cbytes`, `cf32`, `cf64`, `cbin` FROM `t1` WHERE ci8 IS NOT NULL";

            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Select { arguments, columns } = q {
                check_arguments(name, &arguments, "", &mut errors);
                check_columns(
                    name,
                    src,
                    &columns,
                    "id:i32!,cbool:b!,cu8:u8!,cu16:u16!,cu32:u32!,cu64:u64!,
                    ci8:i8!,ci16:i16,ci32:i32,ci64:i64,ctext:str!,cbytes:bytes,cf32:f32,cf64:f64,cbin:bytes",
                    &mut errors,
                );
            } else {
                println!("{name} should be select");
                errors += 1;
            }
        }

        {
            let name = "q2";
            let src =
            "INSERT INTO `t1` (`cbool`, `cu8`, `cu16`, `cu32`, `cu64`, `ci8`, `ci16`, `ci32`, `ci64`,
            `ctext`, `cbytes`, `cf32`, `cf64`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Insert {
                arguments,
                yield_autoincrement,
                returning,
            } = q
            {
                check_arguments(
                    name,
                    &arguments,
                    "b!,u8!,u16!,u32!,u64!,i8,i16,i32,i64,str!,bytes,f32,f64",
                    &mut errors,
                );
                if yield_autoincrement != AutoIncrementId::Yes {
                    println!("{name} should yield autoincrement");
                    errors += 1;
                }
                if returning.is_some() {
                    println!("{name} should not return columns");
                    errors += 1;
                }
            } else {
                println!("{name} should be insert");
                errors += 1;
            }
        }

        {
            let name = "q3";
            let src =
                "DELETE `t1` FROM `t1`, `t2` WHERE `t1`.`id` = `t2`.`t1_id` AND `t2`.`id` = ?";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Delete { arguments, .. } = q {
                check_arguments(name, &arguments, "i", &mut errors);
            } else {
                println!("{name} should be delete");
                errors += 1;
            }
        }

        {
            let name = "q4";
            let src = "INSERT INTO `t2` (`t1_id`) VALUES (?) ON DUPLICATE KEY UPDATE `t1_id`=?";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Insert {
                arguments,
                yield_autoincrement,
                returning,
            } = q
            {
                check_arguments(name, &arguments, "i32!,i32!", &mut errors);
                if yield_autoincrement != AutoIncrementId::Optional {
                    println!("{name} should yield optional auto increment");
                    errors += 1;
                }
                if returning.is_some() {
                    println!("{name} should not return columns");
                    errors += 1;
                }
            } else {
                println!("{name} should be insert");
                errors += 1;
            }
        }

        {
            let name = "q5";
            let src = "INSERT IGNORE INTO `t2` SET `t1_id`=?";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Insert {
                arguments,
                yield_autoincrement,
                returning,
            } = q
            {
                check_arguments(name, &arguments, "i32!", &mut errors);
                if yield_autoincrement != AutoIncrementId::Optional {
                    println!("{name} should yield optional auto increment");
                    errors += 1;
                }
                if returning.is_some() {
                    println!("{name} should not return columns");
                    errors += 1;
                }
            } else {
                println!("{name} should be insert");
                errors += 1;
            }
        }

        {
            let name = "q6";
            let src = "SELECT IF(`ci32` IS NULL, `cbool`, ?) AS `cc` FROM `t1`";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Select { arguments, columns } = q {
                check_arguments(name, &arguments, "b", &mut errors);
                check_columns(name, src, &columns, "cc:b", &mut errors);
            } else {
                println!("{name} should be select");
                errors += 1;
            }
        }

        {
            let name = "q7";
            let src = "SELECT FROM_UNIXTIME(CAST(UNIX_TIMESTAMP() AS DOUBLE)) AS `cc` FROM `t1` WHERE `id`=?";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Select { arguments, columns } = q {
                check_arguments(name, &arguments, "i", &mut errors);
                check_columns(name, src, &columns, "cc:dt!", &mut errors);
            } else {
                println!("{name} should be select");
                errors += 1;
            }
        }

        {
            let name = "q8";
            let src = "REPLACE INTO `t2` SET `id` = ?, `t1_id`=?";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Replace {
                arguments,
                returning,
            } = q
            {
                check_arguments(name, &arguments, "i32!,i32!", &mut errors);
                if returning.is_some() {
                    println!("{name} should not return columns");
                    errors += 1;
                }
            } else {
                println!("{name} should be replace");
                errors += 1;
            }
        }

        {
            let name = "q9";
            let src = "INSERT INTO `t2` (`t1_id`) VALUES (32) ON DUPLICATE KEY UPDATE `t1_id` = `t1_id` + VALUES(`t1_id`)";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Insert { arguments, .. } = q {
                check_arguments(name, &arguments, "", &mut errors);
            } else {
                println!("{name} should be insert");
                errors += 1;
            }
        }

        {
            let name = "q10";
            let src =
                "SELECT SUBSTRING_INDEX(`text`, '/', 5) AS `k` FROM `t3` WHERE `text` LIKE '%T%'";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Select { arguments, columns } = q {
                check_arguments(name, &arguments, "", &mut errors);
                check_columns(name, src, &columns, "k:str!", &mut errors);
            } else {
                println!("{name} should be select");
                errors += 1;
            }
        }

        {
            let name = "q11";
            let src = "SELECT * FROM `t1`, `t2` LEFT JOIN `t3` ON `t3`.`id` = `t1`.`id`";
            let mut issues: Issues<'_> = Issues::new(src);
            type_statement(&schema, src, &mut issues, &options);
            if !issues.get().iter().any(|i| i.level == Level::Error) {
                println!("{name} should be an error");
                errors += 1;
            }
        }

        {
            let name = "q11b";
            let src = "SELECT 1 FROM `t3` WHERE `text` LIKE '%T%' ESCAPE 'ab'";
            let mut issues: Issues<'_> = Issues::new(src);
            type_statement(&schema, src, &mut issues, &options);
            if !issues.get().iter().any(|i| i.level == Level::Error) {
                println!("{name} should be an error (ESCAPE must be one character)");
                errors += 1;
            }
        }

        {
            let name = "q12";
            let src = "SELECT JSON_REPLACE('{ \"A\": 1, \"B\": [2, 3]}', '$.B[1]', 4, '$.C[3]', 3) AS `k` FROM `t3`";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Select { arguments, columns } = q {
                check_arguments(name, &arguments, "", &mut errors);
                check_columns(name, src, &columns, "k:json", &mut errors);
            } else {
                println!("{name} should be select");
                errors += 1;
            }
        }

        {
            let options = options.clone().list_hack(true);
            let name = "q13";
            let src = "SELECT `id` FROM `t1` WHERE `id` IN (_LIST_)";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Select { arguments, columns } = q {
                check_arguments(name, &arguments, "i[]", &mut errors);
                check_columns(name, src, &columns, "id:i32!", &mut errors);
            } else {
                println!("{name} should be select");
                errors += 1;
            }
        }

        {
            let name = "q14";
            let src = "SELECT CAST(NULL AS CHAR) AS `id`";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Select { arguments, columns } = q {
                check_arguments(name, &arguments, "", &mut errors);
                check_columns(name, src, &columns, "id:str", &mut errors);
            } else {
                println!("{name} should be select");
                errors += 1;
            }
        }

        {
            let name = "q15";
            let src =
				"INSERT INTO `t1` (`cbool`, `cu8`, `cu16`, `cu32`, `cu64`, `ci8`, `ci16`, `ci32`, `ci64`,
            `ctext`, `cbytes`, `cf32`, `cf64`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                 RETURNING `id`, `cbool`, `cu8`, `ctext`, `cf64`";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Insert {
                arguments,
                yield_autoincrement,
                returning,
            } = q
            {
                check_arguments(
                    name,
                    &arguments,
                    "b!,u8!,u16!,u32!,u64!,i8,i16,i32,i64,str!,bytes,f32,f64",
                    &mut errors,
                );
                if yield_autoincrement != AutoIncrementId::Yes {
                    println!("{name} should yield autoincrement");
                    errors += 1;
                }
                if let Some(returning) = returning {
                    check_columns(
                        name,
                        src,
                        &returning,
                        "id:i32!,cbool:b!,cu8:u8!,ctext:str!,cf64:f64",
                        &mut errors,
                    );
                } else {
                    println!("{name} should return columns");
                    errors += 1;
                }
            } else {
                println!("{name} should be insert");
                errors += 1;
            }
        }

        {
            let name = "q16";
            let src = "REPLACE INTO `t2` SET `id` = ?, `t1_id`=? RETURNING `id`";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Replace {
                arguments,
                returning,
            } = q
            {
                check_arguments(name, &arguments, "i32!,i32!", &mut errors);
                if let Some(returning) = returning {
                    check_columns(name, src, &returning, "id:i32!", &mut errors);
                } else {
                    println!("{name} should return columns");
                    errors += 1;
                }
            } else {
                println!("{name} should be replace");
                errors += 1;
            }
        }

        {
            let name = "q17";
            let src = "SELECT dt, UNIX_TIMESTAMP(dt) AS t FROM t4";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Select { arguments, columns } = q {
                check_arguments(name, &arguments, "", &mut errors);
                check_columns(name, src, &columns, "dt:dt!,t:i64!", &mut errors);
            } else {
                println!("{name} should be select");
                errors += 1;
            }
        }

        {
            let name = "q17";
            let src = "SELECT CONCAT(?, \"hat\") AS c";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Select { arguments, columns } = q {
                check_arguments(name, &arguments, "any", &mut errors);
                check_columns(name, src, &columns, "c:str", &mut errors);
            } else {
                println!("{name} should be selsect");
                errors += 1;
            }
        }

        {
            let name = "q18";
            let src = "SELECT CAST(\"::0\" AS INET6) AS `id`";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Select { arguments, columns } = q {
                check_arguments(name, &arguments, "", &mut errors);
                check_columns(name, src, &columns, "id:str!", &mut errors);
            } else {
                println!("{name} should be select");
                errors += 1;
            }
        }

        {
            let name: &str = "q18";
            let src = "SELECT SUBSTRING(`cbytes`, 1, 5) AS `k` FROM `t1`";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Select { arguments, columns } = q {
                check_arguments(name, &arguments, "", &mut errors);
                check_columns(name, src, &columns, "k:bytes", &mut errors);
            } else {
                println!("{name} should be select");
                errors += 1;
            }
        }

        {
            let name = "q19";
            let src = "SELECT SUBSTRING(`ctext`, 1, 5) AS `k` FROM `t1`";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Select { arguments, columns } = q {
                check_arguments(name, &arguments, "", &mut errors);
                check_columns(name, src, &columns, "k:str!", &mut errors);
            } else {
                println!("{name} should be select");
                errors += 1;
            }
        }

        {
            let name = "q19";
            let src = "SELECT SUBSTRING(`ctext`, 1, 5) AS `k` FROM `t1`";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Select { arguments, columns } = q {
                check_arguments(name, &arguments, "", &mut errors);
                check_columns(name, src, &columns, "k:str!", &mut errors);
            } else {
                println!("{name} should be select");
                errors += 1;
            }
        }

        {
            let name = "q20";
            let src = "SELECT JSON_QUERY('{ \"A\": 1, \"B\": [2, 3]}', '$.B[1]') AS `k` FROM `t3`";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Select { arguments, columns } = q {
                check_arguments(name, &arguments, "", &mut errors);
                check_columns(name, src, &columns, "k:json", &mut errors);
            } else {
                println!("{name} should be select");
                errors += 1;
            }
        }

        {
            let name = "q21";
            let src = "SELECT JSON_REMOVE('{ \"A\": 1, \"B\": [2, 3]}', '$.B[1]', '$.C[3]') AS `k` FROM `t3`";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Select { arguments, columns } = q {
                check_arguments(name, &arguments, "", &mut errors);
                check_columns(name, src, &columns, "k:json", &mut errors);
            } else {
                println!("{name} should be select");
                errors += 1;
            }
        }

        {
            let name = "q22";
            let src = "SELECT JSON_OVERLAPS('false', 'false') AS `k` FROM `t3`";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Select { arguments, columns } = q {
                check_arguments(name, &arguments, "", &mut errors);
                check_columns(name, src, &columns, "k:b!", &mut errors);
            } else {
                println!("{name} should be select");
                errors += 1;
            }
        }

        {
            let name = "q23";
            let src = "SELECT JSON_OVERLAPS('false', NULL) AS `k` FROM `t3`";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Select { arguments, columns } = q {
                check_arguments(name, &arguments, "", &mut errors);
                check_columns(name, src, &columns, "k:b", &mut errors);
            } else {
                println!("{name} should be select");
                errors += 1;
            }
        }

        {
            let name = "q24";
            let src = "SELECT JSON_CONTAINS('{\"A\": 0, \"B\": [\"x\", \"y\"]}', '\"x\"', '$.B') AS `k` FROM `t3`";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Select { arguments, columns } = q {
                check_arguments(name, &arguments, "", &mut errors);
                check_columns(name, src, &columns, "k:b!", &mut errors);
            } else {
                println!("{name} should be select");
                errors += 1;
            }
        }

        {
            let name = "q25";
            let src = "SELECT JSON_CONTAINS('{\"A\": 0, \"B\": [\"x\", \"y\"]}', NULL, '$.A') AS `k` FROM `t3`";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Select { arguments, columns } = q {
                check_arguments(name, &arguments, "", &mut errors);
                check_columns(name, src, &columns, "k:b", &mut errors);
            } else {
                println!("{name} should be select");
                errors += 1;
            }
        }

        {
            let name = "q26";
            let src = "SELECT `id` FROM `t1` FORCE INDEX (`hat`)";
            let mut issues: Issues<'_> = Issues::new(src);
            type_statement(&schema, src, &mut issues, &options);
            if issues.is_ok() {
                println!("{name} should fail");
                errors += 1;
            }
        }

        {
            let name = "q27";
            let src = "SELECT `id` FROM `t1` USE INDEX (`hat2`)";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Select { arguments, columns } = q {
                check_arguments(name, &arguments, "", &mut errors);
                check_columns(name, src, &columns, "id:i32!", &mut errors);
            } else {
                println!("{name} should be select");
                errors += 1;
            }
        }

        {
            let name = "q28";
            let src = "INSERT INTO t5 (`a`) VALUES (44)";
            check_no_errors(name, src, issues.get(), &mut errors);
        }

        {
            let name = "q29";
            let src = "INSERT INTO t5 (`a`, `b`, `c`) VALUES (?, ?)";
            let mut issues: Issues<'_> = Issues::new(src);
            type_statement(&schema, src, &mut issues, &options);
            if issues.is_ok() {
                println!("{name} should fail");
                errors += 1;
            }
        }

        {
            let name = "q30";
            let src = "INSERT INTO t5 (`a`, `b`, `c`) VALUES (?, ?, ?)";
            check_no_errors(name, src, issues.get(), &mut errors);
        }

        {
            let name = "q31";
            let src = "INSERT INTO t5 (`a`, `b`, `c`) VALUES (?, ?, ?, ?)";
            let mut issues: Issues<'_> = Issues::new(src);
            type_statement(&schema, src, &mut issues, &options);
            if issues.is_ok() {
                println!("{name} should fail");
                errors += 1;
            }
        }

        {
            let name = "q32";
            let src = "INSERT INTO t5 (`b`, `c`) VALUES (44, 45)";
            let mut issues: Issues<'_> = Issues::new(src);
            type_statement(&schema, src, &mut issues, &options);
            if issues.is_ok() {
                println!("{name} should fail");
                errors += 1;
            }
        }

        {
            let mut t = |expr: &str, t: &str| {
                let name = format!("q33 {expr}");
                let src = format!("SELECT {expr} AS q FROM t6");
                let mut issues: Issues<'_> = Issues::new(&src);
                let q = type_statement(&schema, &src, &mut issues, &options);
                check_no_errors(&name, &src, issues.get(), &mut errors);
                if let StatementType::Select { columns, .. } = q {
                    check_columns(&name, &src, &columns, &format!("q:{t}"), &mut errors);
                } else {
                    println!("{name} should be select");
                    errors += 1;
                }
            };
            t("ADD_MONTHS(`d`, 2)", "date!");
            t("DATE_ADD(`d`, INTERVAL 31 DAY)", "date");
            t("ADDDATE(`d`, 31)", "date");
            // t("ADDTIME(`dt`, '1 1:1:1.000002')", "dt!");
            // t("ADDTIME(`t`, '02:00:00.999998')", "time!");
            t("CONVERT_TZ(`dt`, '+00:00','+10:00')", "dt!");
            //t("CURDATE() + 0", "i!");
            t("CURDATE()", "date!");
            t("CURDATE() - INTERVAL 5 DAY", "date!");
            // t("CURTIME() + 0.0", "f!");
            t("CURTIME()", "time!");
            t("CURTIME()", "time!");
            t("DATE('2013-07-18 12:21:32')", "date!");
            t("`dt` + INTERVAL 1 SECOND", "dt!");
            t("INTERVAL 1 DAY + `d`", "date!");
            t("DATE_ADD(`dt`, INTERVAL 1 SECOND)", "dt");
            t("DATE_ADD(`dt`, INTERVAL '1:1' MINUTE_SECOND)", "dt");
            t("DATE_FORMAT(`dt`, '%D %y %a %d %m %b %j')", "str!");
            t("DATE_SUB(`d`, INTERVAL 31 DAY)", "date");
            t("DATE_SUB(`dt`, INTERVAL '1 1:1:1' DAY_SECOND)", "dt");
            t("DATEDIFF(`dt`, `d`)", "i!");
            t("DAYNAME(`d`)", "str!");
            t("DAYOFMONTH(`d`)", "i!");
            t("DAYOFWEEK(`d`)", "i!");
            t("DAYOFYEAR(`d`)", "i!");
            t("EXTRACT(DAY_MINUTE FROM `dt`)", "i!");
            t("FROM_DAYS(730669)", "date!");
            t("FROM_UNIXTIME(1196440219.0)", "dt!");
            // t("FROM_UNIXTIME(1196440219.0) + 0.0", "f!");
            t(
                "FROM_UNIXTIME(1196440219.0, '%Y %D %M %h:%i:%s %x')",
                "str!",
            );
            t("HOUR(`t`)", "i!");
            t("LAST_DAY('2004-01-01 01:01:01')", "date!");
            t("MAKEDATE(2011,31)", "date");
            t("MAKETIME(13,57,33)", "time");
            t("MICROSECOND(`dt`)", "i!");
            t("MINUTE(`dt`)", "i!");
            t("MONTH(`d`)", "i!");
            t("MONTHNAME(`dt`)", "str!");
            t("NOW()", "dt!");
            // t("NOW() + 0.0", "f!");
            t("PERIOD_ADD(200801,2)", "i!");
            t("PERIOD_DIFF(200802,200703)", "i!");
            t("QUARTER(`dt`)", "i!");
            // t("SEC_TO_TIME(12414)+0", "i!");
            t("SEC_TO_TIME(12414)", "time!");
            t("SECOND(`dt`)", "i!");
            t(
                "STR_TO_DATE('Wednesday23423, June 2, 2014', '%W, %M %e, %Y')",
                "dt!",
            );
            //t("SUBTIME(`dt`,'1 1:1:1.000002')", "dt");
            //t("SUBTIME(`t`, '02:00:00.999998')", "time");
            t("SYSDATE()", "dt!");
            t("TIME('2013-07-18 12:21:32')", "time!");
            t("TIME_FORMAT(`t`, '%H %k %h %I %l')", "str!");
            t("TIME_TO_SEC(`t`)", "f!");
            t(
                "TIMEDIFF('2000:01:01 00:00:00', '2000:01:01 00:00:00.000001')",
                "time!",
            );
            t("TIMESTAMP('2003-12-31')", "dt!");
            t("TIMESTAMP('2003-12-31 12:00:00','6:30:00')", "dt!");
            t("TIMESTAMPADD(MINUTE,1,`d`)", "dt!");
            t("TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01')", "i!");
            t("TO_DAYS(`d`)", "i!");
            t("TO_SECONDS(`dt`)", "i!");
            t("UNIX_TIMESTAMP(`dt`)", "i64!");
            t("UNIX_TIMESTAMP()", "i64!");
            // t("UTC_DATE() + 0", "i!");
            t("UTC_DATE()", "date!");
            // t("UTC_TIME() + 0", "f!");
            t("UTC_TIME()", "time!");
            // t("UTC_TIMESTAMP() + 0", "f!");
            t("UTC_TIMESTAMP()", "dt!");
            t("WEEK(`d`)", "i!");
            t("WEEK(`d`, 3)", "i!");
            t("WEEKDAY(`d`)", "i!");
            t("YEAR(`d`)", "i!");
            t("YEARWEEK(`d`)", "i!");
            t("YEARWEEK(`d`, 3)", "i!");
        }
        {
            let name = "q34";
            let src = "TRUNCATE TABLE `t1`";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if !matches!(q, StatementType::Truncate) {
                println!("{name} should be truncate");
                errors += 1;
            }
        }

        {
            let name = "q35";
            let src = "TRUNCATE TABLE `unknown_table`";
            let mut issues: Issues<'_> = Issues::new(src);
            type_statement(&schema, src, &mut issues, &options);
            if issues.is_ok() {
                println!("{name} should fail");
                errors += 1;
            }
        }

        {
            let name = "q36";
            let src = "CALL p1(42, 'hello')";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if !matches!(q, StatementType::Call { .. }) {
                println!("{name} should be call");
                errors += 1;
            }
        }

        {
            let name = "q37";
            let src = "CALL p1(42)";
            let mut issues: Issues<'_> = Issues::new(src);
            type_statement(&schema, src, &mut issues, &options);
            if issues.is_ok() {
                println!("{name} should fail (wrong arg count)");
                errors += 1;
            }
        }

        {
            let name = "q38";
            let src = "CALL unknown_proc(1)";
            let mut issues: Issues<'_> = Issues::new(src);
            type_statement(&schema, src, &mut issues, &options);
            if issues.is_ok() {
                println!("{name} should fail (unknown procedure)");
                errors += 1;
            }
        }

        {
            let name = "q39";
            let src = "BEGIN";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if !matches!(q, StatementType::Transaction) {
                println!("{name} should be transaction");
                errors += 1;
            }
        }

        {
            let name = "q40";
            let src = "COMMIT";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if !matches!(q, StatementType::Transaction) {
                println!("{name} should be transaction");
                errors += 1;
            }
        }

        {
            let name = "q41";
            let src = "SET @var = 42";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if !matches!(q, StatementType::Set) {
                println!("{name} should be set");
                errors += 1;
            }
        }

        {
            let name = "q42";
            let src = "SET @@session.time_zone = 'UTC'";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if !matches!(q, StatementType::Set) {
                println!("{name} should be set");
                errors += 1;
            }
        }

        {
            let name = "q42b";
            let src = "SET @@time_zone = '+00:00'";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if !matches!(q, StatementType::Set) {
                println!("{name} should be set");
                errors += 1;
            }
        }

        {
            let name = "q43";
            let src = "LOCK TABLES `t1` READ";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if !matches!(q, StatementType::Lock) {
                println!("{name} should be lock");
                errors += 1;
            }
        }

        {
            let name = "q44";
            let src = "LOCK TABLES `unknown_table` WRITE";
            let mut issues: Issues<'_> = Issues::new(src);
            type_statement(&schema, src, &mut issues, &options);
            if issues.is_ok() {
                println!("{name} should fail (unknown table)");
                errors += 1;
            }
        }

        {
            let name = "q45";
            let src = "UNLOCK TABLES";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if !matches!(q, StatementType::Lock) {
                println!("{name} should be lock");
                errors += 1;
            }
        }

        if errors != 0 {
            panic!("{errors} errors in test");
        }
    }

    #[test]
    fn postgresql() {
        let schema_src = "
        BEGIN;

        DO $$ BEGIN
            CREATE TYPE my_enum AS ENUM (
            'V1',
            'V2',
            'V3'
        );
        EXCEPTION
            WHEN duplicate_object THEN null;
        END $$;

        CREATE TABLE IF NOT EXISTS t1 (
            id bigint NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
            path text NOT NULL UNIQUE,
            v my_enum NOT NULL,
            time timestamptz NOT NULL DEFAULT now(),
            props jsonb NOT NULL DEFAULT '{}',
            old_id bigint,
            CONSTRAINT t1__old
            FOREIGN KEY(old_id) 
            REFERENCES t1(id)
            ON DELETE SET NULL
        );

        CREATE TABLE IF NOT EXISTS t2 (
            id bigint NOT NULL PRIMARY KEY
        );

        DROP INDEX IF EXISTS t2_index;

        CREATE INDEX t2_index2 ON t2 (id);

        CREATE TABLE IF NOT EXISTS t3 (
            a bigint NOT NULL,
            b bigint NOT NULL
        );

        CREATE UNIQUE INDEX t3u ON t3(a,b);

        COMMIT;
        ";

        let options = TypeOptions::new().dialect(SQLDialect::PostgreSQL);
        let mut issues = Issues::new(schema_src);
        let schema = parse_schemas(schema_src, &mut issues, &options);
        let mut errors = 0;
        check_no_errors("schema", schema_src, issues.get(), &mut errors);

        let options = TypeOptions::new()
            .dialect(SQLDialect::PostgreSQL)
            .arguments(SQLArguments::Dollar);

        {
            let name = "q1";
            let src = "INSERT INTO t2 (id) SELECT id FROM t1 WHERE path=$1 ON CONFLICT (id) DO NOTHING RETURNING id";
            let mut issues = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Insert {
                arguments,
                returning,
                ..
            } = q
            {
                check_arguments(name, &arguments, "str", &mut errors);
                check_columns(
                    name,
                    src,
                    &returning.expect("Returning"),
                    "id:i64!",
                    &mut errors,
                );
            } else {
                println!("{name} should be select");
                errors += 1;
            }
        }

        {
            let name = "q2";
            let src = "WITH hat AS (DELETE FROM t1 WHERE old_id=42 RETURNING id) INSERT INTO t2 (id) SELECT id FROM hat";
            let mut issues = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);

            if let StatementType::Insert { arguments, .. } = q {
                check_arguments(name, &arguments, "", &mut errors);
            } else {
                println!("{name} should be select {q:?}");
                errors += 1;
            }
        }

        {
            let name = "q3";
            let src = "INSERT INTO t1 (path) VALUES ('HI')";
            let mut issues: Issues<'_> = Issues::new(src);
            type_statement(&schema, src, &mut issues, &options);
            if issues.is_ok() {
                println!("{name} should fail");
                errors += 1;
            }
        }

        {
            let name = "q4";
            let src = "INSERT INTO t1 (path, v) VALUES ('HI', 'V1')";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);

            if let StatementType::Insert { arguments, .. } = q {
                check_arguments(name, &arguments, "", &mut errors);
            } else {
                println!("{name} should be insert {q:?}");
                errors += 1;
            }
        }

        {
            let name = "q5";
            let src = "UPDATE t1 SET path='HI' RETURNING id";
            let mut issues: Issues<'_> = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            if let StatementType::Update {
                arguments,
                returning,
                ..
            } = q
            {
                check_arguments(name, &arguments, "", &mut errors);
                if returning.is_none() {
                    println!("{name} should have returning");
                    errors += 1;
                }
            } else {
                println!("{name} should be update {q:?}");
                errors += 1;
            }
        }

        {
            let name = "q6";
            let src = "INSERT INTO t3 (a,b) VALUES (1, 1) ON CONFLICT (a,b) DO UPDATE SET a=t3.a, b=EXCLUDED.b WHERE t3.a != EXCLUDED.a";
            let mut issues = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);

            if let StatementType::Insert { arguments, .. } = q {
                check_arguments(name, &arguments, "", &mut errors);
            } else {
                println!("{name} should be insert {q:?}");
                errors += 1;
            }
        }

        {
            let name = "q7";
            let src = "INSERT INTO t3 (a,b) VALUES (1, 1) ON CONFLICT (a,c) cake DO UPDATE SET a=2";
            let mut issues = Issues::new(src);
            type_statement(&schema, src, &mut issues, &options);
            if issues.is_ok() {
                println!("{name} should fail");
                errors += 1;
            }
        }

        {
            let name = "q8";
            let src = "INSERT INTO t3 (a,b) VALUES (1, 1) ON CONFLICT (a,b) t3u DO UPDATE SET a=2 WHERE b=2";
            let mut issues = Issues::new(src);
            type_statement(&schema, src, &mut issues, &options);
            if issues.is_ok() {
                println!("{name} should fail");
                errors += 1;
            }
        }

        {
            let name = "q9";
            let src = "SELECT left(path, -4) AS k FROM t1";
            let mut issues = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);

            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Select { arguments, columns } = q {
                check_arguments(name, &arguments, "", &mut errors);
                check_columns(name, src, &columns, "k:str!", &mut errors);
            } else {
                println!("{name} should be select");
                errors += 1;
            }
        }

        {
            let name = "json_extract";
            let src = "SELECT props -> 'a' AS k FROM t1";
            let mut issues = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Select { arguments, columns } = q {
                check_arguments(name, &arguments, "", &mut errors);
                // `->` returns a JSON value (jsonb in PostgreSQL), not text.
                // Nullable: a missing key yields SQL NULL.
                check_columns(name, src, &columns, "k:json", &mut errors);
            } else {
                println!("{name} should be select");
                errors += 1;
            }
        }

        {
            let name = "json_extract_text";
            let src = "SELECT props ->> 'a' AS k FROM t1";
            let mut issues = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Select { arguments, columns } = q {
                check_arguments(name, &arguments, "", &mut errors);
                // `->>` returns the value as unquoted text.
                // Nullable: a missing key yields SQL NULL.
                check_columns(name, src, &columns, "k:str", &mut errors);
            } else {
                println!("{name} should be select");
                errors += 1;
            }
        }

        {
            let name = "json_get_path";
            let src = "SELECT props #> '{a,b}' AS k FROM t1";
            let mut issues = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Select { arguments, columns } = q {
                check_arguments(name, &arguments, "", &mut errors);
                // `#>` returns a JSON value at a path, not text.
                // Nullable: a missing path yields SQL NULL.
                check_columns(name, src, &columns, "k:json", &mut errors);
            } else {
                println!("{name} should be select");
                errors += 1;
            }
        }

        {
            let name = "json_get_path_text";
            let src = "SELECT props #>> '{a,b}' AS k FROM t1";
            let mut issues = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if let StatementType::Select { arguments, columns } = q {
                check_arguments(name, &arguments, "", &mut errors);
                // `#>>` returns the value at a path as unquoted text.
                // Nullable: a missing path yields SQL NULL.
                check_columns(name, src, &columns, "k:str", &mut errors);
            } else {
                println!("{name} should be select");
                errors += 1;
            }
        }

        {
            let name = "pg_set1";
            let src = "SET search_path = 'myschema'";
            let mut issues = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if !matches!(q, StatementType::Set) {
                println!("{name} should be set");
                errors += 1;
            }
        }

        {
            let name = "pg_set2";
            let src = "SET @user_var = 42";
            let mut issues = Issues::new(src);
            type_statement(&schema, src, &mut issues, &options);
            if issues.is_ok() {
                println!("{name} should fail: @var not valid in PostgreSQL");
                errors += 1;
            }
        }

        {
            let name = "pg_set3";
            let src = "SET @@session.time_zone = 'UTC'";
            let mut issues = Issues::new(src);
            type_statement(&schema, src, &mut issues, &options);
            if issues.is_ok() {
                println!("{name} should fail: @@var not valid in PostgreSQL");
                errors += 1;
            }
        }

        if errors != 0 {
            panic!("{errors} errors in test");
        }
    }

    #[test]
    fn sqlite() {
        let schema_src = "
         CREATE TABLE IF NOT EXISTS `t1` (
            `id` INTEGER NOT NULL PRIMARY KEY,
            `sid` TEXT NOT NULL) STRICT;
        CREATE UNIQUE INDEX IF NOT EXISTS `t1_sid` ON `t1` (`sid`);
        ";

        let options = TypeOptions::new().dialect(SQLDialect::Sqlite);
        let mut issues = Issues::new(schema_src);
        let schema = parse_schemas(schema_src, &mut issues, &options);
        let mut errors = 0;
        check_no_errors("schema", schema_src, issues.get(), &mut errors);

        let options = TypeOptions::new()
            .dialect(SQLDialect::Sqlite)
            .arguments(SQLArguments::QuestionMark);

        {
            let name = "q1";
            let src = "INSERT INTO `t1` (`sid`) VALUES (?)";
            let mut issues = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &options);
            check_no_errors(name, src, issues.get(), &mut errors);
            if !matches!(q, StatementType::Insert { .. }) {
                println!("{name} should be select");
                errors += 1;
            }
        }

        if errors != 0 {
            panic!("{errors} errors in test");
        }
    }

    /// CREATE SCHEMA / qualified table names work in PostgreSQL.
    #[test]
    fn postgres_schema_support() {
        let schema_src = "
        CREATE SCHEMA myschema;

        CREATE TABLE myschema.items (
            id   bigint NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
            name text   NOT NULL
        );

        -- Unqualified name lands in 'public' schema
        CREATE TABLE products (
            id    bigint NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
            label text   NOT NULL
        );
        ";

        let opts = TypeOptions::new()
            .dialect(SQLDialect::PostgreSQL)
            .arguments(SQLArguments::Dollar);
        let mut issues = Issues::new(schema_src);
        let schema = parse_schemas(schema_src, &mut issues, &opts);
        let mut errors = 0;
        check_no_errors("schema", schema_src, issues.get(), &mut errors);

        // Qualified SELECT against myschema.items
        {
            let src = "SELECT id, name FROM myschema.items WHERE id = $1";
            let mut issues = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &opts);
            check_no_errors("qualified SELECT", src, issues.get(), &mut errors);
            if let StatementType::Select { columns, arguments } = q {
                assert_eq!(columns.len(), 2, "expected 2 columns");
                assert_eq!(arguments.len(), 1, "expected 1 argument");
            } else {
                errors += 1;
                println!("qualified SELECT: expected Select");
            }
        }

        // Unqualified SELECT against public.products
        {
            let src = "SELECT id, label FROM products WHERE id = $1";
            let mut issues = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &opts);
            check_no_errors("unqualified SELECT", src, issues.get(), &mut errors);
            if let StatementType::Select { columns, .. } = q {
                assert_eq!(columns.len(), 2, "expected 2 columns");
            } else {
                errors += 1;
                println!("unqualified SELECT: expected Select");
            }
        }

        // INSERT into qualified table
        {
            let src = "INSERT INTO myschema.items (name) VALUES ($1)";
            let mut issues = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &opts);
            check_no_errors("qualified INSERT", src, issues.get(), &mut errors);
            assert!(matches!(q, StatementType::Insert { .. }), "expected Insert");
        }

        // DROP SCHEMA removes all tables in that schema
        {
            let schema_src2 = "
            CREATE SCHEMA tmp;
            CREATE TABLE tmp.t (id bigint NOT NULL);
            DROP SCHEMA tmp;
            ";
            let mut issues2 = Issues::new(schema_src2);
            let s2 = parse_schemas(schema_src2, &mut issues2, &opts);
            check_no_errors("drop schema", schema_src2, issues2.get(), &mut errors);
            // tmp.t should be gone
            let src = "SELECT id FROM tmp.t";
            let mut issues2 = Issues::new(src);
            let q = type_statement(&s2, src, &mut issues2, &opts);
            assert!(
                !issues2.is_ok(),
                "expected error: tmp.t should not exist after DROP SCHEMA"
            );
            let _ = q;
        }

        if errors != 0 {
            panic!("{errors} errors in postgres_schema_support test");
        }
    }

    /// CREATE SCHEMA must fail in MySQL; schema-qualified names must also fail.
    #[test]
    fn mysql_create_schema_fails() {
        let opts_maria = TypeOptions::new()
            .dialect(SQLDialect::MariaDB)
            .arguments(SQLArguments::QuestionMark);

        // CREATE SCHEMA is not supported in MySQL
        {
            let src = "CREATE SCHEMA myschema;";
            let mut issues = Issues::new(src);
            let _ = parse_schemas(src, &mut issues, &opts_maria);
            assert!(
                !issues.is_ok(),
                "CREATE SCHEMA should produce an error in MySQL"
            );
        }

        // Schema-qualified table name in CREATE TABLE should fail in MySQL
        {
            let src = "CREATE TABLE myschema.items (id int NOT NULL);";
            let mut issues = Issues::new(src);
            let _ = parse_schemas(src, &mut issues, &opts_maria);
            assert!(
                !issues.is_ok(),
                "schema.table in CREATE TABLE should fail in MySQL"
            );
        }

        // Schema-qualified table name in SELECT should fail in MySQL
        {
            let tbl_src = "CREATE TABLE items (id int NOT NULL);";
            let mut issues = Issues::new(tbl_src);
            let schema = parse_schemas(tbl_src, &mut issues, &opts_maria);
            assert!(issues.is_ok());

            let src = "SELECT id FROM myschema.items";
            let mut issues = Issues::new(src);
            let _ = type_statement(&schema, src, &mut issues, &opts_maria);
            assert!(!issues.is_ok(), "schema.table in FROM should fail in MySQL");
        }
    }

    /// PostgreSQL range and multirange types: parsing, operators and functions.
    /// See https://www.postgresql.org/docs/15/rangetypes.html and
    /// https://www.postgresql.org/docs/15/functions-range.html
    #[test]
    fn postgresql_range_types() {
        let schema_src = "
        CREATE TABLE reservation (
            id bigint NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
            during tsrange NOT NULL,
            ids int4multirange NOT NULL
        );
        ";

        let opts = TypeOptions::new()
            .dialect(SQLDialect::PostgreSQL)
            .arguments(SQLArguments::Dollar);
        let mut issues = Issues::new(schema_src);
        let schema = parse_schemas(schema_src, &mut issues, &opts);
        let mut errors = 0;
        check_no_errors("schema", schema_src, issues.get(), &mut errors);

        // Checks that `src` types as a single-column SELECT with the given type, or,
        // when `expected` is `None`, that typing `src` produces a type error.
        let mut check_expr = |src: &'static str, expected: Option<Type<'static>>| {
            let mut issues = Issues::new(src);
            let q = type_statement(&schema, src, &mut issues, &opts);
            let Some(expected) = expected else {
                if issues.is_ok() {
                    println!("{src}: expected a type error");
                    errors += 1;
                }
                return;
            };
            check_no_errors(src, src, issues.get(), &mut errors);
            if let StatementType::Select { columns, .. } = q {
                match columns.first() {
                    Some(c) if c.type_.t == expected => {}
                    Some(c) => {
                        println!("{src}: expected type {expected} got {}", c.type_.t);
                        errors += 1;
                    }
                    None => {
                        println!("{src}: expected a column, got none");
                        errors += 1;
                    }
                }
            } else {
                println!("{src}: expected Select");
                errors += 1;
            }
        };
        // Range constructors (8.17.6)
        check_expr(
            "SELECT int4range(1, 10)",
            Some(Type::Range(Box::new(Type::I32))),
        );
        check_expr(
            "SELECT int4range(1, 10, '[]')",
            Some(Type::Range(Box::new(Type::I32))),
        );
        check_expr(
            "SELECT int8range(1, 10)",
            Some(Type::Range(Box::new(Type::I64))),
        );
        check_expr(
            "SELECT numrange(1.1, 2.2)",
            Some(Type::Range(Box::new(BaseType::Float.into()))),
        );
        check_expr(
            "SELECT daterange('2010-01-01'::date, '2010-01-02'::date)",
            Some(Type::Range(Box::new(BaseType::Date.into()))),
        );
        check_expr(
            "SELECT tstzrange(now(), now())",
            Some(Type::Range(Box::new(BaseType::TimeStamp.into()))),
        );

        // Multirange constructors
        check_expr(
            "SELECT int4multirange(int4range(1, 2), int4range(3, 4))",
            Some(Type::MultiRange(Box::new(Type::I32))),
        );
        check_expr(
            "SELECT int4multirange()",
            Some(Type::MultiRange(Box::new(Type::I32))),
        );
        check_expr(
            "SELECT multirange(int4range(1, 2))",
            Some(Type::MultiRange(Box::new(Type::I32))),
        );

        // Range/multirange operators (Table 9.54, 9.55)
        check_expr(
            "SELECT int4range(10, 20) @> 3",
            Some(Type::Base(BaseType::Bool)),
        );
        check_expr(
            "SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0)",
            Some(Type::Base(BaseType::Bool)),
        );
        check_expr(
            "SELECT int8range(1, 10) << int8range(100, 110)",
            Some(Type::Base(BaseType::Bool)),
        );
        check_expr(
            "SELECT int8range(50, 60) >> int8range(20, 30)",
            Some(Type::Base(BaseType::Bool)),
        );
        check_expr(
            "SELECT int8range(1, 20) &< int8range(18, 20)",
            Some(Type::Base(BaseType::Bool)),
        );
        check_expr(
            "SELECT int8range(7, 20) &> int8range(5, 10)",
            Some(Type::Base(BaseType::Bool)),
        );
        check_expr(
            "SELECT numrange(1.1, 2.2) -|- numrange(2.2, 3.3)",
            Some(Type::Base(BaseType::Bool)),
        );
        check_expr(
            "SELECT numrange(5.0, 15.0) + numrange(10.0, 20.0)",
            Some(Type::Range(Box::new(BaseType::Float.into()))),
        );
        check_expr(
            "SELECT int8range(5, 15) * int8range(10, 20)",
            Some(Type::Range(Box::new(Type::I64))),
        );
        check_expr(
            "SELECT int8range(5, 15) - int8range(10, 20)",
            Some(Type::Range(Box::new(Type::I64))),
        );
        // Plain integer bit-shift/bitwise ops must still work as before.
        check_expr("SELECT 1 << 2", Some(Type::Base(BaseType::Integer)));
        check_expr("SELECT 1 & 2", Some(Type::Base(BaseType::Integer)));

        // Range/multirange functions (Table 9.56, 9.57)
        check_expr("SELECT upper(int8range(15, 25))", Some(Type::I64));
        check_expr(
            "SELECT lower(numrange(1.1, 2.2))",
            Some(Type::Base(BaseType::Float)),
        );
        check_expr(
            "SELECT isempty(numrange(1.0, 5.0))",
            Some(Type::Base(BaseType::Bool)),
        );
        check_expr(
            "SELECT lower_inc(numrange(1.1, 2.2))",
            Some(Type::Base(BaseType::Bool)),
        );
        check_expr(
            "SELECT upper_inc(numrange(1.1, 2.2))",
            Some(Type::Base(BaseType::Bool)),
        );
        check_expr(
            "SELECT lower_inf('(,)'::daterange)",
            Some(Type::Base(BaseType::Bool)),
        );
        check_expr(
            "SELECT upper_inf('(,)'::daterange)",
            Some(Type::Base(BaseType::Bool)),
        );
        check_expr(
            "SELECT range_merge(int4range(1, 2), int4range(3, 4))",
            Some(Type::Range(Box::new(Type::I32))),
        );
        check_expr(
            "SELECT range_merge('{[1,2), [3,4)}'::int4multirange)",
            Some(Type::Range(Box::new(Type::I32))),
        );
        check_expr(
            "SELECT unnest('{[1,2), [3,4)}'::int4multirange)",
            Some(Type::Range(Box::new(Type::I32))),
        );

        // Columns typed from the schema, including lower()/upper() on a `tsrange` column
        // and the `int4multirange` -> `int4range` unnest() table-function overload.
        check_expr(
            "SELECT lower(during) FROM reservation",
            Some(Type::Base(BaseType::DateTime)),
        );
        check_expr(
            "SELECT r FROM unnest('{[1,2), [3,4)}'::int4multirange) AS u(r)",
            Some(Type::Range(Box::new(Type::I32))),
        );

        // Mismatched element types / mismatched range vs. multirange must be type errors.
        check_expr("SELECT int4range(1, 2) + numrange(1.0, 2.0)", None);
        check_expr("SELECT int4range(1, 2) + '{[1,2)}'::int4multirange", None);

        if errors != 0 {
            panic!("{errors} errors in postgresql_range_types test");
        }
    }

    /// A row-constructor `IN` a subquery that yields matching columns via
    /// `UNNEST($1::bigint[], $2::bigint[])`. Reported as a real-world usage that
    /// should type-check.
    #[test]
    fn postgresql_row_in_unnest_subquery() {
        let schema_src = "
        CREATE TABLE t1 (
            id bigint NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
            a bigint NOT NULL,
            b bigint NOT NULL
        );
        ";

        let opts = TypeOptions::new()
            .dialect(SQLDialect::PostgreSQL)
            .arguments(SQLArguments::Dollar);
        let mut issues = Issues::new(schema_src);
        let schema = parse_schemas(schema_src, &mut issues, &opts);
        let mut errors = 0;
        check_no_errors("schema", schema_src, issues.get(), &mut errors);

        let src = "SELECT id, a, b FROM t1
                WHERE (a, b) IN (
                    SELECT * FROM UNNEST($1::bigint[], $2::bigint[])
                )";
        let mut issues = Issues::new(src);
        let q = type_statement(&schema, src, &mut issues, &opts);
        check_no_errors("query", src, issues.get(), &mut errors);
        if let StatementType::Select { columns, arguments } = q {
            if columns.len() != 3 {
                println!("query: expected 3 columns, got {}", columns.len());
                errors += 1;
            }
            if arguments.len() != 2 {
                println!("query: expected 2 arguments, got {}", arguments.len());
                errors += 1;
            }
        } else {
            println!("query: expected Select");
            errors += 1;
        }

        if errors != 0 {
            panic!("{errors} errors in postgresql_row_in_unnest_subquery test");
        }
    }

    /// `col = ANY($1)` with a plain, uncast argument (no `$1::bigint[]`) should infer the
    /// argument as an array matching the column type. Reported as a real-world usage that
    /// should type-check with exactly one (array-typed) argument.
    #[test]
    fn postgresql_eq_any_uncast_arg() {
        let schema_src = "
        CREATE TABLE t1 (
            a_id bigint NOT NULL,
            b_id bigint NOT NULL
        );
        CREATE TABLE t2 (
            id bigint NOT NULL PRIMARY KEY,
            x bigint NOT NULL
        );
        ";

        let opts = TypeOptions::new()
            .dialect(SQLDialect::PostgreSQL)
            .arguments(SQLArguments::Dollar);
        let mut issues = Issues::new(schema_src);
        let schema = parse_schemas(schema_src, &mut issues, &opts);
        let mut errors = 0;
        check_no_errors("schema", schema_src, issues.get(), &mut errors);

        let src = "SELECT a_id, b_id, x FROM t1, t2 WHERE a_id = ANY($1) AND b_id = t2.id";
        let mut issues = Issues::new(src);
        let q = type_statement(&schema, src, &mut issues, &opts);
        check_no_errors("query", src, issues.get(), &mut errors);
        if let StatementType::Select { columns, arguments } = q {
            if columns.len() != 3 {
                println!("query: expected 3 columns, got {}", columns.len());
                errors += 1;
            }
            match arguments.first() {
                Some((_, t)) if t.t == Type::Array(Box::new(BaseType::Integer.into())) => {}
                Some((_, t)) => {
                    println!("query: expected argument 0 of type integer[] got {}", t.t);
                    errors += 1;
                }
                None => {
                    println!("query: expected an argument, got none");
                    errors += 1;
                }
            }
            if arguments.len() != 1 {
                println!("query: expected 1 argument, got {}", arguments.len());
                errors += 1;
            }
        } else {
            println!("query: expected Select");
            errors += 1;
        }

        if errors != 0 {
            panic!("{errors} errors in postgresql_eq_any_uncast_arg test");
        }
    }

    /// `INSERT ... SELECT * FROM UNNEST(...)` where one of the unnested arrays is `bytea[]`.
    /// Reported as a real-world usage that should type-check.
    #[test]
    fn postgresql_insert_unnest_bytea_array() {
        let schema_src = "
        CREATE TABLE t1 (
            a bigint NOT NULL,
            b bigint NOT NULL,
            payload bytea NOT NULL,
            PRIMARY KEY (a, b)
        );
        ";

        let opts = TypeOptions::new()
            .dialect(SQLDialect::PostgreSQL)
            .arguments(SQLArguments::Dollar);
        let mut issues = Issues::new(schema_src);
        let schema = parse_schemas(schema_src, &mut issues, &opts);
        let mut errors = 0;
        check_no_errors("schema", schema_src, issues.get(), &mut errors);

        let src = "INSERT INTO t1 (a, b, payload)
                    SELECT * FROM UNNEST($1::bigint[], $2::bigint[], $3::bytea[])
                    ON CONFLICT DO NOTHING
                    RETURNING a, b";
        let mut issues = Issues::new(src);
        let q = type_statement(&schema, src, &mut issues, &opts);
        check_no_errors("query", src, issues.get(), &mut errors);
        if let StatementType::Insert {
            arguments,
            returning,
            ..
        } = q
        {
            match returning {
                Some(columns) if columns.len() == 2 => {}
                Some(columns) => {
                    println!("query: expected 2 returning columns, got {}", columns.len());
                    errors += 1;
                }
                None => {
                    println!("query: expected returning columns, got none");
                    errors += 1;
                }
            }
            if arguments.len() != 3 {
                println!("query: expected 3 arguments, got {}", arguments.len());
                errors += 1;
            }
        } else {
            println!("query: expected Insert");
            errors += 1;
        }

        if errors != 0 {
            panic!("{errors} errors in postgresql_insert_unnest_bytea_array test");
        }
    }

    /// A writable CTE that unions a range into an `int8multirange` column and then
    /// `unnest()`s the result. Reported as a real-world usage that should type-check.
    #[test]
    fn postgresql_multirange_update_returning_unnest() {
        let schema_src = "
        CREATE TABLE partial_files (
            id uuid NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
            uploaded_bytes int8multirange NOT NULL DEFAULT '{}',
            last_modified timestamptz NOT NULL DEFAULT now()
        );
        ";

        let opts = TypeOptions::new()
            .dialect(SQLDialect::PostgreSQL)
            .arguments(SQLArguments::Dollar);
        let mut issues = Issues::new(schema_src);
        let schema = parse_schemas(schema_src, &mut issues, &opts);
        let mut errors = 0;
        check_no_errors("schema", schema_src, issues.get(), &mut errors);

        let src = "
        WITH the_update AS (
          UPDATE partial_files
          SET last_modified = now(),
            uploaded_bytes = uploaded_bytes + multirange(int8range($2, $3, '[)'))
          WHERE id = $1
          RETURNING uploaded_bytes
        )
        SELECT unnest(uploaded_bytes) AS \"range\"
        FROM the_update
        ";
        let mut issues = Issues::new(src);
        let q = type_statement(&schema, src, &mut issues, &opts);
        check_no_errors("query", src, issues.get(), &mut errors);
        if let StatementType::Select { columns, arguments } = q {
            match columns.first() {
                Some(c) if c.type_.t == Type::Range(Box::new(Type::I64)) => {}
                Some(c) => {
                    println!(
                        "query: expected column 0 of type range(i64) got {}",
                        c.type_.t
                    );
                    errors += 1;
                }
                None => {
                    println!("query: expected a column, got none");
                    errors += 1;
                }
            }
            if arguments.len() != 3 {
                println!("query: expected 3 arguments, got {}", arguments.len());
                errors += 1;
            }
        } else {
            println!("query: expected Select");
            errors += 1;
        }

        if errors != 0 {
            panic!("{errors} errors in postgresql_multirange_update_returning_unnest test");
        }
    }

    #[test]
    fn postgresql_alter_sequence() {
        let schema_src = "
        CREATE SEQUENCE tsng_datasets2_id_seq;
        ALTER SEQUENCE tsng_datasets2_id_seq OWNER TO compute;
        ALTER SEQUENCE tsng_datasets2_id_seq RENAME TO datasets_id_seq;
        ALTER SEQUENCE IF EXISTS datasets_id_seq
            INCREMENT BY 2
            MINVALUE 1
            MAXVALUE 1000
            START WITH 1
            RESTART WITH 42
            CACHE 10
            CYCLE
            OWNED BY none;
        ";

        let opts = TypeOptions::new()
            .dialect(SQLDialect::PostgreSQL)
            .arguments(SQLArguments::Dollar);
        let mut issues = Issues::new(schema_src);
        let schemas = parse_schemas(schema_src, &mut issues, &opts);
        let mut errors = 0;
        check_no_errors("schema", schema_src, issues.get(), &mut errors);

        // The sequence was renamed: the registry must contain the new name and no
        // longer the old one.
        use crate::schema::{QualifiedIdentifier, lookup_name};
        let search_path: &[&str] = &["public"];
        let new_key = QualifiedIdentifier::Qualified(
            Identifier::new("public", 0..0),
            Identifier::new("datasets_id_seq", 0..0),
        );
        if lookup_name(&schemas.sequences, &new_key, search_path).is_none() {
            println!("rename: datasets_id_seq missing from sequence registry");
            errors += 1;
        }
        let old_key = QualifiedIdentifier::Qualified(
            Identifier::new("public", 0..0),
            Identifier::new("tsng_datasets2_id_seq", 0..0),
        );
        if lookup_name(&schemas.sequences, &old_key, search_path).is_some() {
            println!("rename: old sequence name still in registry");
            errors += 1;
        }

        // Altering an unknown sequence is an error (without IF EXISTS)
        {
            let src = "ALTER SEQUENCE unknown_seq OWNER TO compute;";
            let mut issues = Issues::new(src);
            let _schemas = parse_schemas(src, &mut issues, &opts);
            if issues.get().is_empty() {
                println!("unknown seq: expected an error, got none");
                errors += 1;
            }
        }
        // ... but fine with IF EXISTS
        {
            let src = "ALTER SEQUENCE IF EXISTS unknown_seq OWNER TO compute;";
            let mut issues = Issues::new(src);
            let _schemas = parse_schemas(src, &mut issues, &opts);
            check_no_errors("if exists", src, issues.get(), &mut errors);
        }
        // Bare RESTART (with or without value) is valid in CREATE and ALTER
        {
            let src = "
            CREATE SEQUENCE s1 RESTART;
            CREATE SEQUENCE s2 RESTART WITH 42;
            CREATE SEQUENCE s3 RESTART 42;
            ALTER SEQUENCE s1 RESTART;
            ALTER SEQUENCE s2 RESTART WITH 42;
            ALTER SEQUENCE s3 RESTART 42;
            ";
            let mut issues = Issues::new(src);
            let _schemas = parse_schemas(src, &mut issues, &opts);
            check_no_errors("restart forms", src, issues.get(), &mut errors);
        }
        // RESTART WITH requires a value: bare RESTART alone may omit it
        for src in [
            "ALTER SEQUENCE s RESTART WITH;",
            "CREATE SEQUENCE s RESTART WITH;",
        ] {
            let mut issues = Issues::new(src);
            let _schemas = parse_schemas(src, &mut issues, &opts);
            if issues.get().is_empty() {
                println!("restart with: expected an error for '{src}', got none");
                errors += 1;
            }
        }
        // ALTER SEQUENCE with a bare name must resolve through the search path and
        // rename the canonical (schema-qualified) entry.
        {
            let src = "
            CREATE SCHEMA app;
            CREATE SEQUENCE app.s;
            SET search_path TO app;
            ALTER SEQUENCE s RENAME TO t;
            ";
            let mut issues = Issues::new(src);
            let schemas = parse_schemas(src, &mut issues, &opts);
            check_no_errors("search path rename", src, issues.get(), &mut errors);
            let in_app = |name: &str| {
                lookup_name(
                    &schemas.sequences,
                    &QualifiedIdentifier::Unqualified(Identifier::new(name, 0..0)),
                    ["app"].as_slice(),
                )
                .is_some()
            };
            if !in_app("t") {
                println!("search path rename: app.t missing from sequence registry");
                errors += 1;
            }
            if in_app("s") {
                println!("search path rename: app.s still in sequence registry");
                errors += 1;
            }
        }
        // A bare name that is not on the search path must not resolve to a sequence
        // in another schema.
        {
            let src = "
            CREATE SCHEMA app;
            CREATE SEQUENCE app.s;
            ALTER SEQUENCE s RENAME TO t;
            ";
            let mut issues = Issues::new(src);
            let _schemas = parse_schemas(src, &mut issues, &opts);
            if issues.get().is_empty() {
                println!("search path miss: expected an error, got none");
                errors += 1;
            }
        }

        if errors != 0 {
            panic!("{errors} errors in postgresql_alter_sequence test");
        }
    }
}