Description:
MySQL's CHAR type behaves inconsistently when comparing strings with trailing spaces. The WHERE clause and the UNIQUE constraint use different rules for trailing space handling, leading to a contradiction: a SELECT query returns no rows, but an INSERT fails with a duplicate key error.
Steps to reproduce:
1. Create a table with a CHAR(10) UNIQUE column
2. Insert one row
3. Query with a string that has trailing spaces - returns empty set
4. Insert the same string with trailing spaces - fails with duplicate key error
This is inconsistent because:
- The SELECT query suggests the value does not exist in the table
- But the INSERT claims it already exists
Expected behavior: Both operations should use the same comparison rules for trailing spaces.
How to repeat:
CREATE TABLE test (c CHAR(10) UNIQUE, id INT);
INSERT INTO test VALUES ('SM PKG', 1);
-- Query: returns empty set (no row found)
SELECT * FROM test WHERE c = 'SM PKG ';
-- Insert: fails with duplicate key error
INSERT INTO test VALUES ('SM PKG ', 2);
DROP TABLE IF EXISTS test;
Suggested fix:
MySQL should use consistent trailing space handling rules for both WHERE clause comparisons and UNIQUE constraint checks. Either both should ignore trailing spaces, or both should not ignore trailing spaces.
Description: MySQL's CHAR type behaves inconsistently when comparing strings with trailing spaces. The WHERE clause and the UNIQUE constraint use different rules for trailing space handling, leading to a contradiction: a SELECT query returns no rows, but an INSERT fails with a duplicate key error. Steps to reproduce: 1. Create a table with a CHAR(10) UNIQUE column 2. Insert one row 3. Query with a string that has trailing spaces - returns empty set 4. Insert the same string with trailing spaces - fails with duplicate key error This is inconsistent because: - The SELECT query suggests the value does not exist in the table - But the INSERT claims it already exists Expected behavior: Both operations should use the same comparison rules for trailing spaces. How to repeat: CREATE TABLE test (c CHAR(10) UNIQUE, id INT); INSERT INTO test VALUES ('SM PKG', 1); -- Query: returns empty set (no row found) SELECT * FROM test WHERE c = 'SM PKG '; -- Insert: fails with duplicate key error INSERT INTO test VALUES ('SM PKG ', 2); DROP TABLE IF EXISTS test; Suggested fix: MySQL should use consistent trailing space handling rules for both WHERE clause comparisons and UNIQUE constraint checks. Either both should ignore trailing spaces, or both should not ignore trailing spaces.