Bug #12964 bugzilla.org - broken in 5.0.12 works in 5.0.10
Submitted: 3 Sep 2005 9:19 Modified: 7 Sep 2005 18:14
Reporter: Are Casilla Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S2 (Serious)
Version:5.0.12-beta-standard OS:Linux (Debian version 2.6.8-2-686)
Assigned to: Paul DuBois CPU Architecture:Any

[3 Sep 2005 9:19] Are Casilla 
Description:
SELECT DISTINCT 
bugs.bug_id, bugs.groupset, bugs.bug_severity, bugs.priority, bugs.rep_platform, map_assigned_to.login_name, 
bugs.bug_status, bugs.resolution, bugs.short_desc FROM bugs, profiles map_assigned_to, profiles map_reporter 
LEFT JOIN profiles map_qa_contact ON bugs.qa_contact = map_qa_contact.userid LEFT JOIN cc selectVisible_cc ON 
                     bugs.bug_id = selectVisible_cc.bug_id AND 
                     selectVisible_cc.who = 3 WHERE ((bugs.groupset & 127) = bugs.groupset OR (bugs.reporter_accessible = 1 AND 
                     bugs.reporter = 3) OR (bugs.cclist_accessible = 1 AND selectVisible_cc.who = 3 
                     AND not isnull(selectVisible_cc.who)) OR (bugs.assigned_to = 3)) AND bugs.assigned_to = map_assigned_to.userid 
                     AND bugs.reporter = map_reporter.userid AND (bugs.bug_status = 'NEW' OR bugs.bug_status = 'ASSIGNED' OR 
                     bugs.bug_status = 'REOPENED') AND (bugs.product = 'AstBill') ORDER BY bugs.bug_status, bugs.priority, 
                     map_assigned_to.login_name, bugs.bug_id;

The above SQL works EXELENT in 5.10 and erarlier versions of mysql. It is part of standard bugzilla.org installation that have been using mysql for a very long time. 

IT IS NOT WORKING IN 5.0.12-beta-standard

ERROR 1054 (42S22): Unknown column 'bugs.qa_contact' in 'on clause'

The column 'bugs.qa_contact' is in the table.

How to repeat:
CREATE TABLE `bugs` (
  `bug_id` mediumint(9) NOT NULL auto_increment,
  `groupset` bigint(20) NOT NULL,
  `assigned_to` mediumint(9) NOT NULL,
  `bug_file_loc` text,
  `bug_severity` enum('blocker','critical','major','normal','minor','trivial','enhancement') NOT NULL,
  `bug_status` enum('UNCONFIRMED','NEW','ASSIGNED','REOPENED','RESOLVED','VERIFIED','CLOSED') NOT NULL,
  `creation_ts` datetime NOT NULL,
  `delta_ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `short_desc` mediumtext,
  `op_sys` enum('All','Windows 3.1','Windows 95','Windows 98','Windows ME','Windows 2000','Windows NT','Mac System 7','Mac System 7.5','Mac System 7.6.1','Mac System 8.0','Mac System 8.5','Mac System 8.6','Mac System 9.x','MacOS X','Linux','BSDI','FreeBSD','NetBSD','OpenBSD','AIX','BeOS','HP-UX','IRIX','Neutrino','OpenVMS','OS/2','OSF/1','Solaris','SunOS','other') NOT NULL,
  `priority` enum('P1','P2','P3','P4','P5') NOT NULL,
  `product` varchar(64) NOT NULL,
  `rep_platform` enum('All','DEC','HP','Macintosh','PC','SGI','Sun','Other') default NULL,
  `reporter` mediumint(9) NOT NULL,
  `version` varchar(64) NOT NULL,
  `component` varchar(50) NOT NULL,
  `resolution` enum('','FIXED','INVALID','WONTFIX','LATER','REMIND','DUPLICATE','WORKSFORME','MOVED') NOT NULL,
  `target_milestone` varchar(20) NOT NULL default '---',
  `qa_contact` mediumint(9) NOT NULL,
  `status_whiteboard` mediumtext NOT NULL,
  `votes` mediumint(9) NOT NULL,
  `keywords` mediumtext NOT NULL,
  `lastdiffed` datetime NOT NULL,
  `everconfirmed` tinyint(4) NOT NULL,
  `reporter_accessible` tinyint(4) NOT NULL default '1',
  `cclist_accessible` tinyint(4) NOT NULL default '1',
  PRIMARY KEY  (`bug_id`),
  KEY `assigned_to` (`assigned_to`),
  KEY `creation_ts` (`creation_ts`),
  KEY `delta_ts` (`delta_ts`),
  KEY `bug_severity` (`bug_severity`),
  KEY `bug_status` (`bug_status`),
  KEY `op_sys` (`op_sys`),
  KEY `priority` (`priority`),
  KEY `product` (`product`),
  KEY `reporter` (`reporter`),
  KEY `version` (`version`),
  KEY `component` (`component`),
  KEY `resolution` (`resolution`),
  KEY `target_milestone` (`target_milestone`),
  KEY `qa_contact` (`qa_contact`),
  KEY `votes` (`votes`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=36 ;

-- 
-- Dumping data for table `bugs`
-- 

INSERT INTO `bugs` VALUES (1, 0, 3, '', 'normal', 'RESOLVED', '2005-08-28 19:22:47', '2005-08-28 22:32:14', 'blablabla', 'All', 'P2', 'AstBill', 'PC', 1, '0.9.1', 'astentry', 'FIXED', '---', 0, '', 0, '', '2005-08-28 22:32:14', 1, 1, 1);
INSERT INTO `bugs` VALUES (27, 0, 1, '', 'critical', 'RESOLVED', '2005-08-31 23:03:38', '2005-08-31 23:31:52', 'BUGS: Friends & Incoming', 'All', 'P1', 'AstBill', 'PC', 3, '0.9.1', 'AstAdmin', 'FIXED', '---', 0, '', 0, '', '2005-08-31 23:31:52', 1, 1, 1);
INSERT INTO `bugs` VALUES (32, 0, 1, '', 'normal', 'NEW', '2005-09-01 12:43:26', '2005-09-01 13:15:52', 'Show CDR', 'All', 'P3', 'AstBill', 'PC', 3, '0.9.1', 'AstAdmin', '', '---', 0, '', 0, '', '2005-09-01 13:15:52', 1, 1, 1);
INSERT INTO `bugs` VALUES (34, 0, 3, '', 'normal', 'RESOLVED', '2005-09-01 15:17:48', '2005-09-02 13:02:24', 'Startup Script', 'All', 'P3', 'AstBill', 'PC', 3, '0.9.1', 'Server Scripts', 'FIXED', '---', 0, '', 0, '', '2005-09-02 13:02:24', 1, 1, 1);
INSERT INTO `bugs` VALUES (35, 0, 1, '', 'blocker', 'RESOLVED', '2005-09-02 13:47:11', '2005-09-02 15:45:22', 'Database: astdialplan.fri', 'All', 'P1', 'AstBill', 'PC', 3, '0.9.1', 'astentry', 'FIXED', '---', 0, '', 0, '', '2005-09-02 15:45:23', 1, 1, 1);

CREATE TABLE `cc` (
  `bug_id` mediumint(9) NOT NULL,
  `who` mediumint(9) NOT NULL,
  UNIQUE KEY `bug_id` (`bug_id`,`who`),
  KEY `who` (`who`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `profiles` (
  `userid` mediumint(9) NOT NULL auto_increment,
  `login_name` varchar(255) NOT NULL,
  `cryptpassword` varchar(34) default NULL,
  `realname` varchar(255) default NULL,
  `groupset` bigint(20) NOT NULL,
  `disabledtext` mediumtext NOT NULL,
  `mybugslink` tinyint(4) NOT NULL default '1',
  `blessgroupset` bigint(20) NOT NULL default '0',
  `emailflags` mediumtext,
  PRIMARY KEY  (`userid`),
  UNIQUE KEY `login_name` (`login_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

-- 
-- Dumping data for table `profiles`
-- 

INSERT INTO `profiles` VALUES (1, 'testtest@gmail.com', 'dfsadf23rf43f34f34f4', 'User', 9223372036854775807, '', 1, 0, 'ExcludeSelf~on~emailOwnerRemoveme~on~emailOwnerComments~on~emailOwnerAttachments~on~emailOwnerStatus~on~emailOwnerResolved~on~emailOwnerKeywords~on~emailOwnerCC~on~emailOwnerOther~on~emailReporterRemoveme~on~emailReporterComments~on~emailReporterAttachments~on~emailReporterStatus~on~emailReporterResolved~on~emailReporterKeywords~on~emailReporterCC~on~emailReporterOther~on~emailQAcontactRemoveme~on~emailQAcontactComments~on~emailQAcontactAttachments~on~emailQAcontactStatus~on~emailQAcontactResolved~on~emailQAcontactKeywords~on~emailQAcontactCC~on~emailQAcontactOther~on~emailCClistRemoveme~on~emailCClistComments~on~emailCClistAttachments~on~emailCClistStatus~on~emailCClistResolved~on~emailCClistKeywords~on~emailCClistCC~on~emailCClistOther~on~emailVoterRemoveme~on~emailVoterComments~on~emailVoterAttachments~on~emailVoterStatus~on~emailVoterResolved~on~emailVoterKeywords~on~emailVoterCC~on~emailVoterOther~on');

SELECT DISTINCT 
bugs.bug_id, bugs.groupset, bugs.bug_severity, bugs.priority, bugs.rep_platform, map_assigned_to.login_name, 
bugs.bug_status, bugs.resolution, bugs.short_desc FROM bugs, profiles map_assigned_to, profiles map_reporter 
LEFT JOIN profiles map_qa_contact ON bugs.qa_contact = map_qa_contact.userid LEFT JOIN cc selectVisible_cc ON 
                     bugs.bug_id = selectVisible_cc.bug_id AND 
                     selectVisible_cc.who = 3 WHERE ((bugs.groupset & 127) = bugs.groupset OR (bugs.reporter_accessible = 1 AND 
                     bugs.reporter = 3) OR (bugs.cclist_accessible = 1 AND selectVisible_cc.who = 3 
                     AND not isnull(selectVisible_cc.who)) OR (bugs.assigned_to = 3)) AND bugs.assigned_to = map_assigned_to.userid 
                     AND bugs.reporter = map_reporter.userid AND (bugs.bug_status = 'NEW' OR bugs.bug_status = 'ASSIGNED' OR 
                     bugs.bug_status = 'REOPENED') AND (bugs.product = 'AstBill') ORDER BY bugs.bug_status, bugs.priority, 
                     map_assigned_to.login_name, bugs.bug_id;
[4 Sep 2005 10:31] Dan Stillman 
I noticed this in my own code earlier today after upgrading to 5.0.12. I suspect it's not a bug so much as a side effect of bringing JOIN behavior up to spec, but, either way, it's a fairly major change from earlier versions that will break a lot of code. It seems to be an order of operations issue--the LEFT JOIN is treated as a nested join.

Here's a simpler test case:

CREATE TABLE A (`id` TINYINT);
CREATE TABLE B (`id` TINYINT);
CREATE TABLE C (`id` TINYINT);
INSERT INTO A VALUES (1),(2),(3);
INSERT INTO B VALUES (1),(2),(3);
INSERT INTO C VALUES (3);

SELECT A.id,B.id,C.id FROM A,B LEFT JOIN C USING (id) WHERE A.id=B.id; 
+------+------+------+
| id   | id   | id   |
+------+------+------+
|    1 |    1 | NULL |
|    2 |    2 | NULL |
|    3 |    3 |    3 |
+------+------+------+

However, in 5.0.12, the following query fails:

SELECT A.id,B.id,C.id FROM A,B LEFT JOIN C ON (C.id=A.id) WHERE A.id=B.id

ERROR 1054 (42S22): Unknown column 'A.id' in 'on clause'

In earlier versions, it produces the same result as the previous query. Putting parentheses around the A,B fixes the problem (as would using an explicit NATURAL JOIN):

SELECT A.id,B.id,C.id FROM (A,B) LEFT JOIN C ON (C.id=A.id) WHERE A.id=B.id; 
+------+------+------+
| id   | id   | id   |
+------+------+------+
|    1 |    1 | NULL |
|    2 |    2 | NULL |
|    3 |    3 |    3 |
+------+------+------+

Assuming this is correct, perhaps documenting the change would be a good idea, as again, it will break a lot of code that relied on the previous behavior.
[4 Sep 2005 14:58] Hartmut Holzgraefe 
related to the following 5.0.12 changelog item:

* Natural joins and joins with USING, including outer join variants, now are processed according to the SQL:2003 standard. (Bug #4789, Bug #6136, Bug #6276, Bug #6495, Bug #6558, Bug #9978, Bug #10646, Bug #10972, Bug #11710)

maybe this should be made more prominent in the changelog section?

(changed to documentation issue)
[7 Sep 2005 18:14] Paul DuBois 
I'm moving the changelog entry to be more prominent
in the 5.0.12 changes section. I've also added a slight
variant of Dan's example to illustrate a query that now
fails and to show how it can be rewritten.

I'll add similar material to the "Upgrading from 4.1" in
the installation chapter.