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:

citrus/connectors/citrus-sql/src/main/java/org/citrusframework/actions/ExecuteSQLQueryAction.java

Line 339 in f210123

if (!stmt.toLowerCase().startsWith("select")) {

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.