The jOOQ User Manual SQL building QueryParts SQL transformation Pattern based transformation

previous : next

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.

Table of contents

3.24.9.7.1.

AND to NOT IN (new)

3.24.9.7.2.

Arithmetic expressions (new)

3.24.9.7.3.

COUNT(*) scalar subquery comparison (new)

3.24.9.7.4.

COUNT(expr) scalar subquery comparison (new)

3.24.9.7.5.

Empty scalar subquery (new)

3.24.9.7.6.

Hyperbolic functions (new)

3.24.9.7.7.

Idempotent function repetition (new)

3.24.9.7.8.

Inverse hyperbolic functions (new)

3.24.9.7.9.

Logarithmic functions (new)

3.24.9.7.10.

Merge AND predicates (new)

3.24.9.7.11.

Merge BIT_NOT with BIT_NAND (new)

3.24.9.7.12.

Merge BIT_NOT with BIT_NOR (new)

3.24.9.7.13.

Merge BIT_NOT with BIT_XNOR (new)

3.24.9.7.14.

Merge IN predicates (new)

3.24.9.7.15.

Merge NOT with comparison predicates (new)

3.24.9.7.16.

Merge NOT with DISTINCT predicate (new)

3.24.9.7.17.

Merge OR predicates (new)

3.24.9.7.18.

Merge range predicates (new)

3.24.9.7.19.

Normalise associative operations (new)

3.24.9.7.20.

Normalise fields compared to values (new)

3.24.9.7.21.

Normalise IN list with single element to comparison (new)

3.24.9.7.22.

OR to IN (new)

3.24.9.7.23.

Repeated arithmetic negation (new)

3.24.9.7.24.

Repeated bitwise negation (new)

3.24.9.7.25.

Repeated NOT (new)

3.24.9.7.26.

Trigonometric functions (new)

3.24.9.7.27.

Trim (new)

3.24.9.7.28.

Trivial case abbreviations (new)

3.24.9.7.29.

Trivial predicates (new)

The jOOQ User Manual SQL building QueryParts SQL transformation Pattern based transformation

previous : next