"van der Scheun, Willem (GXS)" <[EMAIL PROTECTED]> wrote: > I'm new to mysql and I just installed 4.1.1 and ran into trouble combining a > subquery and 'order by'. I guess the 3 queries below show my problem > > mysql> SELECT host,facility,priority,date,time FROM logs WHERE host=(select > ip from hostip where host='ams602.avctr.gxs.com'); > +---------------+----------+----------+------------+----------+ > | host | facility | priority | date | time | > +---------------+----------+----------+------------+----------+ > | 204.90.248.18 | auth | info | 2004-03-06 | 02:00:03 | > | 204.90.248.18 | auth | info | 2004-03-09 | 02:00:03 | > | 204.90.248.18 | mail | info | 2004-03-09 | 04:15:08 | > | 204.90.248.18 | mail | info | 2004-03-09 | 04:15:09 | > | 204.90.248.18 | auth | info | 2004-03-09 | 09:17:26 | > +---------------+----------+----------+------------+----------+ > 5 rows in set (0.01 sec) > > mysql> SELECT host,facility,priority,date,time FROM logs WHERE host=(select > ip from hostip where host='ams602.avctr.gxs.com') order by date; > Empty set (0.00 sec) > > mysql> SELECT host,facility,priority,date,time FROM logs WHERE host=(select > ip from hostip where host='ams602.avctr.gxs.com') and facility='auth' order > by date; > +---------------+----------+----------+------------+----------+ > | host | facility | priority | date | time | > +---------------+----------+----------+------------+----------+ > | 204.90.248.18 | auth | info | 2004-03-06 | 02:00:03 | > | 204.90.248.18 | auth | info | 2004-03-09 | 02:00:03 | > | 204.90.248.18 | auth | info | 2004-03-09 | 09:17:26 | > +---------------+----------+----------+------------+----------+ > 3 rows in set (0.01 sec) > > The first query selects a few records from a syslog database where entries > are stored with the IP address, but which I want to search using the > hostname. The second query wants to order the output by date but to my big > surprise does not give any results. When I extend the query with a select on > a second field and do the 'order by date' I do get a result. > > Am I missing something here?
Looks like a bug. Could you create a repeatable test case? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
