~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);

173

172

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

174

173

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;

192

191

1 PRIMARY m2 ALL NULL NULL NULL NULL 9

193

192

1 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 Using where; Using join buffer

194

193

2 DERIVED mp ALL NULL NULL NULL NULL 9 Using temporary; Using filesort

195

2 DERIVED m1 eq_ref PRIMARY PRIMARY 4 test.mp.mat_id 1

194

2 DERIVED m1 eq_ref PRIMARY PRIMARY 3 test.mp.mat_id 1

196

195

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;

197

196

id select_type table type possible_keys key key_len ref rows Extra

198

197

1 PRIMARY m2 ALL NULL NULL NULL NULL 9

199

198

1 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 Using where; Using join buffer

200

199

2 DERIVED mp ALL NULL NULL NULL NULL 9 Using temporary; Using filesort

201

2 DERIVED m1 eq_ref PRIMARY PRIMARY 4 test.mp.mat_id 1

200

2 DERIVED m1 eq_ref PRIMARY PRIMARY 3 test.mp.mat_id 1

202

201

drop table t1,t2;

203

202

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

204

203

205

204

205

create user mysqltest_1;

206

create table t1 select 1 as a;

207

select 2 as a from (select * from t1) b;

208

ERROR 3D000: No database selected

209

use test;

210

select 2 as a from (select * from t1) b;

211

212

213

drop table t1;

214

select mail_id, if(folder.f_description!='', folder.f_description, folder.f_name) as folder_name, date, address_id, phrase, address, subject from folder, (select mail.mail_id as mail_id, date_format(mail.h_date, '%b %e, %Y %h:%i') as date, mail.folder_id, sender.address_id as address_id, sender.phrase as phrase, sender.address as address, mail.h_subject as subject from mail left join mxa as mxa_sender on mail.mail_id=mxa_sender.mail_id and mxa_sender.type='from' left join address as sender on mxa_sender.address_id=sender.address_id mxa as mxa_recipient, address as recipient, where 1 and mail.mail_id=mxa_recipient.mail_id and mxa_recipient.address_id=recipient.address_id and mxa_recipient.type='to' and match(sender.phrase, sender.address, sender.comment) against ('jeremy' in boolean mode) and match(recipient.phrase, recipient.address, recipient.comment) against ('monty' in boolean mode) order by mail.h_date desc limit 0, 25 ) as query where query.folder_id=folder.folder_id;

215

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mxa as mxa_recipient, address as recipient, where 1 and mail.mail_id=mxa_r' at line 1

206

216

create table t1 (a int);

207

217

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

218

update (select * from t1) as t1 set a = 5;

219

ERROR HY000: The target table t1 of the UPDATE is not updatable

208

220

delete from (select * from t1);

209

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '(select * from t1)' at line 1

221

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(select * from t1)' at line 1

210

222

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

211

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '(select * from t1) values (5)' at line 1

223

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(select * from t1) values (5)' at line 1

212

224

drop table t1;

213

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

225

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

214

226

);

215

227

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

216

228

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;

218

230

219

231

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;

220

232

id select_type table type possible_keys key key_len ref rows Extra

221

1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 2 Using where

222

1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where; Using join buffer

233

1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2

234

1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 THEMAX.E2 1 Using where

223

235

2 DERIVED A ALL NULL NULL NULL NULL 2 Using where

224

236

3 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 2 Using where

225

237

drop table t1;

242

254

3 UNION t1 ALL NULL NULL NULL NULL 2

243

255

NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL

244

256

drop table t1;

245

CREATE TEMPORARY TABLE t1 (

246

OBJECTID int NOT NULL default 0,

247

SORTORDER int NOT NULL auto_increment,

257

CREATE TABLE `t1` (

258

`N` int(11) unsigned NOT NULL default '0',

259

`M` tinyint(1) default '0'

260

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

261

INSERT INTO `t1` (N, M) VALUES (1, 0),(1, 0),(1, 0),(2, 0),(2, 0),(3, 0);

262

UPDATE `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2;

263

select * from t1;

264

N M

265

1 2

266

1 2

267

1 2

268

2 2

269

2 2

270

3 0

271

UPDATE `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2, P2.N = 2;

272

ERROR HY000: The target table P2 of the UPDATE is not updatable

273

UPDATE `t1` AS P1 INNER JOIN (SELECT aaaa FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2;

274

ERROR 42S22: Unknown column 'aaaa' in 'field list'

275

delete P1.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N;

276

select * from t1;

277

N M

278

3 0

279

delete P1.*,p2.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS p2 ON P1.N = p2.N;

280

ERROR HY000: The target table p2 of the DELETE is not updatable

281

delete P1.* from `t1` AS P1 INNER JOIN (SELECT aaa FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N;

282

ERROR 42S22: Unknown column 'aaa' in 'field list'

283

drop table t1;

284

CREATE TABLE t1 (

285

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

286

SORTORDER int(11) NOT NULL auto_increment,

248

287

KEY t1_SortIndex (SORTORDER),

249

288

KEY t1_IdIndex (OBJECTID)

250

) ENGINE=MyISAM;

251

CREATE TEMPORARY TABLE t2 (

252

ID int default NULL,

253

PARID int default NULL,

289

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

290

CREATE TABLE t2 (

291

ID int(11) default NULL,

292

PARID int(11) default NULL,

254

293

UNIQUE KEY t2_ID_IDX (ID),

255

294

KEY t2_PARID_IDX (PARID)

256

) engine=MyISAM;

295

) engine=MyISAM DEFAULT CHARSET=latin1;

257

296

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

258

CREATE TEMPORARY TABLE t3 (

259

ID int default NULL,

297

CREATE TABLE t3 (

298

ID int(11) default NULL,

260

299

DATA decimal(10,2) default NULL,

261

300

UNIQUE KEY t3_ID_IDX (ID)

262

) engine=MyISAM;

301

) engine=MyISAM DEFAULT CHARSET=latin1;

263

302

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);

264

303

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;

265

304

497 ID NULL

266

305

drop table t1, t2, t3;

267

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

306

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

268

307

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);

269

308

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;

270

309

name median

280

319

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

281

320

explain select a from t2 where a>1;

282

321

id select_type table type possible_keys key key_len ref rows Extra

283

1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 1 Using where; Using index

322

1 SIMPLE t2 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index

284

323

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

285

324

id select_type table type possible_keys key key_len ref rows Extra

286

325

1 PRIMARY <derived2> system NULL NULL NULL NULL 1

287

2 DERIVED t2 range PRIMARY PRIMARY 4 NULL 1 Using where; Using index

326

2 DERIVED t2 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index

288

327

drop table t2;

289

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_`));

328

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

290

329

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

291

330

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

292

331

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;

329

368

create table t1 (a int);

330

369

create table t2 (a int);

331

370

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

332

a a

371

ERROR 42S21: Duplicate column name 'a'

333

372

drop table t1,t2;

334

create table t1 (ID int not null auto_increment,

373

create table t1 (ID int unsigned not null auto_increment,

335

374

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

336

create table t2 (ID int not null auto_increment,

337

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

375

create table t2 (ID int unsigned not null auto_increment,

376

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

338

377

primary key (ID));

339

378

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

340

379

ID DATA FID

343

382

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

344

383

ID DATA FID

345

384

drop table t1, t2;

385

drop user mysqltest_1;

Older »