let dialects = all_dialects_where(|d| d.supports_numeric_literal_underscores());

let canonical = if cfg!(feature = "bigdecimal") {

"SELECT 10000"

} else {

"SELECT 10_000"

};

let select = dialects.verified_only_select_with_canonical("SELECT 10_000", canonical);

assert_eq!(

select.projection,

vec![UnnamedExpr(Expr::Value(number("10_000").with_empty_span()))]

);

let select = verified_only_select("SELECT a.b.c.d(1, 2, 3) FROM T");

let Expr::Function(func) = expr_from_projection(&select.projection[0]) else {

unreachable!()

};

assert_eq!(

ObjectName::from(

["a", "b", "c", "d"]

.into_iter()

.map(Ident::new)

.collect::<Vec<_>>()

),

func.name,

);

let row = vec![

Expr::value(number("1")),

Expr::value(number("2")),

Expr::value(number("3")),

];

let rows1 = vec![row.clone()];

let rows2 = vec![row.clone(), row];

let sql = "INSERT customer VALUES (1, 2, 3)";

check_one(sql, "customer", &[], &rows1, false);

let sql = "INSERT INTO customer VALUES (1, 2, 3)";

check_one(sql, "customer", &[], &rows1, false);

let sql = "INSERT INTO customer VALUES (1, 2, 3), (1, 2, 3)";

check_one(sql, "customer", &[], &rows2, false);

let sql = "INSERT INTO public.customer VALUES (1, 2, 3)";

check_one(sql, "public.customer", &[], &rows1, false);

let sql = "INSERT INTO db.public.customer VALUES (1, 2, 3)";

check_one(sql, "db.public.customer", &[], &rows1, false);

let sql = "INSERT INTO public.customer (id, name, active) VALUES (1, 2, 3)";

check_one(

sql,

"public.customer",

&["id".to_string(), "name".to_string(), "active".to_string()],

&rows1,

false,

);

let sql = r"INSERT INTO t (id, name, active) VALUE (1, 2, 3)";

check_one(

sql,

"t",

&["id".to_string(), "name".to_string(), "active".to_string()],

&rows1,

true,

);

fn check_one(

sql: &str,

expected_table_name: &str,

expected_columns: &[String],

expected_rows: &[Vec<Expr>],

expected_value_keyword: bool,

) {

match verified_stmt(sql) {

Statement::Insert(Insert {

table: table_name,

columns,

source: Some(source),

..

}) => {

assert_eq!(table_name.to_string(), expected_table_name);

assert_eq!(columns.len(), expected_columns.len());

for (index, column) in columns.iter().enumerate() {

assert_eq!(column, &Ident::new(expected_columns[index].clone()));

}

match *source.body {

SetExpr::Values(Values {

rows,

value_keyword,

..

}) => {

assert_eq!(rows.as_slice(), expected_rows);

assert!(value_keyword == expected_value_keyword);

}

_ => unreachable!(),

}

}

_ => unreachable!(),

}

}

verified_stmt("INSERT INTO customer WITH foo AS (SELECT 1) SELECT * FROM foo UNION VALUES (1)");

let dialect = PostgreSqlDialect {};

let sql = "REPLACE INTO public.customer (id, name, active) VALUES (1, 2, 3)";

assert_eq!(

ParserError::ParserError("Unsupported statement REPLACE at Line: 1, Column: 9".to_string()),

Parser::parse_sql(&dialect, sql,).unwrap_err(),

)

let insert_with_default_values = verified_stmt("INSERT INTO test_table DEFAULT VALUES");

match insert_with_default_values {

Statement::Insert(Insert {

after_columns,

columns,

on,

partitioned,

returning,

source,

table: table_name,

..

}) => {

assert_eq!(columns, vec![]);

assert_eq!(after_columns, vec![]);

assert_eq!(on, None);

assert_eq!(partitioned, None);

assert_eq!(returning, None);

assert_eq!(source, None);

assert_eq!(

table_name,

TableObject::TableName(ObjectName::from(vec!["test_table".into()]))

);

}

_ => unreachable!(),

}

let insert_with_default_values_and_returning =

verified_stmt("INSERT INTO test_table DEFAULT VALUES RETURNING test_column");

match insert_with_default_values_and_returning {

Statement::Insert(Insert {

after_columns,

columns,

on,

partitioned,

returning,

source,

table: table_name,

..

}) => {

assert_eq!(after_columns, vec![]);

assert_eq!(columns, vec![]);

assert_eq!(on, None);

assert_eq!(partitioned, None);

assert!(returning.is_some());

assert_eq!(source, None);

assert_eq!(

table_name,

TableObject::TableName(ObjectName::from(vec!["test_table".into()]))

);

}

_ => unreachable!(),

}

let insert_with_default_values_and_on_conflict =

verified_stmt("INSERT INTO test_table DEFAULT VALUES ON CONFLICT DO NOTHING");

match insert_with_default_values_and_on_conflict {

Statement::Insert(Insert {

after_columns,

columns,

on,

partitioned,

returning,

source,

table: table_name,

..

}) => {

assert_eq!(after_columns, vec![]);

assert_eq!(columns, vec![]);

assert!(on.is_some());

assert_eq!(partitioned, None);

assert_eq!(returning, None);

assert_eq!(source, None);

assert_eq!(

table_name,

TableObject::TableName(ObjectName::from(vec!["test_table".into()]))

);

}

_ => unreachable!(),

}

let insert_with_columns_and_default_values = "INSERT INTO test_table (test_col) DEFAULT VALUES";

assert_eq!(

ParserError::ParserError(

"Expected: SELECT, VALUES, or a subquery in the query body, found: DEFAULT".to_string()

),

parse_sql_statements(insert_with_columns_and_default_values).unwrap_err()

);

let insert_with_default_values_and_hive_after_columns =

"INSERT INTO test_table DEFAULT VALUES (some_column)";

assert_eq!(

ParserError::ParserError("Expected: end of statement, found: (".to_string()),

parse_sql_statements(insert_with_default_values_and_hive_after_columns).unwrap_err()

);

let insert_with_default_values_and_hive_partition =

"INSERT INTO test_table DEFAULT VALUES PARTITION (some_column)";

assert_eq!(

ParserError::ParserError("Expected: end of statement, found: PARTITION".to_string()),

parse_sql_statements(insert_with_default_values_and_hive_partition).unwrap_err()

);

let insert_with_default_values_and_values_list = "INSERT INTO test_table DEFAULT VALUES (1)";

assert_eq!(

ParserError::ParserError("Expected: end of statement, found: (".to_string()),

parse_sql_statements(insert_with_default_values_and_values_list).unwrap_err()

);

// Dialects that support `RETURNING` as a column identifier do

// not support this syntax.

let dialects =

all_dialects_where(|d| !d.is_column_alias(&Keyword::RETURNING, &mut Parser::new(d)));

dialects.verified_stmt("INSERT INTO t SELECT 1 RETURNING 2");

let stmt = dialects.verified_stmt("INSERT INTO t SELECT x RETURNING x AS y");

match stmt {

Statement::Insert(Insert {

returning: Some(ret),

source: Some(_),

..

}) => assert_eq!(ret.len(), 1),

_ => unreachable!(),

}

let sql = "INSERT INTO table1 SELECT * FROM table2 RETURNING id";

match verified_stmt(sql) {

Statement::Insert(Insert {

table: TableObject::TableName(table_name),

source: Some(source),

returning: Some(returning),

..

}) => {

assert_eq!("table1", table_name.to_string());

assert!(matches!(*source.body, SetExpr::Select(_)));

assert_eq!(

returning,

vec![SelectItem::UnnamedExpr(Expr::Identifier(Ident::new("id"))),]

);

}

bad_stmt => unreachable!("Expected valid insert, got {:?}", bad_stmt),

}

let dialect = SQLiteDialect {};

let check = |sql: &str, expected_action: Option<SqliteOnConflict>| match Parser::parse_sql(

&dialect, sql,

)

.unwrap()

.pop()

.unwrap()

{

Statement::Insert(Insert { or, .. }) => assert_eq!(or, expected_action),

_ => panic!("{}", sql),

};

let sql = "INSERT INTO test_table(id) VALUES(1)";

check(sql, None);

let sql = "REPLACE INTO test_table(id) VALUES(1)";

check(sql, Some(SqliteOnConflict::Replace));

let sql = "INSERT OR REPLACE INTO test_table(id) VALUES(1)";

check(sql, Some(SqliteOnConflict::Replace));

let sql = "INSERT OR ROLLBACK INTO test_table(id) VALUES(1)";

check(sql, Some(SqliteOnConflict::Rollback));

let sql = "INSERT OR ABORT INTO test_table(id) VALUES(1)";

check(sql, Some(SqliteOnConflict::Abort));

let sql = "INSERT OR FAIL INTO test_table(id) VALUES(1)";

check(sql, Some(SqliteOnConflict::Fail));

let sql = "INSERT OR IGNORE INTO test_table(id) VALUES(1)";

check(sql, Some(SqliteOnConflict::Ignore));

let sql = "UPDATE t SET a = 1, b = 2, c = 3 WHERE d";

match verified_stmt(sql) {

Statement::Update(Update {

table,

assignments,

selection,

..

}) => {

assert_eq!(table.to_string(), "t".to_string());

assert_eq!(

assignments,

vec![

Assignment {

target: AssignmentTarget::ColumnName(ObjectName::from(vec!["a".into()])),

value: Expr::value(number("1")),

},

Assignment {

target: AssignmentTarget::ColumnName(ObjectName::from(vec!["b".into()])),

value: Expr::value(number("2")),

},

Assignment {

target: AssignmentTarget::ColumnName(ObjectName::from(vec!["c".into()])),

value: Expr::value(number("3")),

},

]

);

assert_eq!(selection.unwrap(), Expr::Identifier("d".into()));

}

_ => unreachable!(),

}

verified_stmt("UPDATE t SET a = 1, a = 2, a = 3");

let sql = "UPDATE t WHERE 1";

let res = parse_sql_statements(sql);

assert_eq!(

ParserError::ParserError("Expected: SET, found: WHERE".to_string()),

res.unwrap_err()

);

let sql = "UPDATE t SET a = 1 extrabadstuff";

let res = parse_sql_statements(sql);

assert_eq!(

ParserError::ParserError("Expected: end of statement, found: extrabadstuff".to_string()),

res.unwrap_err()

);

let sql = "UPDATE t1 SET name = t2.name FROM (SELECT name, id FROM t1 GROUP BY id) AS t2 WHERE t1.id = t2.id";

let dialects = TestedDialects::new(vec![

Box::new(GenericDialect {}),

Box::new(DuckDbDialect {}),

Box::new(PostgreSqlDialect {}),

Box::new(BigQueryDialect {}),

Box::new(SnowflakeDialect {}),

Box::new(RedshiftSqlDialect {}),

Box::new(MsSqlDialect {}),

Box::new(SQLiteDialect {}),

]);

let stmt = dialects.verified_stmt(sql);

assert_eq!(

stmt,

Statement::Update(Update {

update_token: AttachedToken::empty(),

optimizer_hints: vec![],

table: TableWithJoins {

relation: table_from_name(ObjectName::from(vec![Ident::new("t1")])),

joins: vec![],

},

assignments: vec![Assignment {

target: AssignmentTarget::ColumnName(ObjectName::from(vec![Ident::new("name")])),

value: Expr::CompoundIdentifier(vec![Ident::new("t2"), Ident::new("name")])

}],

from: Some(UpdateTableFromKind::AfterSet(vec![TableWithJoins {

relation: TableFactor::Derived {

lateral: false,

subquery: Box::new(Query {

with: None,

body: Box::new(SetExpr::Select(Box::new(Select {

select_token: AttachedToken::empty(),

optimizer_hints: vec![],

distinct: None,

select_modifiers: None,

top: None,

top_before_distinct: false,

projection: vec![

SelectItem::UnnamedExpr(Expr::Identifier(Ident::new("name"))),

SelectItem::UnnamedExpr(Expr::Identifier(Ident::new("id"))),

],

exclude: None,

into: None,

from: vec![TableWithJoins {

relation: table_from_name(ObjectName::from(vec![Ident::new("t1")])),

joins: vec![],

}],

lateral_views: vec![],

prewhere: None,

selection: None,

group_by: GroupByExpr::Expressions(

vec![Expr::Identifier(Ident::new("id"))],

vec![]

),

cluster_by: vec![],

distribute_by: vec![],

sort_by: vec![],

having: None,

named_window: vec![],

qualify: None,

window_before_qualify: false,

value_table_mode: None,

connect_by: vec![],

flavor: SelectFlavor::Standard,

}))),

order_by: None,

limit_clause: None,

fetch: None,

locks: vec![],

for_clause: None,

settings: None,

format_clause: None,

pipe_operators: vec![],

}),

alias: table_alias(true, "t2"),

sample: None,

},

joins: vec![]

}])),

selection: Some(Expr::BinaryOp {

left: Box::new(Expr::CompoundIdentifier(vec![

Ident::new("t1"),

Ident::new("id")

])),

op: BinaryOperator::Eq,

right: Box::new(Expr::CompoundIdentifier(vec![

Ident::new("t2"),

Ident::new("id")

])),

}),

returning: None,

output: None,

or: None,

limit: None

})

);

let sql = "UPDATE T SET a = b FROM U, (SELECT foo FROM V) AS W WHERE 1 = 1";

dialects.verified_stmt(sql);

let sql = "UPDATE users AS u SET u.username = 'new_user' WHERE u.username = 'old_user'";

match verified_stmt(sql) {

Statement::Update(Update {

table,

assignments,

from: _from,

selection,

returning,

or: None,

limit: None,

optimizer_hints,

update_token: _,

output: _,

}) if optimizer_hints.is_empty() => {

assert_eq!(

TableWithJoins {

relation: TableFactor::Table {

name: ObjectName::from(vec![Ident::new("users")]),

alias: table_alias(true, "u"),

args: None,

with_hints: vec![],

version: None,

partitions: vec![],

with_ordinality: false,

json_path: None,

sample: None,

index_hints: vec![],

},

joins: vec![],

},

table

);

assert_eq!(

vec![Assignment {

target: AssignmentTarget::ColumnName(ObjectName::from(vec![

Ident::new("u"),

Ident::new("username")

])),

value: Expr::Value(

(Value::SingleQuotedString("new_user".to_string())).with_empty_span()

),

}],

assignments

);

assert_eq!(

Some(Expr::BinaryOp {

left: Box::new(Expr::CompoundIdentifier(vec![

Ident::new("u"),

Ident::new("username"),

])),

op: BinaryOperator::Eq,

right: Box::new(Expr::Value(

(Value::SingleQuotedString("old_user".to_string())).with_empty_span()

)),

}),

selection

);

assert_eq!(None, returning);

}

_ => unreachable!(),

}

let expect_or_clause = |sql: &str, expected_action: SqliteOnConflict| match verified_stmt(sql) {

Statement::Update(Update { or, .. }) => assert_eq!(or, Some(expected_action)),

other => unreachable!("Expected update with or, got {:?}", other),

};

expect_or_clause(

"UPDATE OR REPLACE t SET n = n + 1",

SqliteOnConflict::Replace,

);

expect_or_clause(

"UPDATE OR ROLLBACK t SET n = n + 1",

SqliteOnConflict::Rollback,

);

expect_or_clause("UPDATE OR ABORT t SET n = n + 1", SqliteOnConflict::Abort);

expect_or_clause("UPDATE OR FAIL t SET n = n + 1", SqliteOnConflict::Fail);

expect_or_clause("UPDATE OR IGNORE t SET n = n + 1", SqliteOnConflict::Ignore);

one_statement_parses_to(

"SELECT a, b, c FROM lineitem AS l (A, B, C)",

"SELECT a, b, c FROM lineitem AS l (A, B, C)",

);

// AS is optional

one_statement_parses_to(

"SELECT a, b, c FROM lineitem l (A, B, C)",

"SELECT a, b, c FROM lineitem l (A, B, C)",

);

let select = verified_only_select("SELECT a, b, c FROM lineitem AS l (A, B, C)");

assert_eq!(

select.projection,

vec![

SelectItem::UnnamedExpr(Expr::Identifier(Ident::new("a")),),

SelectItem::UnnamedExpr(Expr::Identifier(Ident::new("b")),),

SelectItem::UnnamedExpr(Expr::Identifier(Ident::new("c")),),

]

);

assert_eq!(

select.from,

vec![TableWithJoins {

relation: TableFactor::Table {

name: ObjectName::from(vec![Ident::new("lineitem")]),

alias: Some(TableAlias {

explicit: true,

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

columns: vec![

TableAliasColumnDef::from_name("A"),

TableAliasColumnDef::from_name("B"),

TableAliasColumnDef::from_name("C"),

],

}),

args: None,

with_hints: vec![],

version: None,

partitions: vec![],

with_ordinality: false,

json_path: None,

sample: None,

index_hints: vec![],

},

joins: vec![],

}]

);

let ast = all_dialects().run_parser_method("db.public..customer", |parser| {

parser.parse_object_name(false)

});

assert!(ast.is_err());

let sql = "DELETE FROM \"table\"";

match verified_stmt(sql) {

Statement::Delete(Delete {

from: FromTable::WithFromKeyword(from),

..

}) => {

assert_eq!(

table_from_name(ObjectName::from(vec![Ident::with_quote('"', "table")])),

from[0].relation

);

}

_ => unreachable!(),

}

let sql = "DELETE \"table\" WHERE 1";

let dialects = all_dialects_except(|d| {

d.is::<BigQueryDialect>() || d.is::<OracleDialect>() || d.is::<GenericDialect>()

});

let res = dialects.parse_sql_statements(sql);

assert_eq!(

ParserError::ParserError("Expected: FROM, found: WHERE".to_string()),

res.unwrap_err()

);

let sql = "DELETE schema1.table1, schema2.table2 FROM schema1.table1 JOIN schema2.table2 ON schema2.table2.col1 = schema1.table1.col1 WHERE schema2.table2.col2 = 1";

let dialects = all_dialects_except(|d| {

d.is::<BigQueryDialect>() || d.is::<OracleDialect>() || d.is::<GenericDialect>()

});

match dialects.verified_stmt(sql) {

Statement::Delete(Delete {

tables,

from: FromTable::WithFromKeyword(from),

..

}) => {

assert_eq!(

ObjectName::from(vec![Ident::new("schema1"), Ident::new("table1")]),

tables[0]

);

assert_eq!(

ObjectName::from(vec![Ident::new("schema2"), Ident::new("table2")]),

tables[1]

);

assert_eq!(

table_from_name(ObjectName::from(vec![

Ident::new("schema1"),

Ident::new("table1")

])),

from[0].relation

);

assert_eq!(

table_from_name(ObjectName::from(vec![

Ident::new("schema2"),

Ident::new("table2")

])),

from[0].joins[0].relation

);

}

_ => unreachable!(),

}

let sql = "DELETE FROM schema1.table1, schema2.table2 USING schema1.table1 JOIN schema2.table2 ON schema2.table2.pk = schema1.table1.col1 WHERE schema2.table2.col2 = 1";

match verified_stmt(sql) {

Statement::Delete(Delete {

from: FromTable::WithFromKeyword(from),

using: Some(using),

..

}) => {

assert_eq!(

table_from_name(ObjectName::from(vec![

Ident::new("schema1"),

Ident::new("table1")

])),

from[0].relation

);

assert_eq!(

table_from_name(ObjectName::from(vec![

Ident::new("schema2"),

Ident::new("table2")

])),

from[1].relation

);

assert_eq!(

table_from_name(ObjectName::from(vec![

Ident::new("schema1"),

Ident::new("table1")

])),

using[0].relation

);

assert_eq!(

table_from_name(ObjectName::from(vec![

Ident::new("schema2"),

Ident::new("table2")

])),

using[0].joins[0].relation

);

}

_ => unreachable!(),

}

use self::BinaryOperator::*;

let sql = "DELETE FROM foo WHERE name = 5";

match verified_stmt(sql) {

Statement::Delete(Delete {

tables: _,

from: FromTable::WithFromKeyword(from),

using,

selection,

returning,

..

}) => {

assert_eq!(

table_from_name(ObjectName::from(vec![Ident::new("foo")])),

from[0].relation,

);

assert_eq!(None, using);

assert_eq!(

Expr::BinaryOp {

left: Box::new(Expr::Identifier(Ident::new("name"))),

op: Eq,

right: Box::new(Expr::value(number("5"))),

},

selection.unwrap(),

);

assert_eq!(None, returning);

}

_ => unreachable!(),

}

use self::BinaryOperator::*;

let sql = "DELETE FROM basket AS a USING basket AS b WHERE a.id < b.id";

match verified_stmt(sql) {

Statement::Delete(Delete {

tables: _,

from: FromTable::WithFromKeyword(from),

using,

selection,

returning,

..

}) => {

assert_eq!(

TableFactor::Table {

name: ObjectName::from(vec![Ident::new("basket")]),

alias: table_alias(true, "a"),

args: None,

with_hints: vec![],

version: None,

partitions: vec![],

with_ordinality: false,

json_path: None,

sample: None,

index_hints: vec![],

},

from[0].relation,

);

assert_eq!(

Some(vec![TableWithJoins {

relation: TableFactor::Table {

name: ObjectName::from(vec![Ident::new("basket")]),

alias: table_alias(true, "b"),

args: None,

with_hints: vec![],

version: None,

partitions: vec![],

with_ordinality: false,

json_path: None,

sample: None,

index_hints: vec![],

},

joins: vec![],

}]),

using

);

assert_eq!(

Expr::BinaryOp {

left: Box::new(Expr::CompoundIdentifier(vec![

Ident::new("a"),

Ident::new("id"),

])),

op: Lt,

right: Box::new(Expr::CompoundIdentifier(vec![

Ident::new("b"),

Ident::new("id"),

])),

},

selection.unwrap(),

);

assert_eq!(None, returning);

}

_ => unreachable!(),

}

verified_stmt("SELECT 1");

verified_stmt("(SELECT 1)");

verified_stmt("((SELECT 1))");

verified_stmt("VALUES (1)");

verified_stmt("VALUES ROW(1, NULL, 'a'), ROW(2, NULL, 'b')");

let sql = "SELECT id, fname, lname FROM customer WHERE id = 1 LIMIT 5";

let select = verified_only_select(sql);

assert!(select.distinct.is_none());

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

let select = verified_query(sql);

let expected_limit_clause = LimitClause::LimitOffset {

limit: Some(Expr::value(number("5"))),

offset: None,

limit_by: vec![],

};

assert_eq!(Some(expected_limit_clause), select.limit_clause);

all_dialects()

.parse_sql_statements("SELECT * FROM user BY name")

.expect_err("BY without LIMIT");

// In dialects supporting LIMIT it shouldn't be parsed as a table alias

let ast = verified_query("SELECT id FROM customer LIMIT 1");

let expected_limit_clause = LimitClause::LimitOffset {

limit: Some(Expr::value(number("1"))),

offset: None,

limit_by: vec![],

};

assert_eq!(Some(expected_limit_clause), ast.limit_clause);

let ast = verified_query("SELECT 1 LIMIT 5");

let expected_limit_clause = LimitClause::LimitOffset {

limit: Some(Expr::value(number("5"))),

offset: None,

limit_by: vec![],

};

assert_eq!(Some(expected_limit_clause), ast.limit_clause);

let sql = "SELECT DISTINCT name FROM customer";

let select = verified_only_select(sql);

assert!(select.distinct.is_some());

assert_eq!(

&SelectItem::UnnamedExpr(Expr::Identifier(Ident::new("name"))),

only(&select.projection)

);

let sql = "SELECT DISTINCT name, id FROM customer";

let select = verified_only_select(sql);

assert!(select.distinct.is_some());

assert_eq!(

&SelectItem::UnnamedExpr(Expr::Identifier(Ident::new("name"))),

&select.projection[0]

);

assert_eq!(

&SelectItem::UnnamedExpr(Expr::Identifier(Ident::new("id"))),

&select.projection[1]

);

let sql = "SELECT DISTINCT (name, id) FROM customer";

let select = verified_only_select(sql);

assert_eq!(

&vec![SelectItem::UnnamedExpr(Expr::Tuple(vec![

Expr::Identifier(Ident::new("name")),