I get this error

Illegal mix of collations for operation <=

for the following WHERE clause:

dt1 = datetime, tim = time

Anybody knows why?

Answer1:

As explained in the manual entry for DATE_ADD():

The return value depends on the arguments:

<li>

DATETIME if the first argument is a DATETIME (or TIMESTAMP) value, or if the first argument is a DATE and the unit value uses HOURS, MINUTES, or SECONDS.

</li> <li>

String otherwise.

</li> </ul>

In your case, because table1.tim is a TIME value, the function is returning a string.

The string is returned in the character set and collation given by character_set_connection and collation_connection.

</li> <li>

As explained in the manual entry for TIME():

Extracts the time part of the time or datetime expression <strong>expr</strong> and returns it as a string.

The string is returned as a binary string (why not using the connection character set & collation is beyond me—perhaps a bug?).

</li> <li>

As explained in the manual entry for Type Conversion in Expression Evaluation:

If both arguments in a comparison operation are strings, they are compared as strings.

Therefore the comparison undertaken is a string comparison, but the binary string cannot be coerced to a suitable collation for comparison (as its encoding is not known). Hence the error you are witnessing.

</li> </ol>

One could force the comparison to be conducted correctly by explicitly casting one or both of the operands, but I'd be tempted just to rework the clause so that conversion takes place implicitly from comparison against a TIME type:

TIME(table1.dt1 - INTERVAL 1 MINUTE) <= table1.tim