~vkolesnikov/pbxt/pbxt-07-diskfull

INSERT INTO t1 VALUES("0c9aab05b15048c59bc35c8461507deb", "System", "System", "2003-06-05 16:30:00", "The system content repository folder.", "3", "2003-06-05 16:30:00", "System", "0", NULL, "9c9aab05b15048c59bc35c8461507deb", "1");

INSERT INTO t1 VALUES("2f6161e879db43c1a5b82c21ddc49089", "Default", "System", "2003-06-09 10:52:02", "The default content repository folder.", "3", "2003-06-05 16:30:00", "System", "0", NULL, "03eea05112b845949f3fd03278b5fe43", "1");

INSERT INTO t1 VALUES("c373e9f5ad0791724315444553544200", "AddDocumentTest", "admin", "2003-06-09 10:51:25", "Movie Reviews", "0", "2003-06-09 10:51:25", "admin", "0", "2f6161e879db43c1a5b82c21ddc49089", "03eea05112b845949f3fd03278b5fe43", NULL);

SELECT 'c373e9f5ad0791a0dab5444553544200' IN(SELECT t1.FOLDERID FROM t1 WHERE t1.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t1.FOLDERNAME = 'Level1');

drop table t1;

# UNION unlocking test

create table t1 (a int) engine=innodb;

create table t2 (a int) engine=innodb;

create table t3 (a int) engine=innodb;

insert into t1 values (1),(2),(3),(4);

insert into t2 values (10),(20),(30),(40);

insert into t3 values (1),(2),(10),(50);

select a from t3 where t3.a in (select a from t1 where a <= 3 union select * from t2 where a <= 30);

drop table t1,t2,t3;

CREATE TABLE t1 (

processor_id INTEGER NOT NULL,

PRIMARY KEY (processor_id)

) ENGINE=InnoDB;

CREATE TABLE t3 (

yod_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,

login_processor INTEGER UNSIGNED ,

PRIMARY KEY (yod_id)

) ENGINE=InnoDB;

CREATE TABLE t2 (

processor_id INTEGER NOT NULL,

yod_id BIGINT UNSIGNED NOT NULL,

PRIMARY KEY (processor_id, yod_id),

INDEX (processor_id),

INDEX (yod_id),

FOREIGN KEY (processor_id) REFERENCES t1(processor_id),

FOREIGN KEY (yod_id) REFERENCES t3(yod_id)

) ENGINE=InnoDB;

INSERT INTO t1 VALUES (1),(2),(3);

INSERT INTO t3 VALUES (1,1),(2,2),(3,3);

INSERT INTO t2 VALUES (1,1),(2,2),(3,3);

SELECT distinct p1.processor_id, (SELECT y.yod_id FROM t1 p2, t2 y WHERE p2.processor_id = p1.processor_id and p2.processor_id = y.processor_id) FROM t1 p1;

drop table t2,t1,t3;

# innodb locking

CREATE TABLE t1 (

id int(11) NOT NULL default '0',

b int(11) default NULL,

c char(3) default NULL,

PRIMARY KEY (id),

KEY t2i1 (b)

) ENGINE=innodb DEFAULT CHARSET=latin1;

INSERT INTO t1 VALUES (0,0,'GPL'),(1,0,'GPL'),(2,1,'GPL'),(3,2,'GPL');

CREATE TABLE t2 (

id int(11) NOT NULL default '0',

b int(11) default NULL,

c char(3) default NULL,

PRIMARY KEY (id),

KEY t2i (b)

) ENGINE=innodb DEFAULT CHARSET=latin1;

INSERT INTO t2 VALUES (0,0,'GPL'),(1,0,'GPL'),(2,1,'GPL'),(3,2,'GPL');

select (select max(id) from t2 where b=1 group by b) as x,b from t1 where b=1;

drop table t1,t2;

# reiniting innodb tables

create table t1 (id int not null, value char(255), primary key(id)) engine=innodb;

create table t2 (id int not null, value char(255)) engine=innodb;

insert into t1 values (1,'a'),(2,'b');

100

insert into t2 values (1,'z'),(2,'x');

101

select t2.id,t2.value,(select t1.value from t1 where t1.id=t2.id) from t2;

102

select t2.id,t2.value,(select t1.value from t1 where t1.id=t2.id) from t2;

103

drop table t1,t2;

104

105

106

# unlocking tables with subqueries in HAVING

107

108

create table t1 (a int, b int) engine=innodb;

109

insert into t1 values (1,2), (1,3), (2,3), (2,4), (2,5), (3,4), (4,5), (4,100);

110

create table t2 (a int) engine=innodb;

111

insert into t2 values (1),(2),(3),(4);

112

select a, sum(b) as b from t1 group by a having b > (select max(a) from t2);

113

drop table t1, t2;

114

115

116

# bug #5220 test suite

117

118

CREATE TABLE `t1` ( `unit` varchar(50) NOT NULL default '', `ingredient` varchar(50) NOT NULL default '') ENGINE=InnoDB DEFAULT CHARSET=latin1;

119

120

CREATE TABLE `t2` ( `ingredient` varchar(50) NOT NULL default '', `unit` varchar(50) NOT NULL default '', PRIMARY KEY (ingredient, unit)) ENGINE=InnoDB DEFAULT CHARSET=latin1;

121

122

INSERT INTO `t1` VALUES ('xx','yy');

123

INSERT INTO `t2` VALUES ('yy','xx');

124

125

SELECT R.unit, R.ingredient FROM t1 R WHERE R.ingredient IN (SELECT N.ingredient FROM t2 N WHERE N.unit = R.unit);

126

127

drop table t1, t2;

128

129

130

# possible early unlock

131

132

CREATE TABLE t1 (

133

id INT NOT NULL auto_increment,

134

date1 DATE, coworkerid INT,

135

description VARCHAR(255),

136

sum_used DOUBLE,

137

sum_remaining DOUBLE,

138

comments VARCHAR(255),

139

PRIMARY KEY(id)

140

) engine=innodb;

141

insert into t1 values (NULL, '1999-01-01', 1,'test', 22, 33, 'comment'), (NULL, '1999-01-01', 1,'test', 22, 33, 'comment'), (NULL, '1999-01-01', 1,'test', 22, 33, 'comment'), (NULL, '1998-01-01', 1,'test', 22, 33, 'comment'), (NULL, '1998-01-01', 1,'test', 22, 33, 'comment'), (NULL, '2004-01-01', 1,'test', 22, 33, 'comment'), (NULL, '2004-01-01', 1,'test', 22, 33, 'comment');

142

SELECT DISTINCT

143

(SELECT sum(sum_used) FROM t1 WHERE sum_used > 0 AND year(date1) <= '2004') as somallontvangsten,

144

(SELECT sum(sum_used) FROM t1 WHERE sum_used < 0 AND year(date1) <= '2004') as somalluitgaven

145

FROM t1;

146

select * from t1;

147

drop table t1;

148

149

150

# cleaning up of results of subselects (BUG#8125)

151

152

CREATE TABLE `t1` ( `a` char(3) NOT NULL default '', `b` char(3) NOT NULL default '', `c` char(3) NOT NULL default '', PRIMARY KEY (`a`,`b`,`c`)) ENGINE=InnoDB;

153

CREATE TABLE t2 LIKE t1;

154

INSERT INTO t1 VALUES (1,1,1);

155

INSERT INTO t2 VALUES (1,1,1);

156

PREPARE my_stmt FROM "SELECT t1.b, count(*) FROM t1 group by t1.b having

157

count(*) > ALL (SELECT COUNT(*) FROM t2 WHERE t2.a=1 GROUP By t2.b)";

158

EXECUTE my_stmt;

159

EXECUTE my_stmt;

160

deallocate prepare my_stmt;

161

drop table t1,t2;

162

163

# End of 4.1 tests

164

165

CREATE TABLE t1 (

166

school_name varchar(45) NOT NULL,

167

country varchar(45) NOT NULL,

168

funds_requested float NOT NULL,

169

schooltype varchar(45) NOT NULL

170

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

171

172

insert into t1 values ("the school", "USA", 1200, "Human");

173

174

select count(country) as countrycount, sum(funds_requested) as smcnt,

175

country, (select sum(funds_requested) from t1) as total_funds

176

from t1

177

group by country;

178

179

select count(country) as countrycount, sum(funds_requested) as smcnt,

180

country, (select sum(funds_requested) from t1) as total_funds

181

from t1

182

group by country;

183

184

drop table t1;

185

186

187

# BUG#14342: wrong placement of subquery internals in complex queries

188

189

CREATE TABLE `t1` (

190

`t3_id` int NOT NULL,

191

`t1_id` int NOT NULL,

192

PRIMARY KEY (`t1_id`)

193

);

194

CREATE TABLE `t2` (

195

`t2_id` int NOT NULL,

196

`t1_id` int NOT NULL,

197

`b` int NOT NULL,

198

PRIMARY KEY (`t2_id`),

199

UNIQUE KEY `idx_t2_t1_b` (`t1_id`,`b`)

200

) ENGINE=InnoDB;

201

CREATE TABLE `t3` (

202

`t3_id` int NOT NULL

203

);

204

INSERT INTO `t3` VALUES (3);

205

select

206

(SELECT rs.t2_id

207

FROM t2 rs

208

WHERE rs.t1_id=

209

(SELECT lt.t1_id

210

FROM t1 lt

211

WHERE lt.t3_id=a.t3_id)

212

ORDER BY b DESC LIMIT 1)

213

from t3 AS a;

214

# repeat above query in SP

215

--disable_warnings

216

DROP PROCEDURE IF EXISTS p1;

217

--enable_warnings

218

delimiter //;

219

create procedure p1()

220

begin

221

declare done int default 3;

222

repeat

223

select

224

(SELECT rs.t2_id

225

FROM t2 rs

226

WHERE rs.t1_id=

227

(SELECT lt.t1_id

228

FROM t1 lt

229

WHERE lt.t3_id=a.t3_id)

230

ORDER BY b DESC LIMIT 1) as x

231

from t3 AS a;

232

set done= done-1;

233

until done <= 0 end repeat;

234

end//

235

delimiter ;//

236

call p1();

237

call p1();

238

call p1();

239

drop procedure p1;

240

drop tables t1,t2,t3;

Older »