Arithmetic expressions
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Some arithmetic expressions are unnecessary and may obscure the SQL query, possibly preventing optimisations that are otherwise possible. Sometimes, this is used as a trick, e.g. to prevent index access where such index access is undersirable
Using Settings.transformPatternsArithmeticExpressions, the following transformations can be achieved:
Identities
-- With Settings.transformPatternsArithmeticExpressions active, this: SELECT 0 + x, 1 * x, POWER(x, 1) FROM tab; -- ... is transformed into the equivalent expression: SELECT x, x, x FROM tab;
-- With Settings.transformPatternsArithmeticExpressions active, this: SELECT 0 - x, x + (-y), x - (-y), (-x) * (-y), (-x) / (-y), (-1) * x, -(x - y) FROM tab; -- ... is transformed into the equivalent expression: SELECT -x, -- 0 - x x - y, -- x + (-y) x + y, -- x - (-y) x * y, -- (-x) * (-y) x / y, -- (-x) / (-y) -x -- (-1) * x (y - x) -- -(x - y) FROM tab;
-- With Settings.transformPatternsArithmeticExpressions active, this: SELECT 1 / y * x, x * x, x + const, x * const FROM tab; -- ... is transformed into the equivalent expression: SELECT x / y, -- 1 / y * x SQUARE(x), -- x * x const + x, -- x + const const * x -- x * const FROM tab;
Feedback
Do you have any feedback about this page? We'd love to hear it!