A transformation takes place for expressions like this:
WHERE column1 = column2 AND column2 = 'x'
For such expressions, since it is known that, if A=B and B=C then A=C (the Transitivity Law), the transformed condition becomes:
WHERE column1='x' AND column2='x'
This transformation occurs for column1
<operator> column2
conditions if and only if
<operator>
is one of these operators:
=, <, >, <=, >=, <>, <=>, LIKE
That is, transitive transformations don't apply for
BETWEEN
. Probably they should not apply for
LIKE
either, but that's a story for another
day.
Constant propagation happens in a loop, so the output from one propagation step can be input for the next step.
See:
/sql/sql_select.cc
,
change_cond_ref_to_const()
. Or
See:
/sql/sql_select.cc
,
propagate_cond_constants()
.