FunctionTest.java

/*-
 * #%L
 * JSQLParser library
 * %%
 * Copyright (C) 2004 - 2023 JSQLParser
 * %%
 * Dual licensed under GNU LGPL 2.1 or Apache License 2.0
 * #L%
 */
package net.sf.jsqlparser.expression;

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertInstanceOf;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.test.TestUtils;
import org.junit.jupiter.api.Disabled;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.params.ParameterizedTest;
import org.junit.jupiter.params.provider.ValueSource;

class FunctionTest {
    @Test
    @Disabled
    // @Todo: Implement the Prediction(... USING ...) functions
    // https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/PREDICTION.html
    void testNestedFunctions() throws JSQLParserException {
        String sqlStr =
                "select cust_gender, count(*) as cnt, round(avg(age)) as avg_age\n"
                        + "   from mining_data_apply_v\n"
                        + "   where prediction(dt_sh_clas_sample cost model\n"
                        + "      using cust_marital_status, education, household_size) = 1\n"
                        + "   group by cust_gender\n"
                        + "   order by cust_gender";

        TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr, true);
    }

    @Test
    void testCallFunction() throws JSQLParserException {
        String sqlStr =
                "call dbms_scheduler.auto_purge ( ) ";
        TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr, true);
    }

    @Test
    void testChainedFunctions() throws JSQLParserException {
        String sqlStr =
                "select f1(a1=1).f2 = 1";
        TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr, true);

        sqlStr =
                "select f1(a1=1).f2(b).f2 = 1";
        TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr, true);
    }


    @Test
    void testDatetimeParameter() throws JSQLParserException {
        String sqlStr = "SELECT DATE(DATETIME '2016-12-25 23:59:59')";
        TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr, true);
    }

    @Test
    void testFunctionArrayParameter() throws JSQLParserException {
        String sqlStr = "select unnest(ARRAY[1,2,3], nested >= true) as a";

        TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr, true);
    }

    @Test
    void testSubSelectArrayWithoutKeywordParameter() throws JSQLParserException {
        String sqlStr = "SELECT\n" +
                "  email,\n" +
                "  REGEXP_CONTAINS(email, r'@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.]+') AS is_valid\n" +
                "FROM\n" +
                "  (SELECT\n" +
                "    ['foo@example.com', 'bar@example.org', 'www.example.net']\n" +
                "    AS addresses),\n" +
                "  UNNEST(addresses) AS email";
        TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr, true);
    }

    @Test
    void testSubSelectParameterWithoutParentheses() throws JSQLParserException {
        String sqlStr = "SELECT COALESCE(SELECT mycolumn FROM mytable, 0)";
        TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr, true,
                parser -> parser.withUnparenthesizedSubSelects(true));
    }

    @Test
    void testSimpleFunctionIssue2059() throws JSQLParserException {
        String sqlStr = "select count(*) from zzz";
        TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr, true, parser -> {
            parser.withAllowComplexParsing(false);
        });
    }

    @ParameterizedTest
    @ValueSource(strings = {
            "select LISTAGG(field, ',' on overflow truncate '...') from dual",
            "select LISTAGG(field, ',' on overflow truncate '...' with count) from dual",
            "select LISTAGG(field, ',' on overflow truncate '...' without count) from dual",
            "select LISTAGG(field, ',' on overflow error) from dual", "SELECT department, \n" +
                    "       LISTAGG(name, ', ' ON OVERFLOW TRUNCATE '... (truncated)' WITH COUNT) WITHIN GROUP (ORDER BY name)\n"
                    +
                    "       AS employee_names\n" +
                    "FROM employees\n" +
                    "GROUP BY department;"
    })
    void testListAggOnOverflow(String sqlStr) throws Exception {
        TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr, true);
    }

    @ParameterizedTest
    @ValueSource(strings = {
            "select RTRIM('string')",
            "select LTRIM('string')",
            "select RTRIM(field) from dual",
            "select LTRIM(field) from dual"
    })
    void testTrimFunctions(String sqlStr) throws JSQLParserException {
        TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr, true);
    }

    @Test
    void TestIntervalParameterIssue2272() throws JSQLParserException {
        String sqlStr =
                "SELECT DATE_SUB('2025-06-19', INTERVAL QUARTER(STR_TO_DATE('20250619', '%Y%m%d')) - 1 QUARTER) from dual";
        TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr, true);
    }

    @Test
    void testAesDecryptWithKeyExpressionParameter() throws JSQLParserException {
        String expression = "aes_decrypt(from_base64(entity), KEY chain.entity)";
        TestUtils.assertExpressionCanBeParsedAndDeparsed(expression, true);

        Function function = (Function) CCJSqlParserUtil.parseExpression(expression);
        KeyExpression keyExpression =
                assertInstanceOf(KeyExpression.class, function.getParameters().get(1));
        assertEquals("chain.entity", keyExpression.getExpression().toString());

        function.accept(new ExpressionVisitorAdapter<>(), null);
    }

    @Test
    void testAesDecryptWithKeyExpressionInSelect() throws JSQLParserException {
        String sqlStr = "SELECT t1.entity, SUM(t2.balance) AS total_balance\n"
                + "FROM (\n"
                + "    SELECT DISTINCT address, aes_decrypt(from_base64(entity), KEY chain.entity) AS entity\n"
                + "    FROM bch_entity\n"
                + ") t1\n"
                + "JOIN bch_address_token_statis t2\n"
                + "ON t1.address = t2.address\n"
                + "GROUP BY t1.entity";

        TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr, true);
    }
}