Bug #93451 The table comment is cut down on selecting with ORDER BY
Submitted: 3 Dec 2018 14:21 Modified: 10 Jan 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:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: regression

[3 Dec 2018 14:21] dbForge Team
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
[4 Dec 2018 6:18] Umesh Shastry
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
[4 Dec 2018 16:07] dbForge Team
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;
[5 Dec 2018 5:03] Umesh Shastry
Thank you for the feedback.
Verified as described.

regards,
Umesh
[27 Dec 2018 13:29] dbForge Team
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
[10 Jan 16:53] Paul Dubois
Posted by developer:
 
Fixed in 8.0.15.

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