MySQL Bugs: #93451: The table comment is cut down on selecting with ORDER BY

Bug #93451	The table comment is cut down on selecting with ORDER BY
Submitted:	3 Dec 2018 14:21	Modified:	10 Jan 2019 16:53
Reporter:	dbForge Team	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S2 (Serious)
Version:	8.0.13	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
The table comment is cut down on selecting with ORDER BY clause from information_schema.Tables

How to repeat:
CREATE DATABASE testdb
CHARACTER SET latin1
COLLATE latin1_swedish_ci;

CREATE TABLE testdb.commentLengthTestTable (
  id int(11) DEFAULT NULL
)
ENGINE = INNODB,
CHARACTER SET latin1,
COLLATE latin1_swedish_ci,
COMMENT = 'aaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeefffffffffffffffffffffffffffffffffffffffffffgggggggggggggggggggggggggggggggggghhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh THE END';
(316 chars here)

SELECT t.TABLE_COMMENT 
from information_schema.TABLES t
WHERE t.TABLE_SCHEMA = 'testdb' 
AND t.TABLE_NAME = 'commentLengthTestTable';

SELECT t.TABLE_COMMENT 
from information_schema.TABLES t
WHERE t.TABLE_SCHEMA = 'testdb' 
AND t.TABLE_NAME = 'commentLengthTestTable'
ORDER BY t.TABLE_SCHEMA;

Expected:
A string of 316 chars returned
(A comment for the table, up to 2048 characters long - https://dev.mysql.com/doc/refman/8.0/en/create-table.html)

Actual:
Query without the ORDER BY clause returned a string of 316 chars 
Query with the ORDER BY clause returned a string of 256 chars

Suggested fix:
ORDER BY should not affect the data returned by the query

Hello Devart,

Thank you for the report and test case.
I'm not seeing any discrepancies in the result with/without ORDER BY on MySQL Server 5.7.24/8.0.13. Please could you provide results from your environment, any missing details from the report? Thank you.

- 5.7.24/8.0.13(OL7, Win7)

mysql> drop database testdb;
Query OK, 1 row affected (0.63 sec)

mysql> CREATE DATABASE testdb CHARACTER SET latin1 COLLATE latin1_swedish_ci;
Query OK, 1 row affected (0.20 sec)

mysql> CREATE TABLE testdb.commentLengthTestTable (
    ->   id int(11) DEFAULT NULL
    -> )
    -> ENGINE = INNODB,
    -> CHARACTER SET latin1,
    -> COLLATE latin1_swedish_ci,
    -> COMMENT = 'aaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeee
eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeefffffffffffffffffffffffffffffffffffffffffffgggggggggggggggggggggggggggggggggghhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh THE END';
Query OK, 0 rows affected (0.47 sec)

mysql> show create table testdb.commentLengthTestTable\G
*************************** 1. row ***************************
       Table: commentLengthTestTable
Create Table: CREATE TABLE `commentlengthtesttable` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='aaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccddddddddddddddddddddddddd
dddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeefffffffffffffffffffffffffffffffffffffffffffgggggggggggggggggggggggggggggggggghhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh THE
 END'
1 row in set (0.00 sec)

mysql> SELECT t.TABLE_COMMENT
    -> from information_schema.TABLES t
    -> WHERE t.TABLE_SCHEMA = 'testdb'
    -> AND t.TABLE_NAME = 'commentLengthTestTable';
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE_COMMENT
                                                                                                                                           |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------+
| aaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeee
eeeeeeeeeeeeeeeeeeeefffffffffffffffffffffffffffffffffffffffffffgggggggggggggggggggggggggggggggggghhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh THE END |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)

mysql> SELECT t.TABLE_COMMENT
    -> from information_schema.TABLES t
    -> WHERE t.TABLE_SCHEMA = 'testdb'
    -> AND t.TABLE_NAME = 'commentLengthTestTable'
    -> ORDER BY t.TABLE_SCHEMA;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE_COMMENT
                                                                                                                                           |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------+
| aaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeee
eeeeeeeeeeeeeeeeeeeefffffffffffffffffffffffffffffffffffffffffffgggggggggggggggggggggggggggggggggghhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh THE END |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

regards,
Umesh

Try query (without filtering by Table_schema) below please

SELECT t.TABLE_COMMENT 
from information_schema.TABLES t
WHERE t.TABLE_NAME = 'commentLengthTestTable'
ORDER BY t.TABLE_SCHEMA;

Thank you for the feedback.
Verified as described.

regards,
Umesh

Hello MySql,

Our customers expect your bug fix bugs.mysql.com/bug.php?id=93451
Please specify the time frame for the error to be corrected.

regards,
Devart

Posted by developer:
 
Fixed in 8.0.15.

Values selected from the TABLE_COMMENT column of the
INFORMATION_SCHEMA.TABLES table could be truncated.