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;
Feedback
Do you have any feedback about this page? We'd love to hear it!