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!

Idempotent function repetition

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

When SQL is complex or generated, there may be accidental repetitions of functions that do not have any effects on the result. Such repetitions can be removed by a single function application.

Using Settings.transformPatternsIdempotentFunctionRepetition, the following transformations can be achieved:

-- With Settings.transformPatternsIdempotentFunctionRepetition active, this:
SELECT
  LTRIM(LTRIM(x)),
  RTRIM(RTRIM(x)),
  TRIM(TRIM(x)),
  TRIM(LTRIM(x)),
  TRIM(RTRIM(x)),
  RTRIM(TRIM(x)),
  LTRIM(TRIM(x)),
  UPPER(UPPER(x)),
  LOWER(LOWER(x)),
  ABS(ABS(x)),
  SIGN(SIGN(x)),
  CEIL(CEIL(x)),
  FLOOR(FLOOR(x)),
  ROUND(ROUND(x)),
  TRUNC(TRUNC(x)),
  CAST(CAST(x AS type) AS type)
FROM tab;

-- ... is transformed into the equivalent expression:
SELECT
  LTRIM(x),       -- LTRIM(LTRIM(x))
  RTRIM(x),       -- RTRIM(RTRIM(x))
  TRIM(x),        -- TRIM(TRIM(x))
  TRIM(x),        -- TRIM(LTRIM(x))
  TRIM(x),        -- TRIM(RTRIM(x))
  TRIM(x),        -- RTRIM(TRIM(x))
  TRIM(x),        -- LTRIM(TRIM(x))
  UPPER(x),       -- UPPER(UPPER(x))
  LOWER(x),       -- LOWER(LOWER(x))
  ABS(x),         -- ABS(ABS(x))
  SIGN(x),        -- SIGN(SIGN(x))
  CEIL(x),        -- CEIL(CEIL(x))
  FLOOR(x),       -- FLOOR(FLOOR(x))
  ROUND(x),       -- ROUND(ROUND(x))
  TRUNC(x),       -- TRUNC(TRUNC(x))
  CAST(x AS type) -- CAST(CAST(x AS type) AS type)
FROM tab;

References to this page

Feedback

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

The jOOQ Logo