1
drop table if exists t1,t2,t3;
2
create table t1 (bandID INT NOT NULL PRIMARY KEY, payoutID int NOT NULL);
3
insert into t1 (bandID,payoutID) VALUES (1,6),(2,6),(3,4),(4,9),(5,10),(6,1),(7,12),(8,12);
4
create table t2 (payoutID int NOT NULL PRIMARY KEY);
5
insert into t2 (payoutID) SELECT DISTINCT payoutID FROM t1;
6
insert into t2 (payoutID) SELECT payoutID+10 FROM t1;
7
ERROR 23000: Duplicate entry '16' for key 'PRIMARY'
8
insert ignore into t2 (payoutID) SELECT payoutID+10 FROM t1;
24
CREATE TEMPORARY TABLE `t1` (
25
`numeropost` bigint NOT NULL default '0',
26
`icone` int NOT NULL default '0',
27
`numreponse` bigint NOT NULL auto_increment,
28
`contenu` text NOT NULL,
29
`pseudo` varchar(50) NOT NULL default '',
31
`ip` bigint NOT NULL default '0',
32
`signature` int NOT NULL default '0',
33
PRIMARY KEY (`numeropost`,`numreponse`)
36
KEY `pseudo` (`pseudo`),
37
KEY `numreponse` (`numreponse`)
39
CREATE TEMPORARY TABLE `t2` (
40
`numeropost` bigint NOT NULL default '0',
41
`icone` int NOT NULL default '0',
42
`numreponse` bigint NOT NULL auto_increment,
43
`contenu` text NOT NULL,
44
`pseudo` varchar(50) NOT NULL default '',
46
`ip` bigint NOT NULL default '0',
47
`signature` int NOT NULL default '0',
48
PRIMARY KEY (`numeropost`,`numreponse`),
51
KEY `pseudo` (`pseudo`),
52
KEY `numreponse` (`numreponse`)
55
(numeropost,icone,numreponse,contenu,pseudo,date,ip,signature) VALUES
56
(9,1,56,'test','joce','2001-07-25 13:50:53'
58
INSERT INTO t1 (numeropost,icone,contenu,pseudo,date,signature,ip)
59
SELECT 1618,icone,contenu,pseudo,date,signature,ip FROM t2
60
WHERE numeropost=9 ORDER BY numreponse ASC;
61
show variables like '%bulk%';
63
bulk_insert_buffer_size 8388608
64
INSERT INTO t1 (numeropost,icone,contenu,pseudo,date,signature,ip)
65
SELECT 1718,icone,contenu,pseudo,date,signature,ip FROM t2
66
WHERE numeropost=9 ORDER BY numreponse ASC;
68
create table t1 (a int not null);
69
create table t2 (a int not null);
70
insert into t1 values (1);
71
insert into t1 values (a+2);
72
insert into t1 values (a+3);
73
insert into t1 values (4),(a+5);
74
insert into t1 select * from t1;
87
insert into t1 select * from t1 as t2;
110
insert into t2 select * from t1 as t2;
133
insert into t1 select t2.a from t1,t2;
556
insert into t1 select * from t1,t1;
557
ERROR 42000: Not unique table/alias: 't1'
559
create table t1 (a int not null primary key, b char(10));
560
create table t2 (a int not null, b char(10));
561
insert into t1 values (1,"t1:1"),(3,"t1:3");
562
insert into t2 values (2,"t2:2"), (3,"t2:3");
563
insert into t1 select * from t2;
564
ERROR 23000: Duplicate entry '3' for key 'PRIMARY'
569
replace into t1 select * from t2;
576
CREATE TABLE t1 ( USID INTEGER, ServerID int, 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, NASPort INTEGER, NASPortType INTEGER, ConnectSpeed INTEGER, CarrierType CHAR(32), CallingStationID CHAR(32), CalledStationID CHAR(32), AssignedAddr INTEGER, SessionTime INTEGER, PacketsIn INTEGER, OctetsIn INTEGER, PacketsOut INTEGER, OctetsOut INTEGER, TerminateCause INTEGER, UnauthTime int, AccessRequestTime DATETIME, AcctStartTime DATETIME, AcctLastTime DATETIME, LastModification TIMESTAMP NOT NULL);
577
CREATE TABLE t2 ( USID INTEGER AUTO_INCREMENT, ServerID int, State ENUM ('unknown', 'Access-Granted', 'Session-Active', 'Session-Closed' ) NOT NULL DEFAULT 'unknown', SessionID CHAR(32), User TEXT NOT NULL, NASAddr INTEGER, NASPort INTEGER, NASPortType INTEGER, ConnectSpeed INTEGER, CarrierType CHAR(32), CallingStationID CHAR(32), CalledStationID CHAR(32), AssignedAddr INTEGER, SessionTime INTEGER, PacketsIn INTEGER, OctetsIn INTEGER, PacketsOut INTEGER, OctetsOut INTEGER, TerminateCause INTEGER, UnauthTime int, AccessRequestTime DATETIME, AcctStartTime DATETIME, AcctLastTime DATETIME, LastModification TIMESTAMP NOT NULL, INDEX(USID,ServerID,NASAddr,SessionID), INDEX(AssignedAddr));
578
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);
579
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;
583
Type int NOT NULL auto_increment,
587
UNIQUE KEY Month (Month,Type,Field)
589
insert into t1 Values (20030901, 1, 1, 100);
590
insert into t1 Values (20030901, 2, 2, 100);
591
insert into t1 Values (20030901, 3, 3, 100);
592
insert into t1 Values (20030901, 4, 4, 100);
593
insert into t1 Values (20030901, 5, 5, 100);
595
Month Type Field Count
601
Select null, Field, Count From t1 Where Month=20030901 and Type=2;
604
create table t2(No int not null, Field int not null, Count int not null);
605
insert into t2 Select 0, Field, Count From t1 Where Month=20030901 and Type=2;
611
ID int NOT NULL auto_increment,
612
NO int NOT NULL default '0',
613
SEQ int NOT NULL default '0',
617
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);
618
select SQL_BUFFER_RESULT * from t1 WHERE (SEQ = 1);
622
create table t1 (f1 int);
623
create table t2 (ff1 int unique, ff2 int default 1);
624
insert into t1 values (1),(1),(2);
625
insert into t2(ff1) select f1 from t1 on duplicate key update ff2=ff2+1;
631
create table t1 (a int unique);
632
create table t2 (a int, b int);
633
create table t3 (c int, d int);
634
insert into t1 values (1),(2);
635
insert into t2 values (1,2);
636
insert into t3 values (1,6),(3,7);
641
insert into t1 select a from t2 on duplicate key update a= t1.a + t2.b;
646
insert into t1 select a+1 from t2 on duplicate key update t1.a= t1.a + t2.b+1;
651
insert into t1 select t3.c from t3 on duplicate key update a= a + t3.d;
657
insert into t1 select t2.a from t2 group by t2.a on duplicate key update a= a + 10;
658
insert into t1 select t2.a from t2 on duplicate key update a= a + t2.b;
659
ERROR 23000: Column 'a' in field list is ambiguous
660
insert into t1 select t2.a from t2 on duplicate key update t2.a= a + t2.b;
661
ERROR 42S22: Unknown column 't2.a' in 'field list'
662
insert into t1 select t2.a from t2 group by t2.a on duplicate key update a= t1.a + t2.b;
663
ERROR 42S22: Unknown column 't2.b' in 'field list'
665
create table t1(f1 varchar(5) key);
666
insert into t1(f1) select if(max(f1) is null, '2000',max(f1)+1) from t1;
667
insert into t1(f1) select if(max(f1) is null, '2000',max(f1)+1) from t1;
668
insert into t1(f1) select if(max(f1) is null, '2000',max(f1)+1) from t1;
675
create table t1(x int, y int);
676
create table t2(x int, z int);
677
insert into t1(x,y) select x,z from t2 on duplicate key update x=values(x);
678
insert into t1(x,y) select x,z from t2 on duplicate key update x=values(z);
679
ERROR 42S22: Unknown column 'z' in 'field list'
680
insert into t1(x,y) select x,z from t2 on duplicate key update x=values(t2.x);
681
ERROR 42S22: Unknown column 't2.x' in 'field list'
683
CREATE TABLE t1 (a int PRIMARY KEY);
684
INSERT INTO t1 values (1), (2);
686
INSERT INTO t1 SELECT a + 2 FROM t1 LIMIT 1;
687
show status like 'Handler_read%';
694
Handler_read_rnd_next 1
697
f1 int NOT NULL auto_increment PRIMARY KEY,
698
f2 varchar(100) NOT NULL default ''
701
f1 varchar(10) NOT NULL default '',
702
f2 char(3) NOT NULL default '',
704
KEY `k1` (`f2`, `f1`)
706
INSERT INTO t1 values(NULL, '');
707
INSERT INTO `t2` VALUES ('486878','WDT'),('486910','WDT');
708
SELECT COUNT(*) FROM t1;
711
SELECT min(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
714
SELECT min(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
715
SELECT COUNT(*) FROM t1;
722
CREATE TABLE t1 (x int, y int);
723
CREATE TABLE t2 (z int, y int);
724
CREATE TABLE t3 (a int, b int);
725
INSERT INTO t3 (SELECT x, y FROM t1 JOIN t2 USING (y) WHERE z = 1);
726
DROP TABLE IF EXISTS t1,t2,t3;
727
CREATE DATABASE bug21774_1;
728
CREATE DATABASE bug21774_2;
729
CREATE TABLE bug21774_1.t1(id VARCHAR(10) NOT NULL,label VARCHAR(255));
730
CREATE TABLE bug21774_2.t1(id VARCHAR(10) NOT NULL,label VARCHAR(255));
731
CREATE TABLE bug21774_1.t2(id VARCHAR(10) NOT NULL,label VARCHAR(255));
732
INSERT INTO bug21774_2.t1 SELECT t1.* FROM bug21774_1.t1;
734
INSERT INTO bug21774_2.t1 SELECT t1.* FROM t1;
735
DROP DATABASE bug21774_1;
736
DROP DATABASE bug21774_2;
738
create table t1(f1 int primary key, f2 int);
739
insert into t1 values (1,1);
741
insert into t1 values (1,1) on duplicate key update f2=1;
743
insert into t1 values (1,1) on duplicate key update f2=2;
749
CREATE TABLE t1 (f1 INT, f2 INT );
750
CREATE TABLE t2 (f1 INT PRIMARY KEY, f2 INT);
751
INSERT INTO t1 VALUES (1,1),(2,2),(10,10);
752
INSERT INTO t2 (f1, f2) SELECT f1, f2 FROM t1;
753
INSERT INTO t2 (f1, f2)
754
SELECT f1, f1 FROM t2 src WHERE f1 < 2
755
ON DUPLICATE KEY UPDATE f1 = 100 + src.f1;
763
id INT AUTO_INCREMENT PRIMARY KEY,
765
join_id INT DEFAULT 0);
766
INSERT INTO t1 (prev_id) VALUES (NULL), (1), (2);
772
CREATE TABLE t2 (join_id INT);
773
INSERT INTO t2 (join_id) VALUES (0);
774
INSERT INTO t1 (prev_id) SELECT id
775
FROM t2 LEFT JOIN t1 ON t1.join_id = t2.join_id
776
ORDER BY id DESC LIMIT 1;
785
# Bug#30384: Having SQL_BUFFER_RESULT option in the
786
# CREATE .. KEY(..) .. SELECT led to creating corrupted index.
788
create table t1(f1 int);
789
insert into t1 values(1),(2),(3);
790
create temporary table t2 (key(f1)) engine=myisam select sql_buffer_result f1 from t1;
791
check table t2 extended;
792
Table Op Msg_type Msg_text
793
test.t2 check status OK
795
##################################################################
added
removed
tests/r/pbxt/insert_select.result
