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

   1 #
   2 # Problem with INSERT ... SELECT
   3 #
   4
   5 --disable_warnings
   6 drop table if exists t1,t2,t3;
   7 --enable_warnings
   8
   9 create table t1 (bandID MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY, payoutID SMALLINT UNSIGNED NOT NULL);
  10 insert into t1 (bandID,payoutID) VALUES (1,6),(2,6),(3,4),(4,9),(5,10),(6,1),(7,12),(8,12);
  11 create table t2 (payoutID SMALLINT UNSIGNED NOT NULL PRIMARY KEY);
  12 insert into t2 (payoutID) SELECT DISTINCT payoutID FROM t1;
  13 --error ER_DUP_ENTRY
  14 insert into t2 (payoutID) SELECT payoutID+10 FROM t1;
  15 insert ignore into t2 (payoutID) SELECT payoutID+10 FROM t1;
  16 select * from t2;
  17 drop table t1,t2;
  18
  19 #
  20 # bug in bulk insert optimization
  21 # test case by Fournier Jocelyn <joc@presence-pc.com>
  22 #
  23
  24 CREATE TABLE `t1` (
  25   `numeropost` bigint(20) unsigned NOT NULL default '0',
  26   `icone` tinyint(4) unsigned NOT NULL default '0',
  27   `numreponse` bigint(20) unsigned NOT NULL auto_increment,
  28   `contenu` text NOT NULL,
  29   `pseudo` varchar(50) NOT NULL default '',
  30   `date` datetime NOT NULL default '0000-00-00 00:00:00',
  31   `ip` bigint(11) NOT NULL default '0',
  32   `signature` tinyint(1) unsigned NOT NULL default '0',
  33   PRIMARY KEY  (`numeropost`,`numreponse`)
  34   ,KEY `ip` (`ip`),
  35   KEY `date` (`date`),
  36   KEY `pseudo` (`pseudo`),
  37   KEY `numreponse` (`numreponse`)
  38 ) ENGINE=MyISAM;
  39
  40 CREATE TABLE `t2` (
  41   `numeropost` bigint(20) unsigned NOT NULL default '0',
  42   `icone` tinyint(4) unsigned NOT NULL default '0',
  43   `numreponse` bigint(20) unsigned NOT NULL auto_increment,
  44   `contenu` text NOT NULL,
  45   `pseudo` varchar(50) NOT NULL default '',
  46   `date` datetime NOT NULL default '0000-00-00 00:00:00',
  47   `ip` bigint(11) NOT NULL default '0',
  48   `signature` tinyint(1) unsigned NOT NULL default '0',
  49   PRIMARY KEY  (`numeropost`,`numreponse`),
  50   KEY `ip` (`ip`),
  51   KEY `date` (`date`),
  52   KEY `pseudo` (`pseudo`),
  53   KEY `numreponse` (`numreponse`)
  54 ) ENGINE=MyISAM;
  55
  56 INSERT INTO t2
  57 (numeropost,icone,numreponse,contenu,pseudo,date,ip,signature) VALUES
  58 (9,1,56,'test','joce','2001-07-25 13:50:53'
  59 ,3649052399,0);
  60
  61
  62 INSERT INTO t1 (numeropost,icone,contenu,pseudo,date,signature,ip)
  63 SELECT 1618,icone,contenu,pseudo,date,signature,ip FROM t2
  64 WHERE numeropost=9 ORDER BY numreponse ASC;
  65
  66 show variables like '%bulk%';
  67
  68 INSERT INTO t1 (numeropost,icone,contenu,pseudo,date,signature,ip)
  69 SELECT 1718,icone,contenu,pseudo,date,signature,ip FROM t2
  70 WHERE numeropost=9 ORDER BY numreponse ASC;
  71
  72 DROP TABLE t1,t2;
  73
  74 #
  75 # Test of insert ... select from same table
  76 #
  77
  78 create table t1 (a int not null);
  79 create table t2 (a int not null);
  80 insert into t1 values (1);
  81 insert into t1 values (a+2);
  82 insert into t1 values (a+3);
  83 insert into t1 values (4),(a+5);
  84 insert into t1 select * from t1;
  85 select * from t1;
  86 insert into t1 select * from t1 as t2;
  87 select * from t1;
  88 insert into t2 select * from t1 as t2;
  89 select * from t1;
  90 insert into t1 select t2.a from t1,t2;
  91 select * from t1;
  92 --error 1066
  93 insert into t1 select * from t1,t1;
  94 drop table t1,t2;
  95
  96 #
  97 # test replace ... select
  98 #
  99
 100 create table t1 (a int not null primary key, b char(10));
 101 create table t2 (a int not null, b char(10));
 102 insert into t1 values (1,"t1:1"),(3,"t1:3");
 103 insert into t2 values (2,"t2:2"), (3,"t2:3");
 104 --error ER_DUP_ENTRY
 105 insert into t1 select * from t2;
 106 select * from t1;
 107 # REPLACE .. SELECT is not yet supported by PS
 108 replace into t1 select * from t2;
 109 select * from t1;
 110 drop table t1,t2;
 111
 112 #
 113 # Test that caused uninitialized memory access in auto_increment_key update
 114 #
 115
 116 CREATE TABLE t1 ( USID INTEGER UNSIGNED, ServerID TINYINT UNSIGNED, State ENUM ('unknown', 'Access-Granted', 'Session-Active', 'Session-Closed' ) NOT NULL DEFAULT 'unknown', SessionID CHAR(32), User CHAR(32) NOT NULL DEFAULT '<UNKNOWN>', NASAddr INTEGER UNSIGNED, NASPort INTEGER UNSIGNED, NASPortType INTEGER UNSIGNED, ConnectSpeed INTEGER UNSIGNED, CarrierType CHAR(32), CallingStationID CHAR(32), CalledStationID CHAR(32), AssignedAddr INTEGER UNSIGNED, SessionTime INTEGER UNSIGNED, PacketsIn INTEGER UNSIGNED, OctetsIn INTEGER UNSIGNED, PacketsOut INTEGER UNSIGNED, OctetsOut INTEGER UNSIGNED, TerminateCause INTEGER UNSIGNED, UnauthTime TINYINT UNSIGNED, AccessRequestTime DATETIME, AcctStartTime DATETIME, AcctLastTime DATETIME, LastModification TIMESTAMP NOT NULL);
 117 CREATE TABLE t2 ( USID INTEGER UNSIGNED AUTO_INCREMENT, ServerID TINYINT UNSIGNED, State ENUM ('unknown', 'Access-Granted', 'Session-Active', 'Session-Closed' ) NOT NULL DEFAULT 'unknown', SessionID CHAR(32), User TEXT NOT NULL, NASAddr INTEGER UNSIGNED, NASPort INTEGER UNSIGNED, NASPortType INTEGER UNSIGNED, ConnectSpeed INTEGER UNSIGNED, CarrierType CHAR(32), CallingStationID CHAR(32), CalledStationID CHAR(32), AssignedAddr INTEGER UNSIGNED, SessionTime INTEGER UNSIGNED, PacketsIn INTEGER UNSIGNED, OctetsIn INTEGER UNSIGNED, PacketsOut INTEGER UNSIGNED, OctetsOut INTEGER UNSIGNED, TerminateCause INTEGER UNSIGNED, UnauthTime TINYINT UNSIGNED, AccessRequestTime DATETIME, AcctStartTime DATETIME, AcctLastTime DATETIME, LastModification TIMESTAMP NOT NULL, INDEX(USID,ServerID,NASAddr,SessionID), INDEX(AssignedAddr));
 118 INSERT INTO t1 VALUES (39,42,'Access-Granted','46','491721000045',2130706433,17690,NULL,NULL,'Localnet','491721000045','49172200000',754974766,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2003-07-18 00:11:21',NULL,NULL,20030718001121);
 119 INSERT INTO t2 SELECT USID, ServerID, State, SessionID, User, NASAddr, NASPort, NASPortType, ConnectSpeed, CarrierType, CallingStationID, CalledStationID, AssignedAddr, SessionTime, PacketsIn, OctetsIn, PacketsOut, OctetsOut, TerminateCause, UnauthTime, AccessRequestTime, AcctStartTime, AcctLastTime, LastModification from t1 LIMIT 1;
 120 drop table t1,t2;
 121
 122 #
 123 # Another problem from Bug #2012
 124 #
 125
 126 CREATE TABLE t1(
 127  Month date NOT NULL,
 128  Type tinyint(3) unsigned NOT NULL auto_increment,
 129  Field int(10) unsigned NOT NULL,
 130  Count int(10) unsigned NOT NULL,
 131  UNIQUE KEY Month (Month,Type,Field)
 132 );
 133
 134 insert into t1 Values
 135 (20030901, 1, 1, 100),
 136 (20030901, 1, 2, 100),
 137 (20030901, 2, 1, 100),
 138 (20030901, 2, 2, 100),
 139 (20030901, 3, 1, 100);
 140
 141 select * from t1;
 142
 143 Select null, Field, Count From t1 Where Month=20030901 and Type=2;
 144
 145 create table t2(No int not null, Field int not null, Count int not null);
 146
 147 insert into t2 Select null, Field, Count From t1 Where Month=20030901 and Type=2;
 148
 149 select * from t2;
 150
 151 drop table t1, t2;
 152
 153 #
 154 # BUG#6034 - Error code 124:  Wrong medium type
 155 #
 156 CREATE TABLE t1 (
 157   ID           int(11) NOT NULL auto_increment,
 158   NO           int(11) NOT NULL default '0',
 159   SEQ          int(11) NOT NULL default '0',
 160   PRIMARY KEY  (ID),
 161   KEY t1$NO    (SEQ,NO)
 162 ) ENGINE=MyISAM;
 163 INSERT INTO t1 (SEQ, NO) SELECT "1" AS SEQ, IF(MAX(NO) IS NULL, 0, MAX(NO)) + 1 AS NO FROM t1 WHERE (SEQ = 1);
 164 select SQL_BUFFER_RESULT * from t1 WHERE (SEQ = 1);
 165 drop table t1;
 166
 167 #
 168 # Bug#10886 - Have to restore default values after update ON DUPLICATE KEY
 169 #
 170 create table t1 (f1 int);
 171 create table t2 (ff1 int unique, ff2 int default 1);
 172 insert into t1 values (1),(1),(2);
 173 insert into t2(ff1) select f1 from t1 on duplicate key update ff2=ff2+1;
 174 select * from t2;
 175 drop table t1, t2;
 176 #
 177 # BUGS #9728 - 'Decreased functionality in "on duplicate key update"'
 178 #      #8147 - 'a column proclaimed ambigous in INSERT ... SELECT .. ON
 179 #      DUPLICATE'
 180 #
 181 create table t1 (a int unique);
 182 create table t2 (a int, b int);
 183 create table t3 (c int, d int);
 184 insert into t1 values (1),(2);
 185 insert into t2 values (1,2);
 186 insert into t3 values (1,6),(3,7);
 187 select * from t1;
 188 insert into t1 select a from t2 on duplicate key update a= t1.a + t2.b;
 189 select * from t1;
 190 insert into t1 select a+1 from t2 on duplicate key update t1.a= t1.a + t2.b+1;
 191 select * from t1;
 192 insert into t1 select t3.c from t3 on duplicate key update a= a + t3.d;
 193 select * from t1;
 194 insert into t1 select t2.a from t2 group by t2.a on duplicate key update a= a + 10;
 195
 196 #Some error cases
 197 --error 1052
 198 insert into t1 select t2.a from t2 on duplicate key update a= a + t2.b;
 199 --error 1054
 200 insert into t1 select t2.a from t2 on duplicate key update t2.a= a + t2.b;
 201 --error 1054
 202 insert into t1 select t2.a from t2 group by t2.a on duplicate key update a= t1.a + t2.b;
 203 drop table t1,t2,t3;
 204
 205 #
 206 # Bug #12695 Item_func_isnull::update_used_tables() did not update
 207 #            const_item_cache
 208 create table t1(f1 varchar(5) key);
 209 insert into t1(f1) select if(max(f1) is null, '2000',max(f1)+1) from t1;
 210 insert into t1(f1) select if(max(f1) is null, '2000',max(f1)+1) from t1;
 211 insert into t1(f1) select if(max(f1) is null, '2000',max(f1)+1) from t1;
 212 select * from t1;
 213 drop table t1;
 214
 215 #
 216 # Bug #13392 values() fails with 'ambiguous' or returns NULL
 217 #            with ON DUPLICATE and SELECT
 218 create table t1(x int, y int);
 219 create table t2(x int, z int);
 220 insert into t1(x,y) select x,z from t2 on duplicate key update x=values(x);
 221 --error 1054
 222 insert into t1(x,y) select x,z from t2 on duplicate key update x=values(z);
 223 --error 1054
 224 insert into t1(x,y) select x,z from t2 on duplicate key update x=values(t2.x);
 225 drop table t1,t2;
 226
 227 #
 228 # Bug #9676: INSERT INTO x SELECT .. FROM x LIMIT 1; slows down with big
 229 #             tables
 230 #
 231
 232 #Note: not an exsaustive test : just a check of the code path.
 233 CREATE TABLE t1 (a int PRIMARY KEY);
 234 INSERT INTO t1 values (1), (2);
 235
 236 flush status;
 237 INSERT INTO t1 SELECT a + 2 FROM t1 LIMIT 1;
 238 show status like 'Handler_read%';
 239
 240 DROP TABLE t1;
 241
 242 #
 243 # Bug #29717 INSERT INTO SELECT inserts values even if SELECT statement itself returns empty
 244 #
 245
 246 CREATE TABLE t1 (
 247     f1 int(10) unsigned NOT NULL auto_increment PRIMARY KEY,
 248     f2 varchar(100) NOT NULL default ''
 249 );
 250 CREATE TABLE t2 (
 251     f1 varchar(10) NOT NULL default '',
 252     f2 char(3) NOT NULL default '',
 253     PRIMARY KEY  (`f1`),
 254     KEY `k1` (`f2`, `f1`)
 255 );
 256
 257 INSERT INTO t1 values(NULL, '');
 258 INSERT INTO `t2` VALUES ('486878','WDT'),('486910','WDT');
 259 SELECT COUNT(*) FROM t1;
 260
 261 SELECT min(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
 262
 263 INSERT INTO t1 (f2)
 264   SELECT min(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
 265
 266 SELECT COUNT(*) FROM t1;
 267 SELECT * FROM t1;
 268 DROP TABLE t1, t2;
 269
 270 # End of 4.1 tests
 271
 272 #
 273 # Bug #18080: INSERT ... SELECT ... JOIN results in ambiguous field list error
 274 #
 275 CREATE TABLE t1 (x int, y int);
 276 CREATE TABLE t2 (z int, y int);
 277 CREATE TABLE t3 (a int, b int);
 278 INSERT INTO t3 (SELECT x, y FROM t1 JOIN t2 USING (y) WHERE z = 1);
 279 DROP TABLE IF EXISTS t1,t2,t3;
 280
 281 #
 282 # Bug #21774: Column count doesn't match value count at row x
 283 #
 284 CREATE DATABASE bug21774_1;
 285 CREATE DATABASE bug21774_2;
 286
 287 CREATE TABLE bug21774_1.t1(id VARCHAR(10) NOT NULL,label VARCHAR(255));
 288 CREATE TABLE bug21774_2.t1(id VARCHAR(10) NOT NULL,label VARCHAR(255));
 289 CREATE TABLE bug21774_1.t2(id VARCHAR(10) NOT NULL,label VARCHAR(255));
 290
 291 INSERT INTO bug21774_2.t1 SELECT t1.* FROM bug21774_1.t1;
 292
 293 use bug21774_1;
 294 INSERT INTO bug21774_2.t1 SELECT t1.* FROM t1;
 295
 296 DROP DATABASE bug21774_1;
 297 DROP DATABASE bug21774_2;
 298 USE test;
 299
 300 #
 301 # Bug#19978: INSERT .. ON DUPLICATE erroneously reports some records were
 302 #            updated.
 303 #
 304 create table t1(f1 int primary key, f2 int);
 305 --enable_info
 306 insert into t1 values (1,1);
 307 insert into t1 values (1,1) on duplicate key update f2=1;
 308 insert into t1 values (1,1) on duplicate key update f2=2;
 309 --disable_info
 310 select * from t1;
 311 drop table t1;
 312
 313 #
 314 # Bug#16630: wrong result, when INSERT t1 SELECT ... FROM t1 ON DUPLICATE
 315 #
 316 CREATE TABLE t1 (f1 INT, f2 INT );
 317 CREATE TABLE t2  (f1 INT PRIMARY KEY, f2 INT);
 318 INSERT INTO t1 VALUES (1,1),(2,2),(10,10);
 319 INSERT INTO t2 (f1, f2) SELECT f1, f2 FROM t1;
 320 INSERT INTO t2 (f1, f2)
 321   SELECT f1, f1 FROM t2 src WHERE f1 < 2
 322   ON DUPLICATE KEY UPDATE f1 = 100 + src.f1;
 323 SELECT * FROM t2;
 324 DROP TABLE t1, t2;
 325
 326 #
 327 # Bug#44306: Assertion fail on duplicate key error in 'INSERT ... SELECT'
 328 # statements
 329 #
 330 CREATE TABLE t1 ( a INT KEY, b INT );
 331 INSERT INTO t1 VALUES ( 0, 1 );
 332 --error ER_DUP_ENTRY
 333 INSERT INTO t1 ( b ) SELECT MAX( b ) FROM t1 WHERE b = 2;
 334 DROP TABLE t1;
 335
 336 #
 337 # Bug #26207: inserts don't work with shortened index
 338 #
 339 SET SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
 340
 341 CREATE TABLE t1 (c VARCHAR(30), INDEX ix_c (c(10)));
 342 CREATE TABLE t2 (d VARCHAR(10));
 343 INSERT INTO t1 (c) VALUES ('7_chars'), ('13_characters');
 344
 345 EXPLAIN
 346   SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1;
 347
 348 SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1;
 349
 350 INSERT INTO t2 (d)
 351   SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1;
 352
 353 INSERT INTO t2 (d)
 354   SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='7_chars') FROM t1;
 355
 356 INSERT INTO t2 (d)
 357   SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c IN (SELECT t1.c FROM t1))
 358   FROM t1;
 359
 360 SELECT * FROM t2;
 361 DROP TABLE t1,t2;
 362
 363 #
 364 # Bug #29095: incorrect pushing of LIMIT into the temporary
 365 # table ignoring ORDER BY clause
 366 #
 367
 368 CREATE TABLE t1 (
 369   id INT AUTO_INCREMENT PRIMARY KEY,
 370   prev_id INT,
 371   join_id INT DEFAULT 0);
 372
 373 INSERT INTO t1 (prev_id) VALUES (NULL), (1), (2);
 374 SELECT * FROM t1;
 375
 376 CREATE TABLE t2 (join_id INT);
 377 INSERT INTO t2 (join_id) VALUES (0);
 378
 379 INSERT INTO t1 (prev_id) SELECT id
 380   FROM t2 LEFT JOIN t1 ON t1.join_id = t2.join_id
 381   ORDER BY id DESC LIMIT 1;
 382 SELECT * FROM t1;
 383
 384 DROP TABLE t1,t2;
 385
 386 --echo #
 387 --echo # Bug#30384: Having SQL_BUFFER_RESULT option in the
 388 --echo #            CREATE .. KEY(..) .. SELECT led to creating corrupted index.
 389 --echo #
 390 create table t1(f1 int);
 391 insert into t1 values(1),(2),(3);
 392 create table t2 (key(f1)) engine=myisam select sql_buffer_result f1 from t1;
 393 check table t2 extended;
 394 drop table t1,t2;
 395 --echo ##################################################################
 396
 397 --echo #
 398 --echo # Bug #46075: Assertion failed: 0, file .\protocol.cc, line 416
 399 --echo #
 400
 401 CREATE TABLE t1(a INT);
 402 # To force MyISAM temp. table in the following INSERT ... SELECT.
 403 SET max_heap_table_size = 16384;
 404 # To overflow the temp. table.
 405 SET @old_myisam_data_pointer_size = @@myisam_data_pointer_size;
 406 SET GLOBAL myisam_data_pointer_size = 2;
 407
 408 INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
 409
 410 call mtr.add_suppression("mysqld.*: The table '.*#sql.*' is full");
 411 --error ER_RECORD_FILE_FULL,ER_RECORD_FILE_FULL
 412 INSERT IGNORE INTO t1 SELECT t1.a FROM t1,t1 t2,t1 t3,t1 t4,t1 t5,t1 t6,t1 t7;
 413
 414 # Cleanup
 415 SET GLOBAL myisam_data_pointer_size = @old_myisam_data_pointer_size;
 416 DROP TABLE t1;
 417
 418 --echo End of 5.0 tests