release/src/router/mysql/mysql-test/t/update.test

   1 #
   2 # test of updating of keys
   3 #
   4
   5 --disable_warnings
   6 drop table if exists t1,t2;
   7 --enable_warnings
   8
   9 create table t1 (a int auto_increment , primary key (a));
  10 insert into t1 values (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
  11 update t1 set a=a+10 where a > 34;
  12 update t1 set a=a+100 where a > 0;
  13
  14 # Some strange updates to test some otherwise unused code
  15 update t1 set a=a+100 where a=1 and a=2;
  16 --error 1054
  17 update t1 set a=b+100 where a=1 and a=2;
  18 --error 1054
  19 update t1 set a=b+100 where c=1 and a=2;
  20 --error 1054
  21 update t1 set d=a+100 where a=1;
  22 select * from t1;
  23 drop table t1;
  24
  25 CREATE TABLE t1
  26  (
  27  place_id int (10) unsigned NOT NULL,
  28  shows int(10) unsigned DEFAULT '0' NOT NULL,
  29  ishows int(10) unsigned DEFAULT '0' NOT NULL,
  30  ushows int(10) unsigned DEFAULT '0' NOT NULL,
  31  clicks int(10) unsigned DEFAULT '0' NOT NULL,
  32  iclicks int(10) unsigned DEFAULT '0' NOT NULL,
  33  uclicks int(10) unsigned DEFAULT '0' NOT NULL,
  34  ts timestamp,
  35  PRIMARY KEY (place_id,ts)
  36  );
  37
  38 INSERT INTO t1 (place_id,shows,ishows,ushows,clicks,iclicks,uclicks,ts)
  39 VALUES (1,0,0,0,0,0,0,20000928174434);
  40 UPDATE t1 SET shows=shows+1,ishows=ishows+1,ushows=ushows+1,clicks=clicks+1,iclicks=iclicks+1,uclicks=uclicks+1 WHERE place_id=1 AND ts>="2000-09-28 00:00:00";
  41 select place_id,shows from t1;
  42 drop table t1;
  43
  44 #
  45 # Test bug with update reported by Jan Legenhausen
  46 #
  47
  48 CREATE TABLE t1 (
  49   lfdnr int(10) unsigned NOT NULL default '0',
  50   ticket int(10) unsigned NOT NULL default '0',
  51   client varchar(255) NOT NULL default '',
  52   replyto varchar(255) NOT NULL default '',
  53   subject varchar(100) NOT NULL default '',
  54   timestamp int(10) unsigned NOT NULL default '0',
  55   tstamp timestamp NOT NULL,
  56   status int(3) NOT NULL default '0',
  57   type varchar(15) NOT NULL default '',
  58   assignment int(10) unsigned NOT NULL default '0',
  59   fupcount int(4) unsigned NOT NULL default '0',
  60   parent int(10) unsigned NOT NULL default '0',
  61   activity int(10) unsigned NOT NULL default '0',
  62   priority tinyint(1) unsigned NOT NULL default '1',
  63   cc varchar(255) NOT NULL default '',
  64   bcc varchar(255) NOT NULL default '',
  65   body text NOT NULL,
  66   comment text,
  67   header text,
  68   PRIMARY KEY  (lfdnr),
  69   KEY k1 (timestamp),
  70   KEY k2 (type),
  71   KEY k3 (parent),
  72   KEY k4 (assignment),
  73   KEY ticket (ticket)
  74 ) ENGINE=MyISAM;
  75
  76 INSERT INTO t1 VALUES (773,773,'','','',980257344,20010318180652,0,'Open',10,0,0,0,1,'','','','','');
  77
  78 alter table t1 change lfdnr lfdnr int(10) unsigned not null auto_increment;
  79 update t1 set status=1 where type='Open';
  80 select status from t1;
  81 drop table t1;
  82
  83 #
  84 # Test of ORDER BY
  85 #
  86
  87 create table t1 (a int not null, b int not null, key (a));
  88 insert into t1 values (1,1),(1,2),(1,3),(3,1),(3,2),(3,3),(3,1),(3,2),(3,3),(2,1),(2,2),(2,3);
  89 SET @tmp=0;
  90 update t1 set b=(@tmp:=@tmp+1) order by a;
  91 update t1 set b=99 where a=1 order by b asc limit 1;
  92 select * from t1 order by a,b;
  93 update t1 set b=100 where a=1 order by b desc limit 2;
  94 update t1 set a=a+10+b where a=1 order by b;
  95 select * from t1 order by a,b;
  96 create table t2 (a int not null, b int not null);
  97 insert into t2 values (1,1),(1,2),(1,3);
  98 update t1 set b=(select distinct 1 from (select * from t2) a);
  99 drop table t1,t2;
 100
 101 #
 102 # Test with limit (Bug #393)
 103 #
 104
 105 CREATE TABLE t1 (
 106    `id_param` smallint(3) unsigned NOT NULL default '0',
 107    `nom_option` char(40) NOT NULL default '',
 108    `valid` tinyint(1) NOT NULL default '0',
 109    KEY `id_param` (`id_param`,`nom_option`)
 110  ) ENGINE=MyISAM;
 111
 112 INSERT INTO t1 (id_param,nom_option,valid) VALUES (185,'600x1200',1);
 113
 114 UPDATE t1 SET nom_option='test' WHERE id_param=185 AND nom_option='600x1200' AND valid=1 LIMIT 1;
 115 select * from t1;
 116 drop table t1;
 117
 118 #
 119 # Multi table update test from bugs
 120 #
 121
 122 create table t1 (F1 VARCHAR(30), F2 VARCHAR(30), F3 VARCHAR(30), cnt int, groupid int, KEY groupid_index (groupid));
 123
 124 insert into t1 (F1,F2,F3,cnt,groupid) values ('0','0','0',1,6),
 125 ('0','1','2',1,5), ('0','2','0',1,3), ('1','0','1',1,2),
 126 ('1','2','1',1,1), ('1','2','2',1,1), ('2','0','1',2,4),
 127 ('2','2','0',1,7);
 128 delete from m1 using t1 m1,t1 m2 where m1.groupid=m2.groupid and (m1.cnt < m2.cnt or m1.cnt=m2.cnt and m1.F3>m2.F3);
 129 select * from t1;
 130 drop table t1;
 131
 132 #
 133 # Bug#5553 - Multi table UPDATE IGNORE fails on duplicate keys
 134 #
 135
 136 CREATE TABLE t1 (
 137    `colA` int(10) unsigned NOT NULL auto_increment,
 138    `colB` int(11) NOT NULL default '0',
 139    PRIMARY KEY  (`colA`)
 140 );
 141 INSERT INTO t1 VALUES (4433,5424);
 142 CREATE TABLE t2 (
 143   `colC` int(10) unsigned NOT NULL default '0',
 144   `colA` int(10) unsigned NOT NULL default '0',
 145   `colD` int(10) unsigned NOT NULL default '0',
 146   `colE` int(10) unsigned NOT NULL default '0',
 147   `colF` int(10) unsigned NOT NULL default '0',
 148   PRIMARY KEY  (`colC`,`colA`,`colD`,`colE`)
 149 );
 150 INSERT INTO t2 VALUES (3,4433,10005,495,500);
 151 INSERT INTO t2 VALUES (3,4433,10005,496,500);
 152 INSERT INTO t2 VALUES (3,4433,10009,494,500);
 153 INSERT INTO t2 VALUES (3,4433,10011,494,500);
 154 INSERT INTO t2 VALUES (3,4433,10005,497,500);
 155 INSERT INTO t2 VALUES (3,4433,10013,489,500);
 156 INSERT INTO t2 VALUES (3,4433,10005,494,500);
 157 INSERT INTO t2 VALUES (3,4433,10005,493,500);
 158 INSERT INTO t2 VALUES (3,4433,10005,492,500);
 159 UPDATE IGNORE t2,t1 set t2.colE = t2.colE + 1,colF=0 WHERE t1.colA = t2.colA AND (t1.colB & 4096) > 0 AND (colE + 1) < colF;
 160 SELECT * FROM t2;
 161 DROP TABLE t1;
 162 DROP TABLE t2;
 163
 164 #
 165 # Bug #6054
 166 #
 167 create table t1 (c1 int, c2 char(6), c3 int);
 168 create table t2 (c1 int, c2 char(6));
 169 insert into t1 values (1, "t1c2-1", 10), (2, "t1c2-2", 20);
 170 update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1";
 171 update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1" where t1.c3 = 10;
 172 drop table t1, t2;
 173
 174 #
 175 # Bug #8057
 176 #
 177 create table t1 (id int not null auto_increment primary key, id_str varchar(32));
 178 insert into t1 (id_str) values ("test");
 179 update t1 set id_str = concat(id_str, id) where id = last_insert_id();
 180 select * from t1;
 181 drop table t1;
 182
 183 #
 184 # Bug #8942: a problem with update and partial key part
 185 #
 186
 187 create table t1 (a int, b char(255), key(a, b(20)));
 188 insert into t1 values (0, '1');
 189 update t1 set b = b + 1 where a = 0;
 190 select * from t1;
 191 drop table t1;
 192
 193 # BUG#9103 "Erroneous data truncation warnings on multi-table updates"
 194 create table t1 (a int, b varchar(10), key b(b(5))) engine=myisam;
 195 create table t2 (a int, b varchar(10)) engine=myisam;
 196 insert into t1 values ( 1, 'abcd1e');
 197 insert into t1 values ( 2, 'abcd2e');
 198 insert into t2 values ( 1, 'abcd1e');
 199 insert into t2 values ( 2, 'abcd2e');
 200 analyze table t1,t2;
 201 update t1, t2 set t1.a = t2.a where t2.b = t1.b;
 202 show warnings;
 203 drop table t1, t2;
 204
 205 #
 206 # Bug #11868 Update with subquery with ref built with a key from the updated
 207 #            table crashes server
 208 #
 209 create table t1(f1 int, f2 int);
 210 create table t2(f3 int, f4 int);
 211 create index idx on t2(f3);
 212 insert into t1 values(1,0),(2,0);
 213 insert into t2 values(1,1),(2,2);
 214 UPDATE t1 SET t1.f2=(SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1);
 215 select * from t1;
 216 drop table t1,t2;
 217
 218 #
 219 # Bug #13180 sometimes server accepts sum func in update/delete where condition
 220 #
 221 create table t1(f1 int);
 222 select DATABASE();
 223 --error 1111
 224 update t1 set f1=1 where count(*)=1;
 225 select DATABASE();
 226 --error 1111
 227 delete from t1 where count(*)=1;
 228 drop table t1;
 229
 230 # BUG#12915: Optimize "DELETE|UPDATE ... ORDER BY ... LIMIT n" to use an index
 231 create table t1 ( a int, b int default 0, index (a) );
 232 insert into t1 (a) values (0),(0),(0),(0),(0),(0),(0),(0);
 233
 234 flush status;
 235 select a from t1 order by a limit 1;
 236 show status like 'handler_read%';
 237
 238 flush status;
 239 update t1 set a=9999 order by a limit 1;
 240 update t1 set b=9999 order by a limit 1;
 241 show status like 'handler_read%';
 242
 243 flush status;
 244 delete from t1 order by a limit 1;
 245 show status like 'handler_read%';
 246
 247 flush status;
 248 delete from t1 order by a desc limit 1;
 249 show status like 'handler_read%';
 250
 251 alter table t1 disable keys;
 252
 253 flush status;
 254 delete from t1 order by a limit 1;
 255 show status like 'handler_read%';
 256
 257 select * from t1;
 258 update t1 set a=a+10,b=1 order by a limit 3;
 259 update t1 set a=a+11,b=2 order by a limit 3;
 260 update t1 set a=a+12,b=3 order by a limit 3;
 261 select * from t1 order by a;
 262
 263 drop table t1;
 264
 265 #
 266 # Bug#14186 select datefield is null not updated
 267 #
 268 create table t1 (f1 date not null);
 269 insert into t1 values('2000-01-01'),('0000-00-00');
 270 update t1 set f1='2002-02-02' where f1 is null;
 271 select * from t1;
 272 drop table t1;
 273
 274 #
 275 # Bug#15028 Multitable update returns different numbers of matched rows
 276 #           depending on table order
 277 create table t1 (f1 int);
 278 create table t2 (f2 int);
 279 insert into t1 values(1),(2);
 280 insert into t2 values(1),(1);
 281 --enable_info
 282 update t1,t2 set f1=3,f2=3 where f1=f2 and f1=1;
 283 --disable_info
 284 update t2 set f2=1;
 285 update t1 set f1=1 where f1=3;
 286 --enable_info
 287 update t2,t1 set f1=3,f2=3 where f1=f2 and f1=1;
 288 --disable_info
 289 drop table t1,t2;
 290
 291
 292 # BUG#15935
 293 create table t1 (a int);
 294 insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 295 create table t2 (a int, filler1 char(200), filler2 char(200), key(a));
 296 insert into t2 select A.a + 10*B.a, 'filler','filler' from t1 A, t1 B;
 297 flush status;
 298 update t2 set a=3 where a=2;
 299 show status like 'handler_read%';
 300 drop table t1, t2;
 301
 302 #
 303 # Bug #16510 Updating field named like '*name' caused server crash
 304 #
 305 create table t1(f1 int, `*f2` int);
 306 insert into t1 values (1,1);
 307 update t1 set `*f2`=1;
 308 drop table t1;
 309
 310 #
 311 # Bug#25126: Wrongly resolved field leads to a crash
 312 #
 313 create table t1(f1 int);
 314 --error 1054
 315 update t1 set f2=1 order by f2;
 316 drop table t1;
 317 # End of 4.1 tests
 318
 319 #
 320 # Bug #24035: performance degradation with condition int_field=big_decimal
 321 #
 322
 323 CREATE TABLE t1 (
 324   request_id int unsigned NOT NULL auto_increment,
 325   user_id varchar(12) default NULL,
 326   time_stamp datetime NOT NULL default '0000-00-00 00:00:00',
 327   ip_address varchar(15) default NULL,
 328   PRIMARY KEY (request_id),
 329   KEY user_id_2 (user_id,time_stamp)
 330 );
 331
 332 INSERT INTO t1 (user_id) VALUES ('user1');
 333 INSERT INTO t1(user_id) SELECT user_id FROM t1;
 334 INSERT INTO t1(user_id) SELECT user_id FROM t1;
 335 INSERT INTO t1(user_id) SELECT user_id FROM t1;
 336 INSERT INTO t1(user_id) SELECT user_id FROM t1;
 337 INSERT INTO t1(user_id) SELECT user_id FROM t1;
 338 INSERT INTO t1(user_id) SELECT user_id FROM t1;
 339 INSERT INTO t1(user_id) SELECT user_id FROM t1;
 340 INSERT INTO t1(user_id) SELECT user_id FROM t1;
 341
 342 flush status;
 343 SELECT user_id FROM t1 WHERE request_id=9999999999999;
 344 show status like '%Handler_read%';
 345 SELECT user_id FROM t1 WHERE request_id=999999999999999999999999999999;
 346 show status like '%Handler_read%';
 347 UPDATE t1 SET user_id=null WHERE request_id=9999999999999;
 348 show status like '%Handler_read%';
 349 UPDATE t1 SET user_id=null WHERE request_id=999999999999999999999999999999;
 350 show status like '%Handler_read%';
 351
 352 DROP TABLE t1;
 353
 354 #
 355 # Bug #24010: INSERT INTO ... SELECT fails on unique constraint with data it
 356 # doesn't select
 357 #
 358 CREATE TABLE t1 (
 359
 360   a INT(11),
 361   quux decimal( 31, 30 ),
 362
 363   UNIQUE KEY bar (a),
 364   KEY quux (quux)
 365 );
 366
 367 INSERT INTO
 368  t1 ( a, quux )
 369 VALUES
 370     ( 1,    1 ),
 371     ( 2,  0.1 );
 372
 373 INSERT INTO t1( a )
 374   SELECT @newA := 1 + a FROM t1 WHERE quux <= 0.1;
 375
 376 SELECT * FROM t1;
 377
 378 DROP TABLE t1;
 379
 380 #
 381 # Bug #22364: Inconsistent "matched rows" when executing UPDATE
 382 #
 383
 384 connect (con1,localhost,root,,test);
 385 connection con1;
 386
 387 set tmp_table_size=1024;
 388
 389 # Create the test tables
 390 create table t1 (id int, a int, key idx(a));
 391 create table t2 (id int unsigned not null auto_increment primary key, a int);
 392 insert into t2(a) values(1),(2),(3),(4),(5),(6),(7),(8);
 393 insert into t2(a) select a from t2;
 394 insert into t2(a) select a from t2;
 395 insert into t2(a) select a from t2;
 396 update t2 set a=id;
 397 insert into t1 select * from t2;
 398
 399 # Check that the number of matched rows is correct when the temporary
 400 # table is small enough to not be converted to MyISAM
 401 select count(*) from t1 join t2 on (t1.a=t2.a);
 402 --enable_info
 403 update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;
 404 --disable_info
 405
 406 # Increase table sizes
 407 insert into t2(a) select a from t2;
 408 update t2 set a=id;
 409 truncate t1;
 410 insert into t1 select * from t2;
 411
 412 # Check that the number of matched rows is correct when the temporary
 413 # table has to be converted to MyISAM
 414 select count(*) from t1 join t2 on (t1.a=t2.a);
 415 --enable_info
 416 update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;
 417 --disable_info
 418
 419 # Check that the number of matched rows is correct when there are duplicate
 420 # key errors
 421 update t1 set a=1;
 422 update t2 set a=1;
 423 select count(*) from t1 join t2 on (t1.a=t2.a);
 424 --enable_info
 425 update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;
 426 --disable_info
 427
 428 drop table t1,t2;
 429
 430 connection default;
 431 disconnect con1;
 432
 433 #
 434 # Bug #40745: Error during WHERE clause calculation in UPDATE
 435 #             leads to an assertion failure
 436 #
 437 --disable_warnings
 438 DROP TABLE IF EXISTS t1;
 439 DROP FUNCTION IF EXISTS f1;
 440 --enable_warnings
 441
 442 CREATE FUNCTION f1() RETURNS INT RETURN f1();
 443 CREATE TABLE t1 (i INT);
 444 INSERT INTO t1 VALUES (1);
 445
 446 --error ER_SP_NO_RECURSION
 447 UPDATE t1 SET i = 3 WHERE f1();
 448 --error ER_SP_NO_RECURSION
 449 UPDATE t1 SET i = f1();
 450
 451 DROP TABLE t1;
 452 DROP FUNCTION f1;
 453
 454 --echo End of 5.0 tests
 455
 456 --echo #
 457 --echo # Bug #47919 assert in open_table during ALTER temporary table
 458 --echo #
 459
 460 CREATE TABLE t1 (f1 INTEGER AUTO_INCREMENT, PRIMARY KEY (f1));
 461 CREATE TEMPORARY TABLE t2 LIKE t1;
 462 INSERT INTO t1 VALUES (1);
 463 INSERT INTO t2 VALUES (1);
 464
 465 ALTER TABLE t2 COMMENT = 'ABC';
 466 UPDATE t2, t1 SET t2.f1 = 2, t1.f1 = 9;
 467 ALTER TABLE t2 COMMENT = 'DEF';
 468
 469 DROP TABLE t1, t2;
 470
 471 --echo #
 472 --echo # Bug#50545: Single table UPDATE IGNORE crashes on join view in
 473 --echo # sql_safe_updates mode.
 474 --echo #
 475 CREATE TABLE t1 ( a INT, KEY( a ) );
 476 INSERT INTO t1 VALUES (0), (1);
 477 CREATE VIEW v1 AS SELECT t11.a, t12.a AS b FROM t1 t11, t1 t12;
 478 SET SESSION sql_safe_updates = 1;
 479
 480 --error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
 481 UPDATE IGNORE v1 SET a = 1;
 482
 483 SET SESSION sql_safe_updates = DEFAULT;
 484 DROP TABLE t1;
 485 DROP VIEW v1;
 486
 487 --echo #
 488 --echo # Bug#54734 assert in Diagnostics_area::set_ok_status
 489 --echo #
 490
 491 --disable_warnings
 492 DROP TABLE IF EXISTS t1, not_exists;
 493 DROP FUNCTION IF EXISTS f1;
 494 DROP VIEW IF EXISTS v1;
 495 --enable_warnings
 496
 497 CREATE TABLE t1 (PRIMARY KEY(pk)) AS SELECT 1 AS pk;
 498 CREATE FUNCTION f1() RETURNS INTEGER RETURN (SELECT 1 FROM not_exists);
 499 CREATE VIEW v1 AS SELECT pk FROM t1 WHERE f1() = 13;
 500 --error ER_VIEW_INVALID
 501 UPDATE v1 SET pk = 7 WHERE pk > 0;
 502
 503 DROP VIEW v1;
 504 DROP FUNCTION f1;
 505 DROP TABLE t1;