Description:
When I use UNION with ORDER BY in each table, sorting does not work at all.
How to repeat:
DROP TABLE IF EXISTS `cms_db`;
CREATE TABLE `cms_db` (
`id` tinyint(3) unsigned NOT NULL auto_increment,
`server_id` smallint(5) unsigned NOT NULL default '0',
`name` varchar(20) collate cp1251_ukrainian_ci NOT NULL default '',
`description_ru` varchar(255) collate cp1251_ukrainian_ci NOT NULL default '',
PRIMARY KEY (`id`),
KEY `server_id` (`server_id`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COLLATE=cp1251_ukrainian_ci COMMENT='Databases list';
INSERT INTO `cms_db` VALUES (1, 1, 'nau', 'Основная база данных');
DROP TABLE IF EXISTS `cms_table`;
CREATE TABLE `cms_table` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`db_id` smallint(5) unsigned NOT NULL default '0',
`name` char(50) collate cp1251_ukrainian_ci NOT NULL default '',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
UNIQUE KEY `name_2` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COLLATE=cp1251_ukrainian_ci COMMENT='Таблицы которые обрабатываются';
INSERT INTO `cms_table` VALUES (1, 1, 'admin_structure');
INSERT INTO `cms_table` VALUES (3, 1, 'admin_auth_group');
INSERT INTO `cms_table` VALUES (7, 1, 'admin_auth_user');
INSERT INTO `cms_table` VALUES (11, 1, 'cms_table');
INSERT INTO `cms_table` VALUES (14, 1, 'admin_vocabulary');
INSERT INTO `cms_table` VALUES (15, 1, 'cms_field');
INSERT INTO `cms_table` VALUES (18, 1, 'cms_regexp');
INSERT INTO `cms_table` VALUES (20, 1, 'admin_auth_action');
INSERT INTO `cms_table` VALUES (29, 1, 'site_structure');
INSERT INTO `cms_table` VALUES (187, 1, 'news_message');
INSERT INTO `cms_table` VALUES (190, 1, 'template_design');
INSERT INTO `cms_table` VALUES (222, 1, 'cms_db');
INSERT INTO `cms_table` VALUES (231, 1, 'cms_server');
INSERT INTO `cms_table` VALUES (265, 1, 'template_page');
INSERT INTO `cms_table` VALUES (292, 1, 'admin_auth_action_change');
INSERT INTO `cms_table` VALUES (293, 1, 'admin_auth_action_view');
INSERT INTO `cms_table` VALUES (296, 1, 'admin_auth_action_event');
INSERT INTO `cms_table` VALUES (297, 1, 'admin_auth_event_group');
INSERT INTO `cms_table` VALUES (298, 1, 'admin_auth_event_file');
INSERT INTO `cms_table` VALUES (299, 1, 'admin_auth_group_action');
INSERT INTO `cms_table` VALUES (353, 1, 'news_nauka');
INSERT INTO `cms_table` VALUES (350, 1, 'language_interface');
INSERT INTO `cms_table` VALUES (351, 1, 'language_list');
INSERT INTO `cms_table` VALUES (352, 1, 'language_usage');
(
SELECT CONCAT( 'cms_table', id ) AS id, id AS real_id, CONCAT( 'cms_db', db_id ) AS parent, name AS name
FROM cms_table
ORDER BY name ASC
)
UNION (
SELECT CONCAT( 'cms_db', id ) AS id, 0 AS real_id, CONCAT( 'cms_server', server_id ) AS parent, name AS name
FROM cms_db
ORDER BY name ASC
)
