Bug #100008 Comparimg chart length of trimed and not trim

• Submitted: 26 Jun 2020 13:06
• Modified: 29 Jun 2020 13:05
• Reporter: Pedro da Costa
• Status: Not a Bug
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 8.0.20
• OS: Any (win 10 x64)
• CPU Architecture: x86 (ryzen 7)
• Tags: length character Compare trim

[26 Jun 2020 13:06] Pedro da Costa

Description:
Comparing the trimed value with the original value doesn't result in expected result.

SELECT 
    * 
FROM `values`
WHERE LENGTH(value1) >  LENGTH(TRIM(value1));

should return 3 rows but does only returns 2

How to repeat:
create table `values` (value1 char(10));

insert into `values` VALUES('test'),('test1  '),(' test2'),(' test3 ');

SELECT 
    * 
FROM `values`
WHERE LENGTH(value1) >  LENGTH(TRIM(value1));

# value1
 test2
 test3

SELECT LENGTH('test1 '),LENGTH(TRIM('test1 '));
# LENGTH('test1 ')	LENGTH(TRIM('test1 '))
6	5

[29 Jun 2020 13:05] MySQL Verification Team

Hi Mr. de Costa,

Thank you for your bug report.

However, this is not a bug.

If we list also the lengths of the stored values and trimmed stored values, you will see why is query result expected.

You should read in our Reference Manual how are data stored in CHAR columns.

This is the output:

value1
 test2
 test3
value1	LENGTH(value1)	LENGTH(TRIM(value1))
test	        4	4
test1	5	5
 test2	6	5
 test3	6	5

Not a bug.