CTE respects sql_mode

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

SET sql_mode=DEFAULT;

WITH RECURSIVE temp (c1, c2) AS 

(SELECT 1, 'init'

  UNION ALL 

 SELECT c1+1, REPEAT('a',c1) FROM temp WHERE c1 < 7)

SELECT * FROM temp;

ERROR 1406 (22001): Data too long for column 'c2' at row 6

SET sql_mode='';

WITH RECURSIVE temp (c1, c2) AS 

(SELECT 1, 'init'

  UNION ALL 

 SELECT c1+1, REPEAT('a',c1) FROM temp WHERE c1 < 7)

SELECT * FROM temp;

+------+------+

| c1   | c2   |

+------+------+

|    1 | init |

|    2 | a    |

|    3 | aa   |

|    4 | aaa  |

|    5 | aaaa |

|    6 | aaaa | <-- truncation

|    7 | aaaa | <-- truncation

+------+------+

7 rows in set, 2 warnings (0.003 sec)

UNION does not respect sql_mode

However, queries with simple UNION do not respect sql_mode. These two scripts:

SET sql_mode=DEFAULT;

SELECT 'a' UNION ALL SELECT REPEAT('b',seq) FROM seq_16777214_to_16777216;

SET sql_mode='';

SELECT 'a' UNION ALL SELECT REPEAT('b',seq) FROM seq_16777214_to_16777216;

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:

1. It looks inconsistent - CTE and simple UNION work differently.
2. Silent truncation in case of simple UNION does not look nice.

Note, the last two scripts may need settings:

[client]

max-allowed-packet=100M

[mysqld]

max-allowed-packet=100M

to avoid max-allowed-packet failures.

Possible desired behavior

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.