summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--mysql-test/include/mix1.inc54
-rw-r--r--mysql-test/r/innodb_mysql.result52
-rw-r--r--mysql-test/r/insert_update.result35
-rw-r--r--mysql-test/t/insert_update.test26
-rw-r--r--sql/sql_insert.cc3
-rw-r--r--sql/sql_select.cc2
6 files changed, 170 insertions, 2 deletions
diff --git a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc
index 352d5a987a2..4d7d86ccd10 100644
--- a/mysql-test/include/mix1.inc
+++ b/mysql-test/include/mix1.inc
@@ -554,6 +554,60 @@ select * from t1;
drop table t1;
+#
+# Bug #28189: optimizer erroniously prefers ref access to range access
+# for an InnoDB table
+#
+
+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;
+
+SELECT COUNT(*) FROM t1;
+SELECT COUNT(*) FROM t1 WHERE acct_id=785;
+
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE stat_id IN (1,3) AND acct_id=785;
+
+INSERT INTO t2 SELECT * FROM t1;
+OPTIMIZE TABLE t2;
+
+EXPLAIN SELECT COUNT(*) FROM t2 WHERE stat_id IN (1,3) AND acct_id=785;
+
+DROP TABLE t1,t2;
+
--echo End of 5.0 tests
#
diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
index 36d6f62c72c..c4bae224846 100644
--- a/mysql-test/r/innodb_mysql.result
+++ b/mysql-test/r/innodb_mysql.result
@@ -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);
diff --git a/mysql-test/r/insert_update.result b/mysql-test/r/insert_update.result
index 98e6878482b..f6a9c2505b9 100644
--- a/mysql-test/r/insert_update.result
+++ b/mysql-test/r/insert_update.result
@@ -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;
diff --git a/mysql-test/t/insert_update.test b/mysql-test/t/insert_update.test
index de4e73dca93..ff6ddf35c7b 100644
--- a/mysql-test/t/insert_update.test
+++ b/mysql-test/t/insert_update.test
@@ -264,3 +264,29 @@ INSERT INTO t1 (c1) VALUES ('A'), ('X'), ('Y'), ('Z')
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;
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index 30d7b5bc5db..ab9449ad2df 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -1276,7 +1276,8 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info)
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;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 20edf37fe1a..ea78e126d9c 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -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;