Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17

This is experimental functionality, and as such subject to change. Use at your own risk!

Pattern based transformation

Applies to ❌ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition

Starting with jOOQ 3.16, a new Query Object Model (QOM) API has been added, which features traversal and replacement SPIs. One out of the box application of replacement are pattern transformations, where we recognise common patterns in a query's expressions and allow for replacing them by a better expression.

For example, the following transformations are possible:

-- With Settings.transformPatternsTrim active, this:
SELECT RTRIM(LTRIM(col)) FROM tab;

-- ... is transformed into the equivalent expression:
SELECT TRIM(col) FROM tab;

The whole feature set is deactivated by default using the Settings.transformPatterns flag as the traversal overhead does not warrant activation by default. Common use-cases for the activation of this feature include:

SQL linting, including as part of an ExecuteListener, where the effort can be controlled in a utility run only during integration testing, for example.
SQL cleanup, including as part of a ParsingConnection, where inputs/outputs are cached by default.
Dialect migration, when moving from a less powerful dialect to a more powerful one, or to a new version, manually written emulations may become obsolete.
Patching specific SQL features only

Individual features can either be turned on in bulk and opted out of individually, or each feature is turned on individually, depending on the use case.

Repetition of pattern replacement

As any replacement SPI implementation, the algorithm iterates over the jOOQ expression tree until it can no longer replace anything, i.e. until the transformations stabilise. For example, the following sequence of transformations is possible:

-- Original SQL
SELECT NOT(1 = 0) AND 1 = 1;

-- Step 1: Settings.transformPatternsNotComparison
SELECT 1 != 0 AND 1 = 1;

-- Step 2: Settings.transformPatternsTrivialPredicates
SELECT TRUE AND TRUE;

-- Step 3: Settings.transformPatternsTrivialPredicates
SELECT TRUE;

Dialect specific result

The following sections will make claims like 1 / TAN(x) or COS(x) / SIN(x) being replaced by COT(x), see e.g. Trigonometric functions. Some dialects may not have native support for COT(), so this is going to be emulated on those dialects, making the transformation effectively redundant. But these things happen at different times:

This pattern transformation feature can either be invoked explicitly by users, or it is enabled by configuration and then happens before all the rendering takes place, only once for the top level org.jooq.QueryPart
The rendering of SQL invokes emulations as the above, after the SQL has been transformed.

In other words, the two operations do not know about each other.

3.26.9.9.1.: AND to NOT IN
3.26.9.9.2.: Arithmetic comparisons (new)
3.26.9.9.3.: Arithmetic expressions
3.26.9.9.4.: BIT_GET function (new)
3.26.9.9.5.: BIT_SET function (new)
3.26.9.9.6.: CASE searched to CASE simple (new)
3.26.9.9.7.: CASE to CASE abbreviation (new)
3.26.9.9.8.: CASE with DISTINCT FROM to DECODE (new)
3.26.9.9.9.: CASE with ELSE NULL (new)
3.26.9.9.10.: COUNT(*) scalar subquery comparison
3.26.9.9.11.: COUNT(const) (new)
3.26.9.9.12.: COUNT(expr) scalar subquery comparison
3.26.9.9.13.: DISTINCT FROM NULL (new)
3.26.9.9.14.: Empty scalar subquery
3.26.9.9.15.: Flatten CASE (new)
3.26.9.9.16.: Flatten CASE abbreviations (new)
3.26.9.9.17.: Flatten DECODE (new)
3.26.9.9.18.: Hyperbolic functions
3.26.9.9.19.: Idempotent function repetition
3.26.9.9.20.: Inverse hyperbolic functions
3.26.9.9.21.: Logarithmic functions
3.26.9.9.22.: Merge AND predicates
3.26.9.9.23.: Merge BIT_NOT with BIT_NAND
3.26.9.9.24.: Merge BIT_NOT with BIT_NOR
3.26.9.9.25.: Merge BIT_NOT with BIT_XNOR
3.26.9.9.26.: Merge CASE .. WHEN and ELSE clauses (new)
3.26.9.9.27.: Merge CASE .. WHEN clauses (new)
3.26.9.9.28.: Merge IN predicates
3.26.9.9.29.: Merge NOT with comparison predicates
3.26.9.9.30.: Merge NOT with DISTINCT predicate
3.26.9.9.31.: Merge OR predicates
3.26.9.9.32.: Merge range predicates
3.26.9.9.33.: Normalise associative operations
3.26.9.9.34.: Normalise fields compared to values
3.26.9.9.35.: Normalise IN list with single element to comparison
3.26.9.9.36.: NOT AND (new)
3.26.9.9.37.: NOT OR (new)
3.26.9.9.38.: NULL ON NULL INPUT (new)
3.26.9.9.39.: OR to IN
3.26.9.9.40.: Repeated arithmetic negation
3.26.9.9.41.: Repeated bitwise negation
3.26.9.9.42.: Repeated NOT
3.26.9.9.43.: Simplify CASE abbreviations (new)
3.26.9.9.44.: Trigonometric functions
3.26.9.9.45.: Trim
3.26.9.9.46.: Trivial bitwise operations (new)
3.26.9.9.47.: Trivial CASE abbreviations
3.26.9.9.48.: Trivial predicates
3.26.9.9.49.: Unnecessary DISTINCT (new)
3.26.9.9.50.: Unnecessary EXISTS subquery clauses (new)
3.26.9.9.51.: Unnecessary GROUP BY expressions (new)
3.26.9.9.52.: Unnecessary INNER JOIN (new)
3.26.9.9.53.: Unnecessary ORDER BY expressions (new)
3.26.9.9.54.: Unnecessary scalar subquery (new)
3.26.9.9.55.: Unreachable CASE clauses (new)
3.26.9.9.56.: Unreachable DECODE clauses (new)

previous : next

References to this page

Feedback

Do you have any feedback about this page? We'd love to hear it!

Tell us a little bit about yourself and join our newsletter (examples)

Pattern based transformation

Repetition of pattern replacement

Dialect specific result

Table of contents

References to this page

Feedback

Community

Support

Legal

Documentation