// Licensed to the Apache Software Foundation (ASF) under one

// or more contributor license agreements. See the NOTICE file

// distributed with this work for additional information

// regarding copyright ownership. The ASF licenses this file

// to you 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.

#![warn(clippy::all)]

//! Test SQL syntax specific to MySQL. The parser based on the generic dialect

//! is also tested (on the inputs it can handle).

use matches::assert_matches;

use sqlparser::ast::MysqlInsertPriority::{Delayed, HighPriority, LowPriority};

use sqlparser::ast::*;

use sqlparser::dialect::{GenericDialect, MySqlDialect};

use sqlparser::parser::{ParserError, ParserOptions};

use sqlparser::tokenizer::Token;

use test_utils::*;

#[macro_use]

mod test_utils;

#[test]

fn parse_identifiers() {

mysql().verified_stmt("SELECT $a$, àà");

}

#[test]

fn parse_literal_string() {

let sql = r#"SELECT 'single', "double""#;

let select = mysql().verified_only_select(sql);

assert_eq!(2, select.projection.len());

assert_eq!(

&Expr::Value(Value::SingleQuotedString("single".to_string())),

expr_from_projection(&select.projection[0])

);

assert_eq!(

&Expr::Value(Value::DoubleQuotedString("double".to_string())),

expr_from_projection(&select.projection[1])

);

}

#[test]

fn parse_flush() {

assert_eq!(

mysql_and_generic().verified_stmt("FLUSH OPTIMIZER_COSTS"),

Statement::Flush {

location: None,

object_type: FlushType::OptimizerCosts,

channel: None,

read_lock: false,

export: false,

tables: vec![]

}

);

assert_eq!(

mysql_and_generic().verified_stmt("FLUSH BINARY LOGS"),

Statement::Flush {

location: None,

object_type: FlushType::BinaryLogs,

channel: None,

read_lock: false,

export: false,

tables: vec![]

}

);

assert_eq!(

mysql_and_generic().verified_stmt("FLUSH ENGINE LOGS"),

Statement::Flush {

location: None,

object_type: FlushType::EngineLogs,

channel: None,

read_lock: false,

export: false,

tables: vec![]

}

);

assert_eq!(

mysql_and_generic().verified_stmt("FLUSH ERROR LOGS"),

Statement::Flush {

location: None,

object_type: FlushType::ErrorLogs,

channel: None,

read_lock: false,

export: false,

tables: vec![]

}

);

assert_eq!(

mysql_and_generic().verified_stmt("FLUSH NO_WRITE_TO_BINLOG GENERAL LOGS"),

Statement::Flush {

location: Some(FlushLocation::NoWriteToBinlog),

object_type: FlushType::GeneralLogs,

channel: None,

read_lock: false,

export: false,

tables: vec![]

}

);

assert_eq!(

mysql_and_generic().verified_stmt("FLUSH RELAY LOGS FOR CHANNEL test"),

Statement::Flush {

location: None,

object_type: FlushType::RelayLogs,

channel: Some("test".to_string()),

read_lock: false,

export: false,

tables: vec![]

}

);

assert_eq!(

mysql_and_generic().verified_stmt("FLUSH LOCAL SLOW LOGS"),

Statement::Flush {

location: Some(FlushLocation::Local),

object_type: FlushType::SlowLogs,

channel: None,

read_lock: false,

export: false,

tables: vec![]

}

);

assert_eq!(

mysql_and_generic().verified_stmt("FLUSH TABLES `mek`.`table1`, table2"),

Statement::Flush {

location: None,

object_type: FlushType::Tables,

channel: None,

read_lock: false,

export: false,

tables: vec![

ObjectName(vec![

Ident {

value: "mek".to_string(),

quote_style: Some('`')

},

Ident {

value: "table1".to_string(),

quote_style: Some('`')

}

]),

ObjectName(vec![Ident {

value: "table2".to_string(),

quote_style: None

}])

]

}

);

assert_eq!(

mysql_and_generic().verified_stmt("FLUSH TABLES WITH READ LOCK"),

Statement::Flush {

location: None,

object_type: FlushType::Tables,

channel: None,

read_lock: true,

export: false,

tables: vec![]

}

);

assert_eq!(

mysql_and_generic().verified_stmt("FLUSH TABLES `mek`.`table1`, table2 WITH READ LOCK"),

Statement::Flush {

location: None,

object_type: FlushType::Tables,

channel: None,

read_lock: true,

export: false,

tables: vec![

ObjectName(vec![

Ident {

value: "mek".to_string(),

quote_style: Some('`')

},

Ident {

value: "table1".to_string(),

quote_style: Some('`')

}

]),

ObjectName(vec![Ident {

value: "table2".to_string(),

quote_style: None

}])

]

}

);

assert_eq!(

mysql_and_generic().verified_stmt("FLUSH TABLES `mek`.`table1`, table2 FOR EXPORT"),

Statement::Flush {

location: None,

object_type: FlushType::Tables,

channel: None,

read_lock: false,

export: true,

tables: vec![

ObjectName(vec![

Ident {

value: "mek".to_string(),

quote_style: Some('`')

},

Ident {

value: "table1".to_string(),

quote_style: Some('`')

}

]),

ObjectName(vec![Ident {

value: "table2".to_string(),

quote_style: None

}])

]

}

);

}

#[test]

fn parse_show_columns() {

assert_eq!(

mysql_and_generic().verified_stmt("SHOW COLUMNS FROM mytable"),

Statement::ShowColumns {

extended: false,

full: false,

show_options: ShowStatementOptions {

show_in: Some(ShowStatementIn {

clause: ShowStatementInClause::FROM,

parent_type: None,

parent_name: Some(ObjectName(vec![Ident::new("mytable")])),

}),

filter_position: None,

limit_from: None,

limit: None,

starts_with: None,

}

}

);

assert_eq!(

mysql_and_generic().verified_stmt("SHOW COLUMNS FROM mydb.mytable"),

Statement::ShowColumns {

extended: false,

full: false,

show_options: ShowStatementOptions {

show_in: Some(ShowStatementIn {

clause: ShowStatementInClause::FROM,

parent_type: None,

parent_name: Some(ObjectName(vec![Ident::new("mydb"), Ident::new("mytable")])),

}),

filter_position: None,

limit_from: None,

limit: None,

starts_with: None,

}

}

);

assert_eq!(

mysql_and_generic().verified_stmt("SHOW EXTENDED COLUMNS FROM mytable"),

Statement::ShowColumns {

extended: true,

full: false,

show_options: ShowStatementOptions {

show_in: Some(ShowStatementIn {

clause: ShowStatementInClause::FROM,

parent_type: None,

parent_name: Some(ObjectName(vec![Ident::new("mytable")])),

}),

filter_position: None,

limit_from: None,

limit: None,

starts_with: None,

}

}

);

assert_eq!(

mysql_and_generic().verified_stmt("SHOW FULL COLUMNS FROM mytable"),

Statement::ShowColumns {

extended: false,

full: true,

show_options: ShowStatementOptions {

show_in: Some(ShowStatementIn {

clause: ShowStatementInClause::FROM,

parent_type: None,

parent_name: Some(ObjectName(vec![Ident::new("mytable")])),

}),

filter_position: None,

limit_from: None,

limit: None,

starts_with: None,

}

}

);

assert_eq!(

mysql_and_generic().verified_stmt("SHOW COLUMNS FROM mytable LIKE 'pattern'"),

Statement::ShowColumns {

extended: false,

full: false,

show_options: ShowStatementOptions {

show_in: Some(ShowStatementIn {

clause: ShowStatementInClause::FROM,

parent_type: None,

parent_name: Some(ObjectName(vec![Ident::new("mytable")])),

}),

filter_position: Some(ShowStatementFilterPosition::Suffix(

ShowStatementFilter::Like("pattern".into())

)),

limit_from: None,

limit: None,

starts_with: None,

}

}

);

assert_eq!(

mysql_and_generic().verified_stmt("SHOW COLUMNS FROM mytable WHERE 1 = 2"),

Statement::ShowColumns {

extended: false,

full: false,

show_options: ShowStatementOptions {

show_in: Some(ShowStatementIn {

clause: ShowStatementInClause::FROM,

parent_type: None,

parent_name: Some(ObjectName(vec![Ident::new("mytable")])),

}),

filter_position: Some(ShowStatementFilterPosition::Suffix(

ShowStatementFilter::Where(mysql_and_generic().verified_expr("1 = 2"))

)),

limit_from: None,

limit: None,

starts_with: None,

}

}

);

mysql_and_generic()

.one_statement_parses_to("SHOW FIELDS FROM mytable", "SHOW COLUMNS FROM mytable");

mysql_and_generic()

.one_statement_parses_to("SHOW COLUMNS IN mytable", "SHOW COLUMNS IN mytable");

mysql_and_generic()

.one_statement_parses_to("SHOW FIELDS IN mytable", "SHOW COLUMNS IN mytable");

mysql_and_generic().one_statement_parses_to(

"SHOW COLUMNS FROM mytable FROM mydb",

"SHOW COLUMNS FROM mydb.mytable",

);

}

#[test]

fn parse_show_status() {

assert_eq!(

mysql_and_generic().verified_stmt("SHOW SESSION STATUS LIKE 'ssl_cipher'"),

Statement::ShowStatus {

filter: Some(ShowStatementFilter::Like("ssl_cipher".into())),

session: true,

global: false

}

);

assert_eq!(

mysql_and_generic().verified_stmt("SHOW GLOBAL STATUS LIKE 'ssl_cipher'"),

Statement::ShowStatus {

filter: Some(ShowStatementFilter::Like("ssl_cipher".into())),

session: false,

global: true

}

);

assert_eq!(

mysql_and_generic().verified_stmt("SHOW STATUS WHERE value = 2"),

Statement::ShowStatus {

filter: Some(ShowStatementFilter::Where(

mysql_and_generic().verified_expr("value = 2")

)),

session: false,

global: false

}

);

}

#[test]

fn parse_show_tables() {

assert_eq!(

mysql_and_generic().verified_stmt("SHOW TABLES"),

Statement::ShowTables {

terse: false,

history: false,

extended: false,

full: false,

external: false,

show_options: ShowStatementOptions {

starts_with: None,

limit: None,

limit_from: None,

show_in: None,

filter_position: None

}

}

);

assert_eq!(

mysql_and_generic().verified_stmt("SHOW TABLES FROM mydb"),

Statement::ShowTables {

terse: false,

history: false,

extended: false,

full: false,

external: false,

show_options: ShowStatementOptions {

starts_with: None,

limit: None,

limit_from: None,

show_in: Some(ShowStatementIn {

clause: ShowStatementInClause::FROM,

parent_type: None,

parent_name: Some(ObjectName(vec![Ident::new("mydb")])),

}),

filter_position: None

}

}

);

assert_eq!(

mysql_and_generic().verified_stmt("SHOW EXTENDED TABLES"),

Statement::ShowTables {

terse: false,

history: false,

extended: true,

full: false,

external: false,

show_options: ShowStatementOptions {

starts_with: None,

limit: None,

limit_from: None,

show_in: None,

filter_position: None

}

}

);

assert_eq!(

mysql_and_generic().verified_stmt("SHOW FULL TABLES"),

Statement::ShowTables {

terse: false,

history: false,

extended: false,

full: true,

external: false,

show_options: ShowStatementOptions {

starts_with: None,

limit: None,

limit_from: None,

show_in: None,

filter_position: None

}

}

);

assert_eq!(

mysql_and_generic().verified_stmt("SHOW TABLES LIKE 'pattern'"),

Statement::ShowTables {

terse: false,

history: false,

extended: false,

full: false,

external: false,

show_options: ShowStatementOptions {

starts_with: None,

limit: None,

limit_from: None,

show_in: None,

filter_position: Some(ShowStatementFilterPosition::Suffix(

ShowStatementFilter::Like("pattern".into())

))

}

}

);

assert_eq!(

mysql_and_generic().verified_stmt("SHOW TABLES WHERE 1 = 2"),

Statement::ShowTables {

terse: false,

history: false,

extended: false,

full: false,

external: false,

show_options: ShowStatementOptions {

starts_with: None,

limit: None,

limit_from: None,

show_in: None,

filter_position: Some(ShowStatementFilterPosition::Suffix(

ShowStatementFilter::Where(mysql_and_generic().verified_expr("1 = 2"))

))

}

}

);

mysql_and_generic().verified_stmt("SHOW TABLES IN mydb");

mysql_and_generic().verified_stmt("SHOW TABLES FROM mydb");

}

#[test]

fn parse_show_extended_full() {

assert!(mysql_and_generic()

.parse_sql_statements("SHOW EXTENDED FULL TABLES")

.is_ok());

assert!(mysql_and_generic()

.parse_sql_statements("SHOW EXTENDED FULL COLUMNS FROM mytable")

.is_ok());

// SHOW EXTENDED/FULL can only be used with COLUMNS and TABLES

assert!(mysql_and_generic()

.parse_sql_statements("SHOW EXTENDED FULL CREATE TABLE mytable")

.is_err());

assert!(mysql_and_generic()

.parse_sql_statements("SHOW EXTENDED FULL COLLATION")

.is_err());

assert!(mysql_and_generic()

.parse_sql_statements("SHOW EXTENDED FULL VARIABLES")

.is_err());

}

#[test]

fn parse_show_create() {

let obj_name = ObjectName(vec![Ident::new("myident")]);

for obj_type in &[

ShowCreateObject::Table,

ShowCreateObject::Trigger,

ShowCreateObject::Event,

ShowCreateObject::Function,

ShowCreateObject::Procedure,

ShowCreateObject::View,

] {

assert_eq!(

mysql_and_generic().verified_stmt(format!("SHOW CREATE {obj_type} myident").as_str()),

Statement::ShowCreate {

obj_type: *obj_type,

obj_name: obj_name.clone(),

}

);

}

}

#[test]

fn parse_show_collation() {

assert_eq!(

mysql_and_generic().verified_stmt("SHOW COLLATION"),

Statement::ShowCollation { filter: None }

);

assert_eq!(

mysql_and_generic().verified_stmt("SHOW COLLATION LIKE 'pattern'"),

Statement::ShowCollation {

filter: Some(ShowStatementFilter::Like("pattern".into())),

}

);

assert_eq!(

mysql_and_generic().verified_stmt("SHOW COLLATION WHERE 1 = 2"),

Statement::ShowCollation {

filter: Some(ShowStatementFilter::Where(

mysql_and_generic().verified_expr("1 = 2")

)),

}

);

}

#[test]

fn parse_use() {

let valid_object_names = [

"mydb",

"SCHEMA",

"DATABASE",

"CATALOG",

"WAREHOUSE",

"DEFAULT",

];

let quote_styles = ['\'', '"', '`'];

for object_name in &valid_object_names {

// Test single identifier without quotes

assert_eq!(

mysql_and_generic().verified_stmt(&format!("USE {}", object_name)),

Statement::Use(Use::Object(ObjectName(vec![Ident::new(

object_name.to_string()

)])))

);

for &quote in &quote_styles {

// Test single identifier with different type of quotes

assert_eq!(

mysql_and_generic()

.verified_stmt(&format!("USE {}{}{}", quote, object_name, quote)),

Statement::Use(Use::Object(ObjectName(vec![Ident::with_quote(

quote,

object_name.to_string(),

)])))

);

}

}

}

#[test]

fn parse_set_variables() {

mysql_and_generic().verified_stmt("SET sql_mode = CONCAT(@@sql_mode, ',STRICT_TRANS_TABLES')");

assert_eq!(

mysql_and_generic().verified_stmt("SET LOCAL autocommit = 1"),

Statement::SetVariable {

local: true,

hivevar: false,

variables: OneOrManyWithParens::One(ObjectName(vec!["autocommit".into()])),

value: vec![Expr::Value(number("1"))],

}

);

}

#[test]

fn parse_create_table_auto_increment() {

let sql = "CREATE TABLE foo (bar INT PRIMARY KEY AUTO_INCREMENT)";

match mysql().verified_stmt(sql) {

Statement::CreateTable(CreateTable { name, columns, .. }) => {

assert_eq!(name.to_string(), "foo");

assert_eq!(

vec![ColumnDef {

name: Ident::new("bar"),

data_type: DataType::Int(None),

collation: None,

options: vec![

ColumnOptionDef {

name: None,

option: ColumnOption::Unique {

is_primary: true,

characteristics: None

},

},

ColumnOptionDef {

name: None,

option: ColumnOption::DialectSpecific(vec![Token::make_keyword(

"AUTO_INCREMENT"

)]),

},

],

}],

columns

);

}

_ => unreachable!(),

}

}

/// if `unique_index_type_display` is `Some` create `TableConstraint::Unique`

/// otherwise create `TableConstraint::Primary`

fn table_constraint_unique_primary_ctor(

name: Option<Ident>,

index_name: Option<Ident>,

index_type: Option<IndexType>,

columns: Vec<Ident>,

index_options: Vec<IndexOption>,

characteristics: Option<ConstraintCharacteristics>,

unique_index_type_display: Option<KeyOrIndexDisplay>,

) -> TableConstraint {

match unique_index_type_display {

Some(index_type_display) => TableConstraint::Unique {

name,

index_name,

index_type_display,

index_type,

columns,

index_options,

characteristics,

},

None => TableConstraint::PrimaryKey {

name,

index_name,

index_type,

columns,

index_options,

characteristics,

},

}

}

#[test]

fn parse_create_table_primary_and_unique_key() {

let sqls = ["UNIQUE KEY", "PRIMARY KEY"]

.map(|key_ty|format!("CREATE TABLE foo (id INT PRIMARY KEY AUTO_INCREMENT, bar INT NOT NULL, CONSTRAINT bar_key {key_ty} (bar))"));

let index_type_display = [Some(KeyOrIndexDisplay::Key), None];

for (sql, index_type_display) in sqls.iter().zip(index_type_display) {

match mysql().one_statement_parses_to(sql, "") {

Statement::CreateTable(CreateTable {

name,

columns,

constraints,

..

}) => {

assert_eq!(name.to_string(), "foo");

let expected_constraint = table_constraint_unique_primary_ctor(

Some(Ident::new("bar_key")),

None,

None,

vec![Ident::new("bar")],

vec![],

None,

index_type_display,

);

assert_eq!(vec![expected_constraint], constraints);

assert_eq!(

vec![

ColumnDef {

name: Ident::new("id"),

data_type: DataType::Int(None),

collation: None,

options: vec![

ColumnOptionDef {

name: None,

option: ColumnOption::Unique {

is_primary: true,

characteristics: None

},

},

ColumnOptionDef {

name: None,

option: ColumnOption::DialectSpecific(vec![

Token::make_keyword("AUTO_INCREMENT")

]),

},

],

},

ColumnDef {

name: Ident::new("bar"),

data_type: DataType::Int(None),

collation: None,

options: vec![ColumnOptionDef {

name: None,

option: ColumnOption::NotNull,

},],

},

],

columns

);

}

_ => unreachable!(),

}

}

}

#[test]

fn parse_create_table_primary_and_unique_key_with_index_options() {

let sqls = ["UNIQUE INDEX", "PRIMARY KEY"]

.map(|key_ty|format!("CREATE TABLE foo (bar INT, var INT, CONSTRAINT constr {key_ty} index_name (bar, var) USING HASH COMMENT 'yes, ' USING BTREE COMMENT 'MySQL allows')"));

let index_type_display = [Some(KeyOrIndexDisplay::Index), None];

for (sql, index_type_display) in sqls.iter().zip(index_type_display) {

match mysql_and_generic().one_statement_parses_to(sql, "") {

Statement::CreateTable(CreateTable {

name, constraints, ..

}) => {

assert_eq!(name.to_string(), "foo");

let expected_constraint = table_constraint_unique_primary_ctor(

Some(Ident::new("constr")),

Some(Ident::new("index_name")),

None,

vec![Ident::new("bar"), Ident::new("var")],

vec![

IndexOption::Using(IndexType::Hash),

IndexOption::Comment("yes, ".into()),

IndexOption::Using(IndexType::BTree),

IndexOption::Comment("MySQL allows".into()),

],

None,

index_type_display,

);

assert_eq!(vec![expected_constraint], constraints);

}

_ => unreachable!(),

}

mysql_and_generic().verified_stmt(sql);

}

}

#[test]

fn parse_create_table_primary_and_unique_key_with_index_type() {

let sqls = ["UNIQUE", "PRIMARY KEY"].map(|key_ty| {

format!("CREATE TABLE foo (bar INT, {key_ty} index_name USING BTREE (bar) USING HASH)")

});

let index_type_display = [Some(KeyOrIndexDisplay::None), None];

for (sql, index_type_display) in sqls.iter().zip(index_type_display) {

match mysql_and_generic().one_statement_parses_to(sql, "") {

Statement::CreateTable(CreateTable {

name, constraints, ..

}) => {

assert_eq!(name.to_string(), "foo");

let expected_constraint = table_constraint_unique_primary_ctor(

None,

Some(Ident::new("index_name")),

Some(IndexType::BTree),

vec![Ident::new("bar")],

vec![IndexOption::Using(IndexType::Hash)],

None,

index_type_display,

);

assert_eq!(vec![expected_constraint], constraints);

}

_ => unreachable!(),

}

mysql_and_generic().verified_stmt(sql);

}

let sql = "CREATE TABLE foo (bar INT, UNIQUE INDEX index_name USING BTREE (bar) USING HASH)";

mysql_and_generic().verified_stmt(sql);

let sql = "CREATE TABLE foo (bar INT, PRIMARY KEY index_name USING BTREE (bar) USING HASH)";

mysql_and_generic().verified_stmt(sql);

}

#[test]

fn parse_create_table_primary_and_unique_key_characteristic_test() {

let sqls = ["UNIQUE INDEX", "PRIMARY KEY"]

.map(|key_ty|format!("CREATE TABLE x (y INT, CONSTRAINT constr {key_ty} (y) NOT DEFERRABLE INITIALLY IMMEDIATE)"));

for sql in &sqls {

mysql_and_generic().verified_stmt(sql);

}

}

#[test]

fn parse_create_table_comment() {

let without_equal = "CREATE TABLE foo (bar INT) COMMENT 'baz'";

let with_equal = "CREATE TABLE foo (bar INT) COMMENT = 'baz'";

for sql in [without_equal, with_equal] {

match mysql().verified_stmt(sql) {

Statement::CreateTable(CreateTable { name, comment, .. }) => {

assert_eq!(name.to_string(), "foo");

assert_eq!(comment.expect("Should exist").to_string(), "baz");

}

_ => unreachable!(),

}

}

}

#[test]

fn parse_create_table_auto_increment_offset() {

let canonical =

"CREATE TABLE foo (bar INT NOT NULL AUTO_INCREMENT) ENGINE=InnoDB AUTO_INCREMENT 123";

let with_equal =

"CREATE TABLE foo (bar INT NOT NULL AUTO_INCREMENT) ENGINE=InnoDB AUTO_INCREMENT=123";

for sql in [canonical, with_equal] {

match mysql().one_statement_parses_to(sql, canonical) {

Statement::CreateTable(CreateTable {

name,

auto_increment_offset,

..

}) => {

assert_eq!(name.to_string(), "foo");

assert_eq!(

auto_increment_offset.expect("Should exist").to_string(),

"123"

);

}

_ => unreachable!(),

}

}

}

#[test]

fn parse_create_table_set_enum() {

let sql = "CREATE TABLE foo (bar SET('a', 'b'), baz ENUM('a', 'b'))";

match mysql().verified_stmt(sql) {

Statement::CreateTable(CreateTable { name, columns, .. }) => {

assert_eq!(name.to_string(), "foo");

assert_eq!(

vec![

ColumnDef {

name: Ident::new("bar"),

data_type: DataType::Set(vec!["a".to_string(), "b".to_string()]),

collation: None,

options: vec![],

},

ColumnDef {

name: Ident::new("baz"),

data_type: DataType::Enum(vec!["a".to_string(), "b".to_string()]),

collation: None,

options: vec![],

}

],

columns

);

}

_ => unreachable!(),

}

}

#[test]

fn parse_create_table_engine_default_charset() {

let sql = "CREATE TABLE foo (id INT(11)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3";

match mysql().verified_stmt(sql) {

Statement::CreateTable(CreateTable {

name,

columns,

engine,

default_charset,

..

}) => {

assert_eq!(name.to_string(), "foo");

assert_eq!(

vec![ColumnDef {

name: Ident::new("id"),

data_type: DataType::Int(Some(11)),

collation: None,

options: vec![],

},],

columns

);

assert_eq!(

engine,

Some(TableEngine {

name: "InnoDB".to_string(),

parameters: None

})

);

assert_eq!(default_charset, Some("utf8mb3".to_string()));

}

_ => unreachable!(),

}

}

#[test]

fn parse_create_table_collate() {

let sql = "CREATE TABLE foo (id INT(11)) COLLATE=utf8mb4_0900_ai_ci";

match mysql().verified_stmt(sql) {

Statement::CreateTable(CreateTable {

name,

columns,

collation,

..

}) => {

assert_eq!(name.to_string(), "foo");

assert_eq!(

vec![ColumnDef {

name: Ident::new("id"),

data_type: DataType::Int(Some(11)),

collation: None,

options: vec![],

},],

columns

);

assert_eq!(collation, Some("utf8mb4_0900_ai_ci".to_string()));

}

_ => unreachable!(),

}

}

#[test]

fn parse_create_table_both_options_and_as_query() {

let sql = "CREATE TABLE foo (id INT(11)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb4_0900_ai_ci AS SELECT 1";

match mysql_and_generic().verified_stmt(sql) {

Statement::CreateTable(CreateTable {

name,

collation,

query,

..

}) => {

assert_eq!(name.to_string(), "foo");

assert_eq!(collation, Some("utf8mb4_0900_ai_ci".to_string()));

assert_eq!(

query.unwrap().body.as_select().unwrap().projection,

vec![SelectItem::UnnamedExpr(Expr::Value(number("1")))]

);

}

_ => unreachable!(),

}

let sql = r"CREATE TABLE foo (id INT(11)) ENGINE=InnoDB AS SELECT 1 DEFAULT CHARSET=utf8mb3";

assert!(matches!(

mysql_and_generic().parse_sql_statements(sql),

Err(ParserError::ParserError(_))

));

}

#[test]

fn parse_create_table_comment_character_set() {

let sql = "CREATE TABLE foo (s TEXT CHARACTER SET utf8mb4 COMMENT 'comment')";

match mysql().verified_stmt(sql) {

Statement::CreateTable(CreateTable { name, columns, .. }) => {

assert_eq!(name.to_string(), "foo");

assert_eq!(

vec![ColumnDef {

name: Ident::new("s"),

data_type: DataType::Text,

collation: None,

options: vec![

ColumnOptionDef {

name: None,

option: ColumnOption::CharacterSet(ObjectName(vec![Ident::new(

"utf8mb4"

)]))

},

ColumnOptionDef {

name: None,

option: ColumnOption::Comment("comment".to_string())