insert_select.test 5.86 KB
Newer Older
1 2 3 4
#
# Problem with INSERT ... SELECT
#

5
--disable_warnings
6
drop table if exists t1,t2;
7 8
--enable_warnings

9 10 11 12
create table t1 (bandID MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY, payoutID SMALLINT UNSIGNED NOT NULL);
insert into t1 (bandID,payoutID) VALUES (1,6),(2,6),(3,4),(4,9),(5,10),(6,1),(7,12),(8,12);
create table t2 (payoutID SMALLINT UNSIGNED NOT NULL PRIMARY KEY);
insert into t2 (payoutID) SELECT DISTINCT payoutID FROM t1;
13
--error 1062
14
insert into t2 (payoutID) SELECT payoutID+10 FROM t1;
15
insert ignore into t2 (payoutID) SELECT payoutID+10 FROM t1;
16 17
select * from t2;
drop table t1,t2;
18

unknown's avatar
unknown committed
19 20 21 22 23
#
# bug in bulk insert optimization
# test case by Fournier Jocelyn <joc@presence-pc.com>
#

24
CREATE TABLE `t1` (
unknown's avatar
unknown committed
25 26 27 28 29 30 31 32 33 34 35 36 37
  `numeropost` bigint(20) unsigned NOT NULL default '0',
  `icone` tinyint(4) unsigned NOT NULL default '0',
  `numreponse` bigint(20) unsigned NOT NULL auto_increment,
  `contenu` text NOT NULL,
  `pseudo` varchar(50) NOT NULL default '',
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  `ip` bigint(11) NOT NULL default '0',
  `signature` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`numeropost`,`numreponse`)
  ,KEY `ip` (`ip`),
  KEY `date` (`date`),
  KEY `pseudo` (`pseudo`),
  KEY `numreponse` (`numreponse`)
unknown's avatar
unknown committed
38
) ENGINE=MyISAM;
unknown's avatar
unknown committed
39

40
CREATE TABLE `t2` (
unknown's avatar
unknown committed
41 42 43 44 45 46 47 48 49 50 51 52 53
  `numeropost` bigint(20) unsigned NOT NULL default '0',
  `icone` tinyint(4) unsigned NOT NULL default '0',
  `numreponse` bigint(20) unsigned NOT NULL auto_increment,
  `contenu` text NOT NULL,
  `pseudo` varchar(50) NOT NULL default '',
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  `ip` bigint(11) NOT NULL default '0',
  `signature` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`numeropost`,`numreponse`),
  KEY `ip` (`ip`),
  KEY `date` (`date`),
  KEY `pseudo` (`pseudo`),
  KEY `numreponse` (`numreponse`)
unknown's avatar
unknown committed
54
) ENGINE=MyISAM;
unknown's avatar
unknown committed
55

56
INSERT INTO t2
unknown's avatar
unknown committed
57 58 59 60 61
(numeropost,icone,numreponse,contenu,pseudo,date,ip,signature) VALUES
(9,1,56,'test','joce','2001-07-25 13:50:53'
,3649052399,0);


62 63
INSERT INTO t1 (numeropost,icone,contenu,pseudo,date,signature,ip)
SELECT 1618,icone,contenu,pseudo,date,signature,ip FROM t2
unknown's avatar
unknown committed
64 65 66 67
WHERE numeropost=9 ORDER BY numreponse ASC;

show variables like '%bulk%';

68 69
INSERT INTO t1 (numeropost,icone,contenu,pseudo,date,signature,ip)
SELECT 1718,icone,contenu,pseudo,date,signature,ip FROM t2
unknown's avatar
unknown committed
70 71
WHERE numeropost=9 ORDER BY numreponse ASC;

unknown's avatar
unknown committed
72
DROP TABLE t1,t2;
unknown's avatar
unknown committed
73

74 75
# Check if a partly-completed INSERT SELECT in a MyISAM table goes
# into the binlog
unknown's avatar
unknown committed
76

77 78 79 80 81 82 83 84 85 86 87 88
create table t1(a int, unique(a));
insert into t1 values(2);
create table t2(a int);
insert into t2 values(1),(2);
reset master;
--error 1062
insert into t1 select * from t2;
# The above should produce an error, but still be in the binlog;
# verify the binlog :
let $VERSION=`select version()`;
--replace_result $VERSION VERSION
show binlog events;
89
select * from t1;
90
drop table t1, t2;
91 92 93 94 95

#
# Test of insert ... select from same table
#

96 97 98 99 100 101 102 103 104 105 106 107 108 109
create table t1 (a int not null);
create table t2 (a int not null);
insert into t1 values (1);
insert into t1 values (a+2);
insert into t1 values (a+3);
insert into t1 values (4),(a+5);
insert into t1 select * from t1;
select * from t1;
insert into t1 select * from t1 as t2;
select * from t1;
insert into t2 select * from t1 as t2;
select * from t1;
insert into t1 select t2.a from t1,t2;
select * from t1;
110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126
--error 1066
insert into t1 select * from t1,t1;
drop table t1,t2;

#
# test replace ... select
#

create table t1 (a int not null primary key, b char(10));
create table t2 (a int not null, b char(10));
insert into t1 values (1,"t1:1"),(3,"t1:3");
insert into t2 values (2,"t2:2"), (3,"t2:3");
--error 1062
insert into t1 select * from t2;
select * from t1;
replace into t1 select * from t2;
select * from t1;
127
drop table t1,t2;
128 129 130 131 132 133 134 135 136 137

#
# Test that caused uninitialized memory access in auto_increment_key update
#

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);
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));
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);
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;
drop table t1,t2;