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

extern crate core;

use helpers::attached_token::AttachedToken;

use matches::assert_matches;

use sqlparser::ast::SelectItem::UnnamedExpr;

use sqlparser::ast::TableFactor::{Pivot, Unpivot};

use sqlparser::ast::*;

use sqlparser::dialect::{

AnsiDialect, BigQueryDialect, ClickHouseDialect, DatabricksDialect, Dialect, DuckDbDialect,

GenericDialect, HiveDialect, MsSqlDialect, MySqlDialect, PostgreSqlDialect, RedshiftSqlDialect,

SQLiteDialect, SnowflakeDialect,

};

use sqlparser::keywords::{Keyword, ALL_KEYWORDS};

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

use sqlparser::tokenizer::Tokenizer;

use sqlparser::tokenizer::{Location, Span};

use test_utils::{

all_dialects, all_dialects_where, alter_table_op, assert_eq_vec, call, expr_from_projection,

join, number, only, table, table_alias, table_from_name, TestedDialects,

};

#[macro_use]

mod test_utils;

#[cfg(test)]

use pretty_assertions::assert_eq;

use sqlparser::ast::ColumnOption::Comment;

use sqlparser::ast::DateTimeField::Seconds;

use sqlparser::ast::Expr::{Identifier, UnaryOp};

use sqlparser::ast::Value::Number;

use sqlparser::test_utils::all_dialects_except;

#[test]

fn parse_numeric_literal_underscore() {

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

))]

);

}

#[test]

fn parse_function_object_name() {

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,

);

}

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

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

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(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, .. }) => {

assert_eq!(rows.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_set() {

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

dialects.verified_stmt("INSERT INTO tbl1 SET col1 = 1, col2 = 'abc', col3 = current_date()");

}

#[test]

fn parse_replace_into() {

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

)

}

#[test]

fn parse_insert_default_values() {

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

);

}

#[test]

fn parse_insert_select_returning() {

// 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!(),

}

}

#[test]

fn parse_insert_select_from_returning() {

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

}

}

#[test]

fn parse_returning_as_column_alias() {

verified_stmt("SELECT 1 AS RETURNING");

}

#[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(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 {

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

);

}

#[test]

fn parse_update_set_from() {

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 {

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

distinct: None,

top: None,

top_before_distinct: false,

projection: vec![

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

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

],

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: None,

flavor: SelectFlavor::Standard,

}))),

order_by: None,

limit_clause: None,

fetch: None,

locks: vec![],

for_clause: None,

settings: None,

format_clause: None,

}),

alias: Some(TableAlias {

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

columns: vec![],

})

},

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,

or: None,

}

);

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

dialects.verified_stmt(sql);

}

#[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,

returning,

or: None,

} => {

assert_eq!(

TableWithJoins {

relation: TableFactor::Table {

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

alias: Some(TableAlias {

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

columns: vec![],

}),

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

}

}

#[test]

fn parse_update_or() {

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

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

}

#[test]

fn parse_select_with_table_alias_as() {

// AS is optional

one_statement_parses_to(

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

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

);

}

#[test]

fn parse_select_with_table_alias() {

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 {

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![],

}]

);

}

#[test]

fn parse_analyze() {

verified_stmt("ANALYZE TABLE test_table");

verified_stmt("ANALYZE test_table");

}

#[test]

fn parse_invalid_table_name() {

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

parser.parse_object_name(false)

});

assert!(ast.is_err());

}

#[test]

fn parse_no_table_name() {

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

assert!(ast.is_err());

}

#[test]

fn parse_delete_statement() {

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

}

}

#[test]

fn parse_delete_without_from_error() {

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

let dialects = all_dialects_except(|d| d.is::<BigQueryDialect>() || d.is::<GenericDialect>());

let res = dialects.parse_sql_statements(sql);

assert_eq!(

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

res.unwrap_err()

);

}

#[test]

fn parse_delete_statement_for_multi_tables() {

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::<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!(),

}

}

#[test]

fn parse_delete_statement_for_multi_tables_with_using() {

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

}

}

#[test]

fn parse_where_delete_statement() {

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

}

}

#[test]

fn parse_where_delete_with_alias_statement() {

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: Some(TableAlias {

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

columns: vec![],

}),

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: Some(TableAlias {

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

columns: vec![],

}),

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

}

}

#[test]

fn parse_top_level() {

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')");

}

#[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.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);

}

#[test]

fn parse_limit() {

verified_stmt("SELECT * FROM user LIMIT 1");

}

#[test]

fn parse_invalid_limit_by() {

all_dialects()

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

.expect_err("BY without 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");

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

}

#[test]

fn parse_select_distinct() {

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)

);

}

#[test]

fn parse_select_distinct_two_fields() {

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]

);

}

#[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_outer_join_operator() {

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

let select = dialects.verified_only_select("SELECT 1 FROM T WHERE a = b (+)");

assert_eq!(

select.selection,

Some(Expr::BinaryOp {

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

op: BinaryOperator::Eq,

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

})

);

let select = dialects.verified_only_select("SELECT 1 FROM T WHERE t1.c1 = t2.c2.d3 (+)");

assert_eq!(

select.selection,

Some(Expr::BinaryOp {

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

Ident::new("t1"),

Ident::new("c1")