Description:
in tables that use utf8 (haven't checked other non iso-8859-1 character sets), a where clause that repeats a column using an 'and' will cause unexpected results. some examples are:
mysql> select * from foo where a = 'there' and a != 'there';
+-------+
| a |
+-------+
| there |
+-------+
1 row in set (0.00 sec)
mysql> select * from foo where a = 'there' and a = 'there';
Empty set (0.00 sec)
though, using column 'a' only once will produce expectable results:
mysql> select * from foo where a = 'there';
+-------+
| a |
+-------+
| there |
+-------+
1 row in set (0.00 sec)
mysql> select * from foo where a != 'there';
+-------+
| a |
+-------+
| hello |
+-------+
1 row in set (0.01 sec)
so far, this bug is only reproducible using tables set to 'utf8' character set.
How to repeat:
mysql> connect test;
Connection id: 660
Current database: test
mysql> show tables;
Empty set (0.00 sec)
mysql> create table foo (a varchar(32) not null);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into foo values ('hello');
Query OK, 1 row affected (0.00 sec)
mysql> insert into foo values ('there');
Query OK, 1 row affected (0.00 sec)
mysql> select * from foo where a = 'there' and a = 'there';
+-------+
| a |
+-------+
| there |
+-------+
1 row in set (0.00 sec)
mysql> select * from foo where a = 'there' and a != 'there';
Empty set (0.00 sec)
mysql> alter table foo character set 'utf8';
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from foo where a = 'there' and a != 'there';
+-------+
| a |
+-------+
| there |
+-------+
1 row in set (0.00 sec)
mysql> select * from foo where a = 'there' and a = 'there';
Empty set (0.00 sec)
Description: in tables that use utf8 (haven't checked other non iso-8859-1 character sets), a where clause that repeats a column using an 'and' will cause unexpected results. some examples are: mysql> select * from foo where a = 'there' and a != 'there'; +-------+ | a | +-------+ | there | +-------+ 1 row in set (0.00 sec) mysql> select * from foo where a = 'there' and a = 'there'; Empty set (0.00 sec) though, using column 'a' only once will produce expectable results: mysql> select * from foo where a = 'there'; +-------+ | a | +-------+ | there | +-------+ 1 row in set (0.00 sec) mysql> select * from foo where a != 'there'; +-------+ | a | +-------+ | hello | +-------+ 1 row in set (0.01 sec) so far, this bug is only reproducible using tables set to 'utf8' character set. How to repeat: mysql> connect test; Connection id: 660 Current database: test mysql> show tables; Empty set (0.00 sec) mysql> create table foo (a varchar(32) not null); Query OK, 0 rows affected (0.00 sec) mysql> insert into foo values ('hello'); Query OK, 1 row affected (0.00 sec) mysql> insert into foo values ('there'); Query OK, 1 row affected (0.00 sec) mysql> select * from foo where a = 'there' and a = 'there'; +-------+ | a | +-------+ | there | +-------+ 1 row in set (0.00 sec) mysql> select * from foo where a = 'there' and a != 'there'; Empty set (0.00 sec) mysql> alter table foo character set 'utf8'; Query OK, 2 rows affected (0.03 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from foo where a = 'there' and a != 'there'; +-------+ | a | +-------+ | there | +-------+ 1 row in set (0.00 sec) mysql> select * from foo where a = 'there' and a = 'there'; Empty set (0.00 sec)