Even more interesting...: 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 | | 43282 | | 55854.1 | | 55854.1 | | 63566.8 | | 70157.6 | | 121185 | | 124671 | | 128803 | | 172269 | | 200622 | | 211235 | | 273802 | | 320097 | | 324012 | | 422318 | | 465117 | | 543541 | | 578753 | | 606568 | | 634915 | | 698947 | | 746309 | | 882563 | | 1.18043e+06 | | 1.50719e+06 | | 1.54081e+06 | | 1.60248e+06 | | 1.68207e+06 | +-------------+ 29 rows in set (0.00 sec)
mysql> The above returns only 1 of 5 nulls actually in my table... but if I do: mysql> (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 NULL) order by avg; +-------------+ | avg | +-------------+ | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | 43282 | | 55854.1 | | 55854.1 | | 63566.8 | | 70157.6 | | 121185 | | 124671 | | 128803 | | 172269 | | 200622 | | 211235 | | 273802 | | 320097 | | 324012 | | 422318 | | 465117 | | 543541 | | 578753 | | 606568 | | 634915 | | 698947 | | 746309 | | 882563 | | 1.18043e+06 | | 1.50719e+06 | | 1.54081e+06 | | 1.60248e+06 | | 1.68207e+06 | +-------------+ 34 rows in set (0.00 sec) Now I get all 5, plus the one I added with the union... so it appears to only affect queries that have () like a union, but do not actually have a union? On Tue, 2005-01-04 at 15:40 -0800, John McCaskey wrote: > 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 > -- 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]
