From 09a1f410cb2e014156f9d6dee87798ab5b28042b Mon Sep 17 00:00:00 2001
From: Igor Babaev <igor@askmonty.org>
Date: Sat, 20 Apr 2013 02:16:55 -0700
Subject: Fixed bug mdev-4406. This bug in the code of
 get_column_range_cardinality() could lead to wrong estimates of number of
 records in ranges for non-nullable columns.

---
 mysql-test/r/selectivity.result        | 34 ++++++++++++++++++++++++++++++++++
 mysql-test/r/selectivity_innodb.result | 34 ++++++++++++++++++++++++++++++++++
 mysql-test/t/selectivity.test          | 31 +++++++++++++++++++++++++++++++
 3 files changed, 99 insertions(+)

(limited to 'mysql-test')

diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result
index 512ef4ffc1b..e59f4310431 100644
--- a/mysql-test/r/selectivity.result
+++ b/mysql-test/r/selectivity.result
@@ -1094,4 +1094,38 @@ f1	f2	f2
 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 DROP TABLE t1,t2;
 set use_stat_tables=@save_use_stat_tables;
+#
+# Bug mdev-4406: range condition for non-nullable column   
+#                when optimizer_use_condition_selectivity=3
+#
+create table t1 (a int not null);
+insert into t1 values
+(7), (6), (4), (9), (1), (5), (2), (1), (3), (8);
+set use_stat_tables='preferably';
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+flush table t1;
+set optimizer_use_condition_selectivity=3;
+select count(*) from t1 where a between 5 and 7;
+count(*)
+3
+explain extended select * from t1 where a between 5 and 7;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	25.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` between 5 and 7)
+alter table t1 change column a a int;
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+flush table t1;
+explain extended select * from t1 where a between 5 and 7;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	25.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` between 5 and 7)
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+drop table t1;
+set use_stat_tables=@save_use_stat_tables;
 set use_stat_tables=@save_use_stat_tables;
diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result
index 2dce973a587..20efb16914b 100644
--- a/mysql-test/r/selectivity_innodb.result
+++ b/mysql-test/r/selectivity_innodb.result
@@ -1102,6 +1102,40 @@ f1	f2	f2
 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 DROP TABLE t1,t2;
 set use_stat_tables=@save_use_stat_tables;
+#
+# Bug mdev-4406: range condition for non-nullable column   
+#                when optimizer_use_condition_selectivity=3
+#
+create table t1 (a int not null);
+insert into t1 values
+(7), (6), (4), (9), (1), (5), (2), (1), (3), (8);
+set use_stat_tables='preferably';
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+flush table t1;
+set optimizer_use_condition_selectivity=3;
+select count(*) from t1 where a between 5 and 7;
+count(*)
+3
+explain extended select * from t1 where a between 5 and 7;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	25.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` between 5 and 7)
+alter table t1 change column a a int;
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+flush table t1;
+explain extended select * from t1 where a between 5 and 7;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	25.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` between 5 and 7)
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+drop table t1;
+set use_stat_tables=@save_use_stat_tables;
 set use_stat_tables=@save_use_stat_tables;
 set optimizer_switch=@save_optimizer_switch_for_selectivity_test;
 SET SESSION STORAGE_ENGINE=DEFAULT;
diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test
index 8bde4718fdd..47f62365816 100644
--- a/mysql-test/t/selectivity.test
+++ b/mysql-test/t/selectivity.test
@@ -676,5 +676,36 @@ DROP TABLE t1,t2;
 
 set use_stat_tables=@save_use_stat_tables;
 
+--echo #
+--echo # Bug mdev-4406: range condition for non-nullable column   
+--echo #                when optimizer_use_condition_selectivity=3
+--echo #
+
+create table t1 (a int not null);
+insert into t1 values
+  (7), (6), (4), (9), (1), (5), (2), (1), (3), (8);
+
+set use_stat_tables='preferably';
+
+analyze table t1;
+flush table t1;
+
+set optimizer_use_condition_selectivity=3;
+
+select count(*) from t1 where a between 5 and 7;
+explain extended select * from t1 where a between 5 and 7;
+
+alter table t1 change column a a int;
+analyze table t1;
+flush table t1;
+
+explain extended select * from t1 where a between 5 and 7;
+
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+drop table t1;
+
+set use_stat_tables=@save_use_stat_tables;
+
 
 set use_stat_tables=@save_use_stat_tables;
-- 
cgit v1.2.1