After the patch for
MDEV-12325, queries with CTE report truncation as follows:
- With sql_mode=DEFAULT an error is returned and the query aborts
- With sql_mode='' only warnings are reported, and the query continues
However, queries with simple UNION do not respect sql_mode. These two scripts:
silently truncate the data on the third row (which has the MEDIUMTEXT data type), and return a result set, with neither errors nor warnings.
This behavior has two problems:
- It looks inconsistent - CTE and simple UNION work differently.
- Silent truncation in case of simple UNION does not look nice.
Note, the last two scripts may need settings:
to avoid max-allowed-packet failures.
Simple UNION could probably be fixed in the same way with CTE to:
- raise an error in case of strict mode
- issue warnings in case of loose mode
Another option is to raise the error for all sql_mode values in both CTE and simple UNION, as there are no real tables involved in these statements. There are only temporary tables involved internally.
In any cases, silent truncation should not happen for sure.