#2621 (ok 2.11.5) Incorrect sorting with AS

[warforge00]

CREATE TABLE `table1` (
`ID` varchar(40) NOT NULL,
`DistributorPartNumber` varchar(40) NOT NULL,
`DistributorID` varchar(10) NOT NULL,
PRIMARY KEY (`ID`,`DistributorPartNumber`,`DistributorID`),
UNIQUE KEY `DistributorPartNumberID` (`DistributorPartNumber`,`DistributorID`),
KEY `DistributorID` (`DistributorID`),
KEY `DistributorPartNumber` (`DistributorPartNumber`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `table1` VALUES('1', '123456', 'C00040');
INSERT INTO `table1` VALUES('2', '123457', 'C00040');
INSERT INTO `table1` VALUES('3', '123458', 'C00040');

CREATE TABLE `table2` (
`DefinitionID` bigint(20) unsigned NOT NULL auto_increment,
`ProductID` int(10) unsigned NOT NULL,
`ID` varchar(40) NOT NULL,
`DistributorPartNumber` varchar(40) NOT NULL,
`DistributorID` varchar(10) NOT NULL,
PRIMARY KEY (`DefinitionID`),
UNIQUE KEY `DistributorPartNumberID` (`DistributorPartNumber`,`DistributorID`),
KEY `ProductID` (`ProductID`),
KEY `ID` (`ID`),
KEY `DistributorID` (`DistributorID`),
KEY `DistributorPartNumber` (`DistributorPartNumber`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `table2` VALUES(1, 1, '123456', '1', '1');
INSERT INTO `table2` VALUES(2, 2, '123458', '2', '1');

Using the above tables and data, run the following SQL statement.

SELECT t2.DefinitionID, t2.ID AS PID, t1.ID AS MID FROM table2 t2 LEFT JOIN table1 t1 ON t2.DistributorPartNumber = t1.DistributorPartNumber AND t1.DistributorID = 'C00040' WHERE t2.DistributorID =1

This statement will execute correctly returning 2 rows. If you click to sort by "PID", you get an error saying

#1054 - Unknown column 't2.PID' in 'order clause', which is invalid.

The correct sorting should be
"ORDER BY PID" or "ORDER BY t2.ID"

[Marc Delisle]

Logged In: YES
user_id=210714
Originator: NO

Fixed in subversion, thanks for reporting.