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().