Sridhar, Wednesday, September 25, 2002, 12:23:29 AM, you wrote: SP> One of our MySQL db's containing BDB tables is exhibiting a strange SP> behavior. SP> A query consisting of an ORDER BY clause is returning wrong result set. SP> The SP> same query when used without an ORDER BY clause returns the correct SP> result SP> set.
SP> Environment: Sun Solaris 2.7, MySQL 3.23.51, Table Type = BerkleyDB SP> Given below is the query for which we are seeing this issue: SP> select orders.order_uid, orders.status, SP> (to_days(now()) - to_days(orders.verification_date)) as age SP> from orders SP> where orders.status = 'verified' SP> order by orders.verification_date; SP> The above query returns: SP> +-----------+--------+------+ SP> | order_uid | status | age | SP> +-----------+--------+------+ SP> | 130 | new | NULL | SP> | 130 | new | NULL | SP> | 130 | new | NULL | SP> | 130 | new | NULL | SP> | 130 | new | NULL | SP> +-----------+--------+------+ SP> What SHOULD have been returned is: SP> +-----------+----------+------+ SP> | order_uid | status | age | SP> +-----------+----------+------+ SP> | 57 | verified | 4 | SP> | 76 | verified | 4 | SP> | 79 | verified | 4 | SP> | 233 | verified | 3 | SP> | 234 | verified | 0 | SP> +-----------+----------+------+ SP> Currently i get around this issue by analyzing the BDB tables, but this SP> seems to be a temporary solution. The issue is intermittent, keeps SP> surfacing SP> again after a certain no of days. Did not find any related bugs with the SP> MySQL version we use (3.23.51). From what i can understand so far, the SP> index SP> statistics related to the BDB tables are not getting updated. SP> What can be done to correct this issue ? SP> Are there any other alternative's to this issue ? SP> Would converting the BerkleyDB (BDB) tables to InnoDB tables solve the SP> issue ? I tested your query on the test BDB table and it worked well, here is ther result that I got: +-----------+----------+------+ | order_uid | status | age | +-----------+----------+------+ | 5 | verified | 501 | | 4 | verified | 267 | +-----------+----------+------+ 2 rows in set (0.00 sec) Could you provide a repeatable test case? BTW if you send bug report, send output of SHOW CREATE TABLE or output of mysqldump, not the output of DESC table_name. I tested it on 3.23.52. -- 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 --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
