tests/sqlparser_oracle.rs

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

//! Test SQL syntax, specific to [sqlparser::dialect::OracleDialect].

#[cfg(test)]
use pretty_assertions::assert_eq;

use sqlparser::{
    ast::{
        BinaryOperator, Expr, Ident, Insert, ObjectName, Query, QuoteDelimitedString, SetExpr,
        Statement, TableAliasWithoutColumns, TableObject, Value, ValueWithSpan,
    },
    dialect::OracleDialect,
    parser::ParserError,
    tokenizer::Span,
};
use test_utils::{all_dialects_where, expr_from_projection, number, TestedDialects};

mod test_utils;

fn oracle() -> TestedDialects {
    TestedDialects::new(vec![Box::new(OracleDialect)])
}

/// Convenience constructor for [QuoteDelimitedstring].
fn quote_delimited_string(
    start_quote: char,
    value: &'static str,
    end_quote: char,
) -> QuoteDelimitedString {
    QuoteDelimitedString {
        start_quote,
        value: value.into(),
        end_quote,
    }
}

/// Oracle: `||` has a lower precedence than `*` and `/`
#[test]
fn muldiv_have_higher_precedence_than_strconcat() {
    // ...............  A .. B ...... C .. D ...........
    let sql = "SELECT 3 / 5 || 'asdf' || 7 * 9 FROM dual";
    let select = oracle().verified_only_select(sql);
    assert_eq!(1, select.projection.len());
    assert_eq!(
        expr_from_projection(&select.projection[0]),
        // (C || D)
        &Expr::BinaryOp {
            // (A || B)
            left: Box::new(Expr::BinaryOp {
                // A
                left: Box::new(Expr::BinaryOp {
                    left: Box::new(Expr::Value(number("3").into())),
                    op: BinaryOperator::Divide,
                    right: Box::new(Expr::Value(number("5").into())),
                }),
                op: BinaryOperator::StringConcat,
                right: Box::new(Expr::Value(ValueWithSpan {
                    value: Value::SingleQuotedString("asdf".into()),
                    span: Span::empty(),
                })),
            }),
            op: BinaryOperator::StringConcat,
            // D
            right: Box::new(Expr::BinaryOp {
                left: Box::new(Expr::Value(number("7").into())),
                op: BinaryOperator::Multiply,
                right: Box::new(Expr::Value(number("9").into())),
            }),
        }
    );
}

/// Oracle: `+`, `-`, and `||` have the same precedence and parse from left-to-right
#[test]
fn plusminus_have_same_precedence_as_strconcat() {
    // ................ A .. B .... C .. D ............
    let sql = "SELECT 3 + 5 || '.3' || 7 - 9 FROM dual";
    let select = oracle().verified_only_select(sql);
    assert_eq!(1, select.projection.len());
    assert_eq!(
        expr_from_projection(&select.projection[0]),
        // D
        &Expr::BinaryOp {
            left: Box::new(Expr::BinaryOp {
                // B
                left: Box::new(Expr::BinaryOp {
                    // A
                    left: Box::new(Expr::BinaryOp {
                        left: Box::new(Expr::Value(number("3").into())),
                        op: BinaryOperator::Plus,
                        right: Box::new(Expr::Value(number("5").into())),
                    }),
                    op: BinaryOperator::StringConcat,
                    right: Box::new(Expr::Value(ValueWithSpan {
                        value: Value::SingleQuotedString(".3".into()),
                        span: Span::empty(),
                    })),
                }),
                op: BinaryOperator::StringConcat,
                right: Box::new(Expr::Value(number("7").into())),
            }),
            op: BinaryOperator::Minus,
            right: Box::new(Expr::Value(number("9").into()))
        }
    );
}

#[test]
fn parse_quote_delimited_string() {
    let dialect = all_dialects_where(|d| d.supports_quote_delimited_string());
    let sql = "SELECT Q'.abc.', \
                      Q'Xab'cX', \
                      Q'|abc'''|', \
                      Q'{abc}d}', \
                      Q'[]abc[]', \
                      Q'<a'bc>', \
                      Q'<<<a'bc>', \
                      Q'('abc'('abc)', \
                      Q'(abc'def))', \
                      Q'(abc'def)))' \
                 FROM dual";
    let select = dialect.verified_only_select(sql);
    assert_eq!(10, select.projection.len());
    assert_eq!(
        &Expr::Value(
            Value::QuoteDelimitedStringLiteral(quote_delimited_string('.', "abc", '.'))
                .with_empty_span()
        ),
        expr_from_projection(&select.projection[0])
    );
    assert_eq!(
        &Expr::Value(
            (Value::QuoteDelimitedStringLiteral(quote_delimited_string('X', "ab'c", 'X')))
                .with_empty_span()
        ),
        expr_from_projection(&select.projection[1])
    );
    assert_eq!(
        &Expr::Value(
            (Value::QuoteDelimitedStringLiteral(quote_delimited_string('|', "abc'''", '|')))
                .with_empty_span()
        ),
        expr_from_projection(&select.projection[2])
    );
    assert_eq!(
        &Expr::Value(
            (Value::QuoteDelimitedStringLiteral(quote_delimited_string('{', "abc}d", '}')))
                .with_empty_span()
        ),
        expr_from_projection(&select.projection[3])
    );
    assert_eq!(
        &Expr::Value(
            (Value::QuoteDelimitedStringLiteral(quote_delimited_string('[', "]abc[", ']')))
                .with_empty_span()
        ),
        expr_from_projection(&select.projection[4])
    );
    assert_eq!(
        &Expr::Value(
            (Value::QuoteDelimitedStringLiteral(quote_delimited_string('<', "a'bc", '>')))
                .with_empty_span()
        ),
        expr_from_projection(&select.projection[5])
    );
    assert_eq!(
        &Expr::Value(
            (Value::QuoteDelimitedStringLiteral(quote_delimited_string('<', "<<a'bc", '>')))
                .with_empty_span()
        ),
        expr_from_projection(&select.projection[6])
    );
    assert_eq!(
        &Expr::Value(
            (Value::QuoteDelimitedStringLiteral(quote_delimited_string('(', "'abc'('abc", ')')))
                .with_empty_span()
        ),
        expr_from_projection(&select.projection[7])
    );
    assert_eq!(
        &Expr::Value(
            (Value::QuoteDelimitedStringLiteral(quote_delimited_string('(', "abc'def)", ')')))
                .with_empty_span()
        ),
        expr_from_projection(&select.projection[8])
    );
    assert_eq!(
        &Expr::Value(
            (Value::QuoteDelimitedStringLiteral(quote_delimited_string('(', "abc'def))", ')')))
                .with_empty_span()
        ),
        expr_from_projection(&select.projection[9])
    );
}

#[test]
fn parse_invalid_quote_delimited_strings() {
    let dialect = all_dialects_where(|d| d.supports_quote_delimited_string());
    // invalid quote delimiter
    for q in [' ', '\t', '\r', '\n'] {
        assert_eq!(
            dialect.parse_sql_statements(&format!("SELECT Q'{q}abc{q}' FROM dual")),
            Err(ParserError::TokenizerError(
                "Invalid space, tab, newline, or EOF after 'Q'' at Line: 1, Column: 10".into()
            )),
            "with quote char {q:?}"
        );
    }
    // invalid eof after quote
    assert_eq!(
        dialect.parse_sql_statements("SELECT Q'"),
        Err(ParserError::TokenizerError(
            "Invalid space, tab, newline, or EOF after 'Q'' at Line: 1, Column: 10".into()
        )),
        "with EOF quote char"
    );
    // unterminated string
    assert_eq!(
        dialect.parse_sql_statements("SELECT Q'|asdfa...."),
        Err(ParserError::TokenizerError(
            "Unterminated string literal at Line: 1, Column: 9".into()
        )),
        "with EOF quote char"
    );
}

#[test]
fn parse_quote_delimited_string_lowercase() {
    let dialect = all_dialects_where(|d| d.supports_quote_delimited_string());
    let sql = "select q'!a'b'c!d!' from dual";
    let select = dialect.verified_only_select_with_canonical(sql, "SELECT Q'!a'b'c!d!' FROM dual");
    assert_eq!(1, select.projection.len());
    assert_eq!(
        &Expr::Value(
            Value::QuoteDelimitedStringLiteral(quote_delimited_string('!', "a'b'c!d", '!'))
                .with_empty_span()
        ),
        expr_from_projection(&select.projection[0])
    );
}

#[test]
fn parse_quote_delimited_string_but_is_a_word() {
    let dialect = all_dialects_where(|d| d.supports_quote_delimited_string());
    let sql = "SELECT q, quux, q.abc FROM dual q";
    let select = dialect.verified_only_select(sql);
    assert_eq!(3, select.projection.len());
    assert_eq!(
        &Expr::Identifier(Ident::with_span(Span::empty(), "q")),
        expr_from_projection(&select.projection[0])
    );
    assert_eq!(
        &Expr::Identifier(Ident::with_span(Span::empty(), "quux")),
        expr_from_projection(&select.projection[1])
    );
    assert_eq!(
        &Expr::CompoundIdentifier(vec![
            Ident::with_span(Span::empty(), "q"),
            Ident::with_span(Span::empty(), "abc")
        ]),
        expr_from_projection(&select.projection[2])
    );
}

#[test]
fn parse_national_quote_delimited_string() {
    let dialect = all_dialects_where(|d| d.supports_quote_delimited_string());
    let sql = "SELECT NQ'.abc.' FROM dual";
    let select = dialect.verified_only_select(sql);
    assert_eq!(1, select.projection.len());
    assert_eq!(
        &Expr::Value(
            Value::NationalQuoteDelimitedStringLiteral(quote_delimited_string('.', "abc", '.'))
                .with_empty_span()
        ),
        expr_from_projection(&select.projection[0])
    );
}

#[test]
fn parse_national_quote_delimited_string_lowercase() {
    let dialect = all_dialects_where(|d| d.supports_quote_delimited_string());
    for prefix in ["nq", "Nq", "nQ", "NQ"] {
        let select = dialect.verified_only_select_with_canonical(
            &format!("select {prefix}'!a'b'c!d!' from dual"),
            "SELECT NQ'!a'b'c!d!' FROM dual",
        );
        assert_eq!(1, select.projection.len());
        assert_eq!(
            &Expr::Value(
                Value::NationalQuoteDelimitedStringLiteral(quote_delimited_string(
                    '!', "a'b'c!d", '!'
                ))
                .with_empty_span()
            ),
            expr_from_projection(&select.projection[0])
        );
    }
}

#[test]
fn parse_national_quote_delimited_string_but_is_a_word() {
    let dialect = all_dialects_where(|d| d.supports_quote_delimited_string());
    let sql = "SELECT nq, nqoo, nq.abc FROM dual q";
    let select = dialect.verified_only_select(sql);
    assert_eq!(3, select.projection.len());
    assert_eq!(
        &Expr::Identifier(Ident::with_span(Span::empty(), "nq")),
        expr_from_projection(&select.projection[0])
    );
    assert_eq!(
        &Expr::Identifier(Ident::with_span(Span::empty(), "nqoo")),
        expr_from_projection(&select.projection[1])
    );
    assert_eq!(
        &Expr::CompoundIdentifier(vec![
            Ident::with_span(Span::empty(), "nq"),
            Ident::with_span(Span::empty(), "abc")
        ]),
        expr_from_projection(&select.projection[2])
    );
}

#[test]
fn test_optimizer_hints() {
    let oracle_dialect = oracle();

    // selects: all `/*+...*/` comments are collected as hints
    let select = oracle_dialect.verified_only_select_with_canonical(
        "SELECT /*+one two three*/ /*+four five six*/ 1 FROM dual",
        "SELECT /*+one two three*/ /*+four five six*/ 1 FROM dual",
    );
    assert_eq!(select.optimizer_hints.len(), 2);
    assert_eq!(select.optimizer_hints[0].text, "one two three");
    assert_eq!(select.optimizer_hints[0].prefix, "");
    assert_eq!(select.optimizer_hints[1].text, "four five six");

    // regular comments are skipped, hints after them are still collected
    let select = oracle_dialect.verified_only_select_with_canonical(
        "SELECT /*one two three*/ /*+four five six*/ 1 FROM dual",
        "SELECT /*+four five six*/ 1 FROM dual",
    );
    assert_eq!(select.optimizer_hints.len(), 1);
    assert_eq!(select.optimizer_hints[0].text, "four five six");

    let select = oracle_dialect.verified_only_select_with_canonical(
        "SELECT --+ one two three /* asdf */\n 1 FROM dual",
        "SELECT --+ one two three /* asdf */\n 1 FROM dual",
    );
    assert_eq!(select.optimizer_hints.len(), 1);
    assert_eq!(select.optimizer_hints[0].text, " one two three /* asdf */");
    assert_eq!(select.optimizer_hints[0].prefix, "");

    // inserts
    oracle_dialect.verified_stmt("INSERT /*+ append */ INTO t1 SELECT * FROM all_objects");

    // updates
    oracle_dialect.verified_stmt("UPDATE /*+ DISABLE_PARALLEL_DML */ table_name SET column1 = 1");

    // deletes
    oracle_dialect.verified_stmt("DELETE --+ ENABLE_PARALLEL_DML\n FROM table_name");

    // merges
    oracle_dialect.verified_stmt(
        "MERGE /*+ CLUSTERING */ INTO people_target pt \
         USING people_source ps \
            ON (pt.person_id = ps.person_id) \
          WHEN NOT MATCHED THEN INSERT \
               (pt.person_id, pt.first_name, pt.last_name, pt.title) \
               VALUES (ps.person_id, ps.first_name, ps.last_name, ps.title)",
    );

    // single-line prefixed hint (Oracle supports `--` without trailing whitespace)
    let select = oracle_dialect.verified_only_select_with_canonical(
        "SELECT --abc+ text\n 1 FROM dual",
        "SELECT --abc+ text\n 1 FROM dual",
    );
    assert_eq!(select.optimizer_hints.len(), 1);
    assert_eq!(select.optimizer_hints[0].prefix, "abc");
    assert_eq!(select.optimizer_hints[0].text, " text");
}

#[test]
fn test_connect_by() {
    let oracle_dialect = oracle();

    oracle_dialect.verified_only_select(
        "SELECT last_name AS \"Employee\", CONNECT_BY_ISCYCLE AS \"Cycle\", \
                LEVEL, \
                SYS_CONNECT_BY_PATH(last_name, '/') AS \"Path\" \
           FROM employees \
          WHERE level <= 3 AND department_id = 80 \
          START WITH last_name = 'King' \
        CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL <= 4 \
          ORDER BY \"Employee\", \"Cycle\", LEVEL, \"Path\"",
    );

    // CONNECT_BY_ROOT
    oracle_dialect.verified_only_select(
        "SELECT last_name AS \"Employee\", CONNECT_BY_ROOT last_name AS \"Manager\", \
                LEVEL - 1 AS \"Pathlen\", SYS_CONNECT_BY_PATH(last_name, '/') AS \"Path\" \
           FROM employees \
          WHERE LEVEL > 1 AND department_id = 110 \
        CONNECT BY PRIOR employee_id = manager_id \
          ORDER BY \"Employee\", \"Manager\", \"Pathlen\", \"Path\"",
    );
}

#[test]
fn test_insert_with_table_alias() {
    let oracle_dialect = oracle();

    fn verify_table_name_with_alias(stmt: &Statement, exp_table_name: &str, exp_table_alias: &str) {
        assert!(matches!(stmt,
            Statement::Insert(Insert {
                table: TableObject::TableName(table_name),
                table_alias: Some(TableAliasWithoutColumns {
                    explicit: false,
                    alias: Ident {
                        value: table_alias,
                        quote_style: None,
                        span: _
                    }
                }),
                ..
            })
            if table_alias == exp_table_alias
            && table_name == &ObjectName::from(vec![Ident {
                value: exp_table_name.into(),
                quote_style: None,
                span: Span::empty(),
            }])
        ));
    }

    let stmt = oracle_dialect.verified_stmt(
        "INSERT INTO foo_t t \
         SELECT 1, 2, 3 FROM dual",
    );
    verify_table_name_with_alias(&stmt, "foo_t", "t");

    let stmt = oracle_dialect.verified_stmt(
        "INSERT INTO foo_t asdf (a, b, c) \
         SELECT 1, 2, 3 FROM dual",
    );
    verify_table_name_with_alias(&stmt, "foo_t", "asdf");

    let stmt = oracle_dialect.verified_stmt(
        "INSERT INTO foo_t t (a, b, c) \
         VALUES (1, 2, 3)",
    );
    verify_table_name_with_alias(&stmt, "foo_t", "t");

    let stmt = oracle_dialect.verified_stmt(
        "INSERT INTO foo_t t \
         VALUES (1, 2, 3)",
    );
    verify_table_name_with_alias(&stmt, "foo_t", "t");

    let stmt =
        oracle_dialect.verified_stmt("INSERT INTO foo_t t (t.id, t.val) SELECT 1, 2 FROM dual");
    verify_table_name_with_alias(&stmt, "foo_t", "t");
    if let Statement::Insert(Insert { columns, .. }) = stmt {
        assert_eq!(
            vec![
                ObjectName::from(vec![Ident::new("t"), Ident::new("id")]),
                ObjectName::from(vec![Ident::new("t"), Ident::new("val")])
            ],
            columns
        );
    } else {
        panic!("not an insert statement");
    };
}

#[test]
fn test_insert_without_alias() {
    let oracle_dialect = oracle();

    // check DEFAULT
    let sql = "INSERT INTO t default SELECT 'a' FROM dual";
    assert_eq!(
        oracle_dialect.parse_sql_statements(sql),
        Err(ParserError::ParserError(
            "Expected: SELECT, VALUES, or a subquery in the query body, found: default".into()
        ))
    );

    // check SELECT
    let sql = "INSERT INTO t SELECT 'a' FROM dual";
    let stmt = oracle_dialect.verified_stmt(sql);
    assert!(matches!(
        &stmt,
        Statement::Insert(Insert {
            table_alias: None,
            source: Some(source),
            ..
        })
        if matches!(&**source, Query { body, .. } if matches!(&**body, SetExpr::Select(_)))));

    // check WITH
    let sql = "INSERT INTO dual WITH w AS (SELECT 1 AS y FROM dual) SELECT y FROM w";
    let stmt = oracle_dialect.verified_stmt(sql);
    assert!(matches!(
        &stmt,
        Statement::Insert(Insert {
            table_alias: None,
            source: Some(source),
            ..
        })
        if matches!(&**source, Query { body, .. } if matches!(&**body, SetExpr::Select(_)))));

    // check VALUES
    let sql = "INSERT INTO t VALUES (1)";
    let stmt = oracle_dialect.verified_stmt(sql);
    assert!(matches!(
        stmt,
        Statement::Insert(Insert {
            table_alias: None,
            source: Some(source),
            ..
        })
        if matches!(&*source, Query { body, .. } if matches!(&**body, SetExpr::Values(_)))
    ));
}