datafusion-sqlparse/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, QuoteDelimitedString, 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])
    );
}