MySQL Bugs: #12661: ORDER BY clause returns 0 rows

Bug #12661	ORDER BY clause returns 0 rows
Submitted:	18 Aug 2005 22:58	Modified:	12 Sep 2005 14:23
Reporter:	Mr Wakazula	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.11	OS:	Windows (Windows 2003 (SP1))
Assigned to:		CPU Architecture:	Any

Description:
The following query returns 0 rows:
SELECT *
FROM log_application aa
JOIN log_severity bb ON aa.log_severity_id = bb.log_severity_id
ORDER BY aa.log_id desc

The following query returns 383 rows:
SELECT *
FROM log_application aa
JOIN log_severity bb ON aa.log_severity_id = bb.log_severity_id

I have spent some time troubleshooting this problem, but am unable to isolate it's cause.  Is it the view? 

How to repeat:
(1) assign user all "available privileges" to schema in question
(2) create and populate the following:

CREATE TABLE `log` (
  `log_id` bigint(20) unsigned NOT NULL auto_increment,
  `log_severity_id` bigint(20) unsigned NOT NULL,
  `log_event_id` bigint(20) unsigned NOT NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY  (`log_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `log_application_data` (
  `log_id` bigint(20) unsigned NOT NULL,
  `context` varchar(128) default NULL,
  `message` varchar(4096) NOT NULL,
  PRIMARY KEY  (`log_id`),
  CONSTRAINT `FK_log_id` FOREIGN KEY (`log_id`) REFERENCES `log` (`log_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='InnoDB free: 407552 kB;';

CREATE ALGORITHM=UNDEFINED VIEW `feed_engine`.`log_application` AS select `l`.`log_id` AS `log_id`,`l`.`log_severity_id` AS `log_severity_id`,`l`.`log_event_id` AS `log_event_id`,`l`.`timestamp` AS `timestamp`,`lad`.`context` AS `context`,`lad`.`message` AS `message` from (`feed_engine`.`log` `l` join `feed_engine`.`log_application_data` `lad` on((`l`.`log_id` = `lad`.`log_id`)));

(3) Run the queries outlined in the "Description"

Could you please provide feed_engine definition ?

I'm not entirely sure what you require.  feed_engine is a schema.

CREATE DATABASE `feed_engine` /*!40100 DEFAULT CHARACTER SET utf8 */;

`log_application_data` and `log` tables are of type: InnoDB

Sorry. But I need the definition of log_severity now.

as requested:

DROP TABLE IF EXISTS `feed_engine`.`log_severity`;
CREATE TABLE `log_severity` (
  `log_severity_id` bigint(20) unsigned NOT NULL auto_increment,
  `log_severity` varchar(16) NOT NULL,
  PRIMARY KEY  (`log_severity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This bug seem duplicat as:

http://bugs.mysql.com/bug.php?id=12941

however with my own data I was unable to repeat.
Could you please provide a dump file with insert command for
to run tha offended query. Also if that query works converting
the table to MyISAM then is the same as the bug mentioned
above.

Thanks in advance.

Unfortunately we are currently in a launch cycle and I do not have time to convert the tables to ISAM.  Perhaps when things settle down a little.

Hopefully the file I uploaded as per Miguel Solorzano's request will be useful.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.11-beta-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT *
    -> FROM log_application aa
    -> JOIN log_severity bb ON aa.log_severity_id = bb.log_severity_id
    -> ORDER BY aa.log_id desc
    -> ;
Empty set (0.19 sec)

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11 to server version: 5.0.13-beta-nt-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT *
    -> FROM log_application aa
    -> JOIN log_severity bb ON aa.log_severity_id = bb.log_severity_id
    -> ORDER BY aa.log_id desc;

<cut>

|    485 |               1 |            2 | 2005-08-17 16:54:00 | Terminate                                               |Terminating aggregator service... |               1 | Information  |
|    484 |               1 |            2 | 2005-08-17 16:54:00 | Stop                                                    |Aggregator service is no longer running. |               1 | Information  |

<cut>

383 rows in set (0.03 sec)