Description:
Trailing whitespace like "\t" and "\r" (but not plain space like " ") in a char or varchar value is handled inconsistently when the value is used in the WHERE clause. The behavior in 4.0.17 is different from 3.23 (tested 3.23.58 and 3.23.49a, which gave the same results), but no version behaves in a self-consistent manner. The inconsistent results are shown to depend on the rows in the table in an odd way, so a SELECT that returns 0 rows may return 1 row after a seemingly unrelated row is deleted.
How to repeat:
Below, I provide some SQL code to create example tables. I then show the output from various queries under 4.0.17 and demonstrate that they give inconsistent results. I then show the results of the same queries under 3.23, which are different but also inconsistent.
Here is the SQL code to generate the sample tables (note that both tables have an "s" column, but in table "taba" the values for that column have a trailing "\t" ["\r" works too] that is not present in the "tabb" table):
CREATE TABLE taba
(
a int not null primary key,
s varchar(10) not null
);
CREATE TABLE tabb
(
s varchar(10) not null primary key,
b int not null
);
INSERT INTO taba VALUES (1, 'one\t'), (2, 'two\t');
INSERT INTO tabb VALUES ('one', 1), ('two', 2);
#--------- QUERY RESULTS UNDER 4.0.17 ----------------
### Seems to indicate that trailing whitespace is ignored.
mysql> SELECT * FROM taba WHERE s='one';
+---+------+
| a | s |
+---+------+
| 1 | one |
+---+------+
1 row in set (0.00 sec)
### Seems to indicate that trailing whitespace NOT ignored.
mysql> SELECT * FROM tabb,taba WHERE taba.s=tabb.s;
Empty set (0.01 sec)
mysql> SELECT * FROM tabb,taba WHERE taba.s=tabb.s AND b=1;
Empty set (0.02 sec)
mysql> SELECT * FROM tabb,taba WHERE taba.s=tabb.s AND a=1;
Empty set (0.00 sec)
### Make the query MORE RESTRICTIVE and get more rows?
mysql> SELECT * FROM tabb,taba WHERE taba.s=tabb.s AND a=1 AND taba.s='one';
+-----+---+---+------+
| s | b | a | s |
+-----+---+---+------+
| one | 1 | 1 | one |
+-----+---+---+------+
1 row in set (0.00 sec)
mysql> SELECT * FROM tabb,taba WHERE taba.s=tabb.s AND a=1 AND tabb.s='one';
+-----+---+---+------+
| s | b | a | s |
+-----+---+---+------+
| one | 1 | 1 | one |
+-----+---+---+------+
1 row in set (0.00 sec)
### Now show that deleting a row can INCREASE the number
### of rows in the result!
mysql> SELECT * FROM tabb,taba WHERE taba.s=tabb.s;
Empty set (0.00 sec)
mysql> DELETE FROM tabb WHERE s='two';
Query OK, 1 row affected (0.02 sec)
mysql> SELECT * FROM tabb,taba WHERE taba.s=tabb.s;
+-----+---+---+------+
| s | b | a | s |
+-----+---+---+------+
| one | 1 | 1 | one |
+-----+---+---+------+
1 row in set (0.00 sec)
#--------- QUERY RESULTS UNDER 3.23 ----------------
### Seems that trailing whitespace is ignored
mysql> SELECT * FROM taba WHERE s='one';
+---+------+
| a | s |
+---+------+
| 1 | one |
+---+------+
1 row in set (0.00 sec)
mysql> SELECT * FROM tabb,taba WHERE taba.s=tabb.s;
+-----+---+---+------+
| s | b | a | s |
+-----+---+---+------+
| one | 1 | 1 | one |
| two | 2 | 2 | two |
+-----+---+---+------+
2 rows in set (0.01 sec)
mysql> SELECT * FROM tabb,taba WHERE taba.s=tabb.s AND b=1;
+-----+---+---+------+
| s | b | a | s |
+-----+---+---+------+
| one | 1 | 1 | one |
+-----+---+---+------+
1 row in set (0.00 sec)
### Almost the same query as the last one, but no rows?
mysql> SELECT * FROM tabb,taba WHERE taba.s=tabb.s AND a=1;
Empty set (0.00 sec)
### Make query MORE RESTRICTIVE and get more rows?
mysql> SELECT * FROM tabb,taba WHERE taba.s=tabb.s AND a=1 AND taba.s='one';
+-----+---+---+------+
| s | b | a | s |
+-----+---+---+------+
| one | 1 | 1 | one |
+-----+---+---+------+
1 row in set (0.00 sec)
mysql> SELECT * FROM tabb,taba WHERE taba.s=tabb.s AND a=1 AND tabb.s='one';
+-----+---+---+------+
| s | b | a | s |
+-----+---+---+------+
| one | 1 | 1 | one |
+-----+---+---+------+
1 row in set (0.00 sec)
### Now show that deleting a row can INCREASE the number
### of rows in the result!
mysql> SELECT * FROM tabb,taba WHERE taba.s=tabb.s AND a=1;
Empty set (0.00 sec)
mysql> DELETE FROM tabb WHERE s='two';
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM tabb,taba WHERE taba.s=tabb.s AND a=1;
+-----+---+---+------+
| s | b | a | s |
+-----+---+---+------+
| one | 1 | 1 | one |
+-----+---+---+------+
1 row in set (0.00 sec)
Description: Trailing whitespace like "\t" and "\r" (but not plain space like " ") in a char or varchar value is handled inconsistently when the value is used in the WHERE clause. The behavior in 4.0.17 is different from 3.23 (tested 3.23.58 and 3.23.49a, which gave the same results), but no version behaves in a self-consistent manner. The inconsistent results are shown to depend on the rows in the table in an odd way, so a SELECT that returns 0 rows may return 1 row after a seemingly unrelated row is deleted. How to repeat: Below, I provide some SQL code to create example tables. I then show the output from various queries under 4.0.17 and demonstrate that they give inconsistent results. I then show the results of the same queries under 3.23, which are different but also inconsistent. Here is the SQL code to generate the sample tables (note that both tables have an "s" column, but in table "taba" the values for that column have a trailing "\t" ["\r" works too] that is not present in the "tabb" table): CREATE TABLE taba ( a int not null primary key, s varchar(10) not null ); CREATE TABLE tabb ( s varchar(10) not null primary key, b int not null ); INSERT INTO taba VALUES (1, 'one\t'), (2, 'two\t'); INSERT INTO tabb VALUES ('one', 1), ('two', 2); #--------- QUERY RESULTS UNDER 4.0.17 ---------------- ### Seems to indicate that trailing whitespace is ignored. mysql> SELECT * FROM taba WHERE s='one'; +---+------+ | a | s | +---+------+ | 1 | one | +---+------+ 1 row in set (0.00 sec) ### Seems to indicate that trailing whitespace NOT ignored. mysql> SELECT * FROM tabb,taba WHERE taba.s=tabb.s; Empty set (0.01 sec) mysql> SELECT * FROM tabb,taba WHERE taba.s=tabb.s AND b=1; Empty set (0.02 sec) mysql> SELECT * FROM tabb,taba WHERE taba.s=tabb.s AND a=1; Empty set (0.00 sec) ### Make the query MORE RESTRICTIVE and get more rows? mysql> SELECT * FROM tabb,taba WHERE taba.s=tabb.s AND a=1 AND taba.s='one'; +-----+---+---+------+ | s | b | a | s | +-----+---+---+------+ | one | 1 | 1 | one | +-----+---+---+------+ 1 row in set (0.00 sec) mysql> SELECT * FROM tabb,taba WHERE taba.s=tabb.s AND a=1 AND tabb.s='one'; +-----+---+---+------+ | s | b | a | s | +-----+---+---+------+ | one | 1 | 1 | one | +-----+---+---+------+ 1 row in set (0.00 sec) ### Now show that deleting a row can INCREASE the number ### of rows in the result! mysql> SELECT * FROM tabb,taba WHERE taba.s=tabb.s; Empty set (0.00 sec) mysql> DELETE FROM tabb WHERE s='two'; Query OK, 1 row affected (0.02 sec) mysql> SELECT * FROM tabb,taba WHERE taba.s=tabb.s; +-----+---+---+------+ | s | b | a | s | +-----+---+---+------+ | one | 1 | 1 | one | +-----+---+---+------+ 1 row in set (0.00 sec) #--------- QUERY RESULTS UNDER 3.23 ---------------- ### Seems that trailing whitespace is ignored mysql> SELECT * FROM taba WHERE s='one'; +---+------+ | a | s | +---+------+ | 1 | one | +---+------+ 1 row in set (0.00 sec) mysql> SELECT * FROM tabb,taba WHERE taba.s=tabb.s; +-----+---+---+------+ | s | b | a | s | +-----+---+---+------+ | one | 1 | 1 | one | | two | 2 | 2 | two | +-----+---+---+------+ 2 rows in set (0.01 sec) mysql> SELECT * FROM tabb,taba WHERE taba.s=tabb.s AND b=1; +-----+---+---+------+ | s | b | a | s | +-----+---+---+------+ | one | 1 | 1 | one | +-----+---+---+------+ 1 row in set (0.00 sec) ### Almost the same query as the last one, but no rows? mysql> SELECT * FROM tabb,taba WHERE taba.s=tabb.s AND a=1; Empty set (0.00 sec) ### Make query MORE RESTRICTIVE and get more rows? mysql> SELECT * FROM tabb,taba WHERE taba.s=tabb.s AND a=1 AND taba.s='one'; +-----+---+---+------+ | s | b | a | s | +-----+---+---+------+ | one | 1 | 1 | one | +-----+---+---+------+ 1 row in set (0.00 sec) mysql> SELECT * FROM tabb,taba WHERE taba.s=tabb.s AND a=1 AND tabb.s='one'; +-----+---+---+------+ | s | b | a | s | +-----+---+---+------+ | one | 1 | 1 | one | +-----+---+---+------+ 1 row in set (0.00 sec) ### Now show that deleting a row can INCREASE the number ### of rows in the result! mysql> SELECT * FROM tabb,taba WHERE taba.s=tabb.s AND a=1; Empty set (0.00 sec) mysql> DELETE FROM tabb WHERE s='two'; Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM tabb,taba WHERE taba.s=tabb.s AND a=1; +-----+---+---+------+ | s | b | a | s | +-----+---+---+------+ | one | 1 | 1 | one | +-----+---+---+------+ 1 row in set (0.00 sec)