Repository: apache/datafusion-sqlparser-rs (rust)

Issue:
`EXPLAIN QUERY PLAN` fails to parse (sqlite)
## problem
queries with [explain query plan](https://sqlite.org/eqp.html) fail when using the sqlite dialect

## example
query:
```sql
select * from "sensor_data"
```

error:
```sh
sql parser error: Expected: end of statement, found: plan at Line: 1, Column: 15
```

Pull request:
Add support of `EXPLAIN QUERY PLAN` syntax for SQLite dialect
This closes #1455 and introduces one keyword: `PLAN`.

For the syntax documentation, please refer to:

https://sqlite.org/lang_explain.html

Patch:
diff --git a/src/ast/ddl.rs b/src/ast/ddl.rs
index 4578ae8..0677e63 100644
--- a/src/ast/ddl.rs
+++ b/src/ast/ddl.rs
@@ -33,6 +33,7 @@ use crate::ast::{
     display_comma_separated, display_separated, DataType, Expr, Ident, MySQLColumnPosition,
     ObjectName, OrderByExpr, ProjectionSelect, SequenceOptions, SqlOption, Value,
 };
+use crate::keywords::Keyword;
 use crate::tokenizer::Token;
 
 /// An `ALTER TABLE` (`Statement::AlterTable`) operation
@@ -1040,6 +1041,7 @@ impl fmt::Display for ColumnDef {
 /// ```sql
 /// name
 /// age OPTIONS(description = "age column", tag = "prod")
+/// amount COMMENT 'The total amount for the order line'
 /// created_at DateTime64
 /// ```
 #[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
@@ -1048,7 +1050,7 @@ impl fmt::Display for ColumnDef {
 pub struct ViewColumnDef {
     pub name: Ident,
     pub data_type: Option<DataType>,
-    pub options: Option<Vec<SqlOption>>,
+    pub options: Option<Vec<ColumnOption>>,
 }
 
 impl fmt::Display for ViewColumnDef {
@@ -1058,11 +1060,7 @@ impl fmt::Display for ViewColumnDef {
             write!(f, " {}", data_type)?;
         }
         if let Some(options) = self.options.as_ref() {
-            write!(
-                f,
-                " OPTIONS({})",
-                display_comma_separated(options.as_slice())
-            )?;
+            write!(f, " {}", display_comma_separated(options.as_slice()))?;
         }
         Ok(())
     }
@@ -1189,6 +1187,9 @@ pub enum ColumnOption {
     /// ```
     /// [MS SQL Server]: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property
     Identity(Option<IdentityProperty>),
+    /// SQLite specific: ON CONFLICT option on column definition
+    /// <https://www.sqlite.org/lang_conflict.html>
+    OnConflict(Keyword),
 }
 
 impl fmt::Display for ColumnOption {
@@ -1297,6 +1298,10 @@ impl fmt::Display for ColumnOption {
                 }
                 Ok(())
             }
+            OnConflict(keyword) => {
+                write!(f, "ON CONFLICT {:?}", keyword)?;
+                Ok(())
+            }
         }
     }
 }
diff --git a/src/ast/mod.rs b/src/ast/mod.rs
index 8c4bc25..bc559a6 100644
--- a/src/ast/mod.rs
+++ b/src/ast/mod.rs
@@ -3111,6 +3111,11 @@ pub enum Statement {
         analyze: bool,
         // Display additional information regarding the plan.
         verbose: bool,
+        /// `EXPLAIN QUERY PLAN`
+        /// Display the query plan without running the query.
+        ///
+        /// [SQLite](https://sqlite.org/lang_explain.html)
+        query_plan: bool,
         /// A SQL query that specifies what to explain
         statement: Box<Statement>,
         /// Optional output format of explain
@@ -3302,12 +3307,16 @@ impl fmt::Display for Statement {
                 describe_alias,
                 verbose,
                 analyze,
+                query_plan,
                 statement,
                 format,
                 options,
             } => {
                 write!(f, "{describe_alias} ")?;
 
+                if *query_plan {
+                    write!(f, "QUERY PLAN ")?;
+                }
                 if *analyze {
                     write!(f, "ANALYZE ")?;
                 }
@@ -3930,6 +3939,12 @@ impl fmt::Display for Statement {
                         .map(|to| format!(" TO {to}"))
                         .unwrap_or_default()
                 )?;
+                if !columns.is_empty() {
+                    write!(f, " ({})", display_comma_separated(columns))?;
+                }
+                if matches!(options, CreateTableOptions::With(_)) {
+                    write!(f, " {options}")?;
+                }
                 if let Some(comment) = comment {
                     write!(
                         f,
@@ -3937,12 +3952,6 @@ impl fmt::Display for Statement {
                         value::escape_single_quote_string(comment)
                     )?;
                 }
-                if matches!(options, CreateTableOptions::With(_)) {
-                    write!(f, " {options}")?;
-                }
-                if !columns.is_empty() {
-                    write!(f, " ({})", display_comma_separated(columns))?;
-                }
                 if !cluster_by.is_empty() {
                     write!(f, " CLUSTER BY ({})", display_comma_separated(cluster_by))?;
                 }
diff --git a/src/dialect/generic.rs b/src/dialect/generic.rs
index 8bec45b..92d720a 100644
--- a/src/dialect/generic.rs
+++ b/src/dialect/generic.rs
@@ -103,4 +103,8 @@ impl Dialect for GenericDialect {
     fn supports_limit_comma(&self) -> bool {
         true
     }
+
+    fn supports_asc_desc_in_column_definition(&self) -> bool {
+        true
+    }
 }
diff --git a/src/dialect/mod.rs b/src/dialect/mod.rs
index 29ad0b9..744f5a8 100644
--- a/src/dialect/mod.rs
+++ b/src/dialect/mod.rs
@@ -557,6 +557,10 @@ pub trait Dialect: Debug + Any {
     fn supports_explain_with_utility_options(&self) -> bool {
         false
     }
+
+    fn supports_asc_desc_in_column_definition(&self) -> bool {
+        false
+    }
 }
 
 /// This represents the operators for which precedence must be defined
diff --git a/src/dialect/sqlite.rs b/src/dialect/sqlite.rs
index 5c563bf..95717f9 100644
--- a/src/dialect/sqlite.rs
+++ b/src/dialect/sqlite.rs
@@ -77,4 +77,8 @@ impl Dialect for SQLiteDialect {
     fn supports_limit_comma(&self) -> bool {
         true
     }
+
+    fn supports_asc_desc_in_column_definition(&self) -> bool {
+        true
+    }
 }
diff --git a/src/keywords.rs b/src/keywords.rs
index 49c6ce2..ecf4bd4 100644
--- a/src/keywords.rs
+++ b/src/keywords.rs
@@ -572,6 +572,7 @@ define_keywords!(
     PERSISTENT,
     PIVOT,
     PLACING,
+    PLAN,
     PLANS,
     POLICY,
     PORTION,
diff --git a/src/parser/mod.rs b/src/parser/mod.rs
index 88c3bd1..cc60bbb 100644
--- a/src/parser/mod.rs
+++ b/src/parser/mod.rs
@@ -6192,6 +6192,20 @@ impl<'a> Parser<'a> {
             Ok(Some(ColumnOption::DialectSpecific(vec![
                 Token::make_keyword("AUTOINCREMENT"),
             ])))
+        } else if self.parse_keyword(Keyword::ASC)
+            && self.dialect.supports_asc_desc_in_column_definition()
+        {
+            // Support ASC for SQLite
+            Ok(Some(ColumnOption::DialectSpecific(vec![
+                Token::make_keyword("ASC"),
+            ])))
+        } else if self.parse_keyword(Keyword::DESC)
+            && self.dialect.supports_asc_desc_in_column_definition()
+        {
+            // Support DESC for SQLite
+            Ok(Some(ColumnOption::DialectSpecific(vec![
+                Token::make_keyword("DESC"),
+            ])))
         } else if self.parse_keywords(&[Keyword::ON, Keyword::UPDATE])
             && dialect_of!(self is MySqlDialect | GenericDialect)
         {
@@ -6224,6 +6238,19 @@ impl<'a> Parser<'a> {
                 None
             };
             Ok(Some(ColumnOption::Identity(property)))
+        } else if dialect_of!(self is SQLiteDialect | GenericDialect)
+            && self.parse_keywords(&[Keyword::ON, Keyword::CONFLICT])
+        {
+            // Support ON CONFLICT for SQLite
+            Ok(Some(ColumnOption::OnConflict(
+                self.expect_one_of_keywords(&[
+                    Keyword::ROLLBACK,
+                    Keyword::ABORT,
+                    Keyword::FAIL,
+                    Keyword::IGNORE,
+                    Keyword::REPLACE,
+                ])?,
+            )))
         } else {
             Ok(None)
         }
@@ -8361,11 +8388,14 @@ impl<'a> Parser<'a> {
     /// Parses a column definition within a view.
     fn parse_view_column(&mut self) -> Result<ViewColumnDef, ParserError> {
         let name = self.parse_identifier(false)?;
-        let options = if dialect_of!(self is BigQueryDialect | GenericDialect)
-            && self.parse_keyword(Keyword::OPTIONS)
+        let options = if (dialect_of!(self is BigQueryDialect | GenericDialect)
+            && self.parse_keyword(Keyword::OPTIONS))
+            || (dialect_of!(self is SnowflakeDialect | GenericDialect)
+                && self.parse_keyword(Keyword::COMMENT))
         {
             self.prev_token();
-            Some(self.parse_options(Keyword::OPTIONS)?)
+            self.parse_optional_column_option()?
+                .map(|option| vec![option])
         } else {
             None
         };
@@ -8632,6 +8662,7 @@ impl<'a> Parser<'a> {
     ) -> Result<Statement, ParserError> {
         let mut analyze = false;
         let mut verbose = false;
+        let mut query_plan = false;
         let mut format = None;
         let mut options = None;
 
@@ -8642,6 +8673,8 @@ impl<'a> Parser<'a> {
             && self.peek_token().token == Token::LParen
         {
             options = Some(self.parse_utility_options()?)
+        } else if self.parse_keywords(&[Keyword::QUERY, Keyword::PLAN]) {
+            query_plan = true;
         } else {
             analyze = self.parse_keyword(Keyword::ANALYZE);
             verbose = self.parse_keyword(Keyword::VERBOSE);
@@ -8658,6 +8691,7 @@ impl<'a> Parser<'a> {
                 describe_alias,
                 analyze,
                 verbose,
+                query_plan,
                 statement: Box::new(statement),
                 format,
                 options,


Test patch:
diff --git a/tests/sqlparser_bigquery.rs b/tests/sqlparser_bigquery.rs
index 55afe47..63517fe 100644
--- a/tests/sqlparser_bigquery.rs
+++ b/tests/sqlparser_bigquery.rs
@@ -272,10 +272,10 @@ fn parse_create_view_with_options() {
                     ViewColumnDef {
                         name: Ident::new("age"),
                         data_type: None,
-                        options: Some(vec![SqlOption::KeyValue {
+                        options: Some(vec![ColumnOption::Options(vec![SqlOption::KeyValue {
                             key: Ident::new("description"),
                             value: Expr::Value(Value::DoubleQuotedString("field age".to_string())),
-                        }])
+                        }])]),
                     },
                 ],
                 columns
diff --git a/tests/sqlparser_common.rs b/tests/sqlparser_common.rs
index 5d5a17c..7068ffc 100644
--- a/tests/sqlparser_common.rs
+++ b/tests/sqlparser_common.rs
@@ -4295,6 +4295,7 @@ fn run_explain_analyze(
             describe_alias: _,
             analyze,
             verbose,
+            query_plan,
             statement,
             format,
             options,
@@ -4303,6 +4304,7 @@ fn run_explain_analyze(
             assert_eq!(analyze, expected_analyze);
             assert_eq!(format, expected_format);
             assert_eq!(options, exepcted_options);
+            assert!(!query_plan);
             assert_eq!("SELECT sqrt(id) FROM foo", statement.to_string());
         }
         _ => panic!("Unexpected Statement, must be Explain"),
@@ -4417,6 +4419,36 @@ fn parse_explain_analyze_with_simple_select() {
     );
 }
 
+#[test]
+fn parse_explain_query_plan() {
+    match all_dialects().verified_stmt("EXPLAIN QUERY PLAN SELECT sqrt(id) FROM foo") {
+        Statement::Explain {
+            query_plan,
+            analyze,
+            verbose,
+            statement,
+            ..
+        } => {
+            assert!(query_plan);
+            assert!(!analyze);
+            assert!(!verbose);
+            assert_eq!("SELECT sqrt(id) FROM foo", statement.to_string());
+        }
+        _ => unreachable!(),
+    }
+
+    // omit QUERY PLAN should be good
+    all_dialects().verified_stmt("EXPLAIN SELECT sqrt(id) FROM foo");
+
+    // missing PLAN keyword should return error
+    assert_eq!(
+        ParserError::ParserError("Expected: end of statement, found: SELECT".to_string()),
+        all_dialects()
+            .parse_sql_statements("EXPLAIN QUERY SELECT sqrt(id) FROM foo")
+            .unwrap_err()
+    );
+}
+
 #[test]
 fn parse_named_argument_function() {
     let sql = "SELECT FUN(a => '1', b => '2') FROM foo";
diff --git a/tests/sqlparser_snowflake.rs b/tests/sqlparser_snowflake.rs
index 50c5f74..e73b699 100644
--- a/tests/sqlparser_snowflake.rs
+++ b/tests/sqlparser_snowflake.rs
@@ -2405,3 +2405,42 @@ fn parse_use() {
         );
     }
 }
+
+#[test]
+fn view_comment_option_should_be_after_column_list() {
+    for sql in [
+        "CREATE OR REPLACE VIEW v (a) COMMENT = 'Comment' AS SELECT a FROM t",
+        "CREATE OR REPLACE VIEW v (a COMMENT 'a comment', b, c COMMENT 'c comment') COMMENT = 'Comment' AS SELECT a FROM t",
+        "CREATE OR REPLACE VIEW v (a COMMENT 'a comment', b, c COMMENT 'c comment') WITH (foo = bar) COMMENT = 'Comment' AS SELECT a FROM t",
+    ] {
+        snowflake_and_generic()
+            .verified_stmt(sql);
+    }
+}
+
+#[test]
+fn parse_view_column_descriptions() {
+    let sql = "CREATE OR REPLACE VIEW v (a COMMENT 'Comment', b) AS SELECT a, b FROM table1";
+
+    match snowflake_and_generic().verified_stmt(sql) {
+        Statement::CreateView { name, columns, .. } => {
+            assert_eq!(name.to_string(), "v");
+            assert_eq!(
+                columns,
+                vec![
+                    ViewColumnDef {
+                        name: Ident::new("a"),
+                        data_type: None,
+                        options: Some(vec![ColumnOption::Comment("Comment".to_string())]),
+                    },
+                    ViewColumnDef {
+                        name: Ident::new("b"),
+                        data_type: None,
+                        options: None,
+                    }
+                ]
+            );
+        }
+        _ => unreachable!(),
+    };
+}
diff --git a/tests/sqlparser_sqlite.rs b/tests/sqlparser_sqlite.rs
index d7fd3b8..d3e670e 100644
--- a/tests/sqlparser_sqlite.rs
+++ b/tests/sqlparser_sqlite.rs
@@ -22,6 +22,7 @@
 #[macro_use]
 mod test_utils;
 
+use sqlparser::keywords::Keyword;
 use test_utils::*;
 
 use sqlparser::ast::SelectItem::UnnamedExpr;
@@ -237,6 +238,43 @@ fn parse_create_table_auto_increment() {
     }
 }
 
+#[test]
+fn parse_create_table_primary_key_asc_desc() {
+    let expected_column_def = |kind| ColumnDef {
+        name: "bar".into(),
+        data_type: DataType::Int(None),
+        collation: None,
+        options: vec![
+            ColumnOptionDef {
+                name: None,
+                option: ColumnOption::Unique {
+                    is_primary: true,
+                    characteristics: None,
+                },
+            },
+            ColumnOptionDef {
+                name: None,
+                option: ColumnOption::DialectSpecific(vec![Token::make_keyword(kind)]),
+            },
+        ],
+    };
+
+    let sql = "CREATE TABLE foo (bar INT PRIMARY KEY ASC)";
+    match sqlite_and_generic().verified_stmt(sql) {
+        Statement::CreateTable(CreateTable { columns, .. }) => {
+            assert_eq!(vec![expected_column_def("ASC")], columns);
+        }
+        _ => unreachable!(),
+    }
+    let sql = "CREATE TABLE foo (bar INT PRIMARY KEY DESC)";
+    match sqlite_and_generic().verified_stmt(sql) {
+        Statement::CreateTable(CreateTable { columns, .. }) => {
+            assert_eq!(vec![expected_column_def("DESC")], columns);
+        }
+        _ => unreachable!(),
+    }
+}
+
 #[test]
 fn parse_create_sqlite_quote() {
     let sql = "CREATE TABLE `PRIMARY` (\"KEY\" INT, [INDEX] INT)";
@@ -281,6 +319,46 @@ fn parse_create_table_gencol() {
     sqlite_and_generic().verified_stmt("CREATE TABLE t1 (a INT, b INT AS (a * 2) STORED)");
 }
 
+#[test]
+fn parse_create_table_on_conflict_col() {
+    for keyword in [
+        Keyword::ROLLBACK,
+        Keyword::ABORT,
+        Keyword::FAIL,
+        Keyword::IGNORE,
+        Keyword::REPLACE,
+    ] {
+        let sql = format!("CREATE TABLE t1 (a INT, b INT ON CONFLICT {:?})", keyword);
+        match sqlite_and_generic().verified_stmt(&sql) {
+            Statement::CreateTable(CreateTable { columns, .. }) => {
+                assert_eq!(
+                    vec![ColumnOptionDef {
+                        name: None,
+                        option: ColumnOption::OnConflict(keyword),
+                    }],
+                    columns[1].options
+                );
+            }
+            _ => unreachable!(),
+        }
+    }
+}
+
+#[test]
+fn test_parse_create_table_on_conflict_col_err() {
+    let sql_err = "CREATE TABLE t1 (a INT, b INT ON CONFLICT BOH)";
+    let err = sqlite_and_generic()
+        .parse_sql_statements(sql_err)
+        .unwrap_err();
+    assert_eq!(
+        err,
+        ParserError::ParserError(
+            "Expected: one of ROLLBACK or ABORT or FAIL or IGNORE or REPLACE, found: BOH"
+                .to_string()
+        )
+    );
+}
+
 #[test]
 fn parse_create_table_untyped() {
     sqlite().verified_stmt("CREATE TABLE t1 (a, b AS (a * 2), c NOT NULL)");