// 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 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::ALL_KEYWORDS;

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

use sqlparser::tokenizer::Tokenizer;

use test_utils::{

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

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

};

#[macro_use]

mod test_utils;

#[cfg(test)]

use pretty_assertions::assert_eq;

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

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

use sqlparser::test_utils::all_dialects_except;

#[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_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_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_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, ObjectName(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_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, ObjectName(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_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, ObjectName(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() {

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

let stmt = 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_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(vec!["a".into()])),

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

},

Assignment {

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

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

},

Assignment {

target: AssignmentTarget::ColumnName(ObjectName(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: TableFactor::Table {

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

alias: None,

args: None,

with_hints: vec![],

version: None,

partitions: vec![],

with_ordinality: false,

},

joins: vec![],

},

assignments: vec![Assignment {

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

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

}],

from: Some(TableWithJoins {

relation: TableFactor::Derived {

lateral: false,

subquery: Box::new(Query {

with: None,

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

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: TableFactor::Table {

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

alias: None,

args: None,

with_hints: vec![],

version: None,

partitions: vec![],

with_ordinality: false,

},

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,

}))),

order_by: None,

limit: None,

limit_by: vec![],

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

}

);

}

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

} => {

assert_eq!(

TableWithJoins {

relation: TableFactor::Table {

name: ObjectName(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,

},

joins: vec![],

},

table

);

assert_eq!(

vec![Assignment {

target: AssignmentTarget::ColumnName(ObjectName(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

);

assert_eq!(None, returning);

}

_ => unreachable!(),

}

}

#[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(vec![Ident::new("lineitem")]),

alias: Some(TableAlias {

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

columns: vec![Ident::new("A"), Ident::new("B"), Ident::new("C"),],

}),

args: None,

with_hints: vec![],

version: None,

partitions: vec![],

with_ordinality: false,

},

joins: vec![],

}]

);

}

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

TableFactor::Table {

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

alias: None,

args: None,

with_hints: vec![],

version: None,

partitions: vec![],

with_ordinality: false,

},

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(vec![Ident::new("schema1"), Ident::new("table1")]),

tables[0]

);

assert_eq!(

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

tables[1]

);

assert_eq!(

TableFactor::Table {

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

alias: None,

args: None,

with_hints: vec![],

version: None,

partitions: vec![],

with_ordinality: false,

},

from[0].relation

);

assert_eq!(

TableFactor::Table {

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

alias: None,

args: None,

with_hints: vec![],

version: None,

partitions: vec![],

with_ordinality: false,

},

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

TableFactor::Table {

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

alias: None,

args: None,

with_hints: vec![],

version: None,

partitions: vec![],

with_ordinality: false,

},

from[0].relation

);

assert_eq!(

TableFactor::Table {

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

alias: None,

args: None,

with_hints: vec![],

version: None,

partitions: vec![],

with_ordinality: false,

},

from[1].relation

);

assert_eq!(

TableFactor::Table {

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

alias: None,

args: None,

with_hints: vec![],

version: None,

partitions: vec![],

with_ordinality: false,

},

using[0].relation

);

assert_eq!(

TableFactor::Table {

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

alias: None,

args: None,

with_hints: vec![],

version: None,

partitions: vec![],

with_ordinality: false,

},

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

TableFactor::Table {

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

alias: None,

args: None,

with_hints: vec![],

version: None,

partitions: vec![],

with_ordinality: false,

},

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

},

from[0].relation,

);

assert_eq!(

Some(vec![TableWithJoins {

relation: TableFactor::Table {

name: ObjectName(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,

},

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

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

}

#[test]

fn parse_limit() {

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

}

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

let sql = "SELECT DISTINCT ON (album_id) name FROM track ORDER BY album_id, milliseconds";

let select = verified_only_select(sql);

assert_eq!(

&Some(Distinct::On(vec![Expr::Identifier(Ident::new("album_id"))])),

&select.distinct

);

let sql = "SELECT DISTINCT ON () name FROM track ORDER BY milliseconds";

let select = verified_only_select(sql);

assert_eq!(&Some(Distinct::On(vec![])), &select.distinct);

let sql = "SELECT DISTINCT ON (album_id, milliseconds) name FROM track";

let select = verified_only_select(sql);

assert_eq!(

&Some(Distinct::On(vec![

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

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

])),

&select.distinct

);

}

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

table: false,

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

},

only(&select.into)

);

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

one_statement_parses_to(

sql,

"SELECT * INTO TEMPORARY UNLOGGED TABLE 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(WildcardAdditionalOptions::default()),

only(&select.projection)

);

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

let select = verified_only_select(sql);

assert_eq!(

&SelectItem::QualifiedWildcard(

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

WildcardAdditionalOptions::default()

),

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

WildcardAdditionalOptions::default(),

),

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