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
104
105
106
107
108
109
110
111
112
|
--source include/have_sjis.inc
--source include/have_innodb.inc
--character_set sjis
--disable_warnings
drop table if exists `sP`;
drop table if exists `sQ`;
drop table if exists `sR`;
drop table if exists `sS`;
drop table if exists `sT`;
drop table if exists `sU`;
drop table if exists `sV`;
drop table if exists `sW`;
drop table if exists `sX`;
--enable_warnings
#
# Test subquery using Japanese characters in sjis encoding
#
SET NAMES sjis;
SET character_set_database = sjis;
CREATE TABLE `sPa` (`bP` char(1) PRIMARY KEY) DEFAULT CHARSET = sjis engine = innodb;
CREATE TABLE `sPb` (`bP` char(1) PRIMARY KEY) DEFAULT CHARSET = sjis engine = innodb;
CREATE TABLE `sQa` (`bP` char(1) PRIMARY KEY) DEFAULT CHARSET = sjis engine = innodb;
CREATE TABLE `sQb` (`bP` char(1) PRIMARY KEY) DEFAULT CHARSET = sjis engine = innodb;
CREATE TABLE `sRa` (`bP` char(1) PRIMARY KEY) DEFAULT CHARSET = sjis engine = innodb;
CREATE TABLE `sRb` (`bP` char(1) PRIMARY KEY) DEFAULT CHARSET = sjis engine = innodb;
CREATE TABLE `sSa` (`bP` char(1) PRIMARY KEY) DEFAULT CHARSET = sjis engine = myisam;
CREATE TABLE `sSb` (`bP` char(1) PRIMARY KEY) DEFAULT CHARSET = sjis engine = myisam;
CREATE TABLE `sTa` (`bP` char(1) PRIMARY KEY) DEFAULT CHARSET = sjis engine = myisam;
CREATE TABLE `sTb` (`bP` char(1) PRIMARY KEY) DEFAULT CHARSET = sjis engine = myisam;
CREATE TABLE `sUa` (`bP` char(1) PRIMARY KEY) DEFAULT CHARSET = sjis engine = myisam;
CREATE TABLE `sUb` (`bP` char(1) PRIMARY KEY) DEFAULT CHARSET = sjis engine = myisam;
CREATE TABLE `sVa` (`bP` char(1) PRIMARY KEY) DEFAULT CHARSET = sjis engine = MEMORY;
CREATE TABLE `sVb` (`bP` char(1) PRIMARY KEY) DEFAULT CHARSET = sjis engine = MEMORY;
CREATE TABLE `sWa` (`bP` char(1) PRIMARY KEY) DEFAULT CHARSET = sjis engine = MEMORY;
CREATE TABLE `sWb` (`bP` char(1) PRIMARY KEY) DEFAULT CHARSET = sjis engine = MEMORY;
CREATE TABLE `sXa` (`bP` char(1) PRIMARY KEY) DEFAULT CHARSET = sjis engine = MEMORY;
CREATE TABLE `sXb` (`bP` char(1) PRIMARY KEY) DEFAULT CHARSET = sjis engine = MEMORY;
#insert the following data in each table
# jisx0201 hankaku-katakana data
# jisx0208 data
# jisx0212 supplemental character data
INSERT INTO `sPa` VALUES (''),(''),('');
INSERT INTO `sPb` VALUES ('');
INSERT INTO `sQa` VALUES (''),(''),('');
INSERT INTO `sQb` VALUES ('');
INSERT INTO `sRa` VALUES ('\'),('\'),('\');
INSERT INTO `sRb` VALUES ('\');
INSERT INTO `sSa` VALUES (''),(''),('');
INSERT INTO `sSb` VALUES ('');
INSERT INTO `sTa` VALUES (''),(''),('');
INSERT INTO `sTb` VALUES ('');
INSERT INTO `sUa` VALUES ('\'),('\'),('\');
INSERT INTO `sUb` VALUES ('\');
INSERT INTO `sVa` VALUES (''),(''),('');
INSERT INTO `sVb` VALUES ('');
INSERT INTO `sWa` VALUES (''),(''),('');
INSERT INTO `sWb` VALUES ('');
INSERT INTO `sXa` VALUES ('\'),('\'),('\');
INSERT INTO `sXb` VALUES ('\');
#Test for innodb
SELECT `bP` FROM `sPa` WHERE `bP` IN (SELECT `bP` FROM `sPb`);
SELECT `bP` FROM `sPa` WHERE EXISTS (SELECT `bP` FROM `sPb` WHERE `sPa`.`bP` = `sPb`.`bP`);
SELECT `bP` FROM `sPa` WHERE NOT EXISTS (SELECT `bP` FROM `sPb` WHERE `sPa`.`bP` = `sPb`.`bP`);
SELECT `bP` FROM `sQa` WHERE `bP` IN (SELECT `bP` FROM `sQb`);
SELECT `bP` FROM `sQa` WHERE EXISTS (SELECT `bP` FROM `sQb` WHERE `sQa`.`bP` = `sQb`.`bP`);
SELECT `bP` FROM `sQa` WHERE NOT EXISTS (SELECT `bP` FROM `sQb` WHERE `sQa`.`bP` = `sQb`.`bP`);
SELECT `bP` FROM `sRa` WHERE `bP` IN (SELECT `bP` FROM `sRb`);
SELECT `bP` FROM `sRa` WHERE EXISTS (SELECT `bP` FROM `sRb` WHERE `sRa`.`bP` = `sRb`.`bP`);
SELECT `bP` FROM `sRa` WHERE NOT EXISTS (SELECT `bP` FROM `sRb` WHERE `sRa`.`bP` = `sRb`.`bP`);
SELECT `bP` FROM `sSa` WHERE `bP` IN (SELECT `bP` FROM `sSb`);
SELECT `bP` FROM `sSa` WHERE EXISTS (SELECT `bP` FROM `sSb` WHERE `sSa`.`bP` = `sSb`.`bP`);
SELECT `bP` FROM `sSa` WHERE NOT EXISTS (SELECT `bP` FROM `sSb` WHERE `sSa`.`bP` = `sSb`.`bP`);
SELECT `bP` FROM `sTa` WHERE `bP` IN (SELECT `bP` FROM `sTb`);
SELECT `bP` FROM `sTa` WHERE EXISTS (SELECT `bP` FROM `sTb` WHERE `sTa`.`bP` = `sTb`.`bP`);
SELECT `bP` FROM `sTa` WHERE NOT EXISTS (SELECT `bP` FROM `sTb` WHERE `sTa`.`bP` = `sTb`.`bP`);
SELECT `bP` FROM `sUa` WHERE `bP` IN (SELECT `bP` FROM `sUb`);
SELECT `bP` FROM `sUa` WHERE EXISTS (SELECT `bP` FROM `sUb` WHERE `sUa`.`bP` = `sUb`.`bP`);
SELECT `bP` FROM `sUa` WHERE NOT EXISTS (SELECT `bP` FROM `sUb` WHERE `sUa`.`bP` = `sUb`.`bP`);
SELECT `bP` FROM `sVa` WHERE `bP` IN (SELECT `bP` FROM `sVb`);
SELECT `bP` FROM `sVa` WHERE EXISTS (SELECT `bP` FROM `sVb` WHERE `sVa`.`bP` = `sVb`.`bP`);
SELECT `bP` FROM `sVa` WHERE NOT EXISTS (SELECT `bP` FROM `sVb` WHERE `sVa`.`bP` = `sVb`.`bP`);
SELECT `bP` FROM `sWa` WHERE `bP` IN (SELECT `bP` FROM `sWb`);
SELECT `bP` FROM `sWa` WHERE EXISTS (SELECT `bP` FROM `sWb` WHERE `sWa`.`bP` = `sWb`.`bP`);
SELECT `bP` FROM `sWa` WHERE NOT EXISTS (SELECT `bP` FROM `sWb` WHERE `sWa`.`bP` = `sWb`.`bP`);
SELECT `bP` FROM `sXa` WHERE `bP` IN (SELECT `bP` FROM `sXb`);
SELECT `bP` FROM `sXa` WHERE EXISTS (SELECT `bP` FROM `sXb` WHERE `sXa`.`bP` = `sXb`.`bP`);
SELECT `bP` FROM `sXa` WHERE NOT EXISTS (SELECT `bP` FROM `sXb` WHERE `sXa`.`bP` = `sXb`.`bP`);
DROP TABLE `sPa`;
DROP TABLE `sPb`;
DROP TABLE `sQa`;
DROP TABLE `sQb`;
DROP TABLE `sRa`;
DROP TABLE `sRb`;
DROP TABLE `sSa`;
DROP TABLE `sSb`;
DROP TABLE `sTa`;
DROP TABLE `sTb`;
DROP TABLE `sUa`;
DROP TABLE `sUb`;
DROP TABLE `sVa`;
DROP TABLE `sVb`;
DROP TABLE `sWa`;
DROP TABLE `sWb`;
DROP TABLE `sXa`;
DROP TABLE `sXb`;
|
