~drizzle-trunk/drizzle/development : revision 717

1

drop table if exists t1,t2,t3;

2

drop database if exists mysqltest;

3

drop view if exists v1;

4

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

5

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

6

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

7

3

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

8

4

create table t2(id2 int not null, t char(12));

9

5

create table t3(id3 int not null, t char(12), index(id3));

76

72

delete t1 from t1,t2 where t1.id1 = t2.id2 and t1.id1 > 500;

77

73

drop table t1,t2;

78

74

CREATE TABLE t1 (

79

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

75

id int NOT NULL default '0',

80

76

name varchar(10) default NULL,

81

77

PRIMARY KEY (id)

82

78

) ENGINE=MyISAM;

83

79

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

84

80

CREATE TABLE t2 (

85

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

81

id int NOT NULL default '0',

86

82

name varchar(10) default NULL,

87

83

PRIMARY KEY (id)

88

84

) ENGINE=MyISAM;

89

85

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

90

86

CREATE TABLE t3 (

91

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

87

id int NOT NULL default '0',

92

88

mydate datetime default NULL,

93

89

PRIMARY KEY (id)

94

90

) ENGINE=MyISAM;

103

99

7 2002-07-22 00:00:00

104

100

DROP TABLE t1,t2,t3;

105

101

CREATE TABLE IF NOT EXISTS `t1` (

106

`id` int(11) NOT NULL auto_increment,

102

`id` int NOT NULL auto_increment,

107

103

`tst` text,

108

104

`tst1` text,

109

105

PRIMARY KEY (`id`)

110

106

) ENGINE=MyISAM;

111

107

CREATE TABLE IF NOT EXISTS `t2` (

112

`ID` int(11) NOT NULL auto_increment,

113

`ParId` int(11) default NULL,

108

`ID` int NOT NULL auto_increment,

109

`ParId` int default NULL,

114

110

`tst` text,

115

111

`tst1` text,

116

112

PRIMARY KEY (`ID`),

146

142

select * from t2 left outer join t1 using (n);

147

143

n

148

144

drop table t1,t2 ;

149

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

150

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

145

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

146

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

151

147

insert into t1 values(1,1);

152

148

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

153

149

LOCK TABLES t1 write, t2 read;

171

167

unlock tables;

172

168

drop table t1,t2;

173

169

set sql_safe_updates=1;

174

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

175

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

170

create table t1 (n int, d int);

171

create table t2 (n int, d int);

176

172

insert into t1 values(1,1);

177

173

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

178

174

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

180

176

set sql_safe_updates=0;

181

177

drop table t1,t2;

182

178

set timestamp=1038401397;

183

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

184

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

179

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

180

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

185

181

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

186

182

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

187

183

set timestamp=1038000000;

194

190

1 10 1038401397

195

191

2 20 1038401397

196

192

UPDATE t1,t2 SET 1=2 WHERE t1.n=t2.n;

197

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 '1=2 WHERE t1.n=t2.n' at line 1

193

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 '1=2 WHERE t1.n=t2.n' at line 1

198

194

drop table t1,t2;

199

195

set timestamp=0;

200

196

set sql_safe_updates=0;

201

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

202

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

197

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

198

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

203

199

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

204

200

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

205

201

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

212

208

1 10

213

209

2 20

214

210

drop table t1,t2;

215

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

216

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

211

create table t1 (n int, d int);

212

create table t2 (n int, d int);

217

213

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

218

214

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

219

215

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

226

222

1 30

227

223

2 20

228

224

drop table t1,t2;

229

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

230

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

225

create table t1 (n int, d int);

226

create table t2 (n int, d int);

231

227

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

232

228

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

233

229

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

255

251

select * from t2;

256

252

n d

257

253

drop table t1,t2;

258

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

254

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

259

255

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

260

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

256

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

261

257

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

262

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

258

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

263

259

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

264

260

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

265

261

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

283

279

update t1,t2 set t1.a=t1.a+100 where t1.a=101;

284

280

select * from t1;

285

281

a b

286

201 1

287

282

102 2

288

283

103 3

289

284

104 4

292

287

107 7

293

288

108 8

294

289

109 9

290

201 1

295

291

update t1,t2 set t1.b=t1.b+10 where t1.b=2;

296

292

select * from t1;

297

293

a b

298

201 1

299

294

102 12

300

295

103 3

301

296

104 4

304

299

107 7

305

300

108 8

306

301

109 9

302

201 1

307

303

update t1,t2 set t1.b=t1.b+2,t2.b=t1.b+10 where t1.b between 3 and 5 and t2.a=t1.a-100;

308

304

select * from t1;

309

305

a b

310

201 1

311

306

102 12

312

307

103 5

313

308

104 6

316

311

107 7

317

312

108 8

318

313

109 9

314

201 1

319

315

select * from t2;

320

316

a b

321

317

1 1

322

318

2 2

323

3 13

324

4 14

325

5 15

319

3 15

320

4 16

321

5 17

326

322

6 6

327

323

7 7

328

324

8 8

329

325

9 9

330

326

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

331

327

drop table t1,t2;

332

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;

328

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;

333

329

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

334

330

create table t1 (A varchar(1));

335

331

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

356

352

3 4

357

353

4 5

358

354

drop table t1;

359

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

360

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

355

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

356

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

361

357

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

362

358

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

363

359

select * from t1;

426

422

a

427

423

2

428

424

DROP TABLE t1,t2;

429

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

430

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

425

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

426

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

431

427

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

432

428

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

433

429

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

440

436

c2_id c2_p_id c2_note c2_active

441

437

1 1 A Note 1

442

438

drop table t1, t2;

443

create database mysqltest;

444

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

445

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

446

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

447

grant select on mysqltest.* to mysqltest_1@localhost;

448

grant update on mysqltest.t1 to mysqltest_1@localhost;

449

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

450

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

451

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

452

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

453

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

454

drop database mysqltest;

455

439

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

456

440

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

457

441

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

467

451

drop table t1,t2;

468

452

create table t1 (

469

453

aclid bigint not null primary key,

470

status tinyint(1) not null

454

status int not null

471

455

) engine = innodb;

472

456

create table t2 (

473

457

refid bigint not null primary key,

507

491

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

508

492

create table t2 (a int);

509

493

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

510

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

494

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

511

495

lock table t1 write;

512

496

alter table t1 add column c int default 100 after a;

513

497

update t1, v1 set t1.b=t1.a+t1.b+v1.b where t1.a=v1.a;

522

506

10

523

507

20

524

508

30

525

drop view v1;

509

drop table v1;

526

510

drop table t1, t2;

527

511

create table t1 (i1 int, i2 int, i3 int);

528

512

create table t2 (id int, c1 varchar(20), c2 varchar(20));

605

589

9 abc ppc

606

590

drop table t1, t2;

607

591

CREATE TABLE `t1` (

608

`a` int(11) NOT NULL auto_increment,

609

`b` int(11) default NULL,

592

`a` int NOT NULL auto_increment,

593

`b` int default NULL,

610

594

PRIMARY KEY (`a`)

611

) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;

595

) ENGINE=MyISAM;

612

596

CREATE TABLE `t2` (

613

`a` int(11) NOT NULL auto_increment,

614

`b` int(11) default NULL,

597

`a` int NOT NULL auto_increment,

598

`b` int default NULL,

615

599

PRIMARY KEY (`a`)

616

) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;

617

set @sav_binlog_format= @@session.binlog_format;

618

set @@session.binlog_format= mixed;

600

) ENGINE=MyISAM;

619

601

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

620

602

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

621

603

reset master;

626

608

3 1

627

609

4 4

628

610

show master status /* there must be the UPDATE query event */;

629

File Position Binlog_Do_DB Binlog_Ignore_DB

630

master-bin.000001 198

611

File Position

612

master-bin.000001 107

631

613

delete from t1;

632

614

delete from t2;

633

615

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

636

618

UPDATE t2,t1 SET t2.a=t2.b where t2.a=t1.a;

637

619

ERROR 23000: Duplicate entry '4' for key 'PRIMARY'

638

620

show master status /* there must be the UPDATE query event */;

639

File Position Binlog_Do_DB Binlog_Ignore_DB

640

master-bin.000001 213

621

File Position

622

master-bin.000001 107

641

623

drop table t1, t2;

642

set @@session.binlog_format= @sav_binlog_format;

643

drop table if exists t1, t2, t3;

644

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

645

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

646

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

647

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

648

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

649

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

650

reset master;

651

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

652

ERROR 23000: Duplicate entry '1' for key 'PRIMARY'

653

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

654

count(*)

655

1

656

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

657

count(*)

658

1

659

drop table t1, t2, t3;

660

624

end of tests