Debian Codegraph @

mysql-test/suite/jp/t/jp_subquery_utf8.test

raw
1 --source include/have_utf8.inc
2 --source include/have_innodb.inc
3
4 --disable_warnings
5 drop table if exists `T1`;
6 drop table if exists `T2`;
7 drop table if exists `T3`;
8 drop table if exists `T4`;
9 drop table if exists `T5`;
10 drop table if exists `T6`;
11 drop table if exists `T7`;
12 drop table if exists `T8`;
13 drop table if exists `T9`;
14 --enable_warnings
15
16 #
17 # Test subquery using Japanese characters in utf8 encoding
18 #
19
20 SET NAMES utf8;
21 SET character_set_database = utf8;
22
23 CREATE TABLE `T1a` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = innodb;
24 CREATE TABLE `T1b` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = innodb;
25 CREATE TABLE `T2a` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = innodb;
26 CREATE TABLE `T2b` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = innodb;
27 CREATE TABLE `T3a` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = innodb;
28 CREATE TABLE `T3b` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = innodb;
29 CREATE TABLE `T4a` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = myisam;
30 CREATE TABLE `T4b` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = myisam;
31 CREATE TABLE `T5a` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = myisam;
32 CREATE TABLE `T5b` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = myisam;
33 CREATE TABLE `T6a` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = myisam;
34 CREATE TABLE `T6b` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = myisam;
35 CREATE TABLE `T7a` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = MEMORY;
36 CREATE TABLE `T7b` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = MEMORY;
37 CREATE TABLE `T8a` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = MEMORY;
38 CREATE TABLE `T8b` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = MEMORY;
39 CREATE TABLE `T9a` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = MEMORY;
40 CREATE TABLE `T9b` (`C1` char(1) PRIMARY KEY) DEFAULT CHARSET = utf8 engine = MEMORY;
41
42 #insert the following data in each table
43 # jisx0201 hankaku-katakana data
44 # jisx0208 data
45 # jisx0212 supplemental character data
46
47 INSERT INTO `T1a` VALUES ('ア'),('カ'),('サ');
48 INSERT INTO `T1b` VALUES ('ア');
49 INSERT INTO `T2a` VALUES ('あ'),('か'),('さ');
50 INSERT INTO `T2b` VALUES ('あ');
51 INSERT INTO `T3a` VALUES ('龔'),('龖'),('龗');
52 INSERT INTO `T3b` VALUES ('龔');
53 INSERT INTO `T4a` VALUES ('ア'),('カ'),('サ');
54 INSERT INTO `T4b` VALUES ('ア');
55 INSERT INTO `T5a` VALUES ('あ'),('か'),('さ');
56 INSERT INTO `T5b` VALUES ('あ');
57 INSERT INTO `T6a` VALUES ('龔'),('龖'),('龗');
58 INSERT INTO `T6b` VALUES ('龔');
59 INSERT INTO `T7a` VALUES ('ア'),('カ'),('サ');
60 INSERT INTO `T7b` VALUES ('ア');
61 INSERT INTO `T8a` VALUES ('あ'),('か'),('さ');
62 INSERT INTO `T8b` VALUES ('あ');
63 INSERT INTO `T9a` VALUES ('龔'),('龖'),('龗');
64 INSERT INTO `T9b` VALUES ('龔');
65
66 #Test for innodb
67 SELECT `C1` FROM `T1a` WHERE `C1` IN (SELECT `C1` FROM `T1b`);
68 SELECT `C1` FROM `T1a` WHERE EXISTS (SELECT `C1` FROM `T1b` WHERE `T1a`.`C1` = `T1b`.`C1`);
69 SELECT `C1` FROM `T1a` WHERE NOT EXISTS (SELECT `C1` FROM `T1b` WHERE `T1a`.`C1` = `T1b`.`C1`);
70 SELECT `C1` FROM `T2a` WHERE `C1` IN (SELECT `C1` FROM `T2b`);
71 SELECT `C1` FROM `T2a` WHERE EXISTS (SELECT `C1` FROM `T2b` WHERE `T2a`.`C1` = `T2b`.`C1`);
72 SELECT `C1` FROM `T2a` WHERE NOT EXISTS (SELECT `C1` FROM `T2b` WHERE `T2a`.`C1` = `T2b`.`C1`);
73 SELECT `C1` FROM `T3a` WHERE `C1` IN (SELECT `C1` FROM `T3b`);
74 SELECT `C1` FROM `T3a` WHERE EXISTS (SELECT `C1` FROM `T3b` WHERE `T3a`.`C1` = `T3b`.`C1`);
75 SELECT `C1` FROM `T3a` WHERE NOT EXISTS (SELECT `C1` FROM `T3b` WHERE `T3a`.`C1` = `T3b`.`C1`);
76 #Test for myisam
77 SELECT `C1` FROM `T4a` WHERE `C1` IN (SELECT `C1` FROM `T4b`);
78 SELECT `C1` FROM `T4a` WHERE EXISTS (SELECT `C1` FROM `T4b` WHERE `T4a`.`C1` = `T4b`.`C1`);
79 SELECT `C1` FROM `T4a` WHERE NOT EXISTS (SELECT `C1` FROM `T4b` WHERE `T4a`.`C1` = `T4b`.`C1`);
80 SELECT `C1` FROM `T5a` WHERE `C1` IN (SELECT `C1` FROM `T5b`);
81 SELECT `C1` FROM `T5a` WHERE EXISTS (SELECT `C1` FROM `T5b` WHERE `T5a`.`C1` = `T5b`.`C1`);
82 SELECT `C1` FROM `T5a` WHERE NOT EXISTS (SELECT `C1` FROM `T5b` WHERE `T5a`.`C1` = `T5b`.`C1`);
83 SELECT `C1` FROM `T6a` WHERE `C1` IN (SELECT `C1` FROM `T6b`);
84 SELECT `C1` FROM `T6a` WHERE EXISTS (SELECT `C1` FROM `T6b` WHERE `T6a`.`C1` = `T6b`.`C1`);
85 SELECT `C1` FROM `T6a` WHERE NOT EXISTS (SELECT `C1` FROM `T6b` WHERE `T6a`.`C1` = `T6b`.`C1`);
86 #Test for memory
87 SELECT `C1` FROM `T7a` WHERE `C1` IN (SELECT `C1` FROM `T7b`);
88 SELECT `C1` FROM `T7a` WHERE EXISTS (SELECT `C1` FROM `T7b` WHERE `T7a`.`C1` = `T7b`.`C1`);
89 SELECT `C1` FROM `T7a` WHERE NOT EXISTS (SELECT `C1` FROM `T7b` WHERE `T7a`.`C1` = `T7b`.`C1`);
90 SELECT `C1` FROM `T8a` WHERE `C1` IN (SELECT `C1` FROM `T8b`);
91 SELECT `C1` FROM `T8a` WHERE EXISTS (SELECT `C1` FROM `T8b` WHERE `T8a`.`C1` = `T8b`.`C1`);
92 SELECT `C1` FROM `T8a` WHERE NOT EXISTS (SELECT `C1` FROM `T8b` WHERE `T8a`.`C1` = `T8b`.`C1`);
93 SELECT `C1` FROM `T9a` WHERE `C1` IN (SELECT `C1` FROM `T9b`);
94 SELECT `C1` FROM `T9a` WHERE EXISTS (SELECT `C1` FROM `T9b` WHERE `T9a`.`C1` = `T9b`.`C1`);
95 SELECT `C1` FROM `T9a` WHERE NOT EXISTS (SELECT `C1` FROM `T9b` WHERE `T9a`.`C1` = `T9b`.`C1`);
96
97 DROP TABLE `T1a`;
98 DROP TABLE `T1b`;
99 DROP TABLE `T2a`;
100 DROP TABLE `T2b`;
101 DROP TABLE `T3a`;
102 DROP TABLE `T3b`;
103 DROP TABLE `T4a`;
104 DROP TABLE `T4b`;
105 DROP TABLE `T5a`;
106 DROP TABLE `T5b`;
107 DROP TABLE `T6a`;
108 DROP TABLE `T6b`;
109 DROP TABLE `T7a`;
110 DROP TABLE `T7b`;
111 DROP TABLE `T8a`;
112 DROP TABLE `T8b`;
113 DROP TABLE `T9a`;
114 DROP TABLE `T9b`;