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

[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] MySQL Verification Team
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] MySQL Verification Team
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 2019 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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.078 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.