5.11.5 Equivalent exchange for scalar operations

When one of the terms of a search condition specifies a scalar operation that contains a column specification^#1, the scalar operation is transposed. That is, the search condition undergoes equivalent exchange so as to leave only a column specification^#1. A scalar operation is transposed when all of the following conditions are met:

The operand satisfies either of the following conditions:
- The operator is an arithmetic operator (addition or subtraction) between a column specification^#1 and a literal.
- The operator is a datetime operator between a column specification^#1 and a labeled duration.
The data type^#2 of the column specification is SMALLINT, INTEGER, BIGINT, TIME, DATE, or TIMESTAMP.
The scalar operation is not nested.

#1: In addition to a column specification, an array element reference for which the array value expression is a column specification and ANY is specified also applies. In the case of an array element reference, as with a column specification, the scalar operation is transposed, and the search condition undergoes equivalent exchange so that one term of the condition consists of only a column specification.
#2: In the case of an array element reference, this is the data type of the result of the array value expression.

Important: When equivalent exchange is performed on search conditions, the indexes to be used during retrieval are determined based on the search conditions obtained after equivalent exchange has been applied.

The following shows examples of equivalent exchange. In the examples, C1 is a column name.

The following are examples of equivalent exchange in the case of a column specification. In the case of an array element reference, replace "column specification" with "array element reference".

Organization of this subsection

(1) Examples where equivalent exchange is performed
(2) Examples where equivalent exchange is not performed
(3) Rules for equivalent exchange

(1) Examples where equivalent exchange is performed

(a) Example 1

[Figure]

Explanation:: A term that includes a column specification has the scalar operation +10. By equivalent exchange, this scalar operation is moved to the right-hand side, leaving the condition with just a column specification. Because the format after equivalent exchange is column-specification comparison-operator literal, an index will be used during retrieval.

(b) Example 2

[Figure]

Explanation:: A term that includes a column specification has the scalar operation +1 DAY. By equivalent exchange, this scalar operation is moved to the right-hand side, leaving just a column specification in the condition. Because the format after equivalent exchange is column-specification comparison-operator literal, an index will be used during retrieval.

(c) Example 3

[Figure]

Explanation:: A term that includes a column specification has the scalar operation +10. By equivalent exchange, this scalar operation is moved to the right-hand side, leaving just a column specification in the condition. Because the format after equivalent exchange is column-specification BETWEEN literal, an index will be used during retrieval.

(d) Example 4

[Figure]

Explanation:: A term that includes a column specification has the scalar operation +10. By equivalent exchange, this scalar operation is moved to the right-hand side, leaving just a column specification in the condition. Because the format after equivalent exchange is column-specification IN literal, an index will be used during retrieval.

(e) Example 5

[Figure]

Explanation:: The row value constructor elements containing column specifications have scalar operations "+10" and "+20". Through equivalent exchange, these scalar operations are transposed to the corresponding row value constructor elements on the right side of the IN predicate, resulting in a condition with only column specifications. Because the format after equivalent exchange is column-specification IN literal, an index will be used during retrieval.

To Page Top

(2) Examples where equivalent exchange is not performed

(a) Example 1

[Figure]

Explanation:: Equivalent exchange does not take place because the scalar operation that includes the column specification is nested.

To Page Top

(3) Rules for equivalent exchange

In the case of a comparison predicate in the following form, the scalar operation part is transposed, and an equivalent exchange is performed on it to a condition consisting only of a column specification or an array element reference specifying ANY of a column specification in an array value expression.
```
{column-specification|column-specification[ANY[(identification-number)]]} {+|-} literal
    comparison-operator literal
```
```
literal
    comparison-operator {column-specification|column-specification[ANY[(identification-number)]]} {+|-} literal
```
Equivalent exchange does not occur if all of the following conditions are met:
- A labeled duration of YEAR or MONTH is specified in a scalar operation that includes a column specification or an array element reference specifying ANY of a column specification in an array value expression.
- The comparison operator of the comparison predicate is specified as <>, !=, or ^=.
In the case of a BETWEEN predicate in the following form, the scalar operation part is transposed, and an equivalent exchange is performed on it to a condition consisting only of a column specification or an array element reference specifying ANY of a column specification in an array value expression.
```
{column-specification|column-specification[ANY[(identification-number)]]} {+|-} literal
    [NOT] BETWEEN literal AND literal
```
Equivalent exchange does not occur if all of the following conditions are met:
- A labeled duration of YEAR or MONTH is specified in a scalar operation that includes a column specification or an array element reference specifying ANY of a column specification in an array value expression.
- NOT BETWEEN is specified in the BETWEEN predicate.
In the case of an IN predicate without a row value constructor specified in the following form, the scalar operation part is transposed, and an equivalent exchange is performed on it to a condition with only a column specification or array element reference specifying ANY of a column specification as an array value expression.
```
{column-specification|column-specification[ANY[(identification-number)]]} {+|-} literal
    [NOT] IN (literal,...)
```
In addition, equivalent exchange is not performed when YEAR or MONTH of labeled duration is specified for scalar operations containing array element references that specify ANY of column specification, or an array value expression with column specification.
For IN predicates specifying row value constructors in the following format, the scalar operation part is transposed, and equivalent exchange is performed on column specification conditions only.
```
row-value-constructor-1 [NOT] IN (row-value-constructor-2 [,row-value-constructor-2]...)
  row-value-constructor-1::=(column-specification {+|-} literal [,column-specification {+|-} literal]...)
  row-value-constructor-2::=(literal [,literal]...)
```
Note that if YEAR or MONTH of labeled duration is specified in the column specification designated in row value constructor 1 on the left side of the IN predicate, equivalent exchange is not performed.

To Page Top