| Bug #15188 | SELECT DISTINCT returns no results even though valid ones exist | ||
|---|---|---|---|
| Submitted: | 23 Nov 2005 15:12 | Modified: | 21 Mar 2006 13:35 |
| Reporter: | Razvan Racasanu | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.15/5.0.17 BK | OS: | Windows (Windows XP, Fedora Core 4) |
| Assigned to: | Georgi Kodinov | CPU Architecture: | Any |
[23 Nov 2005 15:14]
Razvan Racasanu
This table only contains about 700 records and the bug reproduces
Attachment: order_ord_wrong.sql (text/x-sql), 31.73 KiB.
[23 Nov 2005 15:16]
Razvan Racasanu
I have deleted about 200 records from the previous dump, and now it will work correctly when using WHERE
Attachment: order_ord.sql (text/x-sql), 23.71 KiB.
[23 Nov 2005 17:58]
MySQL Verification Team
Thank you for the bug report.
miguel@hegel:~/dbs/5.0> bin/mysqladmin -uroot create db7
miguel@hegel:~/dbs/5.0> bin/mysql -uroot db7
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 5.0.17-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> source /home/miguel/scripts/order_ord_wrong.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 700 rows affected (0.06 sec)
Records: 700 Duplicates: 0 Warnings: 0
mysql> SELECT DISTINCT status_ord FROM order_ord WHERE status_ord != 'Confirmed' AND
-> status_ord != 'Initialized' AND status_ord != 'Shipped' AND status_ord !=
-> 'Contacted' ORDER BY status_ord ASC;
Empty set (0.01 sec)
mysql> alter table order_ord drop index status_ord;
Query OK, 700 rows affected (0.04 sec)
Records: 700 Duplicates: 0 Warnings: 0
mysql> SELECT DISTINCT status_ord FROM order_ord WHERE status_ord != 'Confirmed' AND
-> status_ord != 'Initialized' AND status_ord != 'Shipped' AND status_ord !=
-> 'Contacted' ORDER BY status_ord ASC;
+------------+
| status_ord |
+------------+
| Chargeback |
| Fraud |
| Memo |
| URL Attack |
+------------+
4 rows in set (0.01 sec)
mysql>
[23 Nov 2005 18:39]
MySQL Verification Team
miguel@hegel:~/dbs/4.1> bin/mysqladmin -uroot create db7
miguel@hegel:~/dbs/4.1> bin/mysql -uroot db7
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.16-debug-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> source /home/miguel/scripts/order_ord_wrong.sql
Query OK, 0 rows affected, 1 warning (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 700 rows affected (0.06 sec)
Records: 700 Duplicates: 0 Warnings: 0
mysql> SELECT DISTINCT status_ord FROM order_ord WHERE status_ord != 'Confirmed' AND
-> status_ord != 'Initialized' AND status_ord != 'Shipped' AND status_ord !=
-> 'Contacted' ORDER BY status_ord ASC;
+------------+
| status_ord |
+------------+
| Chargeback |
| Fraud |
| Memo |
| URL Attack |
+------------+
4 rows in set (0.02 sec)
mysql>
[20 Dec 2005 9:21]
Yoshiaki Tajika
Hi, Miguel. At my PC with 5.0.16-nt, I could reproduce it, too. When the query returns an empty set, EXPLAIN says "Impossible WHERE noticed after reading const tables". On the while, when the query returns a good result, EXPLAIN says "Using where; Using index for group-by". I have no idea about what these mean, but it may be a clue for you.
[22 Mar 2006 2:14]
Yoshiaki Tajika
Hi, I got notification that the status was changed into 'Can't repeat'. In fact, I couldn't reproduce with 5.0.19-nt, so it seems to be fixed. But I want to say two things. 1. Do not change the status with no words. You should explain why you change it. 2. It should NOT be closed as 'Can't repeat'. Miguel actually reproduced it. 'Can't repeat' means bug-reporter's defect in a way. Anyway, please treat each bug report with courtesy.
[22 Mar 2006 7:19]
Timour Katchaounov
Hi Yoshiaki, Your point 1. is valid, sorry for that, normally we add a comment why we change the status of a bug, but mistakes can happen. Regarding your point 2., I must say that "Can't repeat" only means that a bug could not be reproduced with the latest build, and doesn't at all imply that the bug reporter made an error. It may happen that the real cause of a bug has already been fixed by the time we try to reproduce it, and we don't investigate such cases any further. In any case we are very thankful to all our bug reporters!
[22 Mar 2006 7:28]
Yoshiaki Tajika
I'm sorry, too, for my misunderstanding. Thank you for your sincere response.
[27 Mar 2006 10:32]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/4186
[27 Mar 2006 10:56]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/4187
Description: I have a table with the following structure: CREATE TABLE `order_ord` ( `id_ord` varchar(255) NOT NULL default '', `status_ord` varchar(255) NOT NULL default 'Initialized', PRIMARY KEY (`id_ord`), KEY `status_ord` (`status_ord`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; The table contains approximately 14000 records with the following values for the field status_ord: Chargeback, Confirmed, Fraud, Initialized, Memo and URL Attack. When I run the SQL statement: SELECT DISTINCT status_ord FROM order_ord WHERE status_ord != 'Confirmed' AND status_ord != 'Initialized' AND status_ord != 'Shipped' AND status_ord != 'Contacted' ORDER BY status_ord ASC I expect to have the values Chargeback, Fraud, Memo and URL Attack returned. Instead, I get no values returned whatsoever. The same thing happens if I rewrite the query using NOT IN instead of a series of ANDs. It seems that if I replace WHERE with HAVING or if I delete the index on the status_ord field it will work correctly. How to repeat: I will attach a dump of the table on which the SQL already mentioned produces the strange results.