----- Original Message ----- From: "John McCaskey" <[EMAIL PROTECTED]> To: <mysql@lists.mysql.com> Sent: Tuesday, January 04, 2005 5:22 PM Subject: not all rows returned when using order by and null values?
> See below: > mysql> (SELECT avg FROM event_log_5minute_20050104 WHERE > monitor_id=479139 AND monitor_server_id=1 AND timestamp >= > 20050104080000 AND timestamp < 20050105080000 order by avg); > +---------+ > | avg | > +---------+ > | NULL | > | NULL | > | NULL | > | NULL | > | NULL | > | 55854.1 | > | 55854.1 | > | 63566.8 | > | 70157.6 | > | 121185 | > | 128803 | > | 172269 | > | 320097 | > +---------+ > 13 rows in set (0.00 sec) > > mysql> (SELECT avg FROM event_log_5minute_20050104 WHERE > monitor_id=479139 AND monitor_server_id=1 AND timestamp >= > 20050104080000 AND timestamp < 20050105080000) order by avg; > +---------+ > | avg | > +---------+ > | NULL | > | 55854.1 | > | 55854.1 | > | 63566.8 | > | 70157.6 | > | 121185 | > | 128803 | > | 172269 | > | 320097 | > +---------+ > 9 rows in set (0.00 sec) > > mysql> > > As you can see, I have 13 rows that match the query, if I put the order > by in the brackets it works fine. But when moved outside of them it > fails. > > The reason I have the brackets is this is a dynamically generated query > and may span tables ie: > > (SELECT avg FROM event_log_5minute_20050104 WHERE monitor_id=479139 AND > monitor_server_id=1 AND timestamp >= 20050104080000 AND timestamp < > 20050105080000) UNION ALL (SELECT avg FROM event_log_5minute_20050105 > WHERE monitor_id=479139 AND monitor_server_id=1 AND timestamp >= > 20050104080000 AND timestamp < 20050105080000) order by avg; > > Am I missing something and this is expected behavior and not a bug? I'm > using 4.0.18, I haven't checked the changelogs yet, maybe its been > fixed. > Wow, that's just weird if you ask me ;-) It's as if you added a 'DISTINCT' to the SELECT when move the ORDER BY outside of the brackets - except for the fact that it returns BOTH of 55854.1 values. Sorry, I don't have any idea what is going on there or if it is a bug that has been fixed already. ;-) Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
