Below is the list of changes that have just been committed into a local
5.1 repository of hf. When hf does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2007-05-11 17:48:20+05:00, holyfoot@hfmain.(none) +7 -0
Merge mysql.com:/home/hf/work/27957/my50-27957
into mysql.com:/home/hf/work/27957/my51-27957
MERGE: 1.1810.2868.42
mysql-test/include/mix1.inc@stripped, 2007-05-11 17:46:18+05:00, holyfoot@hfmain.(none) +2 -2
merging
MERGE: 1.3.23.2
mysql-test/include/mix1.inc@stripped, 2007-05-11 17:45:21+05:00, holyfoot@hfmain.(none) +0 -0
Merge rename: mysql-test/t/innodb_mysql.test -> mysql-test/include/mix1.inc
mysql-test/r/innodb_mysql.result@stripped, 2007-05-11 17:46:48+05:00, holyfoot@hfmain.(none) +0 -0
SCCS merged
MERGE: 1.3.4.15
mysql-test/r/insert_update.result@stripped, 2007-05-11 17:45:21+05:00, holyfoot@hfmain.(none) +0 -0
Auto merged
MERGE: 1.18.1.8
mysql-test/t/insert_update.test@stripped, 2007-05-11 17:45:21+05:00, holyfoot@hfmain.(none) +0 -0
Auto merged
MERGE: 1.20.1.6
sql/item_func.cc@stripped, 2007-05-11 17:45:22+05:00, holyfoot@hfmain.(none) +0 -0
Auto merged
MERGE: 1.270.1.69
sql/sql_insert.cc@stripped, 2007-05-11 17:45:22+05:00, holyfoot@hfmain.(none) +0 -0
Auto merged
MERGE: 1.146.1.85
sql/sql_select.cc@stripped, 2007-05-11 17:48:14+05:00, holyfoot@hfmain.(none) +1 -2
merging
MERGE: 1.312.1.208
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: holyfoot
# Host: hfmain.(none)
# Root: /home/hf/work/27957/my51-27957/RESYNC
--- 1.262/sql/sql_insert.cc 2007-05-11 17:48:26 +05:00
+++ 1.263/sql/sql_insert.cc 2007-05-11 17:48:26 +05:00
@@ -1276,7 +1276,8 @@ int write_record(THD *thd, TABLE *table,
DBUG_ASSERT(info->update_fields->elements ==
info->update_values->elements);
if (fill_record_n_invoke_before_triggers(thd, *info->update_fields,
- *info->update_values, 0,
+ *info->update_values,
+ info->ignore,
table->triggers,
TRG_EVENT_UPDATE))
goto before_trg_err;
--- 1.518/sql/sql_select.cc 2007-05-11 17:48:26 +05:00
+++ 1.519/sql/sql_select.cc 2007-05-11 17:48:26 +05:00
@@ -4269,7 +4269,7 @@ best_access_path(JOIN *join,
!(s->quick && best_key && s->quick->index == best_key->key && // (2)
best_max_key_part >= s->table->quick_key_parts[best_key->key]) &&// (2)
!((s->table->file->ha_table_flags() & HA_TABLE_SCAN_ON_INDEX) && // (3)
- ! s->table->covering_keys.is_clear_all() && best_key) && // (3)
+ ! s->table->covering_keys.is_clear_all() && best_key && !s->quick) &&// (3)
!(s->table->force_index && best_key && !s->quick)) // (4)
{ // Check full join
ha_rows rnd_records= s->found_records;
--- 1.30/mysql-test/r/innodb_mysql.result 2007-05-11 17:48:26 +05:00
+++ 1.31/mysql-test/r/innodb_mysql.result 2007-05-11 17:48:26 +05:00
@@ -548,6 +548,58 @@ id c counter
3 b 2
4 a 2
drop table t1;
+CREATE TABLE t1(
+id int AUTO_INCREMENT PRIMARY KEY,
+stat_id int NOT NULL,
+acct_id int DEFAULT NULL,
+INDEX idx1 (stat_id, acct_id),
+INDEX idx2 (acct_id)
+) ENGINE=MyISAM;
+CREATE TABLE t2(
+id int AUTO_INCREMENT PRIMARY KEY,
+stat_id int NOT NULL,
+acct_id int DEFAULT NULL,
+INDEX idx1 (stat_id, acct_id),
+INDEX idx2 (acct_id)
+) ENGINE=InnoDB;
+INSERT INTO t1(stat_id,acct_id) VALUES
+(1,759), (2,831), (3,785), (4,854), (1,921),
+(1,553), (2,589), (3,743), (2,827), (2,545),
+(4,779), (4,783), (1,597), (1,785), (4,832),
+(1,741), (1,833), (3,788), (2,973), (1,907);
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+UPDATE t1 SET acct_id=785
+WHERE MOD(stat_id,2)=0 AND MOD(id,stat_id)=MOD(acct_id,stat_id);
+OPTIMIZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 optimize status OK
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+40960
+SELECT COUNT(*) FROM t1 WHERE acct_id=785;
+COUNT(*)
+8702
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE stat_id IN (1,3) AND acct_id=785;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range idx1,idx2 idx1 9 NULL 2 Using where; Using index
+INSERT INTO t2 SELECT * FROM t1;
+OPTIMIZE TABLE t2;
+Table Op Msg_type Msg_text
+test.t2 optimize status OK
+EXPLAIN SELECT COUNT(*) FROM t2 WHERE stat_id IN (1,3) AND acct_id=785;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 range idx1,idx2 idx1 9 NULL 2 Using where; Using index
+DROP TABLE t1,t2;
End of 5.0 tests
CREATE TABLE t1 (a int, b int);
insert into t1 values (1,1),(1,2);
--- 1.3.23.1/mysql-test/t/innodb_mysql.test 2007-05-11 17:48:26 +05:00
+++ 1.30/mysql-test/include/mix1.inc 2007-05-11 17:48:26 +05:00
@@ -1,9 +1,37 @@
--- source include/have_innodb.inc
+# include/mix1.inc
+#
+# The variables
+# $engine_type -- storage engine to be tested
+# $other_engine_type -- storage engine <> $engine_type
+# $other_engine_type must point to an all
+# time available storage engine
+# 2006-08 MySQL 5.1 MyISAM and MEMORY only
+# $test_foreign_keys -- 0, skip foreign key tests
+# -- 1, do not skip foreign key tests
+# have to be set before sourcing this script.
+#
+# Note: The comments/expectations refer to InnoDB.
+# They might be not valid for other storage engines.
+#
+# Last update:
+# 2006-08-15 ML refactoring of t/innodb_mysql.test
+# - shift main code of t/innodb_mysql.test to include/mix1.inc
+# - replace hardcoded assignment of storage engine by
+# use of $engine_type and $other_engine_type variables
+# - remove redundant replay testcase of
+# Bug#12882 min/max inconsistent on empty table
+# - corrected analyze table t1; to analyze table t4;
+# Much older versions of this test show that the table
+# where just some indexes have been created must be used.
+#
+
+eval SET SESSION STORAGE_ENGINE = $engine_type;
--disable_warnings
-drop table if exists t1,t2;
+drop table if exists t1,t2,t1m,t1i,t2m,t2i,t4;
--enable_warnings
+
# BUG#16798: Uninitialized row buffer reads in ref-or-null optimizer
# (repeatable only w/innodb).
create table t1 (
@@ -11,8 +39,8 @@ create table t1 (
org_id int(11) default null,
unique key contacts$c_id (c_id),
key contacts$org_id (org_id)
-) engine=innodb;
-insert into t1 values
+);
+insert into t1 values
(2,null),(120,null),(141,null),(218,7), (128,1),
(151,2),(234,2),(236,2),(243,2),(255,2),(259,2),(232,3),(235,3),(238,3),
(246,3),(253,3),(269,3),(285,3),(291,3),(293,3),(131,4),(230,4),(231,4);
@@ -35,7 +63,7 @@ create table t2 (
unique key t2$slai_id (slai_id),
key t2$owner_id (owner_id),
key t2$sla_id (sla_id)
-) engine=innodb;
+);
insert into t2(slai_id, owner_tbl, owner_id, sla_id) values
(1,3,1,1), (3,3,10,2), (4,3,3,6), (5,3,2,5), (6,3,8,3), (7,3,9,7),
(8,3,6,8), (9,3,4,9), (10,3,5,10), (11,3,11,11), (12,3,7,12);
@@ -43,17 +71,17 @@ insert into t2(slai_id, owner_tbl, owner
flush tables;
select si.slai_id
from t1 c join t2 si on
- ((si.owner_tbl = 3 and si.owner_id = c.org_id) or
- ( si.owner_tbl = 2 and si.owner_id = c.c_id))
-where
+ ((si.owner_tbl = 3 and si.owner_id = c.org_id) or
+ ( si.owner_tbl = 2 and si.owner_id = c.c_id))
+where
c.c_id = 218 and expiredate is null;
-
+
select * from t1 where org_id is null;
select si.slai_id
from t1 c join t2 si on
- ((si.owner_tbl = 3 and si.owner_id = c.org_id) or
- ( si.owner_tbl = 2 and si.owner_id = c.c_id))
-where
+ ((si.owner_tbl = 3 and si.owner_id = c.org_id) or
+ ( si.owner_tbl = 2 and si.owner_id = c.c_id))
+where
c.c_id = 218 and expiredate is null;
drop table t1, t2;
@@ -62,14 +90,14 @@ drop table t1, t2;
# Bug#17212: results not sorted correctly by ORDER BY when using index
# (repeatable only w/innodb because of index props)
#
-CREATE TABLE t1 (a int, b int, KEY b (b)) Engine=InnoDB;
-CREATE TABLE t2 (a int, b int, PRIMARY KEY (a,b)) Engine=InnoDB;
-CREATE TABLE t3 (a int, b int, c int, PRIMARY KEY (a),
- UNIQUE KEY b (b,c), KEY a (a,b,c)) Engine=InnoDB;
+CREATE TABLE t1 (a int, b int, KEY b (b));
+CREATE TABLE t2 (a int, b int, PRIMARY KEY (a,b));
+CREATE TABLE t3 (a int, b int, c int, PRIMARY KEY (a),
+ UNIQUE KEY b (b,c), KEY a (a,b,c));
INSERT INTO t1 VALUES (1, 1);
-INSERT INTO t1 SELECT a + 1, b + 1 FROM t1;
-INSERT INTO t1 SELECT a + 2, b + 2 FROM t1;
+INSERT INTO t1 SELECT a + 1, b + 1 FROM t1;
+INSERT INTO t1 SELECT a + 2, b + 2 FROM t1;
INSERT INTO t2 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8);
INSERT INTO t2 SELECT a + 1, b FROM t2;
@@ -81,13 +109,13 @@ INSERT INTO t3 SELECT a + 4, a + 4, 3 FR
# demonstrate a problem when a must-use-sort table flag
# (sort_by_table=1) is being neglected.
-SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE
- t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2)
+SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE
+ t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2)
ORDER BY t1.b LIMIT 2;
# demonstrate the problem described in the bug report
-SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE
- t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2)
+SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE
+ t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2)
ORDER BY t1.b LIMIT 5;
DROP TABLE t1, t2, t3;
@@ -105,9 +133,9 @@ CREATE TABLE `t2` (
`id4` INT NOT NULL,
UNIQUE (`id2`,`id4`),
KEY (`id1`)
-) ENGINE=InnoDB;
+);
-INSERT INTO `t2`(`id1`,`id2`,`id3`,`id4`) VALUES
+INSERT INTO `t2`(`id1`,`id2`,`id3`,`id4`) VALUES
(1,1,1,0),
(1,1,2,1),
(5,1,2,2),
@@ -170,43 +198,22 @@ SELECT * FROM t1;
DROP TABLE t1;
--echo End of 4.1 tests
+
+
#
# Bug #12882 min/max inconsistent on empty table
#
--disable_warnings
-create table t1m (a int) engine=myisam;
-create table t1i (a int) engine=innodb;
-create table t2m (a int) engine=myisam;
-create table t2i (a int) engine=innodb;
+eval create table t1m (a int) engine = $other_engine_type;
+create table t1i (a int);
+eval create table t2m (a int) engine = $other_engine_type;
+create table t2i (a int);
--enable_warnings
insert into t2m values (5);
insert into t2i values (5);
-# test with MyISAM
-select min(a) from t1m;
-select min(7) from t1m;
-select min(7) from DUAL;
-explain select min(7) from t2m join t1m;
-select min(7) from t2m join t1m;
-
-select max(a) from t1m;
-select max(7) from t1m;
-select max(7) from DUAL;
-explain select max(7) from t2m join t1m;
-select max(7) from t2m join t1m;
-
-select 1, min(a) from t1m where a=99;
-select 1, min(a) from t1m where 1=99;
-select 1, min(1) from t1m where a=99;
-select 1, min(1) from t1m where 1=99;
-
-select 1, max(a) from t1m where a=99;
-select 1, max(a) from t1m where 1=99;
-select 1, max(1) from t1m where a=99;
-select 1, max(1) from t1m where 1=99;
-
-# test with InnoDB
+# test with $engine_type
select min(a) from t1i;
select min(7) from t1i;
select min(7) from DUAL;
@@ -229,7 +236,7 @@ select 1, max(a) from t1i where 1=99;
select 1, max(1) from t1i where a=99;
select 1, max(1) from t1i where 1=99;
-# mixed MyISAM/InnoDB test
+# mixed $engine_type/$other_engine_type test
explain select count(*), min(7), max(7) from t1m, t1i;
select count(*), min(7), max(7) from t1m, t1i;
@@ -242,13 +249,13 @@ select count(*), min(7), max(7) from t2m
drop table t1m, t1i, t2m, t2i;
#
-# Bug #12672: primary key implcitly included in every innodb index
+# Bug #12882: primary key implcitly included in every innodb index
# (was part of group_min_max.test)
#
-create table t1 (
+eval create table t1 (
a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' '
-);
+) ENGINE = $other_engine_type;
insert into t1 (a1, a2, b, c, d) values
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
@@ -286,14 +293,14 @@ insert into t1 (a1, a2, b, c, d) values
--disable_warnings
create table t4 (
pk_col int auto_increment primary key, a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' '
-) engine=innodb;
+);
--enable_warnings
insert into t4 (a1, a2, b, c, d, dummy) select * from t1;
create index idx12672_0 on t4 (a1);
create index idx12672_1 on t4 (a1,a2,b,c);
create index idx12672_2 on t4 (a1,a2,b);
-analyze table t1;
+analyze table t4;
select distinct a1 from t4 where pk_col not in (1,2,3,4);
@@ -330,35 +337,35 @@ DROP TABLE t2, t1;
#
-# Bug #6142: a problem with the empty innodb table
+# Bug #6142: a problem with the empty innodb table
# (was part of group_min_max.test)
#
--disable_warnings
create table t1 (
a varchar(30), b varchar(30), primary key(a), key(b)
-) engine=innodb;
+);
--enable_warnings
select distinct a from t1;
drop table t1;
#
-# Bug #9798: group by with rollup
+# Bug #9798: group by with rollup
# (was part of group_min_max.test)
#
--disable_warnings
-create table t1(a int, key(a)) engine=innodb;
+create table t1(a int, key(a));
--enable_warnings
insert into t1 values(1);
select a, count(a) from t1 group by a with rollup;
drop table t1;
#
-# Bug #13293 Wrongly used index results in endless loop.
+# Bug #13293 Wrongly used index results in endless loop.
# (was part of group_min_max.test)
#
-create table t1 (f1 int, f2 char(1), primary key(f1,f2)) engine=innodb;
+create table t1 (f1 int, f2 char(1), primary key(f1,f2));
insert into t1 values ( 1,"e"),(2,"a"),( 3,"c"),(4,"d");
alter table t1 drop primary key, add primary key (f2, f1);
explain select distinct f1 a, f1 b from t1;
@@ -367,35 +374,31 @@ drop table t1;
#
# Test for bug #17164: ORed FALSE blocked conversion of outer join into join
-#
+#
CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20),
- INDEX (name)) ENGINE=InnoDB;
-CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11),
- FOREIGN KEY (fkey) REFERENCES t2(id)) ENGINE=InnoDB;
+ INDEX (name));
+CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11));
+# CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11),
+# FOREIGN KEY (fkey) REFERENCES t2(id));
+if ($test_foreign_keys)
+{
+ ALTER TABLE t2 ADD FOREIGN KEY (fkey) REFERENCES t2(id);
+}
INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B');
INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3);
EXPLAIN
-SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
+SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
WHERE t1.name LIKE 'A%';
EXPLAIN
-SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
+SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
WHERE t1.name LIKE 'A%' OR FALSE;
DROP TABLE t1,t2;
#
-# Bug#17530: Incorrect key truncation on table creation caused server crash.
-#
-create table t1(f1 varchar(800) binary not null, key(f1)) engine = innodb
- character set utf8 collate utf8_general_ci;
-insert into t1 values('aaa');
-drop table t1;
-
-
-#
# Bug#22781: SQL_BIG_RESULT fails to influence sort plan
#
CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c FLOAT, KEY b(b)) ENGINE = INNODB;
@@ -413,7 +416,6 @@ EXPLAIN SELECT b, SUM(c) FROM t1 GROUP B
EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b;
DROP TABLE t1;
-
#
# Bug#26159: crash for a loose scan of a table that has been emptied
#
@@ -472,6 +474,40 @@ drop table test;
set global query_cache_type=@save_qcache_type;
set global query_cache_size=@save_qcache_size;
+--source include/innodb_rollback_on_timeout.inc
+
+#
+# Bug #27210: INNODB ON DUPLICATE KEY UPDATE
+#
+
+set @save_qcache_size=@@global.query_cache_size;
+set @save_qcache_type=@@global.query_cache_type;
+set global query_cache_size=10*1024*1024;
+set global query_cache_type=1;
+connect (con1,localhost,root,,);
+connection con1;
+drop table if exists `test`;
+CREATE TABLE `test` (`test1` varchar(3) NOT NULL,
+ `test2` varchar(4) NOT NULL,PRIMARY KEY (`test1`))
+ ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '5678');
+disconnect con1;
+connect (con2,localhost,root,,);
+connection con2;
+select * from test;
+INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '1234')
+ ON DUPLICATE KEY UPDATE `test2` = '1234';
+select * from test;
+flush tables;
+select * from test;
+disconnect con2;
+connection default;
+drop table test;
+set global query_cache_type=@save_qcache_type;
+set global query_cache_size=@save_qcache_size;
+
+--echo End of 5.0 tests
+
-- source include/have_innodb.inc
#
@@ -573,3 +609,137 @@ EXPLAIN SELECT COUNT(*) FROM t2 WHERE st
DROP TABLE t1,t2;
--echo End of 5.0 tests
+
+#
+# Test of behaviour with CREATE ... SELECT
+#
+
+CREATE TABLE t1 (a int, b int);
+insert into t1 values (1,1),(1,2);
+--error ER_DUP_ENTRY_WITH_KEY_NAME
+CREATE TABLE t2 (primary key (a)) select * from t1;
+# This should give warning
+drop table if exists t2;
+--error ER_DUP_ENTRY_WITH_KEY_NAME
+CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1;
+# This should give warning
+drop table if exists t2;
+CREATE TABLE t2 (a int, b int, primary key (a));
+BEGIN;
+INSERT INTO t2 values(100,100);
+--error ER_DUP_ENTRY_WITH_KEY_NAME
+CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
+SELECT * from t2;
+ROLLBACK;
+SELECT * from t2;
+TRUNCATE table t2;
+--error ER_DUP_ENTRY_WITH_KEY_NAME
+INSERT INTO t2 select * from t1;
+SELECT * from t2;
+drop table t2;
+
+CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a));
+BEGIN;
+INSERT INTO t2 values(100,100);
+--error ER_DUP_ENTRY_WITH_KEY_NAME
+CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
+SELECT * from t2;
+COMMIT;
+BEGIN;
+INSERT INTO t2 values(101,101);
+--error ER_DUP_ENTRY_WITH_KEY_NAME
+CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
+SELECT * from t2;
+ROLLBACK;
+SELECT * from t2;
+TRUNCATE table t2;
+--error ER_DUP_ENTRY_WITH_KEY_NAME
+INSERT INTO t2 select * from t1;
+SELECT * from t2;
+drop table t1,t2;
+
+#
+# Bug#17530: Incorrect key truncation on table creation caused server crash.
+#
+create table t1(f1 varchar(800) binary not null, key(f1))
+ character set utf8 collate utf8_general_ci;
+insert into t1 values('aaa');
+drop table t1;
+
+
+#
+# Bug#22781: SQL_BIG_RESULT fails to influence sort plan
+#
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c FLOAT, KEY b(b)) ENGINE = INNODB;
+
+INSERT INTO t1 VALUES ( 1 , 1 , 1);
+INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20), 1 FROM t1;
+INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20), 1 FROM t1;
+INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20), 1 FROM t1;
+INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20), 1 FROM t1;
+INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20), 1 FROM t1;
+INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20), 1 FROM t1;
+INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20), 1 FROM t1;
+
+EXPLAIN SELECT b, SUM(c) FROM t1 GROUP BY b;
+EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b;
+DROP TABLE t1;
+
+--source include/innodb_rollback_on_timeout.inc
+
+--echo End of 5.0 tests
+
+
+# Fix for BUG#19243 "wrong LAST_INSERT_ID() after ON DUPLICATE KEY
+# UPDATE": if the row is updated, it's like a regular UPDATE:
+# LAST_INSERT_ID() is not affected.
+CREATE TABLE `t2` (
+ `k` int(11) NOT NULL auto_increment,
+ `a` int(11) default NULL,
+ `c` int(11) default NULL,
+ PRIMARY KEY (`k`),
+ UNIQUE KEY `idx_1` (`a`)
+);
+insert into t2 ( a ) values ( 6 ) on duplicate key update c =
+ifnull( c,
+0 ) + 1;
+insert into t2 ( a ) values ( 7 ) on duplicate key update c =
+ifnull( c,
+0 ) + 1;
+select last_insert_id();
+select * from t2;
+insert into t2 ( a ) values ( 6 ) on duplicate key update c =
+ifnull( c,
+0 ) + 1;
+select last_insert_id();
+# test again when last_insert_id() is 0 initially
+select last_insert_id(0);
+insert into t2 ( a ) values ( 6 ) on duplicate key update c =
+ifnull( c,
+0 ) + 1;
+select last_insert_id();
+select * from t2;
+
+# Test of LAST_INSERT_ID() when autogenerated will fail:
+# last_insert_id() should not change
+insert ignore into t2 values (null,6,1),(10,8,1);
+select last_insert_id();
+# First and second autogenerated will fail, last_insert_id() should
+# point to third
+insert ignore into t2 values (null,6,1),(null,8,1),(null,15,1),(null,20,1);
+select last_insert_id();
+select * from t2;
+
+# Test of the workaround which enables people to know the id of the
+# updated row in INSERT ON DUPLICATE KEY UPDATE, by using
+# LAST_INSERT_ID(autoinc_col) in the UPDATE clause.
+
+insert into t2 ( a ) values ( 6 ) on duplicate key update c =
+ifnull( c,
+0 ) + 1, k=last_insert_id(k);
+select last_insert_id();
+select * from t2;
+
+drop table t2;
+
+--echo End of 5.1 tests
--- 1.29/mysql-test/r/insert_update.result 2007-05-11 17:48:26 +05:00
+++ 1.30/mysql-test/r/insert_update.result 2007-05-11 17:48:26 +05:00
@@ -358,3 +358,38 @@ id c1 cnt
5 Y 1
6 Z 1
DROP TABLE t1;
+CREATE TABLE t1 (
+id INT AUTO_INCREMENT PRIMARY KEY,
+c1 INT NOT NULL,
+cnt INT DEFAULT 1
+);
+INSERT INTO t1 (id,c1) VALUES (1,10);
+SELECT * FROM t1;
+id c1 cnt
+1 10 1
+CREATE TABLE t2 (id INT, c1 INT);
+INSERT INTO t2 VALUES (1,NULL), (2,2);
+INSERT INTO t1 (id,c1) SELECT 1,NULL
+ON DUPLICATE KEY UPDATE c1=NULL;
+ERROR 23000: Column 'c1' cannot be null
+SELECT * FROM t1;
+id c1 cnt
+1 10 1
+INSERT IGNORE INTO t1 (id,c1) SELECT 1,NULL
+ON DUPLICATE KEY UPDATE c1=NULL, cnt=cnt+1;
+Warnings:
+Warning 1263 Column was set to data type implicit default; NULL supplied for NOT NULL column 'c1' at row 1
+Error 1048 Column 'c1' cannot be null
+SELECT * FROM t1;
+id c1 cnt
+1 0 2
+INSERT IGNORE INTO t1 (id,c1) SELECT * FROM t2
+ON DUPLICATE KEY UPDATE c1=NULL, cnt=cnt+1;
+Warnings:
+Warning 1263 Column was set to data type implicit default; NULL supplied for NOT NULL column 'c1' at row 1
+Error 1048 Column 'c1' cannot be null
+SELECT * FROM t1;
+id c1 cnt
+1 0 3
+2 2 1
+DROP TABLE t1;
--- 1.26/mysql-test/t/insert_update.test 2007-05-11 17:48:26 +05:00
+++ 1.27/mysql-test/t/insert_update.test 2007-05-11 17:48:26 +05:00
@@ -264,3 +264,29 @@ INSERT INTO t1 (c1) VALUES ('A'), ('X')
ON DUPLICATE KEY UPDATE cnt=cnt+1;
SELECT * FROM t1;
DROP TABLE t1;
+
+#
+# Bug#28000: INSERT IGNORE ... SELECT ... ON DUPLICATE
+# with erroneous UPDATE: NOT NULL field with NULL value.
+#
+CREATE TABLE t1 (
+ id INT AUTO_INCREMENT PRIMARY KEY,
+ c1 INT NOT NULL,
+ cnt INT DEFAULT 1
+);
+INSERT INTO t1 (id,c1) VALUES (1,10);
+SELECT * FROM t1;
+CREATE TABLE t2 (id INT, c1 INT);
+INSERT INTO t2 VALUES (1,NULL), (2,2);
+--error 1048
+INSERT INTO t1 (id,c1) SELECT 1,NULL
+ ON DUPLICATE KEY UPDATE c1=NULL;
+SELECT * FROM t1;
+INSERT IGNORE INTO t1 (id,c1) SELECT 1,NULL
+ ON DUPLICATE KEY UPDATE c1=NULL, cnt=cnt+1;
+SELECT * FROM t1;
+INSERT IGNORE INTO t1 (id,c1) SELECT * FROM t2
+ ON DUPLICATE KEY UPDATE c1=NULL, cnt=cnt+1;
+SELECT * FROM t1;
+
+DROP TABLE t1;
| Thread |
|---|
| • bk commit into 5.1 tree (holyfoot:1.2516) | holyfoot | 11 May |
