Bug #90896 INSERT...SELECT silently truncates LONGTEXT to TEXT
Submitted: 16 May 2018 21:17 Modified: 24 May 2018 18:51
Reporter: Kelly Price Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Errors Severity:S3 (Non-critical)
Version:5.5/5.6/5.7/8.0 OS:Ubuntu (4.13.0-41-generic)
Assigned to: CPU Architecture:x86 (Intel(R) Core(TM) i7-7700HQ CPU @ 2.80GHz)

[16 May 2018 21:17] Kelly Price
Description:
With sql_mode set to STRICT_ALL_TABLES, using INSERT...SELECT to insert a LONGTEXT value into a TEXT column silently truncates the value.

How to repeat:
DROP TABLE IF EXISTS long_texts;
DROP TABLE IF EXISTS texts;
DROP TABLE IF EXISTS tiny_texts;

SHOW VARIABLES WHERE Variable_name = 'sql_mode';

CREATE TABLE long_texts (
  id INT(11) NOT NULL AUTO_INCREMENT,
  long_text_field LONGTEXT,
  PRIMARY KEY (id)
);
CREATE TABLE texts (
  id INT(11) NOT NULL AUTO_INCREMENT,
  text_field TEXT,
  PRIMARY KEY (id)
);
CREATE TABLE tiny_texts (
  id INT(11) NOT NULL AUTO_INCREMENT,
  tiny_text_field TINYTEXT,
  PRIMARY KEY (id)
);

INSERT INTO long_texts (long_text_field)
  VALUES
    (REPLACE(SPACE(10), ' ', '.')),
    (REPLACE(SPACE(100000), ' ', '.'));

SELECT LENGTH(long_text_field), SUBSTR(long_text_field, -10) FROM long_texts;

INSERT INTO texts (text_field)
  SELECT (long_text_field) FROM long_texts;

SELECT LENGTH(text_field), SUBSTR(text_field, -10) FROM texts;

INSERT INTO tiny_texts (tiny_text_field)
  SELECT (long_text_field) FROM long_texts;

SELECT LENGTH(tiny_text_field), SUBSTR(tiny_text_field, -10) FROM tiny_texts;
[24 May 2018 18:51] MySQL Verification Team
Thank you for the bug report. Verified as described on 5.5+ versions.