This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
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.26.9.9.1.
- AND to NOT IN
- 3.26.9.9.2.
- Arithmetic comparisons
- 3.26.9.9.3.
- Arithmetic expressions
- 3.26.9.9.4.
- BIT_GET function
- 3.26.9.9.5.
- BIT_SET function
- 3.26.9.9.6.
- CASE searched to CASE simple
- 3.26.9.9.7.
- CASE to CASE abbreviation
- 3.26.9.9.8.
- CASE with DISTINCT FROM to DECODE
- 3.26.9.9.9.
- CASE with ELSE NULL
- 3.26.9.9.10.
- COUNT(*) scalar subquery comparison
- 3.26.9.9.11.
- COUNT(const)
- 3.26.9.9.12.
- COUNT(expr) scalar subquery comparison
- 3.26.9.9.13.
- DISTINCT FROM NULL
- 3.26.9.9.14.
- Empty scalar subquery
- 3.26.9.9.15.
- Flatten CASE
- 3.26.9.9.16.
- Flatten CASE abbreviations
- 3.26.9.9.17.
- Flatten DECODE
- 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
- 3.26.9.9.27.
- Merge CASE .. WHEN clauses
- 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
- 3.26.9.9.37.
- NOT OR
- 3.26.9.9.38.
- NULL ON NULL INPUT
- 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
- 3.26.9.9.44.
- Trigonometric functions
- 3.26.9.9.45.
- Trim
- 3.26.9.9.46.
- Trivial bitwise operations
- 3.26.9.9.47.
- Trivial CASE abbreviations
- 3.26.9.9.48.
- Trivial predicates
- 3.26.9.9.49.
- Unnecessary DISTINCT
- 3.26.9.9.50.
- Unnecessary EXISTS subquery clauses
- 3.26.9.9.51.
- Unnecessary GROUP BY expressions
- 3.26.9.9.52.
- Unnecessary INNER JOIN
- 3.26.9.9.53.
- Unnecessary ORDER BY expressions
- 3.26.9.9.54.
- Unnecessary scalar subquery
- 3.26.9.9.55.
- Unreachable CASE clauses
- 3.26.9.9.56.
- Unreachable DECODE clauses
| previous : next |
Feedback
Do you have any feedback about this page? We'd love to hear it!