MySQL Bugs: #10523: Incorrect sort from view

Bug #10523	Incorrect sort from view
Submitted:	10 May 2005 18:51	Modified:	21 Aug 2005 9:55
Reporter:	Waldemar Jankowski	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.3-beta-standard	OS:	Linux (linux)
Assigned to:	Sergei Golubchik	CPU Architecture:	Any

Description:
Create the following view:

CREATE VIEW test_view_product_company AS
select
vbi_ic_prod_list.Prod_ID,
vbi_ic_prod_list.Company_ID,
vbi_company.Company_Name
from vbi_ic_prod_list
left join vbi_company on
vbi_ic_prod_list.Company_ID = vbi_company.Company_ID
where vbi_ic_prod_list.Company_ID != '';

A describe on the view gives the following:

mysql> desc test_view_product_company;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| Prod_ID      | varchar(20) | NO   |     |         |       |
| Company_ID   | varchar(20) | NO   |     |         |       |
| Company_Name | varchar(60) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

A simple select query with an order by clause returns the rows in an incorrect order.

mysql> select Company_Name from test_view_product_company order by Company_Name;
+------------------------------------------+
| Company_Name                             |
+------------------------------------------+
| Tanya                                    |
| AIG Annuity Insurance                    |
| Banner Life Insurance Company            |
| Tanya                                    |
| AIG Annuity Insurance                    |
| Empire General                           |
| test                                     |
| AIG Annuity Insurance                    |
| Genworth Financial                       |
| test factor                              |
| American National Life Insurance Company |
| ING                                      |
+------------------------------------------+
12 rows in set (0.00 sec)

A work around is to create the view with the "order by" set right in the view:
DROP VIEW if exists test_view_product_company;
CREATE VIEW test_view_product_company AS
select
vbi_ic_prod_list.Prod_ID,
vbi_ic_prod_list.Company_ID,
vbi_company.Company_Name
from vbi_ic_prod_list
left join vbi_company on
vbi_ic_prod_list.Company_ID = vbi_company.Company_ID
where vbi_ic_prod_list.Company_ID != ''
order by vbi_company.Company_Name;

The query then returns the right rows in the right order:
mysql> select Company_Name from test_view_product_company order by Company_Name;
+------------------------------------------+
| Company_Name                             |
+------------------------------------------+
| AIG Annuity Insurance                    |
| AIG Annuity Insurance                    |
| AIG Annuity Insurance                    |
| American National Life Insurance Company |
| Banner Life Insurance Company            |
| Empire General                           |
| Genworth Financial                       |
| ING                                      |
| Tanya                                    |
| Tanya                                    |
| test                                     |
| test factor                              |
+------------------------------------------+
12 rows in set (0.00 sec)

Note:
Sorting on Prod_ID and Company_ID works fine.  It's just Company_Name that is a problem.

How to repeat:
Create a view without the "order by" clause. Try to select with an "order by" a nullable varchar.
The resulting sort order is incorrect.
See "Description" for details.

Can you please add the CREATE statements for the underlying tables (or maybe even a SQL dump with sample data) to this report?

Here is a mysqldump of the underlying tables:

--
-- Table structure for table `vbi_company`
--

DROP TABLE IF EXISTS `vbi_company`;
CREATE TABLE `vbi_company` (
  `Company_ID` varchar(20) NOT NULL default '',
  `Company_Name` varchar(60) NOT NULL default '',
  `Company_Notes` text NOT NULL,
  `Company_URL` varchar(60) NOT NULL default '',
  PRIMARY KEY  (`Company_ID`),
  UNIQUE KEY `IDX_Company_Name` (`Company_Name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 PACK_KEYS=1;

--
-- Dumping data for table `vbi_company`
--

INSERT INTO `vbi_company` VALUES ('1758-6676','Tanya','notes go here','http://www.richweb.com';);
INSERT INTO `vbi_company` VALUES ('1958-4982','1958-4982','','');
INSERT INTO `vbi_company` VALUES ('2753-8293','test','ffff','');
INSERT INTO `vbi_company` VALUES ('2776-4339','2776-4339','','');
INSERT INTO `vbi_company` VALUES ('4157-8941','4157-8941','','');
INSERT INTO `vbi_company` VALUES ('4862-4465','test factor','afsdaf','');
INSERT INTO `vbi_company` VALUES ('7147-6846','7147-6846','','');
INSERT INTO `vbi_company` VALUES ('7621-1597','7621-1597','','');
INSERT INTO `vbi_company` VALUES ('7833-4732','7833-4732','','');
INSERT INTO `vbi_company` VALUES ('9311-1414','9311-1414','','');
INSERT INTO `vbi_company` VALUES ('9414-8367','9414-8367','','');
INSERT INTO `vbi_company` VALUES ('aig1','AIG Annuity Insurance','','http://www.aiannuityaccess.com';);
INSERT INTO `vbi_company` VALUES ('AN','American National Life Insurance Company','','http://www.imo.anicoweb.com/';);
INSERT INTO `vbi_company` VALUES ('bli','Banner Life Insurance Company','','http://www.bannerlife.com';);
INSERT INTO `vbi_company` VALUES ('dog_food','Dog Food','','http://www.richweb.com';);
INSERT INTO `vbi_company` VALUES ('EG','Empire General','','');
INSERT INTO `vbi_company` VALUES ('Ge','Genworth Financial','','');
INSERT INTO `vbi_company` VALUES ('ING','ING','','');
--
-- Table structure for table `vbi_ic_prod_list`
--

DROP TABLE IF EXISTS `vbi_ic_prod_list`;
CREATE TABLE `vbi_ic_prod_list` (
  `Prod_ID` varchar(20) NOT NULL default '',
  `Company_ID` varchar(20) NOT NULL default '',
  `Prod_Desc` text NOT NULL,
  `Enable_Flag` varchar(1) NOT NULL default '',
  `Variable_Annuity` char(1) NOT NULL default '',
  `Fixed_Annuity` char(1) NOT NULL default '',
  `Life_Insurance` char(1) NOT NULL default '',
  `Long_Term_Care` char(1) NOT NULL default '',
  `Variable_Life_Insurance` char(1) NOT NULL default '',
  `Prod_Rank` int(2) NOT NULL default '10',
  PRIMARY KEY  (`Prod_ID`),
  KEY `Company_ID` (`Company_ID`),
  CONSTRAINT `vbi_ic_prod_list_ibfk_1` FOREIGN KEY (`Company_ID`) REFERENCES `vbi_company` (`Company_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 PACK_KEYS=1;

--
-- Dumping data for table `vbi_ic_prod_list`
--

INSERT INTO `vbi_ic_prod_list` VALUES ('1','aig1','<b>Life Insurance, Long
Term Care, Variable Life:</b><br>\r\nInt. Wholesaler:     800-220-6049 - Jim
Gilbert ext.\r\n212\r\n<br>\r\n','Y','','','Y','','N',10);
INSERT INTO `vbi_ic_prod_list` VALUES ('1368-6126','1758-6676','Its a fun
place to be.','Y','Y','','N','','',10);
INSERT INTO `vbi_ic_prod_list` VALUES
('1393-1174','4862-4465','werwerwer','Y','Y','','Y','','',10);
INSERT INTO `vbi_ic_prod_list` VALUES ('2','aig1','<b>Fixed &
Variable\r\nAnnuities:</b><br>\r\nInt. Wholesaler:
888-237-4210<br>\r\nExternal\r\nWholesaler: 800-630-2262 - John
Anderson\r\nGeneral Support:\r\n800-424-4990','Y','','Y','','','',1);
INSERT INTO `vbi_ic_prod_list` VALUES ('3','EG','<b>Fixed &
Variable\r\nAnnuities:</b><br>\r\nInt. Wholesaler:     800-833-4360 -
press\r\n2<br>\r\nExternal Wholesaler: 800-833-4360 - Doug Culp\r\nGeneral
Support:\r\n800-424-4990','Y','','Y','','','',10);
INSERT INTO `vbi_ic_prod_list` VALUES
('3134-2445','aig1','sd','N','','','','','',5);
INSERT INTO `vbi_ic_prod_list` VALUES ('4','bli','<b>Life Insurance, Long
Term Care, Variable Life:</b><br>\r\nInt. Wholesaler:\r\n800-220-6049 - Jim
Gilbert Ext. 212<br>\r\n\r\nEd Meakim  Ext.  225<br>\r\n Larry Anmuth
\r\nExt. 217  LTC\r\n\r\n<br>\r\n','Y','','','Y','Y','N',10);
INSERT INTO `vbi_ic_prod_list` VALUES ('5','ING','<b>Life Insurance, Long
Term Care, Variable Life:</b><br>\r\nInt. Wholesaler:\r\n800-220-6049 - Jim
Gilbert Ext. 212<br>\r\n\r\n','Y','','','Y','','',10);
INSERT INTO `vbi_ic_prod_list` VALUES ('5287-1182','1758-6676','Great
Place\r\n4235 Innslake Drive\r\nGlen Allen, VA
23060\r\n804.747.8592\r\nAttention: Tanya Noble-Marx','Y','N','Y','','','Y',10);
INSERT INTO `vbi_ic_prod_list` VALUES
('5646-9463','2753-8293','test','N','','Y','','','',10);
INSERT INTO `vbi_ic_prod_list` VALUES ('6','AN','<b>Fixed &\r\nVariable
Annuities:</b><br>\r\nInt. Wholesaler:\r\n888-000-0000<br>\r\nExternal
Wholesaler: 800-000-0000- \r\n\r\nGeneral Support: \r\n800-000-0000','Y','Y','Y','','','',10);
INSERT INTO `vbi_ic_prod_list` VALUES ('7','Ge','<b>Life Insurance, Long
Term Care, Variable Life:</b><br>\r\nInt. Wholesaler:     800-220-6049 - Jim
Gilbert Ext.\r\n212\r\n                                    Ed Meakim  Ext.
225\r\n\r\n','Y','','','Y','','Y',10);

Verified, also happens with latest 5.0bk, i've attached a complete test script
containing tables, view and failing query as dump.sql in the files section

Full test script

Attachment: dump.sql (application/octet-stream, text), 5.30 KiB.

Does the fix for Bug #7422 released in 5.0.8 cover this?

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

Additional info:

Apparently, it's fixed in 5.0.12
(test case produces correct reults for me)