Bug #33102 WHERE returning incorrect results

• Submitted: 10 Dec 2007 3:35
• Modified: 10 Dec 2007 4:33
• Reporter: Jared S (Silver Quality Contributor)
• Status: Not a Bug
• Category: MySQL Server: Parser
• Severity: S2 (Serious)
• Version: 5.1.22, 5.0.52
• OS: Windows (Any)
• CPU Architecture: Any
• Tags: qc, space, varchar, where

[10 Dec 2007 3:35] Jared S

Description:
Hi,

MySQL is returning incorrect results using WHERE `field` = ' '

How to repeat:
DROP TABLE IF EXISTS `new table`;
CREATE TABLE `new table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `label` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

INSERT INTO `new table` (`id`,`label`) VALUES 
 (1,' '),
 (2,' '),
 (3,''),
 (4,''),
 (5,' ');

SELECT *, length(label) FROM `new table` where label = ' '

[10 Dec 2007 4:33] Valeriy Kravchuk

Thank you for a problem report. It is not a bug, though. Our manual clearly says, http://dev.mysql.com/doc/refman/5.0/en/char.html:

"All MySQL collations are of type PADSPACE. This means that all CHAR and VARCHAR values in MySQL are compared without regard to any trailing spaces. For example:

mysql> CREATE TABLE names (myname CHAR(10), yourname VARCHAR(10));
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO names VALUES ('Monty ', 'Monty ');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT myname = 'Monty  ', yourname = 'Monty  ' FROM names;
+--------------------+----------------------+
| myname = 'Monty  ' | yourname = 'Monty  ' |
+--------------------+----------------------+
|                  1 |                    1 |
+--------------------+----------------------+
1 row in set (0.00 sec)

This is true for all MySQL versions, and it makes no difference whether your version trims trailing spaces from VARCHAR values before storing them. Nor does the server SQL mode make any difference in this regard."