Fixed bug mdev-4349.

Range analysis of the condition for a non-indexed column may
return an impossible range. This must be taken into account.

1 files changed, 41 insertions, 0 deletions

diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result
index 8a620de68e8..02f58dbfebe 100644
--- a/mysql-test/r/selectivity_innodb.result
+++ b/mysql-test/r/selectivity_innodb.result
@@ -681,6 +681,47 @@ DROP VIEW v1;
 DROP TABLE t1,t2;
 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 set use_stat_tables=@tmp_use_stat_tables;
+#
+# Bug mdev-4349: impossible range for non-indexed column 
+#
+set optimizer_use_condition_selectivity=3;
+create table t1 (a int);
+insert into t1 values
+(3), (7), (2), (5), (7), (1), (2), (2);
+set optimizer_use_condition_selectivity=1;
+explain extended
+select * from t1 where a < 1 and a > 7;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` < 1) and (`test`.`t1`.`a` > 7))
+select * from t1 where a < 1 and a > 7;
+a
+set optimizer_use_condition_selectivity=3;
+explain extended
+select * from t1 where a < 1 and a > 7;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0
+select * from t1 where a < 1 and a > 7;
+a
+drop table t1;
+create table t1 (a int);
+insert into t1 values (1);
+create table t2 (b int);
+insert into t2 values (2),(3);
+explain extended 
+select * from t1 where a in ( select b from t2 ) AND ( a > 3 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	0.00	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	0	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b` = `test`.`t1`.`a`) and (`test`.`t1`.`a` > 3))
+select * from t1 where a in ( select b from t2 ) AND ( a > 3 );
+a
+drop table t1,t2;
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 set use_stat_tables=@save_use_stat_tables;
 set optimizer_switch=@save_optimizer_switch_for_selectivity_test;
 SET SESSION STORAGE_ENGINE=DEFAULT;