Operator Precedence
The SQL-92 standard is imprecise with regard to operator precedence; assumptions on this matter differ amongst SQL implementations. InterSystems SQL can be configured to support either type of precedence:
-
At InterSystems IRIS 2019.1 and subsequent, InterSystems SQL supports ANSI precedence of arithmetic operators by default. This is a system-wide configuration setting. When ANSI precedence is configured, the "*", "\", "/", and "#" operators have a higher precedence than the "+", "-", and "||" operators. Operators with a higher precedence are executed before operators with a lower precedence. Thus, 3+3*5 equals 18. You can use parentheses to override precedence when desired. Thus, (3+3)*5 equals 30.
Default ANSI precedence is supported for a clean install of InterSystems IRIS 2019.1; when you upgrade InterSystems IRIS 2018.1 to InterSystems IRIS 2019.1, the operator precedence remains configured to the InterSystems IRIS 2018.1 default: strict left-to-right order.
-
At InterSystems IRIS 2018.1, InterSystems SQL does not provide precedence of arithmetic operators by default. By default, InterSystems SQL executes arithmetic expressions in strict left-to-right order, with no operator precedence. This is the same convention used in ObjectScript. Thus, 3+3*5 equals 30. You can use parentheses to enforce the desired precedence. Thus, 3+(3*5) equals 18. Careful developers should use parentheses to explicitly state their intentions.
You can configure either type of SQL operator precedence system-wide using the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method, as follows: SET status=$SYSTEM.SQL.Util.SetOption("ANSIPrecedence",1,.oldval) sets ANSI precedence; SET status=$SYSTEM.SQL.Util.SetOption("ANSIPrecedence",0,.oldval) sets strict left-to-right evaluation. To determine the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab which displays the Apply ANSI operator precedence option. Changing this SQL option takes effect immediately system-wide. Changing this option causes all cached queries to be purged system-wide.
Changing SQL precedence has no effect on ObjectScript. ObjectScript always follows strict left-to-right execution of arithmetic operators.
