1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
|
--source include/have_ujis.inc
--source include/have_innodb.inc
--disable_warnings
drop table if exists `｣ﾔ｣ｱ`;
drop table if exists `｣ﾔ｣ｲ`;
drop table if exists `｣ﾔ｣ｳ`;
drop table if exists `｣ﾔ｣ｴ`;
drop table if exists `｣ﾔ｣ｵ`;
drop table if exists `｣ﾔ｣ｶ`;
drop table if exists `｣ﾔ｣ｷ`;
drop table if exists `｣ﾔ｣ｸ`;
drop table if exists `｣ﾔ｣ｹ`;
--enable_warnings
#
# Test UNION with Japanese characters in ujis encoding
#
SET NAMES ujis;
SET character_set_database = ujis;
#InnoDB
CREATE TABLE `｣ﾔ｣ｱ` (`｣ﾃ｣ｱ` char(1), INDEX(`｣ﾃ｣ｱ`)) DEFAULT CHARSET = ujis engine = innodb;
CREATE TABLE `｣ﾔ｣ｲ` (`｣ﾃ｣ｱ` char(1), INDEX(`｣ﾃ｣ｱ`)) DEFAULT CHARSET = ujis engine = innodb;
CREATE TABLE `｣ﾔ｣ｳ` (`｣ﾃ｣ｱ` char(1), INDEX(`｣ﾃ｣ｱ`)) DEFAULT CHARSET = ujis engine = innodb;
INSERT INTO `｣ﾔ｣ｱ` VALUES('竺'),('竺'),('､｢'),('､｢'),('常ﾜ'),('常ﾜ');
INSERT INTO `｣ﾔ｣ｲ` VALUES('軸'),('軸'),('､､'),('､､'),('常ﾝ'),('常ﾝ');
INSERT INTO `｣ﾔ｣ｳ` VALUES('宍'),('宍'),('､ｦ'),('､ｦ'),('常ﾞ'),('常ﾞ');
SELECT * FROM `｣ﾔ｣ｱ` UNION DISTINCT SELECT * FROM `｣ﾔ｣ｲ` ORDER BY `｣ﾃ｣ｱ`;
SELECT * FROM `｣ﾔ｣ｱ` UNION ALL SELECT * FROM `｣ﾔ｣ｲ` ORDER BY `｣ﾃ｣ｱ`;
(SELECT * FROM `｣ﾔ｣ｱ`) UNION (SELECT * FROM `｣ﾔ｣ｲ`) UNION (SELECT '､ｦ') ORDER BY `｣ﾃ｣ｱ`;
(SELECT '､ｦ' AS `｣ﾃ｣ｱ`) UNION (SELECT * FROM `｣ﾔ｣ｱ`) UNION (SELECT * FROM `｣ﾔ｣ｲ`) ORDER BY `｣ﾃ｣ｱ`;
SELECT `｣ﾃ｣ｱ`, COUNT(*) FROM `｣ﾔ｣ｱ` GROUP BY `｣ﾃ｣ｱ` UNION
SELECT `｣ﾃ｣ｱ`, COUNT(*) FROM `｣ﾔ｣ｲ` GROUP BY `｣ﾃ｣ｱ` ORDER BY `｣ﾃ｣ｱ`;
SELECT * FROM `｣ﾔ｣ｱ` UNION DISTINCT
SELECT * FROM `｣ﾔ｣ｲ` UNION ALL
SELECT * FROM `｣ﾔ｣ｳ` ORDER BY `｣ﾃ｣ｱ`;
SELECT * FROM `｣ﾔ｣ｱ` UNION ALL
SELECT * FROM `｣ﾔ｣ｲ` UNION DISTINCT
SELECT * FROM `｣ﾔ｣ｳ` ORDER BY `｣ﾃ｣ｱ`;
SELECT * FROM `｣ﾔ｣ｱ` UNION SELECT REPEAT(`｣ﾃ｣ｱ`,5) FROM `｣ﾔ｣ｲ` ORDER BY `｣ﾃ｣ｱ`;
DROP TABLE `｣ﾔ｣ｱ` ;
DROP TABLE `｣ﾔ｣ｲ` ;
DROP TABLE `｣ﾔ｣ｳ` ;
#MyISAM
CREATE TABLE `｣ﾔ｣ｱ` (`｣ﾃ｣ｱ` char(1), INDEX(`｣ﾃ｣ｱ`)) DEFAULT CHARSET = ujis engine = MYISAM;
CREATE TABLE `｣ﾔ｣ｲ` (`｣ﾃ｣ｱ` char(1), INDEX(`｣ﾃ｣ｱ`)) DEFAULT CHARSET = ujis engine = MYISAM;
CREATE TABLE `｣ﾔ｣ｳ` (`｣ﾃ｣ｱ` char(1), INDEX(`｣ﾃ｣ｱ`)) DEFAULT CHARSET = ujis engine = MYISAM;
INSERT INTO `｣ﾔ｣ｱ` VALUES('竺'),('竺'),('､｢'),('､｢'),('常ﾜ'),('常ﾜ');
INSERT INTO `｣ﾔ｣ｲ` VALUES('軸'),('軸'),('､､'),('､､'),('常ﾝ'),('常ﾝ');
INSERT INTO `｣ﾔ｣ｳ` VALUES('宍'),('宍'),('､ｦ'),('､ｦ'),('常ﾞ'),('常ﾞ');
SELECT * FROM `｣ﾔ｣ｱ` UNION DISTINCT SELECT * FROM `｣ﾔ｣ｲ` ORDER BY `｣ﾃ｣ｱ`;
SELECT * FROM `｣ﾔ｣ｱ` UNION ALL SELECT * FROM `｣ﾔ｣ｲ` ORDER BY `｣ﾃ｣ｱ`;
(SELECT * FROM `｣ﾔ｣ｱ`) UNION (SELECT * FROM `｣ﾔ｣ｲ`) UNION (SELECT '､ｦ') ORDER BY `｣ﾃ｣ｱ`;
(SELECT '､ｦ' AS `｣ﾃ｣ｱ`) UNION (SELECT * FROM `｣ﾔ｣ｱ`) UNION (SELECT * FROM `｣ﾔ｣ｲ`) ORDER BY `｣ﾃ｣ｱ`;
SELECT `｣ﾃ｣ｱ`, COUNT(*) FROM `｣ﾔ｣ｱ` GROUP BY `｣ﾃ｣ｱ` UNION
SELECT `｣ﾃ｣ｱ`, COUNT(*) FROM `｣ﾔ｣ｲ` GROUP BY `｣ﾃ｣ｱ` ORDER BY `｣ﾃ｣ｱ`;
SELECT * FROM `｣ﾔ｣ｱ` UNION DISTINCT
SELECT * FROM `｣ﾔ｣ｲ` UNION ALL
SELECT * FROM `｣ﾔ｣ｳ` ORDER BY `｣ﾃ｣ｱ`;
SELECT * FROM `｣ﾔ｣ｱ` UNION ALL
SELECT * FROM `｣ﾔ｣ｲ` UNION DISTINCT
SELECT * FROM `｣ﾔ｣ｳ` ORDER BY `｣ﾃ｣ｱ`;
SELECT * FROM `｣ﾔ｣ｱ` UNION SELECT REPEAT(`｣ﾃ｣ｱ`,5) FROM `｣ﾔ｣ｲ` ORDER BY `｣ﾃ｣ｱ`;
DROP TABLE `｣ﾔ｣ｱ` ;
DROP TABLE `｣ﾔ｣ｲ` ;
DROP TABLE `｣ﾔ｣ｳ` ;
#MEMORY
CREATE TABLE `｣ﾔ｣ｱ` (`｣ﾃ｣ｱ` char(1), INDEX(`｣ﾃ｣ｱ`)) DEFAULT CHARSET = ujis engine = MEMORY;
CREATE TABLE `｣ﾔ｣ｲ` (`｣ﾃ｣ｱ` char(1), INDEX(`｣ﾃ｣ｱ`)) DEFAULT CHARSET = ujis engine = MEMORY;
CREATE TABLE `｣ﾔ｣ｳ` (`｣ﾃ｣ｱ` char(1), INDEX(`｣ﾃ｣ｱ`)) DEFAULT CHARSET = ujis engine = MEMORY;
INSERT INTO `｣ﾔ｣ｱ` VALUES('竺'),('竺'),('､｢'),('､｢'),('常ﾜ'),('常ﾜ');
INSERT INTO `｣ﾔ｣ｲ` VALUES('軸'),('軸'),('､､'),('､､'),('常ﾝ'),('常ﾝ');
INSERT INTO `｣ﾔ｣ｳ` VALUES('宍'),('宍'),('､ｦ'),('､ｦ'),('常ﾞ'),('常ﾞ');
SELECT * FROM `｣ﾔ｣ｱ` UNION DISTINCT SELECT * FROM `｣ﾔ｣ｲ` ORDER BY `｣ﾃ｣ｱ`;
SELECT * FROM `｣ﾔ｣ｱ` UNION ALL SELECT * FROM `｣ﾔ｣ｲ` ORDER BY `｣ﾃ｣ｱ`;
(SELECT * FROM `｣ﾔ｣ｱ`) UNION (SELECT * FROM `｣ﾔ｣ｲ`) UNION (SELECT '､ｦ') ORDER BY `｣ﾃ｣ｱ`;
(SELECT '､ｦ' AS `｣ﾃ｣ｱ`) UNION (SELECT * FROM `｣ﾔ｣ｱ`) UNION (SELECT * FROM `｣ﾔ｣ｲ`) ORDER BY `｣ﾃ｣ｱ`;
SELECT `｣ﾃ｣ｱ`, COUNT(*) FROM `｣ﾔ｣ｱ` GROUP BY `｣ﾃ｣ｱ` UNION
SELECT `｣ﾃ｣ｱ`, COUNT(*) FROM `｣ﾔ｣ｲ` GROUP BY `｣ﾃ｣ｱ` ORDER BY `｣ﾃ｣ｱ`;
SELECT * FROM `｣ﾔ｣ｱ` UNION DISTINCT
SELECT * FROM `｣ﾔ｣ｲ` UNION ALL
SELECT * FROM `｣ﾔ｣ｳ` ORDER BY `｣ﾃ｣ｱ`;
SELECT * FROM `｣ﾔ｣ｱ` UNION ALL
SELECT * FROM `｣ﾔ｣ｲ` UNION DISTINCT
SELECT * FROM `｣ﾔ｣ｳ` ORDER BY `｣ﾃ｣ｱ`;
SELECT * FROM `｣ﾔ｣ｱ` UNION SELECT REPEAT(`｣ﾃ｣ｱ`,5) FROM `｣ﾔ｣ｲ` ORDER BY `｣ﾃ｣ｱ`;
DROP TABLE `｣ﾔ｣ｱ` ;
DROP TABLE `｣ﾔ｣ｲ` ;
DROP TABLE `｣ﾔ｣ｳ` ;
# Test mixed strage engine
CREATE TABLE `｣ﾔ｣ｱ` (`｣ﾃ｣ｱ` char(1), INDEX(`｣ﾃ｣ｱ`)) DEFAULT CHARSET = ujis engine = InnoDB;
CREATE TABLE `｣ﾔ｣ｲ` (`｣ﾃ｣ｱ` char(1), INDEX(`｣ﾃ｣ｱ`)) DEFAULT CHARSET = ujis engine = MyISAM;
INSERT INTO `｣ﾔ｣ｱ` VALUES('竺'),('竺'),('､｢'),('､｢'),('常ﾜ'),('常ﾜ');
INSERT INTO `｣ﾔ｣ｲ` VALUES('軸'),('軸'),('､､'),('､､'),('常ﾝ'),('常ﾝ');
SELECT * FROM `｣ﾔ｣ｱ` UNION DISTINCT SELECT * FROM `｣ﾔ｣ｲ` ORDER BY `｣ﾃ｣ｱ`;
SELECT * FROM `｣ﾔ｣ｱ` UNION ALL SELECT * FROM `｣ﾔ｣ｲ` ORDER BY `｣ﾃ｣ｱ`;
DROP TABLE `｣ﾔ｣ｱ`;
DROP TABLE `｣ﾔ｣ｲ`;
|
