----- Original Message -----
From: "Mohammed Sameer" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Tuesday, May 09, 2006 9:56 AM
Subject: Order by leads to an empty set.
Hi all,
I have a strange problem and I can't really understand what's going on!
mysql> SELECT n.nid, n.sticky, n.created FROM node n WHERE n.type='image'
AND (n.uid = 1 OR n.status = 1) ORDER BY n.created desc;
Empty set (0.00 sec)
mysql> SELECT n.nid, n.sticky, n.created FROM node n WHERE n.type='image'
AND (n.uid = 1 OR n.status = 1);
+-----+--------+------------+
| nid | sticky | created |
+-----+--------+------------+
| 73 | 0 | 1141048224 |
| 75 | 0 | 1141736038 |
.............................
| 93 | 0 | 1145039899 |
| 97 | 0 | 1145189131 |
+-----+--------+------------+
51 rows in set (0.00 sec)
I'm using a standar drupal installation:
mysql> desc node;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| nid | int(10) unsigned | | PRI | NULL | auto_increment |
| type | varchar(32) | | MUL | | |
| title | varchar(128) | | MUL | | |
| uid | int(10) | | MUL | 0 | |
| status | int(4) | | MUL | 1 | |
| created | int(11) | | MUL | 0 | |
| changed | int(11) | | MUL | 0 | |
| comment | int(2) | | | 0 | |
| promote | int(2) | | MUL | 0 | |
| moderate | int(2) | | MUL | 0 | |
| sticky | int(2) | | | 0 | |
| vid | int(10) unsigned | | | 0 | |
+----------+------------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)
| version | 4.1.12
CentOS release 4.2 (Final)
Any idea ? Am I doing something wrong ? Did I hit a bug ?
If the two queries really are identical except that one has an ORDER BY
added to it, this would appear to be a bug, although it would be a VERY
strange one! But a bug isn't the _only_ possibility; in fact, I can think of
three things that are probably much more likely.
1. Is there any possibility that a DELETE took place between the first query
and the second? If the query without the ORDER BY returned 51 rows, then a
DELETE executed by you - or someone unknown to you - removed all the rows,
then the query WITH the ORDER BY executed, this would explain the behaviour
you saw without any bug being involved.
2. Is there any possibility that the two queries took place against
different tables or databases or systems? If the query without the ORDER BY
ran against a version of the table that had 51 rows in it (perhaps the
production version of the table) and the query with the ORDER BY ran against
a different version of the table, maybe one that was empty (perhaps a test
version of the table), this would explain the behaviour you saw without any
bug being involved. This kind of thing could happen if you had two command
prompts open and each was pointed at a different version of the table. If
you were juggling several things at once, you might forget that the two
prompts pointed at different systems and not realize that the queries had
been done against different tables.
3. Is there any possibility that the query with the ORDER BY which you have
given us in your email is not the one which returned 0 rows and that it is
not identical to the other query that lacks the ORDER BY? The best approach
for reporting query problems is to copy and paste the query from your MySQL
environment into your email but some people simply type the query directly
into the email. That opens the possibility that you typed the query
inaccurately and may explain the problem.
Frankly, I find it quite unlikely that ORDER BY would fail so I would
strongly recommend that you consider the alternate scenarios I have
suggested and rule those out first. If you can rule them out, then you may
have encountered a real bug. Naturally, you should report that bug if you
can satisfy yourself that it really IS a bug.
--
Rhino
Another
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.5/334 - Release Date: 08/05/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
