Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17

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!

The jOOQ Logo