On Tue, May 09, 2006 at 12:13:41PM -0400, Rhino wrote: > > ----- 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.
Then I did hit a bug, I tried various combinations of queries! It's very strange: mysql> SELECT n.nid 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 COUNT(n.nid) FROM node n WHERE n.type='image' AND (n.uid = 1 OR n.status = 1) ORDER BY n.created desc; +--------------+ | COUNT(n.nid) | +--------------+ | 53 | +--------------+ 1 row in set (0.00 sec) I thought it might be a bug in the mysql client, I tried to connect remotely but it failed too. > 1. Is there any possibility that a DELETE took place between the first > query and the second? No! > 2. Is there any possibility that the two queries took place against > different tables or databases or systems? No! > 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. I did a copy and paste from my terminal! > 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. Looks like it is really a bug and honestly, I'm shocked! I know it's FLOSS as I'm a FLOSS developer myself and nothing is perfect but order by!! Thanks for your reply, I'll report it. -- GNU/Linux registered user #224950 Proud Egyptian GNU/Linux User Group <www.eglug.org> Member. Life powered by Debian, Homepage: www.foolab.org -- Don't send me any attachment in Micro$oft (.DOC, .PPT) format please Read http://www.gnu.org/philosophy/no-word-attachments.html Preferable attachments: .PDF, .HTML, .TXT Thanx for adding this text to Your signature -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
