MySQL Bugs: #31203: left join returns error only on v5.0.45 but work fine on versions 3.x and 4.x

Bug #31203	left join returns error only on v5.0.45 but work fine on versions 3.x and 4.x
Submitted:	26 Sep 2007 10:00	Modified:	26 Sep 2007 10:39
Reporter:	Kevin Saitta	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S1 (Critical)
Version:	5.0.45	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Joins return Unknown column 'j.JournalId' in 'on clause' error.

select count(h.JournalId) as TimesViewed, j.JournalId,
  DATE_FORMAT(FROM_UNIXTIME(j.EntryDate),'%m/%d/%Y %h:%i %p') as EntryDate,
   j.EntryTitle, j.TopicID, t.Topic, j.IsPublic, j.DisplayAtEnd
   from journals j, journal_topics t left join journals_hits h on (h.JournalId = j.JournalId)
     where j.TopicId = t.TopicId
     and j.IsPublic = 'Y'
     and EntryDate >= UNIX_TIMESTAMP()-((60*60*24) * 365)
       group by j.JournalId, j.EntryTitle, j.TopicID, t.Topic, j.IsPublic, j.DisplayAtEnd
       order by j.EntryDate desc, t.Topic

CREATE TABLE `journal_topics` (
  `TopicId` int(10) unsigned NOT NULL default '0',
  `Topic` varchar(25) NOT NULL default '',
  `IsActive` enum('Y','N') NOT NULL default 'Y',
  PRIMARY KEY  (`TopicId`),
  UNIQUE KEY `unix_Topic` (`Topic`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `journals` (
  `JournalId` int(10) unsigned NOT NULL default '0',
  `EntryDate` int(11) default NULL,
  `EntryTitle` varchar(255) default NULL,
  `Entry` text,
  `TopicId` smallint(6) default NULL,
  `IsPublic` enum('Y','N') default 'N',
  `DisplayAtEnd` enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (`JournalId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `journals_hits` (
  `JournalId` int(10) unsigned NOT NULL default '0',
  `DateOfHit` int(10) unsigned NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

How to repeat:
Create the tables above on version 5.0.45 and run it.  The query works fine on 3.x and 4.x version of mySQL and fails on version 5.0.45

Suggested fix:
Fix the underlying source code for the database engine.

See http://dev.mysql.com/doc/refman/5.0/en/join.html

  [...]

  Join Processing Changes in MySQL 5.0.12

  Beginning with MySQL 5.0.12, natural joins and joins with USING, 
  including outer join variants, are processed according to the 
  SQL:2003 standard. The goal was to align the syntax and semantics 
  of MySQL with respect to NATURAL JOIN and JOIN ... USING according 
  to SQL:2003. However, these changes in join processing can result 
  in different output columns for some joins. Also, some queries that 
  appeared to work correctly in older versions must be rewritten to 
  comply with the standard. 

  [...]