UNION of unparenthesised queries with ORDER BY/LIMIT is prohibited in MySQL (backward compatibility broken in 3.6.0)

[bigboynaruto]

Since 3.6.0, ktorm removed parenthesis from queries in UNION (#329), which apparently doesn't work in MySQL if the queries contain certain clauses, e.g. ORDER BY, LIMIT or FOR UPDATE.

3.5.0

(SELECT * FROM my_table WHERE (my_table.created_at <= ?) AND (my_table.status = ?) ORDER BY my_table.created_at DESC LIMIT ?, ?) UNION (SELECT * FROM my_table WHERE (my_table.created_at BETWEEN ? AND ?) AND (my_table.status = ?))

3.6.0

Syntax error in SQL statement
SELECT * FROM my_table WHERE (my_table.created_at <= ?) AND (my_table.status = ?) ORDER BY my_table.created_at DESC LIMIT ?, ? [*]UNION SELECT * FROM my_table WHERE (my_table.created_at BETWEEN ? AND ?) AND (my_table.status = ?) 

The above queries were tested in H2 with MySQL and MariaDB compatibility.

jdbc:h2:mem:test;mode=mysql;DATABASE_TO_UPPER=FALSE
jdbc:h2:mem:test;mode=mariadb;DATABASE_TO_UPPER=FALSE

I have both dependencies ktorm-core and ktorm-support-mysql installed. Is there any way to force parenthesis?

[vincentlauvlwj]

Thank you for your feedback!

To force parenthesis, you can write your own SqlFormatter and override the visitUnion function.

[ikezedev]

This also fails for Postgres when used with limit, and or orderBy.

[ikezedev]

For anyone having the same issues, you can create custom formatter as suggested by @vincentlauvlwj like so:

class CustomSqlFormatter(database: Database, beautifySql: Boolean, identSize: Int) : 
   PostgreSqlFormatter(database, beautifySql, identSize), PostgreSqlExpressionVisitor {

    private fun surround(expr: QueryExpression) {
        if (expr.removeBrackets) {
            when (expr) {
                is SelectExpression -> visitSelect(expr)
                is UnionExpression -> visitUnion(expr)
            }
        } else {
            when (expr) {
                is SelectExpression -> {
                    write("(")
                    visitSelect(expr)
                    write(") ")
                }
                is UnionExpression -> visitUnion(expr)
            }
            removeLastBlank()
        }
    }


    override fun visitUnion(expr: UnionExpression): UnionExpression {
        surround(expr.left)

        if (expr.isUnionAll) {
            newLine(Indentation.SAME)
            writeKeyword("union all ")
            newLine(Indentation.SAME)
        } else {
            newLine(Indentation.SAME)
            writeKeyword("union ")
            newLine(Indentation.SAME)
        }

        surround(expr.right)

        if (expr.orderBy.isNotEmpty()) {
            newLine(Indentation.SAME)
            writeKeyword("order by ")
            visitExpressionList(expr.orderBy)
        }

        if (expr.offset != null || expr.limit != null) {
            writePagination(expr)
        }

        return expr
    }
}

Use the formatter to extend your dialect, in my case, I am using Postgres

object CustomDialect: PostgreSqlDialect() {
    override fun createSqlFormatter(database: Database, beautifySql: Boolean, indentSize: Int): SqlFormatter {
        return CustomSqlFormatter(database, beautifySql, indentSize)
    }
}

Use the dialect when create your database instance

 val database = Database.connect(getDataSource(config.source), CustomDialect)

@vincentlauvlwj It would be nice if the docs covers advanced use cases like this, should I contribute this?