FYI, I have now verified this bug (?) occurs in 4.0.23 as well. On Tue, 2005-01-04 at 15:03 -0800, John McCaskey wrote: > 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 > -- 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]
