Operators and Values

This chapter discusses operators, value specifications and default values in Mimer SQL. It also discusses assignments, comparisons and result data types.

Operators

Operators manipulate individual data items (operands) and return a result. Mimer SQL uses the following operators:

•Set Operators

•Arithmetical Operators

•Comparison Operators

•Logical Operators.

Set Operators

UNION or UNION ALL

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.

 

EXCEPT or EXCEPT ALL

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.

INTERSECT or INTERSECT ALL

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

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

Binary Arithmetical

String Operators

String operators are used in forming expressions, see Expressions.

String

Bit Operators

Bit operations:

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

 

Comparison Operators

Comparison operators are used tocompare 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

Quantifiers

Logical Operators

 

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.

Operator Precedence

Operators with the same precedence are evaluated from left to right.

Examples

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.

Value Specifications

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 value-specification may be replaced by the following construction:

 

 

Standard Compliance

This section summarizes standard compliance for value specifications.

Default Values

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 assignment-compatible with the data type of the context to which it will be applied.

In the syntax diagrams, the term default-value may be replaced by the following construction:

 

 

For more information about what can be specified for literal, see Literals.

Standard Compliance

This section summarizes standard compliance concerning the specification of default values.

Assignments

The following sections explain the rules that apply when values are assigned in SQL statements to database columns or to host variables.

String Assignments

If a string value assigned to a fixed-length or variable-length 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 fixed-length 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 variable-length 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 fixed-length 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:

Numerical Assignments

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:

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.

Datetime Assignment Rules

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 cross-assign.

Interval Assignment Rules

The following compatibility rules apply when assigning INTERVAL values to one another:

•When assigning a non-null value to an INTERVAL column, the leading precision of the target must be sufficient to represent the value.

•All YEAR-MONTH INTERVAL values are compatible with one another.

•All DAY-TIME INTERVAL values are compatible with one another.

Binary Assignment Rules

A binary value assigned to a fixed-length 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 variable-length 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 variable-length 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 fixed-length 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:

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.

Comparisons

Values to be compared must be of compatible data types. If values with incompatible data types are compared, an error occurs.

Character String Comparisons

Both fixed-length and variable-length 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 variable-length column with the value 'town ', one trailing blank, is equal to the variable-length 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 column-definition 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 type-of-B);

Q = (A = CAST(A' AS type-of-A));

 

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 assignment-compatible, 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 assignment-compatible, as defined in Interval Assignment Rules.

INTERVAL comparisons are performed in accordance with their sign and magnitude.

It is not possible to compare YEAR-MONTH intervals with DAY-TIME 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 re-written 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.:

boolean-primary IS TRUE

boolean-primary IS FALSE

boolean-primary IS UNKNOWN

boolean-primary IS NOT TRUE

boolean-primary IS NOT FALSE

boolean-primary 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.

Truth Tables

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

 

 

AND

OR

IS

 

Standard Compliance

This section summarizes standard compliance concerning comparisons.

Result Data Types

This section describes the syntax rules for, and the resulting data types of, UNION, INTERSECT and EXCEPT operations specified in a query-expression (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 variable-length (national) character string, then the data type of the result will be variable-length (national) character with maximum length equal to the largest of the specified expressions.

•If all specified expressions are fixed-length (national) character strings, then the data type of the result will be a fixed-length (national) character string with a length equal to the maximum length of the largest of the specified fixed-length 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 variable-length binary, then the data type of the result will be variable-length binary with maximum length equal to the maximum length of the largest of the specified expressions.

•If all specified expressions are fixed-length binary, then the data type of the result will be fixed-length 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:

•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