~drizzle-trunk/drizzle/development

#

# Test of update statement that uses many tables.

#

source include/have_log_bin.inc;

--disable_warnings

drop table if exists t1,t2,t3;

drop database if exists mysqltest;

--enable_warnings

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

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

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

disable_query_log;

let $1 = 100;

while ($1)

 {

  let $2 = 5;

  eval insert into t1(t) values ('$1'); 

  while ($2)

   {

     eval insert into t2(id2,t) values ($1,'$2'); 

     let $3 = 10;

     while ($3)

     {

       eval insert into t3(id3,t) values ($1,'$2'); 

       dec $3;

     }

     dec $2; 

   }

  dec $1;

 }

enable_query_log;

select count(*) from t1 where id1 > 95;

select count(*) from t2 where id2 > 95;

select count(*) from t3 where id3 > 95;

update t1,t2,t3 set t1.t="aaa", t2.t="bbb", t3.t="cc" where  t1.id1 = t2.id2 and t2.id2 = t3.id3  and t1.id1 > 90;

select count(*) from t1 where t = "aaa";

select count(*) from t1 where id1 > 90;

select count(*) from t2 where t = "bbb";

select count(*) from t2 where id2 > 90;

select count(*) from t3 where t = "cc";

select count(*) from t3 where id3 > 90;

delete t1.*, t2.*, t3.*  from t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3  and t1.id1 > 95;

check table t1, t2, t3;

select count(*) from t1 where id1 > 95;

select count(*) from t2 where id2 > 95;

select count(*) from t3 where id3 > 95;

delete t1, t2, t3  from t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3  and t1.id1 > 5;

select count(*) from t1 where id1 > 5;

select count(*) from t2 where id2 > 5;

select count(*) from t3 where id3 > 5;

delete from t1, t2, t3  using t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3  and t1.id1 > 0;

# These queries will force a scan of the table

select count(*) from t1 where id1;

select count(*) from t2 where id2;

select count(*) from t3 where id3;

drop table t1,t2,t3;

create table t1(id1 int not null  primary key, t varchar(100)) pack_keys = 1;

create table t2(id2 int not null, t varchar(100), index(id2)) pack_keys = 1;

disable_query_log;

let $1 = 1000;

while ($1)

 {

  let $2 = 5;

  eval insert into t1 values ($1,'aaaaaaaaaaaaaaaaaaaa'); 

  while ($2)

   {

     eval insert into t2(id2,t) values ($1,'bbbbbbbbbbbbbbbbb'); 

     dec $2; 

   }

  dec $1;

 }

enable_query_log;

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

drop table t1,t2;

CREATE TABLE t1 (

  id int NOT NULL default '0',

  name varchar(10) default NULL,

  PRIMARY KEY  (id)

) ENGINE=MyISAM;

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

CREATE TABLE t2 (

  id int NOT NULL default '0',

  name varchar(10) default NULL,

  PRIMARY KEY  (id)

) ENGINE=MyISAM;

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

CREATE TABLE t3 (

  id int NOT NULL default '0',

  mydate datetime default NULL,

  PRIMARY KEY  (id)

) ENGINE=MyISAM;

INSERT INTO t3 VALUES (1,'2002-02-04 00:00:00'),(3,'2002-05-12 00:00:00'),(5,'2002-05-12 00:00:00'),(6,'2002-06-22

00:00:00'),(7,'2002-07-22 00:00:00');

delete t1,t2,t3 from t1,t2,t3 where to_days(now())-to_days(t3.mydate)>=30 and t3.id=t1.id and t3.id=t2.id;

select * from t3;

DROP TABLE t1,t2,t3;

CREATE TABLE IF NOT EXISTS `t1` (

  `id` int NOT NULL auto_increment,

  `tst` text,

  `tst1` text,

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM;

CREATE TABLE IF NOT EXISTS `t2` (

  `ID` int NOT NULL auto_increment,

  `ParId` int default NULL,

  `tst` text,

  `tst1` text,

  PRIMARY KEY  (`ID`),

  KEY `IX_ParId_t2` (`ParId`),

  FOREIGN KEY (`ParId`) REFERENCES `t1` (`id`)

) ENGINE=MyISAM;

INSERT INTO t1(tst,tst1) VALUES("MySQL","MySQL AB"), ("MSSQL","Microsoft"), ("ORACLE","ORACLE");

INSERT INTO t2(ParId) VALUES(1), (2), (3);

select * from t2;

UPDATE t2, t1 SET t2.tst = t1.tst, t2.tst1 = t1.tst1 WHERE t2.ParId = t1.Id;

select * from t2;

drop table t1, t2 ;

create table t1 (n numeric(10));

create table t2 (n numeric(10));

insert into t2 values (1),(2),(4),(8),(16),(32);

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

delete  t1,t2 from t2 left outer join t1  using (n);

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

drop table t1,t2 ;

#

# Test with locking

#

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

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

insert into t1 values(1,1);

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

LOCK TABLES t1 write, t2 read;

--error 1099

DELETE t1.*, t2.* FROM t1,t2 where t1.n=t2.n;

--error 1099

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

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

unlock tables;

LOCK TABLES t1 write, t2 write;

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

select * from t1;

DELETE t1.*, t2.* FROM t1,t2 where t1.n=t2.n;

select * from t1;

select * from t2;

unlock tables;

drop table t1,t2;

#

# Test safe updates and timestamps

#

set sql_safe_updates=1;

create table t1 (n int, d int);

create table t2 (n int, d int);

insert into t1 values(1,1);

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

--error 1175

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

set sql_safe_updates=0;

drop table t1,t2;

set timestamp=1038401397;

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

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

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

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

set timestamp=1038000000;

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

select n,d,unix_timestamp(t) from t1;

select n,d,unix_timestamp(t) from t2;

--error 1064

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

drop table t1,t2;

set timestamp=0;

set sql_safe_updates=0;

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

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

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

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

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

select * from t1;

select * from t2;

drop table t1,t2;

create table t1 (n int, d int);

create table t2 (n int, d int);

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

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

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

select * from t1;

select * from t2;

drop table t1,t2;

create table t1 (n int, d int);

create table t2 (n int, d int);

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

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

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

select * from t1;

select * from t2;

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

select * from t1;

select * from t2;

DELETE a, b  FROM t1 a,t2 b where a.n=b.n;

select * from t1;

select * from t2;

drop table t1,t2;

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

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

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

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

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

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

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

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

drop table t1,t2,t3;

#

# Test multi update with different join methods

#

CREATE TABLE t1 (a int not null primary key, b int not null, key (b));

CREATE TABLE t2 (a int not null primary key, b int not null, key (b));

INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);

INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);

# Full join, without key

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

select * from t1;

# unique key

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

select * from t1;

# ref key

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

select * from t1;

# Range key (in t1)

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;

select * from t1;

select * from t2;

# test for non-updating table which is also used in sub-select

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

drop table t1,t2;

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;

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

create table t1 (A varchar(1));

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

create table t2(Z varchar(15));

insert into t2(Z)  select concat(a.a,b.a,c.a,d.a) from t1 as a, t1 as b, t1 as c, t1 as d;

update t2,t3 set Z =param_scenario_costs;

drop table t1,t2,t3;

create table t1 (a int, b int);

create table t2 (a int, b int);

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

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

update t1 left join t2  on t1.a=t2.a set t1.b=2, t2.b=2 where t1.b=1 and t2.b=1 or t2.a is NULL;

select t1.a, t1.b,t2.a, t2.b from t1 left join t2  on t1.a=t2.a where t1.b=1 and t2.b=1 or t2.a is NULL;

drop table t1,t2;

#

# Test reuse of same table

#

create table t1 (a int not null auto_increment primary key, b int not null);

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

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

select * from t1;

drop table t1;

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

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

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

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

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

select * from t1;

select * from t2;

update t2 inner join t1 on t1.id1=t2.id2

  set t2.field=t1.field

  where 0=1;

update t2, t1 set t2.field=t1.field

  where t1.id1=t2.id2 and 0=1;

delete t1, t2 from t2 inner join t1 on t1.id1=t2.id2

  where 0=1;

delete t1, t2 from t2,t1 

  where t1.id1=t2.id2 and 0=1;

drop table t1,t2;

#

# Test for bug #1820.

#

create table t1 ( a int not null, b int not null) ;

--disable_query_log

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

let $1=19;

set @d=4;

while ($1)

{

  eval insert into t1 select a+@d,b+@d from t1;

  eval set @d=@d*2;

  dec $1;

}

--enable_query_log

alter table t1 add index i1(a);

delete from t1 where a > 2000000;

create table t2 like t1;

insert into t2 select * from t1;

select 't2 rows before small delete', count(*) from t1;

delete t1,t2 from t1,t2 where t1.b=t2.a and t1.a < 2;

select 't2 rows after small delete', count(*) from t2;

select 't1 rows after small delete', count(*) from t1;

## Try deleting many rows 

delete t1,t2 from t1,t2 where t1.b=t2.a and t1.a < 100*1000;

select 't2 rows after big delete', count(*) from t2;

select 't1 rows after big delete', count(*) from t1;

drop table t1,t2;

#

# Test alias (this is not correct in 4.0)

#

CREATE TABLE t1 ( a int );

CREATE TABLE t2 ( a int );

DELETE t1 FROM t1, t2 AS t3;

DELETE t4 FROM t1, t1 AS t4;

DELETE t3 FROM t1 AS t3, t1 AS t4;

--error 1109

DELETE t1 FROM t1 AS t3, t2 AS t4;

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

INSERT INTO t2 values (1),(2);

DELETE t1 FROM t1 AS t2, t2 AS t1 where t1.a=t2.a and t1.a=1;

SELECT * from t1;

SELECT * from t2;

DELETE t2 FROM t1 AS t2, t2 AS t1 where t1.a=t2.a and t1.a=2;

SELECT * from t1;

SELECT * from t2;

DROP TABLE t1,t2;

#

# Test update with const tables

#

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

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

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

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

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

update t1 left join t2 on p_id = c2_p_id set c2_note = 'asdf-1' where p_id = 2; 

select * from t1;

select * from t2;

drop table t1, t2;

#

# multi delete wrong table check

#

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

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

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

-- error 1109

delete t1,t3 from t1,t2 where t1.a=t2.a and t2.a=(select t3.a from t3 where t1.a=t3.a);

drop table t1, t2, t3;

#

# multi* unique updating table check

#

create table t1 (col1 int); 

create table t2 (col1 int);

-- error 1093

update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1;

-- error 1093

delete t1 from t1,t2 where t1.col1 < (select max(col1) from t1) and t1.col1 = t2.col1;

drop table t1,t2;

# Test for BUG#5837 - delete with outer join and const tables

--disable_warnings

create table t1 (

  aclid bigint not null primary key, 

  status int not null 

) engine = innodb;

create table t2 (

  refid bigint not null primary key, 

  aclid bigint, index idx_acl(aclid) 

) engine = innodb;

--enable_warnings

insert into t2 values(1,null);

delete t2, t1 from t2 left join t1 on (t2.aclid=t1.aclid) where t2.refid='1';

drop table t1, t2;

#

# Bug#19225: unchecked error leads to server crash

#

create table t1(a int);

create table t2(a int);

--error 1093

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

drop table t1, t2;

# End of 4.1 tests

#

# Test for bug #1980.

#

--disable_warnings

create table t1 ( c char(8) not null ) engine=innodb;

--enable_warnings

insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');

insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');

alter table t1 add b char(8) not null;

alter table t1 add a char(8) not null;

alter table t1 add primary key (a,b,c);

update t1 set a=c, b=c;

create table t2 like t1;

insert into t2 select * from t1;

delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;

drop table t1,t2;

--disable_warnings

create table t1 ( c char(8) not null ) engine=innodb;

--enable_warnings

insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');

insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');

alter table t1 add b char(8) not null;

alter table t1 add a char(8) not null;

alter table t1 add primary key (a,b,c);

update t1 set a=c, b=c;

create table t2 like t1;

insert into t2 select * from t1;

delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;

drop table t1,t2;

#

# Test alter table and a concurrent multi update

# (This will force update to reopen tables)

#

create table t1 (a int, b int);

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

create table t2 (a int);

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

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

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

connection locker;

lock table t1 write;

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

connection changer;

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

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

connection updater;

sleep 2;

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

connection locker;

sleep 2;

unlock tables;

connection changer;

reap;

connection updater;

reap;

select * from t1;

select * from t2;

drop table v1;

drop table t1, t2;

#

# Test multi updates and deletes using primary key and without.

#

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

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

insert into t1 values (1,5,10),(3,7,12),(4,5,2),(9,10,15),(2,2,2);