| Bug #14896 | ORDER BY returns empty set | ||
|---|---|---|---|
| Submitted: | 12 Nov 2005 11:38 | Modified: | 4 Jul 2006 7:20 |
| Reporter: | Derrick Tan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 4.1.16-BK, 4.1.15-nt | OS: | Linux (Linux, Windows XP SP2) |
| Assigned to: | Igor Babaev | CPU Architecture: | Any |
[12 Nov 2005 11:38]
Derrick Tan
[12 Nov 2005 11:46]
Derrick Tan
I tried converting to InnoDB with no luck.
[12 Nov 2005 17:40]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described on 4.1.16-BK (ChangeSet@1.2465.1.1, 2005-11-10 15:12:22+01:00) build on Linux:
mysql> CREATE TABLE `node` (
-> `nid` int(10) unsigned NOT NULL auto_increment,
-> `type` varchar(16) collate utf8_unicode_ci NOT NULL default '',
-> `title` varchar(128) collate utf8_unicode_ci NOT NULL default '',
-> `uid` int(10) NOT NULL default '0',
-> `status` int(4) NOT NULL default '1',
-> `created` int(11) NOT NULL default '0',
-> `changed` int(11) NOT NULL default '0',
-> `comment` int(2) NOT NULL default '0',
-> `promote` int(2) NOT NULL default '0',
-> `moderate` int(2) NOT NULL default '0',
-> `teaser` longtext collate utf8_unicode_ci NOT NULL,
-> `body` longtext collate utf8_unicode_ci NOT NULL,
-> `revisions` longtext collate utf8_unicode_ci NOT NULL,
-> `sticky` int(2) NOT NULL default '0',
-> `format` int(4) NOT NULL default '0',
-> `language` varchar(12) collate utf8_unicode_ci NOT NULL default '',
-> PRIMARY KEY (`nid`),
-> KEY `node_type` (`type`(4)),
-> KEY `node_title_type` (`title`,`type`(4)),
-> KEY `status` (`status`),
-> KEY `uid` (`uid`),
-> KEY `node_moderate` (`moderate`),
-> KEY `node_promote_status` (`promote`,`status`),
-> KEY `node_created` (`created`),
-> KEY `node_changed` (`changed`),
-> KEY `node_status_type` (`status`,`type`,`nid`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=15;
Query OK, 0 rows affected (0,04 sec)
mysql> INSERT INTO `node` VALUES (1, 'page', 'My first page', 1, 1, 1122828792,
-> 1123573332, 2, 0, 0, 'Abracadabra', 'Abracadabra', '', 0, 1, '');
Query OK, 1 row affected (0,01 sec)
mysql> INSERT INTO `node` VALUES (2, 'page', 'Number 2', 1, 1, 1123573359, 1123651539,
-> 2, 1, 0, '', '', 0, 1, '');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> INSERT INTO `node` VALUES (3, 'page', 'Another Page', 1, 1, 1123651336,
-> 11123651396, 2, 1, 0, 'body body body', 'body body body', '', 1, 1, '');
Query OK, 1 row affected (0,00 sec)
mysql> INSERT INTO `node` VALUES (4, 'page', 'Another one', 1, 1, 1123651525,
-> 1123651525, 2, 1, 0, 'bites the dust', 'bites the dust', '', 0, 1, '');
Query OK, 1 row affected (0,01 sec)
mysql> INSERT INTO `node` VALUES (5, 'page', 'New event', 1, 1, 1125533318, 1125533318,
-> 2, 1, 0, 'Here''s my body', 'Here''s my body', '', 0, 1, '');
Query OK, 1 row affected (0,00 sec)
mysql> INSERT INTO `node` VALUES (6, 'page', 'Another Event', 1, 1, 1125538010,
-> 1125538010, 2, 1, 0, 'Event it up', 'Event it up', '', 0, 1, '');
Query OK, 1 row affected (0,01 sec)
mysql> INSERT INTO `node` VALUES (7, 'forum', 'Help!', 1, 1, 1125648944, 1125648944, 2,
-> 1, 0, 'Help me please', 'Help me please', '', 0, 1, '');
Query OK, 1 row affected (0,00 sec)
mysql> INSERT INTO `node` VALUES (8, 'poll', 'Poll', 1, 1, 1128665336, 1128665456, 2,
-> 1, 0, '* Here\\n* Another One\\n', '', '', 0, 0, '');
Query OK, 1 row affected (0,01 sec)
mysql> INSERT INTO `node` VALUES (11, 'blog', 'BLog post 1', 1, 1, 1131783459,
-> 1131783459, 2, 1, 0, 'Blogging', 'Blogging', '', 0, 1, '');
Query OK, 1 row affected (0,00 sec)
mysql> INSERT INTO `node` VALUES (12, 'blog', 'Blog Post 2', 1, 1, 1131783474,
-> 1131783474, 2, 1, 0, 'Blog 2', 'Blog 2', '', 0, 1, '');
Query OK, 1 row affected (0,00 sec)
mysql> INSERT INTO `node` VALUES (13, 'story', 'Story', 1, 1, 1131787457, 1131787457,
-> 2, 1, 0, 'story', 'story', '', 0, 1, '');
Query OK, 1 row affected (0,00 sec)
mysql> INSERT INTO `node` VALUES (14, 'forum', 'Post 2', 1, 1, 1131787495, 1131787495,
-> 2, 1, 0, 'sds', 'sds', '', 0, 1, '');
Query OK, 1 row affected (0,00 sec)
Just to prove that there are rows of every type, even after the errors above:
mysql> select nid,type,status from node;
+-----+-------+--------+
| nid | type | status |
+-----+-------+--------+
| 11 | blog | 1 |
| 12 | blog | 1 |
| 7 | forum | 1 |
| 14 | forum | 1 |
| 1 | page | 1 |
| 3 | page | 1 |
| 4 | page | 1 |
| 5 | page | 1 |
| 6 | page | 1 |
| 8 | poll | 1 |
| 13 | story | 1 |
+-----+-------+--------+
11 rows in set (0,00 sec)
mysql> SELECT n.nid, n.title, n.created FROM node n WHERE n.type = 'blog' AND
-> n.status = 1 ORDER BY n.created DESC;
+-----+-------------+------------+
| nid | title | created |
+-----+-------------+------------+
| 12 | Blog Post 2 | 1131783474 |
| 11 | BLog post 1 | 1131783459 |
+-----+-------------+------------+
2 rows in set (0,06 sec)
mysql> SELECT n.nid, n.title, n.created FROM node n WHERE n.type = 'forum' AND n.status = 1 ORDER BY n.created DESC;
Empty set (0,00 sec)
mysql> SELECT n.nid, n.title, n.created FROM node n WHERE n.type = 'page' AND n.status = 1 ORDER BY n.created DESC;
+-----+---------------+------------+
| nid | title | created |
+-----+---------------+------------+
| 6 | Another Event | 1125538010 |
| 5 | New event | 1125533318 |
| 4 | Another one | 1123651525 |
| 3 | Another Page | 1123651336 |
| 1 | My first page | 1122828792 |
+-----+---------------+------------+
5 rows in set (0,00 sec)
mysql> SELECT n.nid, n.title, n.created FROM node n WHERE n.type = 'story' AND n.status = 1 ORDER BY n.created DESC;
Empty set (0,00 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.16 |
+-----------+
1 row in set (0,02 sec)
So, yes, your first attempt was successfull - IT IS A BUG! Note, that 5.0.17-BK gave correct results fro all the queries.
[13 Nov 2005 7:58]
Derrick Tan
Workaround is to use LIKE instead of =
[7 Mar 2006 0:33]
Baron Schwartz
Please see also bug 17742 for a similar issue. http://bugs.mysql.com/bug.php?id=17742 In that case, it looks like the ORDER BY is casting values to a different type before ordering. ORDER BY should not cast values to a different type. The ordering seems to happen before the HAVING clause is evaluated -- probably not the most efficient way. This combination of things (casting, then ordering before the HAVING clause is evaluated) is the only way I can think of to explain the behavior mentioned in that bug.
[7 Jun 2006 5:13]
Igor Babaev
The same problem can be demonstrated with a simpler example:
mysql> CREATE TABLE t1 (id int PRIMARY KEY,
-> a varchar(16) collate utf8_unicode_ci NOT NULL default '',
-> b int,
-> f varchar(128) default 'XXX',
-> INDEX (a(4))
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1(id, a, b) VALUES
-> (1, 'cccc', 50), (2, 'cccc', 70), (3, 'cccc', 30),
-> (4, 'cccc', 30), (5, 'cccc', 20), (6, 'bbbbbb', 40),
-> (7, 'dddd', 30), (8, 'aaaa', 10), (9, 'aaaa', 50),
-> (10, 'eeeee', 40), (11, 'bbbbbb', 60);
Query OK, 11 rows affected (0.00 sec)
Records: 11 Duplicates: 0 Warnings: 0
mysql> SELECT id, a, b FROM t1;
+----+--------+------+
| id | a | b |
+----+--------+------+
| 1 | cccc | 50 |
| 2 | cccc | 70 |
| 3 | cccc | 30 |
| 4 | cccc | 30 |
| 5 | cccc | 20 |
| 6 | bbbbbb | 40 |
| 7 | dddd | 30 |
| 8 | aaaa | 10 |
| 9 | aaaa | 50 |
| 10 | eeeee | 40 |
| 11 | bbbbbb | 60 |
+----+--------+------+
11 rows in set (0.00 sec)
mysql> SELECT id, a, b FROM t1 WHERE a BETWEEN 'aaaa' AND 'bbbbbb';
+----+------+------+
| id | a | b |
+----+------+------+
| 8 | aaaa | 10 |
| 9 | aaaa | 50 |
+----+------+------+
2 rows in set (0.00 sec)
mysql> EXPLAIN SELECT id, a, b FROM t1 WHERE a BETWEEN 'aaaa' AND 'bbbbbb';
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | range | a | a | 12 | NULL | 4 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> SELECT id, a, b FROM t1 WHERE a > 'cccc';
+----+-------+------+
| id | a | b |
+----+-------+------+
| 7 | dddd | 30 |
| 10 | eeeee | 40 |
+----+-------+------+
2 rows in set (0.00 sec)
mysql> EXPLAIN SELECT id, a, b FROM t1 WHERE a > 'cccc';
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | range | a | a | 12 | NULL | 3 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
[7 Jun 2006 5:13]
Igor Babaev
The problem does not appear though with non-multibyte character sets:
mysql> INSERT INTO t2(id, a, b) VALUES
-> (1, 'cccc', 50), (2, 'cccc', 70), (3, 'cccc', 30),
-> (4, 'cccc', 30), (5, 'cccc', 20), (6, 'bbbbbb', 40),
-> (7, 'dddd', 30), (8, 'aaaa', 10), (9, 'aaaa', 50),
-> (10, 'eeeee', 40), (11, 'bbbbbb', 60);
Query OK, 11 rows affected (0.00 sec)
Records: 11 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t2(id, a, b) VALUES
-> (1, 'cccc', 50), (2, 'cccc', 70), (3, 'cccc', 30),
-> (4, 'cccc', 30), (5, 'cccc', 20), (6, 'bbbbbb', 40),
-> (7, 'dddd', 30), (8, 'aaaa', 10), (9, 'aaaa', 50),
-> (10, 'eeeee', 40), (11, 'bbbbbb', 60);
Query OK, 11 rows affected (0.00 sec)
Records: 11 Duplicates: 0 Warnings: 0
mysql> SELECT id, a, b FROM t2;
+----+--------+------+
| id | a | b |
+----+--------+------+
| 1 | cccc | 50 |
| 2 | cccc | 70 |
| 3 | cccc | 30 |
| 4 | cccc | 30 |
| 5 | cccc | 20 |
| 6 | bbbbbb | 40 |
| 7 | dddd | 30 |
| 8 | aaaa | 10 |
| 9 | aaaa | 50 |
| 10 | eeeee | 40 |
| 11 | bbbbbb | 60 |
+----+--------+------+
11 rows in set (0.00 sec)
mysql> SELECT id, a, b FROM t1 WHERE a BETWEEN 'aaaa' AND 'bbbbbb';
+----+------+------+
| id | a | b |
+----+------+------+
| 8 | aaaa | 10 |
| 9 | aaaa | 50 |
+----+------+------+
2 rows in set (0.00 sec)
mysql> EXPLAIN SELECT id, a, b FROM t1 WHERE a BETWEEN 'aaaa' AND 'bbbbbb';
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | range | a | a | 12 | NULL | 4 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> SELECT id, a, b FROM t1 WHERE a > 'cccc';
+----+-------+------+
| id | a | b |
+----+-------+------+
| 7 | dddd | 30 |
| 10 | eeeee | 40 |
+----+-------+------+
2 rows in set (5.99 sec)
mysql> EXPLAIN SELECT id, a, b FROM t1 WHERE a > 'cccc';
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | range | a | a | 12 | NULL | 3 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE t3 (id int PRIMARY KEY,
-> a varchar(16) collate latin1_german2_ci NOT NULL default '',
-> b int,
-> f varchar(128) default 'XXX',
-> INDEX (a(4))
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.08 sec)
mysql> INSERT INTO t3(id, a, b) VALUES
-> (1, 'cccc', 50), (2, 'cccc', 70), (3, 'cccc', 30),
-> (4, 'cccc', 30), (5, 'cccc', 20), (6, 'bbbbbb', 40),
-> (7, 'dddd', 30), (8, 'aaaa', 10), (9, 'aaaa', 50),
-> (10, 'eeeee', 40), (11, 'bbbbbb', 60);
Query OK, 11 rows affected (0.01 sec)
Records: 11 Duplicates: 0 Warnings: 0
mysql> SELECT id, a, b FROM t3;
+----+--------+------+
| id | a | b |
+----+--------+------+
| 1 | cccc | 50 |
| 2 | cccc | 70 |
| 3 | cccc | 30 |
| 4 | cccc | 30 |
| 5 | cccc | 20 |
| 6 | bbbbbb | 40 |
| 7 | dddd | 30 |
| 8 | aaaa | 10 |
| 9 | aaaa | 50 |
| 10 | eeeee | 40 |
| 11 | bbbbbb | 60 |
+----+--------+------+
11 rows in set (0.00 sec)
mysql> SELECT id, a, b FROM t3 WHERE a BETWEEN 'aaaa' AND 'bbbbbb';
+----+--------+------+
| id | a | b |
+----+--------+------+
| 8 | aaaa | 10 |
| 9 | aaaa | 50 |
| 6 | bbbbbb | 40 |
| 11 | bbbbbb | 60 |
+----+--------+------+
4 rows in set (0.00 sec)
mysql> EXPLAIN SELECT id, a, b FROM t3 WHERE a BETWEEN 'aaaa' AND 'bbbbbb';
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t3 | range | a | a | 4 | NULL | 4 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> SELECT id, a, b FROM t3 WHERE a > 'cccc';
+----+-------+------+
| id | a | b |
+----+-------+------+
| 7 | dddd | 30 |
| 10 | eeeee | 40 |
+----+-------+------+
2 rows in set (3.66 sec)
mysql> EXPLAIN SELECT id, a, b FROM t3 WHERE a > 'cccc';
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t3 | range | a | a | 4 | NULL | 3 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
[7 Jun 2006 5:26]
Igor Babaev
Correction for the previous comment concerning table t2:
mysql> CREATE TABLE t2(id int PRIMARY KEY,
-> a varchar(16) NOT NULL default '',
-> b int,
-> f varchar(128) default 'XXX',
-> INDEX (a(4))
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t2(id, a, b) VALUES
-> (1, 'cccc', 50), (2, 'cccc', 70), (3, 'cccc', 30),
-> (4, 'cccc', 30), (5, 'cccc', 20), (6, 'bbbbbb', 40),
-> (7, 'dddd', 30), (8, 'aaaa', 10), (9, 'aaaa', 50),
-> (10, 'eeeee', 40), (11, 'bbbbbb', 60);
Query OK, 11 rows affected (0.01 sec)
Records: 11 Duplicates: 0 Warnings: 0
mysql> SELECT id, a, b FROM t2;
+----+--------+------+
| id | a | b |
+----+--------+------+
| 1 | cccc | 50 |
| 2 | cccc | 70 |
| 3 | cccc | 30 |
| 4 | cccc | 30 |
| 5 | cccc | 20 |
| 6 | bbbbbb | 40 |
| 7 | dddd | 30 |
| 8 | aaaa | 10 |
| 9 | aaaa | 50 |
| 10 | eeeee | 40 |
| 11 | bbbbbb | 60 |
+----+--------+------+
11 rows in set (0.00 sec)
mysql> SELECT id, a, b FROM t2 WHERE a BETWEEN 'aaaa' AND 'bbbbbb';
+----+--------+------+
| id | a | b |
+----+--------+------+
| 8 | aaaa | 10 |
| 9 | aaaa | 50 |
| 6 | bbbbbb | 40 |
| 11 | bbbbbb | 60 |
+----+--------+------+
4 rows in set (0.00 sec)
mysql> EXPLAIN SELECT id, a, b FROM t2 WHERE a BETWEEN 'aaaa' AND 'bbbbbb';
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t2 | range | a | a | 4 | NULL | 4 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT id, a, b FROM t2 WHERE a > 'cccc';
+----+-------+------+
| id | a | b |
+----+-------+------+
| 7 | dddd | 30 |
| 10 | eeeee | 40 |
+----+-------+------+
2 rows in set (2.70 sec)
mysql> EXPLAIN SELECT id, a, b FROM t2 WHERE a > 'cccc';
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t2 | range | a | a | 4 | NULL | 3 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
[13 Jun 2006 4:08]
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/7555
[14 Jun 2006 2:58]
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/7593
[19 Jun 2006 0:15]
Evgeny Potemkin
Fixed in 4.1.21, 5.0.23, 5.1.12
[20 Jun 2006 10:55]
Evgeny Potemkin
This bug in Field_string::cmp resulted in a wrong comparison with keys in partial indexes over multi-byte character fields. Given field a is declared as a varchar(16) collate utf8_unicode_ci INDEX(a(4)) gives us an example of such an index. Wrong key comparisons could lead to wrong result sets if the selected query execution plan used a range scan by a partial index over a utf8 character field. This also caused wrong results in many other cases.
[4 Jul 2006 7:20]
Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.
If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
[4 Jul 2006 7:20]
Jon Stephens
Documented bugfix in 4.1.21/5.0.23/5.1.12 changelogs. Closed.
