Bug #3736 where a = 'foo' and a != 'foo' expression returns rows in utf8 tables
Submitted: 13 May 2004 5:41 Modified: 13 May 2004 18:39
Reporter: visnu pitiyanuvath Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.1 OS:Linux (redhat linux 9)
Assigned to: Assigned Account CPU Architecture:Any

[13 May 2004 5:41] visnu pitiyanuvath
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)
[13 May 2004 18:39] Dean Ellis
This is a duplicate of bug 2959; the correction is available in 4.1.2.