Monotonic Function

Streaming GROUP BY requires that at least one of the grouped expressions be monotonically increasing and non-constant. The only column known in advance to be monotonically increasing is ROWTIME. (See also Monotonic Expressions and Operators.)

The MONOTONIC function allows you to declare that a given expression is monotonically increasing, enabling a streaming GROUP BY to use that expression as a key. The MONOTONIC function evaluates its argument and returns the result (as the same type as its argument).

Note: In an s-Server context, monotonicity always means that a stream is increasing (even though “monotonic” can also refer to steadily decreasing values). Thus, to declare that an expression is “monotonic” is to declare that it is monotonically increasing.

Syntax

MONOTONIC(<expression>)

Notes

By enclosing an expression in MONOTONIC, you are asserting that values of that expression are increasing and never change direction. For example, if you have a stream LINEITEMS consisting of the line items of orders, and you wrote MONOTONIC(orderId), you are asserting that line items are consecutive in the stream. It would be OK if there were line items for order 1000, followed by line items for order 1001, followed by line items for order 1005. It would be illegal if there were then a line item for order 1001, i.e., the line item sequence became 1000, 1001, 1005, 1001. Similarly, the following line item sequences would be illegal:

  • 987, 974, 823, 973
  • 987, 974, 823, 1056

For example the strings in following sequence are not ascending, since “F” alphabetically precedes the other first letters. ‘Monday 26th April, 2010’ ‘Monday 26th April, 2010’ ‘Tuesday 27th April, 2010’ ‘Wednesday 28th April, 2010’ ‘Wednesday 28th April, 2010’ ‘Wednesday 28th April, 2010’ ‘Friday 30th April, 2010’ Note that the definition of MONOTONIC is precisely what is needed for GROUP BY to make progress.

If an expression declared monotonic is not monotonically increasing – i.e., if the assertion is not valid for the actual data – then s-Server’s behavior is unspecified.

In other words, if you are certain that an expression is monotonic, you can use this MONOTONIC function to enable SQLstream to treat the expression as monotonically increasing.

However, if you are mistaken and the values resulting from evaluating the expression change from ascending to descending or from descending to ascending, unexpected results may arise. SQLstream streaming SQL will take you at your word and operate on your assurance that the expression is monotonic. But if in fact it is not monotonic, the resulting SQLstream behavior cannot be determined in advance, and so results may not be as expected or desired.