MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0

8.2.1.14 Constant-Folding Optimization

Comparisons between constants and column values in which the constant value is out of range or of the wrong type with respect to the column type are now handled once during query optimization rather row-by-row than during execution. The comparisons that can be treated in this manner are >, >=, <, <=, <>/!=, =, and <=>.

Consider the table created by the following statement:

CREATE TABLE t (c TINYINT UNSIGNED NOT NULL);

The WHERE condition in the query SELECT * FROM t WHERE c < 256 contains the integral constant 256 which is out of range for a TINYINT UNSIGNED column. Previously, this was handled by treating both operands as the larger type, but now, since any allowed value for c is less than the constant, the WHERE expression can instead be folded as WHERE 1, so that the query is rewritten as SELECT * FROM t WHERE 1.

This makes it possible for the optimizer to remove the WHERE expression altogether. If the column c were nullable (that is, defined only as TINYINT UNSIGNED) the query would be rewritten like this:

SELECT * FROM t WHERE ti IS NOT NULL

Folding is performed for constants compared to supported MySQL column types as follows:

Limitations.  This optimization cannot be used in the following cases:

  1. With comparisons using BETWEEN or IN.

  2. With BIT columns or columns using date or time types.

  3. During the preparation phase for a prepared statement, although it can be applied during the optimization phase when the prepared statement is actually executed. This due to the fact that, during statement preparation, the value of the constant is not yet known.