1drop table if exists t1,t2,t3; 2create table t1 (bandID MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY, payoutID SMALLINT UNSIGNED NOT NULL); 3insert into t1 (bandID,payoutID) VALUES (1,6),(2,6),(3,4),(4,9),(5,10),(6,1),(7,12),(8,12); 4create table t2 (payoutID SMALLINT UNSIGNED NOT NULL PRIMARY KEY); 5insert into t2 (payoutID) SELECT DISTINCT payoutID FROM t1; 6insert into t2 (payoutID) SELECT payoutID+10 FROM t1; 7ERROR 23000: Duplicate entry '16' for key 'PRIMARY' 8insert ignore into t2 (payoutID) SELECT payoutID+10 FROM t1; 9Warnings: 10Warning 1062 Duplicate entry '16' for key 'PRIMARY' 11Warning 1062 Duplicate entry '16' for key 'PRIMARY' 12Warning 1062 Duplicate entry '22' for key 'PRIMARY' 13select * from t2; 14payoutID 151 164 176 189 1910 2011 2112 2214 2316 2419 2520 2622 27drop table t1,t2; 28CREATE TABLE `t1` ( 29`numeropost` bigint(20) unsigned NOT NULL default '0', 30`icone` tinyint(4) unsigned NOT NULL default '0', 31`numreponse` bigint(20) unsigned NOT NULL auto_increment, 32`contenu` text NOT NULL, 33`pseudo` varchar(50) NOT NULL default '', 34`date` datetime NOT NULL default '0000-00-00 00:00:00', 35`ip` bigint(11) NOT NULL default '0', 36`signature` tinyint(1) unsigned NOT NULL default '0', 37PRIMARY KEY (`numeropost`,`numreponse`) 38,KEY `ip` (`ip`), 39KEY `date` (`date`), 40KEY `pseudo` (`pseudo`), 41KEY `numreponse` (`numreponse`) 42) ENGINE=MyISAM; 43CREATE TABLE `t2` ( 44`numeropost` bigint(20) unsigned NOT NULL default '0', 45`icone` tinyint(4) unsigned NOT NULL default '0', 46`numreponse` bigint(20) unsigned NOT NULL auto_increment, 47`contenu` text NOT NULL, 48`pseudo` varchar(50) NOT NULL default '', 49`date` datetime NOT NULL default '0000-00-00 00:00:00', 50`ip` bigint(11) NOT NULL default '0', 51`signature` tinyint(1) unsigned NOT NULL default '0', 52PRIMARY KEY (`numeropost`,`numreponse`), 53KEY `ip` (`ip`), 54KEY `date` (`date`), 55KEY `pseudo` (`pseudo`), 56KEY `numreponse` (`numreponse`) 57) ENGINE=MyISAM; 58INSERT INTO t2 59(numeropost,icone,numreponse,contenu,pseudo,date,ip,signature) VALUES 60(9,1,56,'test','joce','2001-07-25 13:50:53' 61,3649052399,0); 62INSERT INTO t1 (numeropost,icone,contenu,pseudo,date,signature,ip) 63SELECT 1618,icone,contenu,pseudo,date,signature,ip FROM t2 64WHERE numeropost=9 ORDER BY numreponse ASC; 65show variables like '%bulk%'; 66Variable_name Value 67bulk_insert_buffer_size 8388608 68INSERT INTO t1 (numeropost,icone,contenu,pseudo,date,signature,ip) 69SELECT 1718,icone,contenu,pseudo,date,signature,ip FROM t2 70WHERE numeropost=9 ORDER BY numreponse ASC; 71DROP TABLE t1,t2; 72create table t1 (a int not null); 73create table t2 (a int not null); 74insert into t1 values (1); 75insert into t1 values (a+2); 76insert into t1 values (a+3); 77insert into t1 values (4),(a+5); 78insert into t1 select * from t1; 79select * from t1; 80a 811 822 833 844 855 861 872 883 894 905 91insert into t1 select * from t1 as t2; 92select * from t1; 93a 941 952 963 974 985 991 1002 1013 1024 1035 1041 1052 1063 1074 1085 1091 1102 1113 1124 1135 114insert into t2 select * from t1 as t2; 115select * from t1; 116a 1171 1182 1193 1204 1215 1221 1232 1243 1254 1265 1271 1282 1293 1304 1315 1321 1332 1343 1354 1365 137insert into t1 select t2.a from t1,t2; 138select * from t1; 139a 1401 1412 1423 1434 1445 1451 1462 1473 1484 1495 1501 1512 1523 1534 1545 1551 1562 1573 1584 1595 1601 1611 1621 1631 1641 1651 1661 1671 1681 1691 1701 1711 1721 1731 1741 1751 1761 1771 1781 1791 1802 1812 1822 1832 1842 1852 1862 1872 1882 1892 1902 1912 1922 1932 1942 1952 1962 1972 1982 1992 2003 2013 2023 2033 2043 2053 2063 2073 2083 2093 2103 2113 2123 2133 2143 2153 2163 2173 2183 2193 2204 2214 2224 2234 2244 2254 2264 2274 2284 2294 2304 2314 2324 2334 2344 2354 2364 2374 2384 2394 2405 2415 2425 2435 2445 2455 2465 2475 2485 2495 2505 2515 2525 2535 2545 2555 2565 2575 2585 2595 2601 2611 2621 2631 2641 2651 2661 2671 2681 2691 2701 2711 2721 2731 2741 2751 2761 2771 2781 2791 2802 2812 2822 2832 2842 2852 2862 2872 2882 2892 2902 2912 2922 2932 2942 2952 2962 2972 2982 2992 3003 3013 3023 3033 3043 3053 3063 3073 3083 3093 3103 3113 3123 3133 3143 3153 3163 3173 3183 3193 3204 3214 3224 3234 3244 3254 3264 3274 3284 3294 3304 3314 3324 3334 3344 3354 3364 3374 3384 3394 3405 3415 3425 3435 3445 3455 3465 3475 3485 3495 3505 3515 3525 3535 3545 3555 3565 3575 3585 3595 3601 3611 3621 3631 3641 3651 3661 3671 3681 3691 3701 3711 3721 3731 3741 3751 3761 3771 3781 3791 3802 3812 3822 3832 3842 3852 3862 3872 3882 3892 3902 3912 3922 3932 3942 3952 3962 3972 3982 3992 4003 4013 4023 4033 4043 4053 4063 4073 4083 4093 4103 4113 4123 4133 4143 4153 4163 4173 4183 4193 4204 4214 4224 4234 4244 4254 4264 4274 4284 4294 4304 4314 4324 4334 4344 4354 4364 4374 4384 4394 4405 4415 4425 4435 4445 4455 4465 4475 4485 4495 4505 4515 4525 4535 4545 4555 4565 4575 4585 4595 4601 4611 4621 4631 4641 4651 4661 4671 4681 4691 4701 4711 4721 4731 4741 4751 4761 4771 4781 4791 4802 4812 4822 4832 4842 4852 4862 4872 4882 4892 4902 4912 4922 4932 4942 4952 4962 4972 4982 4992 5003 5013 5023 5033 5043 5053 5063 5073 5083 5093 5103 5113 5123 5133 5143 5153 5163 5173 5183 5193 5204 5214 5224 5234 5244 5254 5264 5274 5284 5294 5304 5314 5324 5334 5344 5354 5364 5374 5384 5394 5405 5415 5425 5435 5445 5455 5465 5475 5485 5495 5505 5515 5525 5535 5545 5555 5565 5575 5585 5595 560insert into t1 select * from t1,t1; 561ERROR 42000: Not unique table/alias: 't1' 562drop table t1,t2; 563create table t1 (a int not null primary key, b char(10)); 564create table t2 (a int not null, b char(10)); 565insert into t1 values (1,"t1:1"),(3,"t1:3"); 566insert into t2 values (2,"t2:2"), (3,"t2:3"); 567insert into t1 select * from t2; 568ERROR 23000: Duplicate entry '3' for key 'PRIMARY' 569select * from t1; 570a b 5711 t1:1 5723 t1:3 5732 t2:2 574replace into t1 select * from t2; 575select * from t1; 576a b 5771 t1:1 5782 t2:2 5793 t2:3 580drop table t1,t2; 581CREATE 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); 582CREATE 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)); 583INSERT 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); 584INSERT 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; 585drop table t1,t2; 586CREATE TABLE t1( 587Month date NOT NULL, 588Type tinyint(3) unsigned NOT NULL auto_increment, 589Field int(10) unsigned NOT NULL, 590Count int(10) unsigned NOT NULL, 591UNIQUE KEY Month (Month,Type,Field) 592); 593insert into t1 Values 594(20030901, 1, 1, 100), 595(20030901, 1, 2, 100), 596(20030901, 2, 1, 100), 597(20030901, 2, 2, 100), 598(20030901, 3, 1, 100); 599select * from t1; 600Month Type Field Count 6012003-09-01 1 1 100 6022003-09-01 1 2 100 6032003-09-01 2 1 100 6042003-09-01 2 2 100 6052003-09-01 3 1 100 606Select null, Field, Count From t1 Where Month=20030901 and Type=2; 607NULL Field Count 608NULL 1 100 609NULL 2 100 610create table t2(No int not null, Field int not null, Count int not null); 611insert ignore into t2 Select null, Field, Count From t1 Where Month=20030901 and Type=2; 612Warnings: 613Warning 1048 Column 'No' cannot be null 614Warning 1048 Column 'No' cannot be null 615select * from t2; 616No Field Count 6170 1 100 6180 2 100 619drop table t1, t2; 620CREATE TABLE t1 ( 621ID int(11) NOT NULL auto_increment, 622NO int(11) NOT NULL default '0', 623SEQ int(11) NOT NULL default '0', 624PRIMARY KEY (ID), 625KEY t1$NO (SEQ,NO) 626) ENGINE=MyISAM; 627INSERT INTO t1 (SEQ, NO) SELECT "1" AS SEQ, IF(MAX(NO) IS NULL, 0, MAX(NO)) + 1 AS NO FROM t1 WHERE (SEQ = 1); 628select SQL_BUFFER_RESULT * from t1 WHERE (SEQ = 1); 629ID NO SEQ 6301 1 1 631drop table t1; 632create table t1 (f1 int); 633create table t2 (ff1 int unique, ff2 int default 1); 634insert into t1 values (1),(1),(2); 635insert into t2(ff1) select f1 from t1 on duplicate key update ff2=ff2+1; 636select * from t2; 637ff1 ff2 6381 2 6392 1 640drop table t1, t2; 641create table t1 (a int unique); 642create table t2 (a int, b int); 643create table t3 (c int, d int); 644insert into t1 values (1),(2); 645insert into t2 values (1,2); 646insert into t3 values (1,6),(3,7); 647select * from t1; 648a 6491 6502 651insert into t1 select a from t2 on duplicate key update a= t1.a + t2.b; 652select * from t1; 653a 6542 6553 656insert into t1 select a+1 from t2 on duplicate key update t1.a= t1.a + t2.b+1; 657select * from t1; 658a 6593 6605 661insert into t1 select t3.c from t3 on duplicate key update a= a + t3.d; 662select * from t1; 663a 6641 6655 66610 667insert into t1 select t2.a from t2 group by t2.a on duplicate key update a= a + 10; 668insert into t1 select t2.a from t2 on duplicate key update a= a + t2.b; 669ERROR 23000: Column 'a' in field list is ambiguous 670insert into t1 select t2.a from t2 on duplicate key update t2.a= a + t2.b; 671ERROR 42S22: Unknown column 't2.a' in 'field list' 672insert into t1 select t2.a from t2 group by t2.a on duplicate key update a= t1.a + t2.b; 673ERROR 42S22: Unknown column 't2.b' in 'field list' 674drop table t1,t2,t3; 675create table t1(f1 varchar(5) key); 676insert into t1(f1) select if(max(f1) is null, '2000',max(f1)+1) from t1; 677insert into t1(f1) select if(max(f1) is null, '2000',max(f1)+1) from t1; 678insert into t1(f1) select if(max(f1) is null, '2000',max(f1)+1) from t1; 679select * from t1; 680f1 6812000 6822001 6832002 684drop table t1; 685create table t1(x int, y int); 686create table t2(x int, z int); 687insert into t1(x,y) select x,z from t2 on duplicate key update x=values(x); 688insert into t1(x,y) select x,z from t2 on duplicate key update x=values(z); 689ERROR 42S22: Unknown column 'z' in 'field list' 690insert into t1(x,y) select x,z from t2 on duplicate key update x=values(t2.x); 691ERROR 42S22: Unknown column 't2.x' in 'field list' 692drop table t1,t2; 693CREATE TABLE t1 (a int PRIMARY KEY); 694INSERT INTO t1 values (1), (2); 695flush status; 696INSERT INTO t1 SELECT a + 2 FROM t1 LIMIT 1; 697show status like 'Handler_read%'; 698Variable_name Value 699Handler_read_first 1 700Handler_read_key 0 701Handler_read_last 0 702Handler_read_next 0 703Handler_read_prev 0 704Handler_read_retry 0 705Handler_read_rnd 0 706Handler_read_rnd_deleted 0 707Handler_read_rnd_next 1 708DROP TABLE t1; 709CREATE TABLE t1 ( 710f1 int(10) unsigned NOT NULL auto_increment PRIMARY KEY, 711f2 varchar(100) NOT NULL default '' 712); 713CREATE TABLE t2 ( 714f1 varchar(10) NOT NULL default '', 715f2 char(3) NOT NULL default '', 716PRIMARY KEY (`f1`), 717KEY `k1` (`f2`, `f1`) 718); 719INSERT INTO t1 values(NULL, ''); 720INSERT INTO `t2` VALUES ('486878','WDT'),('486910','WDT'); 721SELECT COUNT(*) FROM t1; 722COUNT(*) 7231 724SELECT min(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1; 725min(t2.f1) 726INSERT INTO t1 (f2) 727SELECT min(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1; 728SELECT COUNT(*) FROM t1; 729COUNT(*) 7301 731SELECT * FROM t1; 732f1 f2 7331 734DROP TABLE t1, t2; 735CREATE TABLE t1 (x int, y int); 736CREATE TABLE t2 (z int, y int); 737CREATE TABLE t3 (a int, b int); 738INSERT INTO t3 (SELECT x, y FROM t1 JOIN t2 USING (y) WHERE z = 1); 739DROP TABLE IF EXISTS t1,t2,t3; 740CREATE DATABASE bug21774_1; 741CREATE DATABASE bug21774_2; 742CREATE TABLE bug21774_1.t1(id VARCHAR(10) NOT NULL,label VARCHAR(255)); 743CREATE TABLE bug21774_2.t1(id VARCHAR(10) NOT NULL,label VARCHAR(255)); 744CREATE TABLE bug21774_1.t2(id VARCHAR(10) NOT NULL,label VARCHAR(255)); 745INSERT INTO bug21774_2.t1 SELECT t1.* FROM bug21774_1.t1; 746use bug21774_1; 747INSERT INTO bug21774_2.t1 SELECT t1.* FROM t1; 748DROP DATABASE bug21774_1; 749DROP DATABASE bug21774_2; 750USE test; 751create table t1(f1 int primary key, f2 int); 752insert into t1 values (1,1); 753affected rows: 1 754insert into t1 values (1,1) on duplicate key update f2=1; 755affected rows: 0 756insert into t1 values (1,1) on duplicate key update f2=2; 757affected rows: 2 758select * from t1; 759f1 f2 7601 2 761drop table t1; 762CREATE TABLE t1 (f1 INT, f2 INT ); 763CREATE TABLE t2 (f1 INT PRIMARY KEY, f2 INT); 764INSERT INTO t1 VALUES (1,1),(2,2),(10,10); 765INSERT INTO t2 (f1, f2) SELECT f1, f2 FROM t1; 766INSERT INTO t2 (f1, f2) 767SELECT f1, f1 FROM t2 src WHERE f1 < 2 768ON DUPLICATE KEY UPDATE f1 = 100 + src.f1; 769SELECT * FROM t2; 770f1 f2 77110 10 772101 1 7732 2 774DROP TABLE t1, t2; 775SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 776CREATE TABLE t1 ( a INT KEY, b INT ); 777INSERT INTO t1 VALUES ( 0, 1 ); 778INSERT INTO t1 ( b ) SELECT MAX( b ) FROM t1 WHERE b = 2; 779ERROR 23000: Duplicate entry '0' for key 'PRIMARY' 780DROP TABLE t1; 781SET sql_mode = DEFAULT; 782SET SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; 783CREATE TABLE t1 (c VARCHAR(30), INDEX ix_c (c(10))); 784CREATE TABLE t2 (d VARCHAR(10)); 785INSERT INTO t1 (c) VALUES ('7_chars'), ('13_characters'); 786EXPLAIN 787SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1; 788id select_type table type possible_keys key key_len ref rows Extra 7891 PRIMARY t1 ALL NULL NULL NULL NULL 2 7902 SUBQUERY t1 ref ix_c ix_c 13 const 1 Using where 791SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1; 792(SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') 79313 79413 795INSERT INTO t2 (d) 796SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1; 797INSERT INTO t2 (d) 798SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='7_chars') FROM t1; 799INSERT INTO t2 (d) 800SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c IN (SELECT t1.c FROM t1)) 801FROM t1; 802SELECT * FROM t2; 803d 80413 80513 8067 8077 80820 80920 810DROP TABLE t1,t2; 811CREATE TABLE t1 ( 812id INT AUTO_INCREMENT PRIMARY KEY, 813prev_id INT, 814join_id INT DEFAULT 0); 815INSERT INTO t1 (prev_id) VALUES (NULL), (1), (2); 816SELECT * FROM t1; 817id prev_id join_id 8181 NULL 0 8192 1 0 8203 2 0 821CREATE TABLE t2 (join_id INT); 822INSERT INTO t2 (join_id) VALUES (0); 823INSERT INTO t1 (prev_id) SELECT id 824FROM t2 LEFT JOIN t1 ON t1.join_id = t2.join_id 825ORDER BY id DESC LIMIT 1; 826SELECT * FROM t1; 827id prev_id join_id 8281 NULL 0 8292 1 0 8303 2 0 8314 3 0 832DROP TABLE t1,t2; 833# 834# Bug#30384: Having SQL_BUFFER_RESULT option in the 835# CREATE .. KEY(..) .. SELECT led to creating corrupted index. 836# 837create table t1(f1 int); 838insert into t1 values(1),(2),(3); 839create table t2 (key(f1)) engine=myisam select sql_buffer_result f1 from t1; 840check table t2 extended; 841Table Op Msg_type Msg_text 842test.t2 check status OK 843drop table t1,t2; 844End of 5.0 tests 845################################################################## 846# 847# Bug #46075: Assertion failed: 0, file .\protocol.cc, line 416 848# 849CREATE TABLE t1(a INT); 850SET max_heap_table_size = 16384; 851SET @old_myisam_data_pointer_size = @@myisam_data_pointer_size; 852SET GLOBAL myisam_data_pointer_size = 2; 853INSERT INTO t1 VALUES (1), (2), (3), (4), (5); 854call mtr.add_suppression("mysqld.*: The table '.*#sql.*' is full"); 855INSERT IGNORE INTO t1 SELECT t1.a FROM t1,t1 t2,t1 t3,t1 t4,t1 t5,t1 t6,t1 t7; 856SET GLOBAL myisam_data_pointer_size = @old_myisam_data_pointer_size; 857DROP TABLE t1; 858End of 5.1 tests 859create table t1 (i int); 860create table t2 as select value(i) as a from t1; 861show create table t2; 862Table Create Table 863t2 CREATE TABLE `t2` ( 864 `a` binary(0) DEFAULT NULL 865) ENGINE=MyISAM DEFAULT CHARSET=latin1 866drop table t1, t2; 867End of 5.5 tests 868# 869# Beginning of 10.2 test 870# 871# MDEV-26698: Incorrect row number upon INSERT .. SELECT from the same 872# table: rows are counted twice 873# 874CREATE TABLE t1(a TINYINT); 875INSERT INTO t1 VALUES (1), (100); 876INSERT INTO t1 SELECT a*2 FROM t1; 877Warnings: 878Warning 1264 Out of range value for column 'a' at row 2 879TRUNCATE TABLE t1; 880# using ORDER BY 881INSERT INTO t1 VALUES(1), (2), (100), (3); 882INSERT INTO t1 SELECT a*2 FROM t1 ORDER BY a; 883Warnings: 884Warning 1264 Out of range value for column 'a' at row 4 885DROP TABLE t1; 886# End of 10.2 test 887