~drizzle-trunk/drizzle/development

insert into t1 values (NULL, 'a', 1), (NULL, 'b', 2), (NULL, 'c', 3), (NULL, 'd', 4), (NULL, 'e', 5), (NULL, 'f', 6), (NULL, 'g', 7), (NULL, 'h', 8), (NULL, 'i', 9);

insert into t2 values (1, 100), (1, 101), (1, 102), (2, 100), (2, 103), (2, 104), (3, 101), (3, 102), (3, 105);

SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;

SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;

explain SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;

explain SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;

drop table t1,t2;

# derived table reference

SELECT a.x FROM (SELECT 1 AS x) AS a HAVING a.x = 1;

100

101

102

# UPDATE/DELETE/INSERT of derived tables

103

104

create table t1 (a int);

105

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

106

delete from (select * from t1);

107

insert into (select * from t1) values (5);

108

drop table t1;

109

110

111

# deived tables with subquery inside all by one table

112

113

create table t1 (E1 INTEGER NOT NULL, E2 INTEGER NOT NULL, E3 INTEGER NOT NULL, PRIMARY KEY(E1)

114

);

115

insert into t1 VALUES(1,1,1), (2,2,1);

116

--sort_result

117

select count(*) from t1 INNER JOIN (SELECT A.E1, A.E2, A.E3 FROM t1 AS A WHERE A.E3 = (SELECT MAX(B.E3) FROM t1 AS B WHERE A.E2 = B.E2)) AS THEMAX ON t1.E1 = THEMAX.E2 AND t1.E1 = t1.E2;

118

explain select count(*) from t1 INNER JOIN (SELECT A.E1, A.E2, A.E3 FROM t1 AS A WHERE A.E3 = (SELECT MAX(B.E3) FROM t1 AS B WHERE A.E2 = B.E2)) AS THEMAX ON t1.E1 = THEMAX.E2 AND t1.E1 = t1.E2;

119

drop table t1;

120

121

create table t1 (a int);

122

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

123

--sort_result

124

select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b;

125

explain select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b;

126

drop table t1;

127

128

129

# correct lex->current_select

130

131

CREATE TEMPORARY TABLE t1 (

132

OBJECTID int NOT NULL default 0,

133

SORTORDER int NOT NULL auto_increment,

134

KEY t1_SortIndex (SORTORDER),

135

KEY t1_IdIndex (OBJECTID)

136

) ENGINE=MyISAM;

137

CREATE TEMPORARY TABLE t2 (

138

ID int default NULL,

139

PARID int default NULL,

140

UNIQUE KEY t2_ID_IDX (ID),

141

KEY t2_PARID_IDX (PARID)

142

) engine=MyISAM;

143

INSERT INTO t2 VALUES (1000,0),(1001,0),(1002,0),(1003,0),(1008,1),(1009,1),(1010,1),(1011,1),(1016,2);

144

CREATE TEMPORARY TABLE t3 (

145

ID int default NULL,

146

DATA decimal(10,2) default NULL,

147

UNIQUE KEY t3_ID_IDX (ID)

148

) engine=MyISAM;

149

INSERT INTO t3 VALUES (1000,0.00),(1001,0.25),(1002,0.50),(1003,0.75),(1008,1.00),(1009,1.25),(1010,1.50),(1011,1.75);

150

select 497, TMP.ID, NULL from (select 497 as ID, MAX(t3.DATA) as DATA from t1 join t2 on (t1.ObjectID = t2.ID) join t3 on (t1.ObjectID = t3.ID) group by t2.ParID order by DATA DESC) as TMP;

151

drop table t1, t2, t3;

152

153

154

# explain derived

155

156

CREATE TABLE t1 (name char(1) default NULL, val int default NULL);

157

INSERT INTO t1 VALUES ('a',1), ('a',2), ('a',2), ('a',2), ('a',3), ('a',6), ('a',7), ('a',11), ('a',11), ('a',12), ('a',13), ('a',13), ('a',20), ('b',2), ('b',3), ('b',4), ('b',5);

158

SELECT s.name, AVG(s.val) AS median FROM (SELECT x.name, x.val FROM t1 x, t1 y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val <= x.val) >= COUNT(*)/2 AND SUM(y.val >= x.val) >= COUNT(*)/2) AS s GROUP BY s.name;

159

explain SELECT s.name, AVG(s.val) AS median FROM (SELECT x.name, x.val FROM t1 x, t1 y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val <= x.val) >= COUNT(*)/2 AND SUM(y.val >= x.val) >= COUNT(*)/2) AS s GROUP BY s.name;

160

drop table t1;

161

162

163

# "Using index" in explain

164

165

create table t2 (a int, b int, primary key (a));

166

insert into t2 values (1,7),(2,7);

167

explain select a from t2 where a>1;

168

explain select a from (select a from t2 where a>1) tt;

169

drop table t2;

170

171

172

# select list counter

173

174

CREATE TABLE `t1` ( `itemid` int NOT NULL default 0, `grpid` varchar(15) NOT NULL default '', `vendor` int NOT NULL default 0, `date_` date NOT NULL default '1900-01-01', `price` decimal(12,2) NOT NULL default '0.00', PRIMARY KEY (`itemid`,`grpid`,`vendor`,`date_`), KEY `itemid` (`itemid`,`vendor`), KEY `itemid_2` (`itemid`,`date_`));

175

insert into t1 values (128, 'rozn', 2, curdate(), 10),

176

(128, 'rozn', 1, curdate(), 10);

177

SELECT MIN(price) min, MAX(price) max, AVG(price) avg FROM (SELECT SUBSTRING( MAX(concat(date_,";",price)), 12) price FROM t1 WHERE itemid=128 AND grpid='rozn' GROUP BY itemid, grpid, vendor) lastprices;

178

DROP TABLE t1;

179

180

181

# DISTINCT over grouped select on subquery in the FROM clause

182

183

create table t1 (a integer, b integer);

184

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

185

select distinct sum(b) from t1 group by a;

186

select distinct sum(b) from (select a,b from t1) y group by a;

187

drop table t1;

188

189

190

191

# Test for bug #7413 "Subquery with non-scalar results participating in

192

# select list of derived table crashes server" aka "VIEW with sub query can

193

# cause the MySQL server to crash". If we have encountered problem during

194

# filling of derived table we should report error and perform cleanup

195

# properly.

196

197

CREATE TABLE t1 (a char(10), b char(10));

198

INSERT INTO t1 VALUES ('root','localhost'), ('root','%');

199

--error ER_SUBQUERY_NO_1_ROW

200

SELECT * FROM (SELECT (SELECT a.a FROM t1 AS a WHERE a.a = b.a) FROM t1 AS b) AS c;

201

DROP TABLE t1;

202

203

# test of union subquery in the FROM clause with complex distinct/all (BUG#6565)

204

205

create table t1(a int);

206

create table t2(a int);

207

create table t3(a int);

208

insert into t1 values(1),(1);

209

insert into t2 values(2),(2);

210

insert into t3 values(3),(3);

211

select * from t1 union distinct select * from t2 union all select * from t3;

212

select * from (select * from t1 union distinct select * from t2 union all select * from t3) X;

213

drop table t1, t2, t3;

214

215

216

# Bug #11864 non unique names are allowed in subquery

217

218

create table t1 (a int);

219

create table t2 (a int);

220

select * from (select * from t1,t2) foo;

221

drop table t1,t2;

222

223

224

# Bug#10586 - query works with 4.1.8, but not with 4.1.11

225

226

create table t1 (ID int not null auto_increment,

227

DATA varchar(5) not null, primary key (ID));

228

create table t2 (ID int not null auto_increment,

229

DATA varchar(5) not null, FID int not null,

230

primary key (ID));

231

select A.* from (t1 inner join (select * from t2) as A on t1.ID = A.FID);

232

select t2.* from ((select * from t1) as A inner join t2 on A.ID = t2.FID);

233

select t2.* from (select * from t1) as A inner join t2 on A.ID = t2.FID;

234

drop table t1, t2;

235

Older »