1
--source include/have_utf8.inc
2
--source include/have_innodb.inc
4
drop table if exists `T1a`;
5
drop table if exists `T1b`;
6
drop table if exists `T2a`;
7
drop table if exists `T2b`;
8
drop table if exists `T3a`;
9
drop table if exists `T3b`;
10
drop table if exists `T4a`;
11
drop table if exists `T4b`;
12
drop table if exists `T5a`;
13
drop table if exists `T5b`;
14
drop table if exists `T6a`;
15
drop table if exists `T6b`;
16
drop table if exists `T7a`;
17
drop table if exists `T7b`;
18
drop table if exists `T8a`;
19
drop table if exists `T8b`;
20
drop table if exists `T9a`;
21
drop table if exists `T9b`;
25
# Test table JOIN using Japanese characters in utf8
29
SET character_set_database = utf8;
31
CREATE TABLE `T1a` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = InnoDB;
32
CREATE TABLE `T1b` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = InnoDB;
33
CREATE TABLE `T2a` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = InnoDB;
34
CREATE TABLE `T2b` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = InnoDB;
35
CREATE TABLE `T3a` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = InnoDB;
36
CREATE TABLE `T3b` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = InnoDB;
37
CREATE TABLE `T4a` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = MyISAM;
38
CREATE TABLE `T4b` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = MyISAM;
39
CREATE TABLE `T5a` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = MyISAM;
40
CREATE TABLE `T5b` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = MyISAM;
41
CREATE TABLE `T6a` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = MyISAM;
42
CREATE TABLE `T6b` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = MyISAM;
43
CREATE TABLE `T7a` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = MEMORY;
44
CREATE TABLE `T7b` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = MEMORY;
45
CREATE TABLE `T8a` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = MEMORY;
46
CREATE TABLE `T8b` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = MEMORY;
47
CREATE TABLE `T9a` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = MEMORY;
48
CREATE TABLE `T9b` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = MEMORY;
50
#insert the following data in each table
51
# jisx0201 hankaku-katakana data
53
# jisx0212 supplemental character data
55
INSERT INTO `T1a` VALUES ('ア'),('カ'),('サ');
56
INSERT INTO `T1b` VALUES ('ア');
57
INSERT INTO `T2a` VALUES ('あ'),('か'),('さ');
58
INSERT INTO `T2b` VALUES ('あ');
59
INSERT INTO `T3a` VALUES ('龔'),('龖'),('龗');
60
INSERT INTO `T3b` VALUES ('龔');
61
INSERT INTO `T4a` VALUES ('ア'),('カ'),('サ');
62
INSERT INTO `T4b` VALUES ('ア');
63
INSERT INTO `T5a` VALUES ('あ'),('か'),('さ');
64
INSERT INTO `T5b` VALUES ('あ');
65
INSERT INTO `T6a` VALUES ('龔'),('龖'),('龗');
66
INSERT INTO `T6b` VALUES ('龔');
67
INSERT INTO `T7a` VALUES ('ア'),('カ'),('サ');
68
INSERT INTO `T7b` VALUES ('ア');
69
INSERT INTO `T8a` VALUES ('あ'),('か'),('さ');
70
INSERT INTO `T8b` VALUES ('あ');
71
INSERT INTO `T9a` VALUES ('龔'),('龖'),('龗');
72
INSERT INTO `T9b` VALUES ('龔');
75
SELECT * FROM `T1a` JOIN `T1b`;
76
SELECT * FROM `T1a` INNER JOIN `T1b`;
77
SELECT * FROM `T1a` JOIN `T1b` USING (`C1`);
78
SELECT * FROM `T1a` INNER JOIN `T1b` USING (`C1`);
79
SELECT * FROM `T1a` CROSS JOIN `T1b`;
80
SELECT * FROM `T1a` LEFT JOIN `T1b` USING (`C1`);
81
SELECT * FROM `T1a` LEFT JOIN `T1b` ON (`T1a`.`C1` = `T1b`.`C1`);
82
SELECT * FROM `T1b` RIGHT JOIN `T1a` USING (`C1`);
83
SELECT * FROM `T1b` RIGHT JOIN `T1a` ON (`T1a`.`C1` = `T1b`.`C1`);
84
SELECT * FROM `T2a` JOIN `T2b`;
85
SELECT * FROM `T2a` INNER JOIN `T2b`;
86
SELECT * FROM `T2a` JOIN `T2b` USING (`C1`);
87
SELECT * FROM `T2a` INNER JOIN `T2b` USING (`C1`);
88
SELECT * FROM `T2a` CROSS JOIN `T2b`;
89
SELECT * FROM `T2a` LEFT JOIN `T2b` USING (`C1`);
90
SELECT * FROM `T2a` LEFT JOIN `T2b` ON (`T2a`.`C1` = `T2b`.`C1`);
91
SELECT * FROM `T2b` RIGHT JOIN `T2a` USING (`C1`);
92
SELECT * FROM `T2b` RIGHT JOIN `T2a` ON (`T2a`.`C1` = `T2b`.`C1`);
93
SELECT * FROM `T3a` JOIN `T3b`;
94
SELECT * FROM `T3a` INNER JOIN `T3b`;
95
SELECT * FROM `T3a` JOIN `T3b` USING (`C1`);
96
SELECT * FROM `T3a` INNER JOIN `T3b` USING (`C1`);
97
SELECT * FROM `T3a` CROSS JOIN `T3b`;
98
SELECT * FROM `T3a` LEFT JOIN `T3b` USING (`C1`);
99
SELECT * FROM `T3a` LEFT JOIN `T3b` ON (`T3a`.`C1` = `T3b`.`C1`);
100
SELECT * FROM `T3b` RIGHT JOIN `T3a` USING (`C1`);
101
SELECT * FROM `T3b` RIGHT JOIN `T3a` ON (`T3a`.`C1` = `T3b`.`C1`);
104
SELECT * FROM `T4a` JOIN `T4b`;
105
SELECT * FROM `T4a` INNER JOIN `T4b`;
106
SELECT * FROM `T4a` JOIN `T4b` USING (`C1`);
107
SELECT * FROM `T4a` INNER JOIN `T4b` USING (`C1`);
108
SELECT * FROM `T4a` CROSS JOIN `T4b`;
109
SELECT * FROM `T4a` LEFT JOIN `T4b` USING (`C1`);
110
SELECT * FROM `T4a` LEFT JOIN `T4b` ON (`T4a`.`C1` = `T4b`.`C1`);
111
SELECT * FROM `T4b` RIGHT JOIN `T4a` USING (`C1`);
112
SELECT * FROM `T4b` RIGHT JOIN `T4a` ON (`T4a`.`C1` = `T4b`.`C1`);
113
SELECT * FROM `T5a` JOIN `T5b`;
114
SELECT * FROM `T5a` INNER JOIN `T5b`;
115
SELECT * FROM `T5a` JOIN `T5b` USING (`C1`);
116
SELECT * FROM `T5a` INNER JOIN `T5b` USING (`C1`);
117
SELECT * FROM `T5a` CROSS JOIN `T5b`;
118
SELECT * FROM `T5a` LEFT JOIN `T5b` USING (`C1`);
119
SELECT * FROM `T5a` LEFT JOIN `T5b` ON (`T5a`.`C1` = `T5b`.`C1`);
120
SELECT * FROM `T5b` RIGHT JOIN `T5a` USING (`C1`);
121
SELECT * FROM `T5b` RIGHT JOIN `T5a` ON (`T5a`.`C1` = `T5b`.`C1`);
122
SELECT * FROM `T6a` JOIN `T6b`;
123
SELECT * FROM `T6a` INNER JOIN `T6b`;
124
SELECT * FROM `T6a` JOIN `T6b` USING (`C1`);
125
SELECT * FROM `T6a` INNER JOIN `T6b` USING (`C1`);
126
SELECT * FROM `T6a` CROSS JOIN `T6b`;
127
SELECT * FROM `T6a` LEFT JOIN `T6b` USING (`C1`);
128
SELECT * FROM `T6a` LEFT JOIN `T6b` ON (`T6a`.`C1` = `T6b`.`C1`);
129
SELECT * FROM `T6b` RIGHT JOIN `T6a` USING (`C1`);
130
SELECT * FROM `T6b` RIGHT JOIN `T6a` ON (`T6a`.`C1` = `T6b`.`C1`);
133
SELECT * FROM `T7a` JOIN `T7b`;
134
SELECT * FROM `T7a` INNER JOIN `T7b`;
135
SELECT * FROM `T7a` JOIN `T7b` USING (`C1`);
136
SELECT * FROM `T7a` INNER JOIN `T7b` USING (`C1`);
137
SELECT * FROM `T7a` CROSS JOIN `T7b`;
138
SELECT * FROM `T7a` LEFT JOIN `T7b` USING (`C1`);
139
SELECT * FROM `T7a` LEFT JOIN `T7b` ON (`T7a`.`C1` = `T7b`.`C1`);
140
SELECT * FROM `T7b` RIGHT JOIN `T7a` USING (`C1`);
141
SELECT * FROM `T7b` RIGHT JOIN `T7a` ON (`T7a`.`C1` = `T7b`.`C1`);
142
SELECT * FROM `T8a` JOIN `T8b`;
143
SELECT * FROM `T8a` INNER JOIN `T8b`;
144
SELECT * FROM `T8a` JOIN `T8b` USING (`C1`);
145
SELECT * FROM `T8a` INNER JOIN `T8b` USING (`C1`);
146
SELECT * FROM `T8a` CROSS JOIN `T8b`;
147
SELECT * FROM `T8a` LEFT JOIN `T8b` USING (`C1`);
148
SELECT * FROM `T8a` LEFT JOIN `T8b` ON (`T8a`.`C1` = `T8b`.`C1`);
149
SELECT * FROM `T8b` RIGHT JOIN `T8a` USING (`C1`);
150
SELECT * FROM `T8b` RIGHT JOIN `T8a` ON (`T8a`.`C1` = `T8b`.`C1`);
151
SELECT * FROM `T9a` JOIN `T9b`;
152
SELECT * FROM `T9a` INNER JOIN `T9b`;
153
SELECT * FROM `T9a` JOIN `T9b` USING (`C1`);
154
SELECT * FROM `T9a` INNER JOIN `T9b` USING (`C1`);
155
SELECT * FROM `T9a` CROSS JOIN `T9b`;
156
SELECT * FROM `T9a` LEFT JOIN `T9b` USING (`C1`);
157
SELECT * FROM `T9a` LEFT JOIN `T9b` ON (`T9a`.`C1` = `T9b`.`C1`);
158
SELECT * FROM `T9b` RIGHT JOIN `T9a` USING (`C1`);
159
SELECT * FROM `T9b` RIGHT JOIN `T9a` ON (`T9a`.`C1` = `T9b`.`C1`);