~drizzle-trunk/drizzle/development : revision 717

7

--disable_warnings

8

drop table if exists t1,t2,t3;

9

drop database if exists mysqltest;

10

drop view if exists v1;

11

--error 0,1141,1147

12

revoke all privileges on mysqltest.t1 from mysqltest_1@localhost;

13

--error 0,1141,1147

14

revoke all privileges on mysqltest.* from mysqltest_1@localhost;

15

delete from mysql.user where user=_binary'mysqltest_1';

16

10

--enable_warnings

17

11

18

12

create table t1(id1 int not null auto_increment primary key, t char(12));

91

85

drop table t1,t2;

92

86

93

87

CREATE TABLE t1 (

94

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

88

id int NOT NULL default '0',

95

89

name varchar(10) default NULL,

96

90

PRIMARY KEY (id)

97

91

) ENGINE=MyISAM;

98

92

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

99

93

CREATE TABLE t2 (

100

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

94

id int NOT NULL default '0',

101

95

name varchar(10) default NULL,

102

96

PRIMARY KEY (id)

103

97

) ENGINE=MyISAM;

104

98

INSERT INTO t2 VALUES (2,'bbb'),(3,'bbb'),(4,'bbb');

105

99

CREATE TABLE t3 (

106

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

100

id int NOT NULL default '0',

107

101

mydate datetime default NULL,

108

102

PRIMARY KEY (id)

109

103

) ENGINE=MyISAM;

114

108

DROP TABLE t1,t2,t3;

115

109

116

110

CREATE TABLE IF NOT EXISTS `t1` (

117

`id` int(11) NOT NULL auto_increment,

111

`id` int NOT NULL auto_increment,

118

112

`tst` text,

119

113

`tst1` text,

120

114

PRIMARY KEY (`id`)

121

115

) ENGINE=MyISAM;

122

116

123

117

CREATE TABLE IF NOT EXISTS `t2` (

124

`ID` int(11) NOT NULL auto_increment,

125

`ParId` int(11) default NULL,

118

`ID` int NOT NULL auto_increment,

119

`ParId` int default NULL,

126

120

`tst` text,

127

121

`tst1` text,

128

122

PRIMARY KEY (`ID`),

153

147

# Test with locking

154

148

#

155

149

156

create table t1 (n int(10) not null primary key, d int(10));

157

create table t2 (n int(10) not null primary key, d int(10));

150

create table t1 (n int not null primary key, d int);

151

create table t2 (n int not null primary key, d int);

158

152

insert into t1 values(1,1);

159

153

insert into t2 values(1,10),(2,20);

160

154

LOCK TABLES t1 write, t2 read;

177

171

# Test safe updates and timestamps

178

172

#

179

173

set sql_safe_updates=1;

180

create table t1 (n int(10), d int(10));

181

create table t2 (n int(10), d int(10));

174

create table t1 (n int, d int);

175

create table t2 (n int, d int);

182

176

insert into t1 values(1,1);

183

177

insert into t2 values(1,10),(2,20);

184

178

--error 1175

186

180

set sql_safe_updates=0;

187

181

drop table t1,t2;

188

182

set timestamp=1038401397;

189

create table t1 (n int(10) not null primary key, d int(10), t timestamp);

190

create table t2 (n int(10) not null primary key, d int(10), t timestamp);

183

create table t1 (n int not null primary key, d int, t timestamp);

184

create table t2 (n int not null primary key, d int, t timestamp);

191

185

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

192

186

insert into t2 values(1,10,NULL),(2,20,NULL);

193

187

set timestamp=1038000000;

199

193

drop table t1,t2;

200

194

set timestamp=0;

201

195

set sql_safe_updates=0;

202

create table t1 (n int(10) not null primary key, d int(10));

203

create table t2 (n int(10) not null primary key, d int(10));

196

create table t1 (n int not null primary key, d int);

197

create table t2 (n int not null primary key, d int);

204

198

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

205

199

insert into t2 values(1,10),(2,20);

206

200

UPDATE t2 left outer join t1 on t1.n=t2.n SET t1.d=t2.d;

207

201

select * from t1;

208

202

select * from t2;

209

203

drop table t1,t2;

210

create table t1 (n int(10), d int(10));

211

create table t2 (n int(10), d int(10));

204

create table t1 (n int, d int);

205

create table t2 (n int, d int);

212

206

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

213

207

insert into t2 values(1,10),(2,20);

214

208

UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;

215

209

select * from t1;

216

210

select * from t2;

217

211

drop table t1,t2;

218

create table t1 (n int(10), d int(10));

219

create table t2 (n int(10), d int(10));

212

create table t1 (n int, d int);

213

create table t2 (n int, d int);

220

214

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

221

215

insert into t2 values(1,10),(1,20);

222

216

UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;

230

224

select * from t2;

231

225

drop table t1,t2;

232

226

233

CREATE TABLE t1 ( broj int(4) NOT NULL default '0', naziv char(25) NOT NULL default 'NEPOZNAT', PRIMARY KEY (broj)) ENGINE=MyISAM;

227

CREATE TABLE t1 ( broj int NOT NULL default '0', naziv char(25) NOT NULL default 'NEPOZNAT', PRIMARY KEY (broj)) ENGINE=MyISAM;

234

228

INSERT INTO t1 VALUES (1,'jedan'),(2,'dva'),(3,'tri'),(4,'xxxxxxxxxx'),(5,'a'),(10,''),(11,''),(12,''),(13,'');

235

CREATE TABLE t2 ( broj int(4) NOT NULL default '0', naziv char(25) NOT NULL default 'NEPOZNAT', PRIMARY KEY (broj)) ENGINE=MyISAM;

229

CREATE TABLE t2 ( broj int NOT NULL default '0', naziv char(25) NOT NULL default 'NEPOZNAT', PRIMARY KEY (broj)) ENGINE=MyISAM;

236

230

INSERT INTO t2 VALUES (1,'jedan'),(2,'dva'),(3,'tri'),(4,'xxxxxxxxxx'),(5,'a');

237

CREATE TABLE t3 ( broj int(4) NOT NULL default '0', naziv char(25) NOT NULL default 'NEPOZNAT', PRIMARY KEY (broj)) ENGINE=MyISAM;

231

CREATE TABLE t3 ( broj int NOT NULL default '0', naziv char(25) NOT NULL default 'NEPOZNAT', PRIMARY KEY (broj)) ENGINE=MyISAM;

238

232

INSERT INTO t3 VALUES (1,'jedan'),(2,'dva');

239

233

update t1,t2 set t1.naziv="aaaa" where t1.broj=t2.broj;

240

234

update t1,t2,t3 set t1.naziv="bbbb", t2.naziv="aaaa" where t1.broj=t2.broj and t2.broj=t3.broj;

271

265

update t1,t2 set t1.b=t2.b, t1.a=t2.a where t1.a=t2.a and not exists (select * from t2 where t2.a > 10);

272

266

273

267

drop table t1,t2;

274

CREATE TABLE t3 ( KEY1 varchar(50) NOT NULL default '', PARAM_CORR_DISTANCE_RUSH double default NULL, PARAM_CORR_DISTANCE_GEM double default NULL, PARAM_AVG_TARE double default NULL, PARAM_AVG_NB_DAYS double default NULL, PARAM_DEFAULT_PROP_GEM_SRVC varchar(50) default NULL, PARAM_DEFAULT_PROP_GEM_NO_ETIK varchar(50) default NULL, PARAM_SCENARIO_COSTS varchar(50) default NULL, PARAM_DEFAULT_WAGON_COST double default NULL, tmp int(11) default NULL, PRIMARY KEY (KEY1)) ENGINE=MyISAM;

268

CREATE TABLE t3 ( KEY1 varchar(50) NOT NULL default '', PARAM_CORR_DISTANCE_RUSH double default NULL, PARAM_CORR_DISTANCE_GEM double default NULL, PARAM_AVG_TARE double default NULL, PARAM_AVG_NB_DAYS double default NULL, PARAM_DEFAULT_PROP_GEM_SRVC varchar(50) default NULL, PARAM_DEFAULT_PROP_GEM_NO_ETIK varchar(50) default NULL, PARAM_SCENARIO_COSTS varchar(50) default NULL, PARAM_DEFAULT_WAGON_COST double default NULL, tmp int default NULL, PRIMARY KEY (KEY1)) ENGINE=MyISAM;

275

269

INSERT INTO t3 VALUES ('A',1,1,22,3.2,'R','R','BASE2',0.24,NULL);

276

270

create table t1 (A varchar(1));

277

271

insert into t1 values ("A") ,("B"),("C"),("D");

299

293

300

294

# Test multi-update and multi-delete with impossible where

301

295

302

create table t1(id1 int(5), field char(5));

303

create table t2(id2 int(5), field char(5));

296

create table t1(id1 int, field char(5));

297

create table t2(id2 int, field char(5));

304

298

305

299

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

306

300

insert into t2 values (1, 'b'), (2, 'bb');

380

374

#

381

375

# Test update with const tables

382

376

#

383

create table `t1` (`p_id` int(10) NOT NULL auto_increment, `p_code` varchar(20) NOT NULL default '', `p_active` int(1) NOT NULL default '1', PRIMARY KEY (`p_id`) );

384

create table `t2` (`c2_id` int(10) NULL auto_increment, `c2_p_id` int(10) NOT NULL default '0', `c2_note` text NOT NULL, `c2_active` int(1) NOT NULL default '1', PRIMARY KEY (`c2_id`), KEY `c2_p_id` (`c2_p_id`) );

377

create table `t1` (`p_id` int NOT NULL auto_increment, `p_code` varchar(20) NOT NULL default '', `p_active` int NOT NULL default '1', PRIMARY KEY (`p_id`) );

378

create table `t2` (`c2_id` int NULL auto_increment, `c2_p_id` int NOT NULL default '0', `c2_note` text NOT NULL, `c2_active` int NOT NULL default '1', PRIMARY KEY (`c2_id`), KEY `c2_p_id` (`c2_p_id`) );

385

379

insert into t1 values (0,'A01-Comp',1);

386

380

insert into t1 values (0,'B01-Comp',1);

387

381

insert into t2 values (0,1,'A Note',1);

391

385

drop table t1, t2;

392

386

393

387

#

394

# privilege check for multiupdate with other tables

395

#

396

397

connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);

398

connection root;

399

--disable_warnings

400

create database mysqltest;

401

--enable_warnings

402

create table mysqltest.t1 (a int, b int, primary key (a));

403

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

404

create table mysqltest.t3 (a int, b int, primary key (a));

405

grant select on mysqltest.* to mysqltest_1@localhost;

406

grant update on mysqltest.t1 to mysqltest_1@localhost;

407

connect (user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK);

408

connection user1;

409

update t1, t2 set t1.b=1 where t1.a=t2.a;

410

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

411

connection root;

412

revoke all privileges on mysqltest.t1 from mysqltest_1@localhost;

413

revoke all privileges on mysqltest.* from mysqltest_1@localhost;

414

delete from mysql.user where user=_binary'mysqltest_1';

415

drop database mysqltest;

416

417

#

418

388

# multi delete wrong table check

419

389

#

420

390

create table t1 (a int, primary key (a));

439

409

--disable_warnings

440

410

create table t1 (

441

411

aclid bigint not null primary key,

442

status int(1) not null

412

status int not null

443

413

) engine = innodb;

444

414

445

415

create table t2 (

511

481

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

512

482

create table t2 (a int);

513

483

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

514

create view v1 as select a as b, a/10 as a from t2;

484

create table v1 as select a as b, a/10 as a from t2;

515

485

516

486

connect (locker,localhost,root,,test);

517

487

connection locker;

537

507

reap;

538

508

select * from t1;

539

509

select * from t2;

540

drop view v1;

510

drop table v1;

541

511

drop table t1, t2;

542

512

543

513

#

574

544

# Bug#27716 multi-update did partially and has not binlogged

575

545

#

576

546

CREATE TABLE `t1` (

577

`a` int(11) NOT NULL auto_increment,

578

`b` int(11) default NULL,

547

`a` int NOT NULL auto_increment,

548

`b` int default NULL,

579

549

PRIMARY KEY (`a`)

580

) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;

550

) ENGINE=MyISAM;

581

551

582

552

CREATE TABLE `t2` (

583

`a` int(11) NOT NULL auto_increment,

584

`b` int(11) default NULL,

553

`a` int NOT NULL auto_increment,

554

`b` int default NULL,

585

555

PRIMARY KEY (`a`)

586

) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;

556

) ENGINE=MyISAM;

587

557

558

#

559

# @TODO Only row format now...commenting out.

588

560

# as the test is about to see erroed queries in binlog

589

set @sav_binlog_format= @@session.binlog_format;

590

set @@session.binlog_format= mixed;

561

#set @sav_binlog_format= @@session.binlog_format;

562

#set @@session.binlog_format= mixed;

591

563

592

564

593

565

# A. testing multi_update::send_error() effective update

613

585

614

586

# cleanup bug#27716

615

587

drop table t1, t2;

616

set @@session.binlog_format= @sav_binlog_format;

588

#set @@session.binlog_format= @sav_binlog_format;

617

589

618

590

#

619

591

# Bug #29136 erred multi-delete on trans table does not rollback

620

592

#

621

593

# @TODO JRP: This doesn't test any rollback! Commenting out...

622

594

# prepare

623

--disable_warnings

624

drop table if exists t1, t2, t3;

625

--enable_warnings

626

CREATE TABLE t1 (a int, PRIMARY KEY (a));

627

CREATE TABLE t2 (a int, PRIMARY KEY (a));

628

CREATE TABLE t3 (a int, PRIMARY KEY (a)) ENGINE=MyISAM;

629

create trigger trg_del_t3 before delete on t3 for each row insert into t1 values (1);

595

#--disable_warnings

596

#drop table if exists t1, t2, t3;

597

#--enable_warnings

598

#CREATE TABLE t1 (a int, PRIMARY KEY (a));

599

#CREATE TABLE t2 (a int, PRIMARY KEY (a));

600

#CREATE TABLE t3 (a int, PRIMARY KEY (a)) ENGINE=MyISAM;

601

#create trigger trg_del_t3 before delete on t3 for each row insert into t1 values (1);

630

602

631

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

632

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

633

reset master;

603

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

604

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

605

#reset master;

634

606

635

607

# exec cases B, A - see innodb.test

636

608

637

609

# B. send_eof() and send_error() afterward

638

610

639

--error ER_DUP_ENTRY

640

delete t3.* from t2,t3 where t2.a=t3.a;

611

#--error ER_DUP_ENTRY

612

#delete t3.* from t2,t3 where t2.a=t3.a;

641

613

642

614

# check

643

select count(*) from t1 /* must be 1 */;

644

select count(*) from t3 /* must be 1 */;

615

#select count(*) from t1 /* must be 1 */;

616

#select count(*) from t3 /* must be 1 */;

645

617

646

618

# cleanup bug#29136

647

drop table t1, t2, t3;

619

#drop table t1, t2, t3;

648

620

649

621

#

650

622

# Add further tests from here