~drizzle-trunk/drizzle/development

EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');

164

--error ER_OPERAND_COLUMNS

164

-- error 1241

165

SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM

166

t8 WHERE pseudo='joce');

167

--error ER_OPERAND_COLUMNS

167

-- error 1241

168

SELECT pseudo FROM t8 WHERE pseudo=(SELECT * FROM t8 WHERE

169

pseudo='joce');

170

SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');

171

--error ER_SUBQUERY_NO_1_ROW

171

-- error 1242

172

SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo LIKE '%joce%');

173

174

drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;

188

SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';

189

SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');

190

SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1) UNION ALL SELECT 1;

191

--error ER_SUBQUERY_NO_1_ROW

191

-- error 1242

192

SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1;

193

EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1);

194

drop table t1;

214

INSERT INTO t2 (mot,topic,date,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce');

215

select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);

216

SELECT numeropost,maxnumrep FROM t1 WHERE exists (SELECT 1 FROM t2 WHERE (mot='joce') AND date >= '2002-10-21' AND t1.numeropost = t2.topic) ORDER BY maxnumrep DESC LIMIT 0, 20;

217

--error ER_BAD_FIELD_ERROR

217

-- error 1054

218

SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1) b;

219

--error ER_BAD_FIELD_ERROR

219

-- error 1054

220

SELECT 1 IN (SELECT 1 FROM t2 HAVING a);

221

222

SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic);

245

);

246

247

INSERT INTO t1 (numeropost,maxnumrep) VALUES (1,0),(2,1);

248

--error ER_SUBQUERY_NO_1_ROW

248

-- error 1242

249

select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);

250

--error ER_SUBQUERY_NO_1_ROW

250

-- error 1242

251

select numeropost as a FROM t1 ORDER BY (SELECT 1 FROM t1 HAVING a=1);

252

drop table t1;

253

259

--echo #iftest

260

CREATE TEMPORARY TABLE t1 (field char(1) NOT NULL DEFAULT 'b') ENGINE=MyISAM;

261

INSERT INTO t1 VALUES ();

262

--error ER_SUBQUERY_NO_1_ROW

262

-- error 1242

263

SELECT field FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) a HAVING field='b');

264

drop table t1;

265

272

UNIQUE KEY `numreponse` (`numreponse`),

273

KEY `pseudo` (`pseudo`,`numeropost`)

274

);

275

--error ER_ILLEGAL_REFERENCE

275

-- error 1247

276

SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * FROM t1) as a;

277

--error ER_BAD_FIELD_ERROR

277

-- error 1054

278

SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a;

279

SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT * FROM t1) as a;

280

INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test');

281

--error ER_SUBQUERY_NO_1_ROW

281

-- error 1242

282

EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');

283

--replace_column 9 #

284

EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';

297

insert into t1 values (0, 10),(1, 11),(2, 12);

298

insert into t2 values (1, 21),(2, 22),(3, 23);

299

select * from t1;

300

--error ER_UPDATE_TABLE_USED

300

-- error 1093

301

update t1 set b= (select b from t1);

302

--error ER_SUBQUERY_NO_1_ROW

302

-- error 1242

303

update t1 set b= (select b from t2);

304

update t1 set b= (select b from t2 where t1.a = t2.a);

305

select * from t1;

312

insert into t2 values (1, 21),(2, 12),(3, 23);

313

select * from t1;

314

select * from t1 where b = (select b from t2 where t1.a = t2.a);

315

--error ER_UPDATE_TABLE_USED

315

-- error 1093

316

delete from t1 where b = (select b from t1);

317

--error ER_SUBQUERY_NO_1_ROW

317

-- error 1242

318

delete from t1 where b = (select b from t2);

319

delete from t1 where b = (select b from t2 where t1.a = t2.a);

320

select * from t1;

326

create table t3 (b int);

327

insert into t2 values (1);

328

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

329

--error ER_UPDATE_TABLE_USED

329

-- error 1093

330

INSERT INTO t1 (x) VALUES ((SELECT x FROM t1));

331

--error ER_SUBQUERY_NO_1_ROW

331

-- error 1242

332

INSERT INTO t1 (x) VALUES ((SELECT b FROM t3));

333

INSERT INTO t1 (x) VALUES ((SELECT a FROM t2));

334

select * from t1;

340

--echo # After this, only data based on old t1 records should have been added.

341

INSERT INTO t1 (x) select (SELECT SUM(x)+2 FROM t1) FROM t2;

342

select * from t1;

343

--error ER_BAD_FIELD_ERROR

343

-- error 1054

344

INSERT INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));

345

INSERT INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));

346

select * from t1;

357

insert into t2 values (1);

358

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

359

select * from t1;

360

--error ER_UPDATE_TABLE_USED

360

-- error 1093

361

replace into t1 (x, y) VALUES ((SELECT x FROM t1), (SELECT a+1 FROM t2));

362

--error ER_SUBQUERY_NO_1_ROW

362

-- error 1242

363

replace into t1 (x, y) VALUES ((SELECT a FROM t3), (SELECT a+1 FROM t2));

364

replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+1 FROM t2));

365

select * from t1;

375

select * from t1;

376

drop table t1, t2, t3;

377

378

--error ER_NO_TABLES_USED

378

-- error 1096

379

SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *);

380

381

CREATE TABLE t2 (id int default NULL, KEY id (id));

388

EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);

389

SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);

390

SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2);

391

--error ER_UPDATE_TABLE_USED

391

-- error 1093

392

INSERT INTO t2 VALUES ((SELECT * FROM t2));

393

--error ER_UPDATE_TABLE_USED

393

-- error 1093

394

INSERT INTO t2 VALUES ((SELECT id FROM t2));

395

SELECT * FROM t2;

396

CREATE TEMPORARY TABLE t1 (id int default NULL, KEY id (id)) ENGINE=MyISAM;

397

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

398

--error ER_SUBQUERY_NO_1_ROW

398

-- error 1242

399

UPDATE t2 SET id=(SELECT * FROM t1);

400

drop table t2, t1;

401

464

465

--echo #LIMIT is not supported now

466

--echo #create table t1 (a float) ENGINE=MyISAM;

467

--echo #--error ER_NOT_SUPPORTED_YET

467

--echo #-- error 1235

468

--echo #select 10.5 IN (SELECT * from t1 LIMIT 1);

469

--echo #--error ER_NOT_SUPPORTED_YET

469

--echo #-- error 1235

470

--echo #select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);

471

--echo #drop table t1;

472

--echo #

490

--echo #select ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a');

491

--echo #select ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a');

492

--echo #select ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a');

493

--echo #--error ER_NOT_SUPPORTED_YET

493

--echo #-- error 1235

494

--echo #select ROW(1, 1, 'a') IN (select b,a,c from t1 limit 2);

495

--echo #drop table t1;

496

567

--echo #

568

--echo # error in IN

569

--echo #

570

--error ER_TABLE_UNKNOWN

570

-- error 1146

571

select t1.Continent, t2.Name, t2.Population from t1 LEFT JOIN t2 ON t1.Code = t2.Country where t2.Population IN (select max(t2.Population) AS Population from t2, t1 where t2.Country = t1.Code group by Continent);

572

573

--echo #

638

INSERT INTO t1 VALUES (1);

639

UPDATE t1 SET i=i+(SELECT MAX(i) FROM (SELECT 1) t) WHERE i=(SELECT MAX(i));

640

UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));

641

--error ER_BAD_FIELD_ERROR

641

-- error 1054

642

UPDATE t1 SET t.i=i+(SELECT MAX(i) FROM (SELECT 1) t);

643

select * from t1;

644

drop table t1;

783

create temporary table t2 (s1 int) ENGINE=MyISAM;

784

insert into t1 values (1);

785

insert into t2 values (1);

786

--error ER_BAD_FIELD_ERROR

786

-- error 1054

787

update t1 set s1 = s1 + 1 where 1 = (select x.s1 as A from t2 WHERE t2.s1 > t1.s1 order by A);

788

DROP TABLE t1, t2;

789

793

--echo #CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci,

794

--echo # s2 CHAR(5) COLLATE latin1_swedish_ci) ENGINE=MyISAM;

795

--echo #INSERT INTO t1 VALUES ('z','?');

796

--echo #--error ER_CANT_AGGREGATE_2COLLATIONS

796

--echo #-- error 1267

797

--echo #select * from t1 where s1 > (select max(s2) from t1);

798

--echo #--error ER_CANT_AGGREGATE_2COLLATIONS

798

--echo #-- error 1267

799

--echo #select * from t1 where s1 > any (select max(s2) from t1);

800

--echo #drop table t1;

801

813

--echo # row union

814

--echo #

815

create table t1 (s1 char(5));

816

--error ER_OPERAND_COLUMNS

816

-- error 1241

817

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

818

insert into t1 values ('tttt');

819

select * from t1 where ('a','b')=(select 'a','b' from t1 union select 'a','b' from t1);

892

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

893

CREATE TEMPORARY TABLE t2 (id int default NULL) ENGINE=MyISAM;

894

INSERT INTO t2 VALUES (2),(6);

895

--error ER_OPERAND_COLUMNS

895

-- error 1241

896

select * from t1 where (1,2,6) in (select * from t2);

897

DROP TABLE t1,t2;

898

922

--echo #

923

create temporary table t1 (s1 int) ENGINE=MyISAM;

924

create temporary table t2 (s1 int) ENGINE=MyISAM;

925

--error ER_BAD_FIELD_ERROR

925

-- error 1054

926

select * from t1 where (select count(*) from t2 where t1.s2) = 1;

927

--error ER_BAD_FIELD_ERROR

927

-- error 1054

928

select * from t1 where (select count(*) from t2 group by t1.s2) = 1;

929

--error ER_BAD_FIELD_ERROR

929

-- error 1054

930

select count(*) from t2 group by t1.s2;

931

drop table t1, t2;

932

1032

create temporary table t1(id int) ENGINE=MyISAM;

1033

create temporary table t2(id int) ENGINE=MyISAM;

1034

create temporary table t3(flag int) ENGINE=MyISAM;

1035

--error ER_PARSE_ERROR

1035

-- error 1064

1036

select (select * from t3 where id not null) from t1, t2;

1037

drop table t1,t2,t3;

1038

1121

SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);

1122

--echo # row tests

1123

--echo # < > >= <= and = ALL/ <> ANY do not support row operation

1124

--error ER_OPERAND_COLUMNS

1124

-- error 1241

1125

SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a FROM t1 WHERE b = 2);

1126

--error ER_OPERAND_COLUMNS

1126

-- error 1241

1127

SELECT a FROM t1 WHERE a > ANY (SELECT a,2 FROM t1 WHERE b = 2);

1128

--error ER_OPERAND_COLUMNS

1128

-- error 1241

1129

SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a,2 FROM t1 WHERE b = 2);

1130

--error ER_OPERAND_COLUMNS

1130

-- error 1241

1131

SELECT a FROM t1 WHERE (1,2) > ALL (SELECT a FROM t1 WHERE b = 2);

1132

--error ER_OPERAND_COLUMNS

1132

-- error 1241

1133

SELECT a FROM t1 WHERE a > ALL (SELECT a,2 FROM t1 WHERE b = 2);

1134

--error ER_OPERAND_COLUMNS

1134

-- error 1241

1135

SELECT a FROM t1 WHERE (1,2) > ALL (SELECT a,2 FROM t1 WHERE b = 2);

1136

--error ER_OPERAND_COLUMNS

1136

-- error 1241

1137

SELECT a FROM t1 WHERE (1,2) = ALL (SELECT a,2 FROM t1 WHERE b = 2);

1138

--error ER_OPERAND_COLUMNS

1138

-- error 1241

1139

SELECT a FROM t1 WHERE (1,2) <> ANY (SELECT a,2 FROM t1 WHERE b = 2);

1140

--echo # following should be converted to IN

1141

--error ER_OPERAND_COLUMNS

1141

-- error 1241

1142

SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a FROM t1 WHERE b = 2);

1143

--error ER_OPERAND_COLUMNS

1143

-- error 1241

1144

SELECT a FROM t1 WHERE a = ANY (SELECT a,2 FROM t1 WHERE b = 2);

1145

SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a,2 FROM t1 WHERE b = 2);

1146

--error ER_OPERAND_COLUMNS

1146

-- error 1241

1147

SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a FROM t1 WHERE b = 2);

1148

--error ER_OPERAND_COLUMNS

1148

-- error 1241

1149

SELECT a FROM t1 WHERE a <> ALL (SELECT a,2 FROM t1 WHERE b = 2);

1150

SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a,2 FROM t1 WHERE b = 2);

1151

SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 WHERE b = 2);

1315

INSERT INTO `t1` VALUES (1,0,0,700),(1,1,1,400),(1,5,5,400),(1,12,12,400),(1,12,32,400),(4,12,32,400);

1316

CREATE TEMPORARY TABLE `t2` ( `id` int NOT NULL default '0', `pid` int NOT NULL default '0', `map` int NOT NULL default '0', `level` int NOT NULL default '0', `title` varchar(255) default NULL, PRIMARY KEY (`id`,`pid`,`map`), KEY `level` (`level`), KEY `id` (`id`,`map`)) ENGINE=MyISAM ;

1317

INSERT INTO `t2` VALUES (6,5,12,7,'a'),(12,0,0,7,'a'),(12,1,0,7,'a'),(12,5,5,7,'a'),(12,5,12,7,'a');

1318

--error ER_BAD_FIELD_ERROR

1318

-- error 1054

1319

SELECT b.sc FROM (SELECT (SELECT a.access FROM t1 a WHERE a.map = op.map AND a.slave = op.pid AND a.master = 1) ac FROM t2 op WHERE op.id = 12 AND op.map = 0) b;

1320

SELECT b.ac FROM (SELECT (SELECT a.access FROM t1 a WHERE a.map = op.map AND a.slave = op.pid AND a.master = 1) ac FROM t2 op WHERE op.id = 12 AND op.map = 0) b;

1321

drop tables t1,t2;

1411

--echo # Comparison subquery and row with nested rows

1412

--echo #

1413

create temporary table t1 (a integer, b integer) ENGINE=MyISAM;

1414

--error ER_OPERAND_COLUMNS

1414

-- error 1241

1415

select row(1,(2,2)) in (select * from t1 );

1416

--error ER_OPERAND_COLUMNS

1416

-- error 1241

1417

select row(1,(2,2)) = (select * from t1 );

1418

--error ER_OPERAND_COLUMNS

1418

-- error 1241

1419

select (select * from t1) = row(1,(2,2));

1420

drop table t1;

1421

1424

--echo #

1425

create table t1 (a integer);

1426

insert into t1 values (1);

1427

--error ER_ILLEGAL_REFERENCE

1427

-- error 1247

1428

select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx ;

1429

--error ER_ILLEGAL_REFERENCE

1429

-- error 1247

1430

select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx;

1431

select 1 as xx, 1 = ALL ( select 1 from t1 where 1 = xx );

1432

--error ER_ILLEGAL_REFERENCE

1432

-- error 1247

1433

select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx;

1434

drop table t1;

1435

1551

--echo #

1552

create temporary table t1 (a int, b int) ENGINE=MyISAM;

1553

insert into t1 values (1,2);

1554

--error ER_OPERAND_COLUMNS

1554

-- error 1241

1555

select 1 = (select * from t1);

1556

--error ER_OPERAND_COLUMNS

1556

-- error 1241

1557

select (select * from t1) = 1;

1558

--error ER_OPERAND_COLUMNS

1558

-- error 1241

1559

select (1,2) = (select a from t1);

1560

--error ER_OPERAND_COLUMNS

1560

-- error 1241

1561

select (select a from t1) = (1,2);

1562

--error ER_OPERAND_COLUMNS

1562

-- error 1241

1563

select (1,2,3) = (select * from t1);

1564

--error ER_OPERAND_COLUMNS

1564

-- error 1241

1565

select (select * from t1) = (1,2,3);

1566

drop table t1;

1567

2043

HAVING t1.a > ALL(SELECT t2.c FROM t2

2044

WHERE EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e

2045

HAVING SUM(t1.a+t2.c) < t3.e/4));

2046

--error ER_INVALID_GROUP_FUNC_USE

2046

-- error 1111

2047

SELECT t1.a FROM t1 GROUP BY t1.a

2048

HAVING t1.a > ALL(SELECT t2.c FROM t2

2049

WHERE EXISTS(SELECT t3.e FROM t3

2050

WHERE SUM(t1.a+t2.c) < t3.e/4));

2051

--error ER_INVALID_GROUP_FUNC_USE

2051

-- error 1111

2052

SELECT t1.a from t1 GROUP BY t1.a HAVING AVG(SUM(t1.b)) > 20;

2053

2054

SELECT t1.a FROM t1 GROUP BY t1.a

3119

--error ER_PARSE_ERROR

3120

SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1;

3121

3122

--error ER_CARTESIAN_JOIN_ATTEMPTED

3122

--sorted_result

3123

SELECT * FROM t1 JOIN (t1 t1a) ON 1;

3124

--error ER_CARTESIAN_JOIN_ATTEMPTED

3125

3124

SELECT * FROM t1 JOIN ((t1 t1a)) ON 1;

3126

3125

3127

3126

SELECT * FROM (t1 t1a);

Older »