This chapter discusses operators, value specifications and default values in Mimer SQL. It also discusses assignments, comparisons and result data types.
Operators manipulate individual data items (operands) and return a result. Mimer SQL uses the following operators:
Derives a final result set by combining two other result sets.
If you specify UNION ALL, the result consists of all rows in both results sets.
If you only specify UNION, the final result set is the set of all rows in both of the result sets, with duplicate rows removed.
See The UNION Operator for more information.
The except operator is used to combine two result sets to one where the combined result set is all records from the first result which is not present in the second result set. If except is specified without the ALL quantifier, duplicates are removed from the combined result set. If ALL is specified, duplicates are not removed.
See The EXCEPT Operator for more information.
The intersect operator is used to combine two result sets to one where the combined result set is the records that are present in both result sets. If intersect is specified without the ALL quantifier, duplicates are removed from the combined result set. If ALL is specified, duplicates are not removed.
See The INTERSECT Operator for more information.
Arithmetical operators are used in forming expressions, see Expressions.
The operators are:
•unary arithmetical (i.e. one argument operators)
•binary arithmetical (i.e. two argument operators)
Unary Arithmetical
+ 
leaves operand unchanged 
 
changes sign of operand 
Binary Arithmetical
+ 
addition 
 
subtraction 
* 
multiplication 
/ 
division 
% 
modulo 
String Operators
String operators are used in forming expressions, see Expressions.
String
 
concatenation 
Bit Operators
& 
bitwise AND operation 
 
bitwise OR operation 
^ 
bitwise XOR operation 
~ 
bitwise complement 
<< 
left shift 
>> 
right shift 
Bit operations are supported on integer data types, i.e. the operations are performed on the integer bit representation. When right shift is performed a so called arithmetic shift is performed. This means that the sign bit will be used to replace the bits as the shift to the right is made.
Examples
Expression 
Result 

set ? = 9  3 
11 
set ? = cast(x'0f23' as int) & 
3872 
set ? = cast(cast(x'0f23' as int) & cast(x'fff0' as int) as binary(4)) 
X'00000F20' 
select cast(~(1 << 7) as binary(8)) 
X'FFFFFFFFFFFFFF7F' 
Comparison operators are used to compare operands in basic and quantified predicates. (Relational operators are used to compare operands in all other predicates, see Predicates.)
Both comparison and relational operators perform essentially similar functions. However, comparison operators are common to most programming languages, while the relational operators are more or less specific to SQL.
Comparison Operators
Comparison operator 
Explanation 

= 
equal to 
<> 
not equal to 
< 
less than 
<= 
less than or equal to 
> 
greater than 
>= 
greater than or equal to 
Quantifiers
Quantifier 

ALL 
SOME 
ANY 
Logical operator 

AND 
OR 
NOT 
The operators AND and OR are used to combine several predicates to form search conditions, see Search Conditions.
The operator NOT may be used to reverse the truth value of a predicate in forming search conditions. This operator is also available in predicate constructions to reverse the function of a relational operator, see Search Conditions.
Category 
SQL 

Postfix 
(, ) 
Unary, complement 
+, , ~ 
Multiplicative 
*, /, % 
Additive, concatenation 
+, ,  
Shift 
<<, >> 
Bitwise AND 
& 
Bitwise OR 
 
Comparison operators 
=, <>, <, <=, >, >=, IS, IS DISTINCT FROM 
Negation 
NOT 
Boolean AND 
AND 
Boolean OR 
OR 
Assignment 
= 
Operators with the same precedence are evaluated from left to right.
Examples
Expression 
Parenthesized execution order 

not false = true 
not (false = true) 
a  b & c  d = 110 
(a  (b & c)  d) = 110 
~10 + 5 * 8 
(~10) + (5 * 8) 
1 / 2 * 10 
(1 / 2) * 10 
1 + 3 * 15 % 2 * 4 
1 + ((3 * 15) % 2) * 4 
Please note that the precedence of operators vary between different database vendors. Use parenthesis to make sure the operations are executed as intended.
Standard Compliance
This section summarizes standard compliance concerning operators.
Standard 
Compliance 
Comments 

SQL2016 
Core 
Fully compliant. 

Mimer SQL extension 
Support for % modulo operator is a Mimer SQL extension. Support for bit operators is a Mimer SQL extension. 
Specifying fixed values in expressions.
Value specifications are values which are fixed within the context of one SQL statement. Value specifications are different to values derived from column contents, which can change as different rows or sets of rows are addressed.
The value specifications which may be used in expressions are:
•literals, see Literals
•parameter markers and host variables, see Parameter Markers and Host Identifiers, and Mimer SQL Programmer's Manual, Using Host Variables respectively.
•the keyword CURRENT_USER, SESSION_USER or USER, representing the name of a current ident (a national character varying string with maximum length 128). See SESSION_USER and CURRENT_USER respectively.
In the syntax diagrams, the term valuespecification may be replaced by the following construction:
Standard Compliance
This section summarizes standard compliance for value specifications.
Standard 
Compliance 
Comments 

SQL2016 
Core 
Fully compliant. 
SQL2016 
Feature outside core 
Feature F561, “Full value expressions”. 
There are various places in the Mimer SQL syntax where a default value can be specified. The value resulting from a default value specification must always be assignmentcompatible with the data type of the context to which it will be applied.
In the syntax diagrams, the term defaultvalue may be replaced by the following construction:
For more information about what can be specified for literal, see Literals.
This section summarizes standard compliance concerning the specification of default values.
Standard 
Compliance 
Comments 

SQL2016 
Core 
Fully compliant. 
SQL2016 
Features outside core 
Feature F555, “Enhanced seconds precision”. Localtime and Localtimestamp functions with fractions of seconds. Feature T176, “Sequence generator support” 

Mimer SQL extension 
Current value for sequences is a Mimer SQL extension. 
The following sections explain the rules that apply when values are assigned in SQL statements to database columns or to host variables.
If a string value assigned to a fixedlength or variablelength character column is longer than the defined length of the column (except for trailing spaces), the assignment will fail and an error is returned.
If a string value assigned to a fixedlength character column is shorter than the defined length of the column, the content of the column is padded to the right with blanks after the assignment.
If a string value assigned to a variablelength character column is shorter than the defined maximum length of the column, no blank padding occurs.
Character (both fixed length and variable length) column values assigned to fixedlength host variables in SQL statements are padded with blanks to the right if necessary. Column values assigned to host variables are truncated if they are longer than the declared length of the variable, and a warning is issued.
The following table summarizes the rules for character string assignment:
Assignment 
Source too long 
Source too short 

To column 
Error if nonblank character would be truncated. 
Pad right with blanks for fixedlength columns. No blank padding for variable length columns. 
To variable 
Truncate and warn. 
Pad right with blanks for fixedlength variables. No blank padding for variable length variables. 
Numbers assigned to columns or host variables assume the data type of the item to which they are assigned, regardless of the data type of the source.
Integral parts of INTEGER, DECIMAL or FLOAT values are never truncated. Fractional parts of DECIMAL and FLOAT numbers may be truncated if required. No precision is lost when converting INTEGER values to DECIMAL, but this may happen when converting INTEGER values to FLOAT.
When DECIMAL or FLOAT values are converted to INTEGER, the fractional part of the number is truncated (not rounded). Note that the range of numbers represented by DECIMAL and INTEGER is smaller than the range represented by FLOAT. Assignment of a FLOAT number to an INTEGER or DECIMAL produces an overflow error if the source number is too large.
In assigning DECIMAL values to DECIMAL targets, the length of the integer part of the source (i.e. the difference between the precision and scale) may not exceed the precision of the target. The necessary number of leading zeros is appended or eliminated, and trailing zeros are added to or digits truncated from the fractional part as required.
Note:Truncation effects can be avoided by explicitly using the ROUND function, see ROUND.
In converting DECIMAL values to FLOAT, the mantissa of the target is treated as a decimal number with the same precision as the source (for example, 1234.56 becomes 1.23456E3).
In converting FLOAT values to DECIMAL, digits are truncated from the fractional part of the result as required by the scale of the target. An overflow error occurs if the precision of the target cannot accommodate the integral part of the result.
When converting INTEGER, DECIMAL or FLOAT numbers to REAL or DOUBLE PRECISION, a rounding operation is often required. The number will be rounded to the nearest binary floating point representation (rounding to even if there is a tie). Note that such rounding is necessary for simple decimal numbers such as 0.1 which cannot be represented exactly as a binary floating point number.
When converting a REAL or DOUBLE PRECISION number to INTEGER, DECIMAL or FLOAT, the value will be rounded to the nearest number that is possible to represent in the target.
The following table illustrates the main features of numerical assignments:
Source: 
Target: INTEGER 
SMALLINT 
DECIMAL(9,2) 
FLOAT(8) 
REAL 

INTEGER(6): 





DECIMAL(6,3): 





FLOAT(6): 





FLOAT(6): 





FLOAT(6): 





REAL: 





Leading zeros are shown where appropriate to indicate the maximum number of digits available. Leading zeros in numerical data are not normally displayed on output.
The following compatibility rules apply when assigning DATETIME values to one another:
•If the value to be assigned is a DATE, the target must also be a DATE.
•If the value to be assigned is a TIME, the target must also be a TIME.
•If the value to be assigned is a TIMESTAMP, the target must also be a TIMESTAMP.
•The CAST function can be used in order to crossassign.
The following compatibility rules apply when assigning INTERVAL values to one another:
•When assigning a nonnull value to an INTERVAL column, the leading precision of the target must be sufficient to represent the value.
•All YEARMONTH INTERVAL values are compatible with one another.
•All DAYTIME INTERVAL values are compatible with one another.
Binary Assignment Rules
A binary value assigned to a fixedlength binary column must have the same length as the defined length of the column, otherwise the assignment will fail and an error is returned.
If a binary value assigned to a variablelength binary column is shorter than the defined maximum length of the column, current length is set for the column.
If a binary value assigned to a variablelength binary column is longer than the defined maximum length of the column, the assignment will fail and an error is returned.
Binary (both fixed length and variable length) column values assigned to fixedlength host variables in SQL statements are padded with null values to the right if necessary. Column values assigned to host variables are truncated if they are longer than the declared length of the variable, and a warning is issued.
The following table summarizes the rules for binary string assignment:
Assignment 
Source too long 
Source too short 

To column 
Error. 
Error for fixedlength columns. Current length set for variable length columns. 
To variable 
Truncate and warn. 
Pad right with null values for fixedlength variables. No null value padding for variable length variables. 
Boolean Assignment Rules
The BOOLEAN type can be assigned boolean values, i.e. TRUE and FALSE.
Note:Do not enclose boolean literals in string delimiters. 'TRUE' is a string literal, not a boolean literal.
Standard Compliance
This section summarizes standard compliance concerning assignments.
Standard 
Compliance 
Comments 

SQL2016 
Core 
Fully compliant. 
Values to be compared must be of compatible data types. If values with incompatible data types are compared, an error occurs.
Both fixedlength and variablelength character strings are compared character by character from left to right.
If the strings are of different length, the shorter string is conceptually padded to the right with blanks before the comparison is made, that is, character differences take precedence over length differences.
For example, the variablelength column with the value 'town ', one trailing blank, is equal to the variablelength column with the value 'town ', two trailing blanks.
When comparing a character string to a national character string, the character string is implicitly converted to a national character string, before the comparison is performed.
Collations
A collation determines whether a character string is less than, equal to, or greater than another when sorting or comparing data.
SQL only permits compatible character strings to be compared. That is, you can compare character strings only if the source and target strings belong to the same collation or are coerced into having the same collation.
A character string that is defined with a named collation can only be compared to a character string that is either defined with the same named collation or is defined without a collation.
In the case where one of the strings is not associated with a named collation then it will be implicitly coerced to the same collation as the other string.
A collation specified in the columndefinition will take precedence over a domain collation.
For more information on character sets, see Character Sets.
For more information on collations, see Mimer SQL User's Manual, Collations.
Numerical Comparisons
INTEGER, DECIMAL and FLOAT values are always compared according to their algebraic values.
INTEGER values compared with DECIMAL or FLOAT values are treated as DECIMAL or FLOAT respectively. When DECIMAL values are compared with DECIMAL, the lower precision value is conceptually padded with leading and trailing zeros as necessary. DECIMAL values compared with FLOAT values are treated as FLOAT.
Thus all the following comparisons evaluate to TRUE:
1 = 1.0
2 < 2.3E0
35.3 = 035.300
35.3 > 3.5E1
A REAL, DOUBLE PRECISION or FLOAT value (A) compared with an INTEGER, DECIMAL or FLOAT value (B) is handled in the following manner:
A’ = CAST(A AS typeofB);
Q = (A = CAST(A' AS typeofA));
If Q is TRUE then A’ is a close approximation of A in he type of B. Comparisons are made between A’ and B in the type B.
If Q is FALSE then A’ is NOT a close approximation of A in the type of B. A is considered to be unequal to any value in type B. However A’ and B can be compared for magnitude (> or <).
Thus all the following comparisons evaluate to TRUE:
CAST(1 AS REAL) = 1
CAST(1.1 AS REAL) <> 1
CAST(1.1 AS REAL) = 1.1
CAST(1.1 AS REAL) <> 1.10000000
CAST(1.1 AS REAL) = 1.10000002
Datetime and Interval Comparisons
Two DATETIME values may be compared if they are assignmentcompatible, as defined in Datetime Assignment Rules.
DATETIME comparisons are performed in accordance with chronological ordering.
When two TIME or two TIMESTAMP values are compared, the seconds precision of the value with the lowest seconds precision is extended by adding trailing zeros.
Two INTERVAL values may be compared if they are assignmentcompatible, as defined in Interval Assignment Rules.
INTERVAL comparisons are performed in accordance with their sign and magnitude.
It is not possible to compare YEARMONTH intervals with DAYTIME intervals.
Comparable INTERVAL types with different interval precisions are conceptually converted to the same interval precision, prior to any comparison, by adding fields as required.
Binary Comparisons
Binary values are compared bytewise. If the two binary values have different lengths they are not equal.
Boolean Comparisons
Boolean values are compared to TRUE or FALSE. When comparing truth values FALSE is less than TRUE.
When equals true is to be evaluated it is unnecessary to write the = TRUE part. I.e.
WHERE boolcol = TRUE
is typically written as
WHERE boolcol
Similarly, = FALSE is typically rewritten using NOT. I.e.
WHERE boolcol = FALSE
is usually expressed as
WHERE NOT boolcol
The BOOLEAN TEST syntax is supported for truth value tests, i.e.:
booleanprimary IS TRUE
booleanprimary IS FALSE
booleanprimary IS UNKNOWN
booleanprimary IS NOT TRUE
booleanprimary IS NOT FALSE
booleanprimary IS NOT UNKNOWN
Null Comparisons
All comparisons involving a null value on either side of the comparison operator evaluate to unknown. Null is never equal to, greater than or less than anything else.
SQL provides a special NULL predicate to test for the presence or absence of null value in a column, see The NULL Predicate.
The DISTINCT predicate provides a comparison mechanism that treats two null values as the same, see The DISTINCT Predicate.
Considerable care is required in writing search conditions involving columns which may contain null values. It is often very easy to overlook the effect of null comparisons, with the result that rows which should be included in the result table are omitted or vice versa. See the Mimer SQL User's Manual, Handling Null Values, for further discussion of this point.
The following truth tables summarize the outcome of conditional expressions where comparisons are negated by NOT or joined by AND or OR.
A question mark (?) represents the truth value unknown, T represents the value TRUE and F represents the value FALSE.
NOT
NOT 


T 
F 
F 
T 
? 
? 
AND
AND 
T 
F 
? 

T 
T 
F 
? 
F 
F 
F 
F 
? 
? 
F 
? 
OR
OR 
T 
F 
? 

T 
T 
T 
T 
F 
T 
F 
? 
? 
T 
? 
? 
IS
IS 
T 
F 
? 

T 
T 
F 
F 
F 
F 
T 
F 
? 
F 
F 
T 
Standard Compliance
This section summarizes standard compliance concerning comparisons.
Standard 
Compliance 
Comments 

SQL2016 
Core 
Fully compliant. 
SQL2016 
Features outside core 
Feature F571, “Truth value tests” Feature T031, “BOOLEAN data type” 
This section describes the syntax rules for, and the resulting data types of, UNION, INTERSECT and EXCEPT operations specified in a queryexpression (see SELECT) and CASE expressions, see CASE Expression.
•The data type of all specified expressions must be comparable.
•If any of the specified expressions is a variablelength (national) character string, then the data type of the result will be variablelength (national) character with maximum length equal to the largest of the specified expressions.
•If all specified expressions are fixedlength (national) character strings, then the data type of the result will be a fixedlength (national) character string with a length equal to the maximum length of the largest of the specified fixedlength character string values.
•If all specified expressions are boolean, then the data type of the result will be boolean.
•If any of the specified expressions is variablelength binary, then the data type of the result will be variablelength binary with maximum length equal to the maximum length of the largest of the specified expressions.
•If all specified expressions are fixedlength binary, then the data type of the result will be fixedlength binary with the same length.
•If all specified expressions are exact numeric, then the data type of the result will be exact numeric with precision and scale equal to the maximum precision and scale of the specified expressions.
•If any of the specified expressions is approximate numeric, then the data type of the result will be approximate numeric with precision equal to the maximum precision of the specified expressions.
•If two numeric data types are specified, the precision and scale of the result is determined by the rules in the table below and which are described in the points that follow:

FLOAT(p") 
INTEGER(p") 
DECIMAL(p",s") 
FLOAT(p') 
FLOAT(p) ^{a} 
FLOAT(p) ^{a} 
FLOAT(p) ^{a} 
INTEGER(p') 
FLOAT(p) ^{a} 
INTEGER(p) ^{a} 
DECIMAL(p,s) ^{b} 
DECIMAL(p',s') 
FLOAT(p) ^{a} 
DECIMAL(p,s) ^{b} 
DECIMAL(p,s) ^{b} 
•If either of the specified expressions is floating point, the result is floating point. The precision of the result is the highest operand precision.
Thus:
DOUBLE PRECISION UNION REAL gives DOUBLE PRECISION
DOUBLE PRECISION UNION INTEGER gives DOUBLE PRECISION
REAL UNION SMALLINT gives REAL.
•If both the specified expressions are integer, the result is integer. The precision of the result is the highest operand precision.
Thus:
INTEGER UNION SMALLINT gives INTEGER
INTEGER UNION BIGINT gives BIGINT.
•If both the specified expressions are decimal, or one is decimal and the other is integer, the result is decimal. For expressions mixing decimal and integer operands, INTEGER(p) is treated as DECIMAL(p,0).
The number of positions to the left of the decimal point (i.e. the difference between precision and scale) in the result is the greatest number of positions in either operand. The scale of the result is the greatest scale of the operands. The precision may not exceed 45.
Thus:
SMALLINT UNION DECIMAL(10,4) gives DECIMAL(10,4)
INTEGER UNION DECIMAL(10,4) gives DECIMAL(14,4)
DECIMAL(9,2) UNION DECIMAL(6,4) gives DECIMAL(9,4).
•For INTERVAL operands, see Interval, the interval precision of the result is the combined interval precision of the two operands, the scale (seconds precision) is the greatest of the two operands and the leading precision of the result is the greatest of the two operands, expressed in terms of the most significant field of the result.
Thus:
DAY TO HOUR UNION MINUTE TO SECOND gives DAY TO SECOND
HOUR TO SECOND(2,2) UNION MINUTE TO SECOND(1,6)gives HOUR TO SECOND(2,6)
DAY(2) TO HOUR UNION HOUR(6) TO MINUTE gives DAY(5) TO MINUTE.
Standard Compliance
Standard 
Compliance 
Comments 

SQL2016 
Core 
Fully compliant. 