| Bug #6474 | select returns wrong result | ||
|---|---|---|---|
| Submitted: | 6 Nov 2004 6:16 | Modified: | 11 Nov 2004 6:30 |
| Reporter: | Georg Richter | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.0.2 | OS: | Any (all) |
| Assigned to: | Igor Babaev | CPU Architecture: | Any |
[6 Nov 2004 10:28]
Heikki Tuuri
With the latest 4.1 pull on Linux this works correctly. Regards, Heikki
[6 Nov 2004 16:11]
Alexander Keremidarski
ChangeSet@1.1631, 2004-11-05 22:49:15-08:00, brian@avenger.(none) gives correct result: mysql> select K2C4, K4N4, F2I4 from testTATGPA where K2C4 = 'WART'; +------+------+------+ | K2C4 | K4N4 | F2I4 | +------+------+------+ | WART | 0100 | 1 | | WART | 0200 | 1 | | WART | 0300 | 3 | +------+------+------+
[6 Nov 2004 17:41]
Georg Richter
The bug still exists with changeset 1.1632.
[6 Nov 2004 17:48]
Alexander Keremidarski
mysql> select K2C4, K4N4, F2I4 from testTATGPA where K2C4 = 'WART' and (F2I4 = 2 and K2C4 = 'WART' or (F2I4 = 2 or K4N4 = '0200')); +------+------+------+ | K2C4 | K4N4 | F2I4 | +------+------+------+ | WART | 0100 | 1 | | WART | 0200 | 1 | | WART | 0300 | 3 | +------+------+------+ 3 rows in set (0.00 sec)
[9 Nov 2004 19:44]
MySQL Verification Team
Verified with 5.0.2. Works fine with 4.1.8.
[11 Nov 2004 6:30]
Igor Babaev
This bug happened on all platforms. I reproduced it with a MYISAM table as well. The bug manifested itself when a query had the same equality of the form field=const at different AND levels. In the query from the report it was equality K2C4 = 'WART'. I also found another bug in the eliminate_item_equal function. The second bug could be reproduced with the query: SELECT K2C4, K4N4, F2I4 FROM testTATGPA WHERE K2C4 = 'WART' AND (K2C4 = 'WART' OR K4N4 = '0200'); The query erroniously returned an empty result set. As the second bug was closely related to the reported one it made sense to submit one patch to fix both of them. ChangeSet 1.1640 04/11/10 11:22:39 igor@rurik.mysql.com +3 -0 select.result, select.test: sql_select.cc:
Description: After latest pull SELECT returns wrong results. Expected result (this worked before pull): 5.0.2-alpha-debug-log [07:00] root@test> select K2C4, K4N4, F2I4 from testTATGPA where K2C4 = 'WART' and (F2I4 = 2 and K2C4 = 'WART' or (F2I4 2 or K4N4 = '0200')); +------+------+------+ | K2C4 | K4N4 | F2I4 | +------+------+------+ | WART | 0200 | 1 | +------+------+------+ 1 row in set (0.00 sec) How to repeat: 5.0.2-alpha-debug-log [07:10] root@test> CREATE TABLE `testTATGPA` ( -> `K2C4` varchar(4) character set latin1 collate latin1_bin NOT NULL default '', -> `K4N4` varchar(4) character set latin1 collate latin1_bin NOT NULL default '0000', -> `F2I4` int(11) NOT NULL default '0' -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.06 sec) 5.0.2-alpha-debug-log [07:11] root@test> insert into testTATGPA -> values ('W%RT', '0100', 1), -> ('W-RT', '0100', 1), -> ('WART', '0100', 1), -> ('WART', '0200', 1), -> ('WERT', '0100', 2), -> ('WORT','0200', 2), -> ('WT', '0100', 2), -> ('W_RT', '0100', 2), -> ('WaRT', '0100', 3), -> ('WART', '0300', 3), -> ('WRT' , '0400', 3), -> ('WURM', '0500', 3), -> ('W%T', '0600', 4), -> ('WA%T', '0700', 4), -> ('WA_T', '0800', 4) -> ; Query OK, 15 rows affected (0.01 sec) Records: 15 Duplicates: 0 Warnings: 0 5.0.2-alpha-debug-log [07:11] root@test> select K2C4, K4N4, F2I4 from testTATGPA where K2C4 = 'WART' and (F2I4 = 2 and K2C4 = 'WART' or (F2I4 = 2 or K4N4 = '0200')); +------+------+------+ | K2C4 | K4N4 | F2I4 | +------+------+------+ | WART | 0100 | 1 | | WART | 0200 | 1 | | WART | 0300 | 3 | +------+------+------+ 3 rows in set (0.00 sec)