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

#![warn(clippy::all)]

//! Test SQL syntax, which all sqlparser dialects must parse in the same way.

//!

//! Note that it does not mean all SQL here is valid in all the dialects, only

//! that 1) it's either standard or widely supported and 2) it can be parsed by

//! sqlparser regardless of the chosen dialect (i.e. it doesn't conflict with

//! dialect-specific parsing rules).

#[macro_use]

mod test_utils;

use matches::assert_matches;

use sqlparser::ast::*;

use sqlparser::dialect::{

AnsiDialect, GenericDialect, MsSqlDialect, PostgreSqlDialect, SQLiteDialect, SnowflakeDialect,

};

use sqlparser::keywords::ALL_KEYWORDS;

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

use test_utils::{

all_dialects, expr_from_projection, join, number, only, table, table_alias, TestedDialects,

};

#[test]

fn parse_insert_values() {

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 INTO customer VALUES (1, 2, 3)";

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

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

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

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

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

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

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

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,

);

fn check_one(

sql: &str,

expected_table_name: &str,

expected_columns: &[String],

expected_rows: &[Vec<Expr>],

) {

match verified_stmt(sql) {

Statement::Insert {

table_name,

columns,

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(values)) => assert_eq!(values.as_slice(), expected_rows),

_ => unreachable!(),

}

}

_ => unreachable!(),

}

}

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

}

#[test]

fn parse_insert_invalid() {

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

let res = parse_sql_statements(sql);

assert_eq!(

ParserError::ParserError("Expected one of INTO or OVERWRITE, found: public".to_string()),

res.unwrap_err()

);

}

#[test]

fn parse_insert_sqlite() {

let dialect = SQLiteDialect {};

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

&dialect, sql,

)

.unwrap()

.pop()

.unwrap()

{

Statement::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));

}

#[test]

fn parse_update() {

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

match verified_stmt(sql) {

Statement::Update {

table,

assignments,

selection,

..

} => {

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

assert_eq!(

assignments,

vec![

Assignment {

id: vec!["a".into()],

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

},

Assignment {

id: vec!["b".into()],

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

},

Assignment {

id: 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()

);

}

#[test]

fn parse_update_with_table_alias() {

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

match verified_stmt(sql) {

Statement::Update {

table,

assignments,

from: _from,

selection,

} => {

assert_eq!(

TableWithJoins {

relation: TableFactor::Table {

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

alias: Some(TableAlias {

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

columns: vec![]

}),

args: vec![],

with_hints: vec![],

},

joins: vec![]

},

table

);

assert_eq!(

vec![Assignment {

id: vec![Ident::new("u"), Ident::new("username")],

value: Expr::Value(Value::SingleQuotedString("new_user".to_string()))

}],

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()

)))

}),

selection

);

}

_ => unreachable!(),

}

}

#[test]

fn parse_invalid_table_name() {

let ast = all_dialects()

.run_parser_method("db.public..customer", |parser| parser.parse_object_name());

assert!(ast.is_err());

}

#[test]

fn parse_no_table_name() {

let ast = all_dialects().run_parser_method("", |parser| parser.parse_object_name());

assert!(ast.is_err());

}

#[test]

fn parse_delete_statement() {

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

match verified_stmt(sql) {

Statement::Delete { table_name, .. } => {

assert_eq!(

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

table_name

);

}

_ => unreachable!(),

}

}

#[test]

fn parse_where_delete_statement() {

use self::BinaryOperator::*;

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

match verified_stmt(sql) {

Statement::Delete {

table_name,

selection,

..

} => {

assert_eq!(ObjectName(vec![Ident::new("foo")]), table_name);

assert_eq!(

Expr::BinaryOp {

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

op: Eq,

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

},

selection.unwrap(),

);

}

_ => unreachable!(),

}

}

#[test]

fn parse_top_level() {

verified_stmt("SELECT 1");

verified_stmt("(SELECT 1)");

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

verified_stmt("VALUES (1)");

}

#[test]

fn parse_simple_select() {

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

let select = verified_only_select(sql);

assert!(!select.distinct);

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

let select = verified_query(sql);

assert_eq!(Some(Expr::Value(number("5"))), select.limit);

}

#[test]

fn parse_limit_is_not_an_alias() {

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

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

assert_eq!(Some(Expr::Value(number("1"))), ast.limit);

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

assert_eq!(Some(Expr::Value(number("5"))), ast.limit);

}

#[test]

fn parse_select_distinct() {

let sql = "SELECT DISTINCT name FROM customer";

let select = verified_only_select(sql);

assert!(select.distinct);

assert_eq!(

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

only(&select.projection)

);

}

#[test]

fn parse_select_distinct_two_fields() {

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

let select = verified_only_select(sql);

assert!(select.distinct);

assert_eq!(

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

&select.projection[0]

);

assert_eq!(

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

&select.projection[1]

);

}

#[test]

fn parse_select_distinct_tuple() {

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")),

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

]))],

&select.projection

);

}

#[test]

fn parse_select_distinct_missing_paren() {

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

assert_eq!(

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

result.unwrap_err(),

);

}

#[test]

fn parse_select_all() {

one_statement_parses_to("SELECT ALL name FROM customer", "SELECT name FROM customer");

}

#[test]

fn parse_select_all_distinct() {

let result = parse_sql_statements("SELECT ALL DISTINCT name FROM customer");

assert_eq!(

ParserError::ParserError("Cannot specify both ALL and DISTINCT".to_string()),

result.unwrap_err(),

);

}

#[test]

fn parse_select_into() {

let sql = "SELECT * INTO table0 FROM table1";

one_statement_parses_to(sql, "SELECT * INTO table0 FROM table1");

let select = verified_only_select(sql);

assert_eq!(

&SelectInto {

temporary: false,

unlogged: false,

name: ObjectName(vec![Ident::new("table0")])

},

only(&select.into)

);

let sql = "SELECT * INTO TEMPORARY UNLOGGED table0 FROM table1";

one_statement_parses_to(sql, "SELECT * INTO TEMPORARY UNLOGGED table0 FROM table1");

// Do not allow aliases here

let sql = "SELECT * INTO table0 asdf FROM table1";

let result = parse_sql_statements(sql);

assert_eq!(

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

result.unwrap_err()

)

}

#[test]

fn parse_select_wildcard() {

let sql = "SELECT * FROM foo";

let select = verified_only_select(sql);

assert_eq!(&SelectItem::Wildcard, only(&select.projection));

let sql = "SELECT foo.* FROM foo";

let select = verified_only_select(sql);

assert_eq!(

&SelectItem::QualifiedWildcard(ObjectName(vec![Ident::new("foo")])),

only(&select.projection)

);

let sql = "SELECT myschema.mytable.* FROM myschema.mytable";

let select = verified_only_select(sql);

assert_eq!(

&SelectItem::QualifiedWildcard(ObjectName(vec![

Ident::new("myschema"),

Ident::new("mytable"),

])),

only(&select.projection)

);

let sql = "SELECT * + * FROM foo;";

let result = parse_sql_statements(sql);

assert_eq!(

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

result.unwrap_err(),

);

}

#[test]

fn parse_count_wildcard() {

verified_only_select("SELECT COUNT(*) FROM Order WHERE id = 10");

verified_only_select(

"SELECT COUNT(Employee.*) FROM Order JOIN Employee ON Order.employee = Employee.id",

);

}

#[test]

fn parse_column_aliases() {

let sql = "SELECT a.col + 1 AS newname FROM foo AS a";

let select = verified_only_select(sql);

if let SelectItem::ExprWithAlias {

expr: Expr::BinaryOp {

ref op, ref right, ..

},

ref alias,

} = only(&select.projection)

{

assert_eq!(&BinaryOperator::Plus, op);

assert_eq!(&Expr::Value(number("1")), right.as_ref());

assert_eq!(&Ident::new("newname"), alias);

} else {

panic!("Expected ExprWithAlias")

}

// alias without AS is parsed correctly:

one_statement_parses_to("SELECT a.col + 1 newname FROM foo AS a", sql);

}

#[test]

fn test_eof_after_as() {

let res = parse_sql_statements("SELECT foo AS");

assert_eq!(

ParserError::ParserError("Expected an identifier after AS, found: EOF".to_string()),

res.unwrap_err()

);

let res = parse_sql_statements("SELECT 1 FROM foo AS");

assert_eq!(

ParserError::ParserError("Expected an identifier after AS, found: EOF".to_string()),

res.unwrap_err()

);

}

#[test]

fn test_no_infix_error() {

let res = Parser::parse_sql(&GenericDialect {}, "ASSERT-URA<<");

assert_eq!(

ParserError::ParserError("No infix parser for token ShiftLeft".to_string()),

res.unwrap_err()

);

}

#[test]

fn parse_select_count_wildcard() {

let sql = "SELECT COUNT(*) FROM customer";

let select = verified_only_select(sql);

assert_eq!(

&Expr::Function(Function {

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

args: vec![FunctionArg::Unnamed(FunctionArgExpr::Wildcard)],

over: None,

distinct: false,

}),

expr_from_projection(only(&select.projection))

);

}

#[test]

fn parse_select_count_distinct() {

let sql = "SELECT COUNT(DISTINCT + x) FROM customer";

let select = verified_only_select(sql);

assert_eq!(

&Expr::Function(Function {

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

args: vec![FunctionArg::Unnamed(FunctionArgExpr::Expr(Expr::UnaryOp {

op: UnaryOperator::Plus,

expr: Box::new(Expr::Identifier(Ident::new("x"))),

}))],

over: None,

distinct: true,

}),

expr_from_projection(only(&select.projection))

);

one_statement_parses_to(

"SELECT COUNT(ALL + x) FROM customer",

"SELECT COUNT(+ x) FROM customer",

);

let sql = "SELECT COUNT(ALL DISTINCT + x) FROM customer";

let res = parse_sql_statements(sql);

assert_eq!(

ParserError::ParserError("Cannot specify both ALL and DISTINCT".to_string()),

res.unwrap_err()

);

}

#[test]

fn parse_not() {

let sql = "SELECT id FROM customer WHERE NOT salary = ''";

let _ast = verified_only_select(sql);

//TODO: add assertions

}

#[test]

fn parse_invalid_infix_not() {

let res = parse_sql_statements("SELECT c FROM t WHERE c NOT (");

assert_eq!(

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

res.unwrap_err(),

);

}

#[test]

fn parse_collate() {

let sql = "SELECT name COLLATE \"de_DE\" FROM customer";

assert_matches!(

only(&all_dialects().verified_only_select(sql).projection),

SelectItem::UnnamedExpr(Expr::Collate { .. })

);

}

#[test]

fn parse_select_string_predicate() {

let sql = "SELECT id, fname, lname FROM customer \

WHERE salary <> 'Not Provided' AND salary <> ''";

let _ast = verified_only_select(sql);

//TODO: add assertions

}

#[test]

fn parse_projection_nested_type() {

let sql = "SELECT customer.address.state FROM foo";

let _ast = verified_only_select(sql);

//TODO: add assertions

}

#[test]

fn parse_null_in_select() {

let sql = "SELECT NULL";

let select = verified_only_select(sql);

assert_eq!(

&Expr::Value(Value::Null),

expr_from_projection(only(&select.projection)),

);

}

#[test]

fn parse_select_with_date_column_name() {

let sql = "SELECT date";

let select = verified_only_select(sql);

assert_eq!(

&Expr::Identifier(Ident {

value: "date".into(),

quote_style: None

}),

expr_from_projection(only(&select.projection)),

);

}

#[test]

fn parse_escaped_single_quote_string_predicate() {

use self::BinaryOperator::*;

let sql = "SELECT id, fname, lname FROM customer \

WHERE salary <> 'Jim''s salary'";

let ast = verified_only_select(sql);

assert_eq!(

Some(Expr::BinaryOp {

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

op: NotEq,

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

"Jim's salary".to_string()

)))

}),

ast.selection,

);

}

#[test]

fn parse_number() {

let expr = verified_expr("1.0");

#[cfg(feature = "bigdecimal")]

assert_eq!(

expr,

Expr::Value(Value::Number(bigdecimal::BigDecimal::from(1), false))

);

#[cfg(not(feature = "bigdecimal"))]

assert_eq!(expr, Expr::Value(Value::Number("1.0".into(), false)));

}

#[test]

fn parse_compound_expr_1() {

use self::BinaryOperator::*;

use self::Expr::*;

let sql = "a + b * c";

assert_eq!(

BinaryOp {

left: Box::new(Identifier(Ident::new("a"))),

op: Plus,

right: Box::new(BinaryOp {

left: Box::new(Identifier(Ident::new("b"))),

op: Multiply,

right: Box::new(Identifier(Ident::new("c")))

})

},

verified_expr(sql)

);

}

#[test]

fn parse_compound_expr_2() {

use self::BinaryOperator::*;

use self::Expr::*;

let sql = "a * b + c";

assert_eq!(

BinaryOp {

left: Box::new(BinaryOp {

left: Box::new(Identifier(Ident::new("a"))),

op: Multiply,

right: Box::new(Identifier(Ident::new("b")))

}),

op: Plus,

right: Box::new(Identifier(Ident::new("c")))

},

verified_expr(sql)

);

}

#[test]

fn parse_unary_math() {

use self::Expr::*;

let sql = "- a + - b";

assert_eq!(

BinaryOp {

left: Box::new(UnaryOp {

op: UnaryOperator::Minus,

expr: Box::new(Identifier(Ident::new("a"))),

}),

op: BinaryOperator::Plus,

right: Box::new(UnaryOp {

op: UnaryOperator::Minus,

expr: Box::new(Identifier(Ident::new("b"))),

}),

},

verified_expr(sql)

);

}

#[test]

fn parse_is_null() {

use self::Expr::*;

let sql = "a IS NULL";

assert_eq!(

IsNull(Box::new(Identifier(Ident::new("a")))),

verified_expr(sql)

);

}

#[test]

fn parse_is_not_null() {

use self::Expr::*;

let sql = "a IS NOT NULL";

assert_eq!(

IsNotNull(Box::new(Identifier(Ident::new("a")))),

verified_expr(sql)

);

}

#[test]

fn parse_is_distinct_from() {

use self::Expr::*;

let sql = "a IS DISTINCT FROM b";

assert_eq!(

IsDistinctFrom(

Box::new(Identifier(Ident::new("a"))),

Box::new(Identifier(Ident::new("b")))

),

verified_expr(sql)

);

}

#[test]

fn parse_is_not_distinct_from() {

use self::Expr::*;

let sql = "a IS NOT DISTINCT FROM b";

assert_eq!(

IsNotDistinctFrom(

Box::new(Identifier(Ident::new("a"))),

Box::new(Identifier(Ident::new("b")))

),

verified_expr(sql)

);

}

#[test]

fn parse_not_precedence() {

// NOT has higher precedence than OR/AND, so the following must parse as (NOT true) OR true

let sql = "NOT true OR true";

assert_matches!(

verified_expr(sql),

Expr::BinaryOp {

op: BinaryOperator::Or,

..

}

);

// But NOT has lower precedence than comparison operators, so the following parses as NOT (a IS NULL)

let sql = "NOT a IS NULL";

assert_matches!(

verified_expr(sql),

Expr::UnaryOp {

op: UnaryOperator::Not,

..

}

);

// NOT has lower precedence than BETWEEN, so the following parses as NOT (1 NOT BETWEEN 1 AND 2)

let sql = "NOT 1 NOT BETWEEN 1 AND 2";

assert_eq!(

verified_expr(sql),

Expr::UnaryOp {

op: UnaryOperator::Not,

expr: Box::new(Expr::Between {

expr: Box::new(Expr::Value(number("1"))),

low: Box::new(Expr::Value(number("1"))),

high: Box::new(Expr::Value(number("2"))),

negated: true,

}),

},

);

// NOT has lower precedence than LIKE, so the following parses as NOT ('a' NOT LIKE 'b')

let sql = "NOT 'a' NOT LIKE 'b'";

assert_eq!(

verified_expr(sql),

Expr::UnaryOp {

op: UnaryOperator::Not,

expr: Box::new(Expr::BinaryOp {

left: Box::new(Expr::Value(Value::SingleQuotedString("a".into()))),

op: BinaryOperator::NotLike,

right: Box::new(Expr::Value(Value::SingleQuotedString("b".into()))),

}),

},

);

// NOT has lower precedence than IN, so the following parses as NOT (a NOT IN 'a')

let sql = "NOT a NOT IN ('a')";

assert_eq!(

verified_expr(sql),

Expr::UnaryOp {

op: UnaryOperator::Not,

expr: Box::new(Expr::InList {

expr: Box::new(Expr::Identifier("a".into())),

list: vec![Expr::Value(Value::SingleQuotedString("a".into()))],

negated: true,

}),

},

);

}

#[test]

fn parse_like() {

fn chk(negated: bool) {

let sql = &format!(

"SELECT * FROM customers WHERE name {}LIKE '%a'",

if negated { "NOT " } else { "" }

);

let select = verified_only_select(sql);

assert_eq!(

Expr::BinaryOp {

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

op: if negated {

BinaryOperator::NotLike

} else {

BinaryOperator::Like

},

right: Box::new(Expr::Value(Value::SingleQuotedString("%a".to_string()))),

},

select.selection.unwrap()

);

// This statement tests that LIKE and NOT LIKE have the same precedence.

// This was previously mishandled (#81).

let sql = &format!(

"SELECT * FROM customers WHERE name {}LIKE '%a' IS NULL",

if negated { "NOT " } else { "" }

);

let select = verified_only_select(sql);

assert_eq!(

Expr::IsNull(Box::new(Expr::BinaryOp {

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

op: if negated {

BinaryOperator::NotLike

} else {

BinaryOperator::Like

},

right: Box::new(Expr::Value(Value::SingleQuotedString("%a".to_string()))),

})),

select.selection.unwrap()

);

}

chk(false);

chk(true);

}

#[test]

fn parse_ilike() {

fn chk(negated: bool) {

let sql = &format!(

"SELECT * FROM customers WHERE name {}ILIKE '%a'",

if negated { "NOT " } else { "" }

);

let select = verified_only_select(sql);

assert_eq!(

Expr::BinaryOp {

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

op: if negated {

BinaryOperator::NotILike

} else {

BinaryOperator::ILike

},

right: Box::new(Expr::Value(Value::SingleQuotedString("%a".to_string()))),

},

select.selection.unwrap()

);

// This statement tests that LIKE and NOT LIKE have the same precedence.

// This was previously mishandled (#81).

let sql = &format!(

"SELECT * FROM customers WHERE name {}ILIKE '%a' IS NULL",

if negated { "NOT " } else { "" }

);

let select = verified_only_select(sql);

assert_eq!(

Expr::IsNull(Box::new(Expr::BinaryOp {

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

op: if negated {

BinaryOperator::NotILike

} else {

BinaryOperator::ILike

},

right: Box::new(Expr::Value(Value::SingleQuotedString("%a".to_string()))),

})),

select.selection.unwrap()

);

}

chk(false);

chk(true);

}

#[test]

fn parse_in_list() {

fn chk(negated: bool) {

let sql = &format!(

"SELECT * FROM customers WHERE segment {}IN ('HIGH', 'MED')",

if negated { "NOT " } else { "" }

);

let select = verified_only_select(sql);

assert_eq!(

Expr::InList {

expr: Box::new(Expr::Identifier(Ident::new("segment"))),

list: vec![

Expr::Value(Value::SingleQuotedString("HIGH".to_string())),

Expr::Value(Value::SingleQuotedString("MED".to_string())),

],

negated,

},

select.selection.unwrap()

);

}

chk(false);

chk(true);

}

#[test]

fn parse_in_subquery() {

let sql = "SELECT * FROM customers WHERE segment IN (SELECT segm FROM bar)";

let select = verified_only_select(sql);

assert_eq!(

Expr::InSubquery {

expr: Box::new(Expr::Identifier(Ident::new("segment"))),

subquery: Box::new(verified_query("SELECT segm FROM bar")),

negated: false,

},

select.selection.unwrap()

);

}

#[test]

fn parse_in_unnest() {

fn chk(negated: bool) {

let sql = &format!(

"SELECT * FROM customers WHERE segment {}IN UNNEST(expr)",

if negated { "NOT " } else { "" }

);

let select = verified_only_select(sql);

assert_eq!(

Expr::InUnnest {

expr: Box::new(Expr::Identifier(Ident::new("segment"))),

array_expr: Box::new(verified_expr("expr")),

negated,

},

select.selection.unwrap()

);

}

chk(false);

chk(true);

}

#[test]

fn parse_string_agg() {

let sql = "SELECT a || b";

let select = verified_only_select(sql);

assert_eq!(

SelectItem::UnnamedExpr(Expr::BinaryOp {

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

op: BinaryOperator::StringConcat,

right: Box::new(Expr::Identifier(Ident::new("b"))),

}),

select.projection[0]

);

}

#[test]

fn parse_bitwise_ops() {

let bitwise_ops = &[

("^", BinaryOperator::BitwiseXor),

("|", BinaryOperator::BitwiseOr),

("&", BinaryOperator::BitwiseAnd),

];

for (str_op, op) in bitwise_ops {

let select = verified_only_select(&format!("SELECT a {} b", &str_op));

assert_eq!(

SelectItem::UnnamedExpr(Expr::BinaryOp {

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

op: op.clone(),

right: Box::new(Expr::Identifier(Ident::new("b"))),

}),

select.projection[0]

);

}

}

#[test]

fn parse_logical_xor() {

let sql = "SELECT true XOR true, false XOR false, true XOR false, false XOR true";

let select = verified_only_select(sql);

assert_eq!(

SelectItem::UnnamedExpr(Expr::BinaryOp {

left: Box::new(Expr::Value(Value::Boolean(true))),

op: BinaryOperator::Xor,

right: Box::new(Expr::Value(Value::Boolean(true))),

}),

select.projection[0]