query statements may not start using the 'WITH' keyword

[leonschenk]

Citrus Version 3.4.0

Expected behavior The statement/query, starting with the keyword 'WITH' instead of 'SELECT' will be executed.

Actual behavior Exception is thrown while validating the statement, before executing.

Test case sample

enter a recursive query like the following (example taken from source https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-recursive-query/) query is not executed because of ExecuteSqlQueryAction.validateSqlStatement

query(dataSource).statement("WITH RECURSIVE subordinates AS (
    SELECT
        employee_id,
        manager_id,
        full_name
    FROM
        employees
    WHERE
        employee_id = 2
    UNION
        SELECT
            e.employee_id,
            e.manager_id,
            e.full_name
        FROM
            employees e
        INNER JOIN subordinates s ON s.employee_id = e.manager_id
) SELECT
    *
FROM
    subordinates;").build();

Workaround:

testRunner.run(new ExecuteSQLQueryAction(sqlQueryActionBuilder) {
    @Override
    protected void validateSqlStatement(final String stmt) {
        if (!(stmt.toLowerCase().startsWith("select") || stmt.toLowerCase().startsWith("with"))) {
            throw new CitrusRuntimeException("Missing keyword SELECT or WITH in statement: " + stmt);
        }
    }
});

[bbortt]

that is a good report! the WITH clause (also known as Common Table Expressions (CTEs)) is part of the SQL standard SQL:1999. according to the list of SQL reserved words all modern databases support it (as they should). it is therefore not PostgreSQL specific.

I think it's fair to say that fix would be welcome:

https://github.com/citrusframework/citrus/blob/f21012346a1fc2c8804838085bd40a2d6ff3ec5b/connectors/citrus-sql/src/main/java/org/citrusframework/actions/ExecuteSQLQueryAction.java#L339

do you need it backported to 3.x, or is it okay to include into 4.x @leonschenk?

[leonschenk]

@bbortt thank you! I will be fine with the workaround for now.