On Tue, 2005-01-04 at 17:43 -0500, Rhino wrote: > ----- 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. ;-) >
Yeah, thats exactly what it is like, and its causing my computations to be corrupted. I haven't been able to come up with a good workaround that doesn't result in a significant slowdown in my application (these queries are run several million times daily... using a limit x,1 to select the 95th or 99th percentile value, and then store it in another table). If anyone else has ideas please comment! Thanks. > Rhino > -- John A. McCaskey Software Development Engineer Klir Technologies, Inc. [EMAIL PROTECTED] 206.902.2027 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
