[Commits] Rev 4507: Backport fixes from 10.1 into 10.0: in file:///home/psergey/dev2/10.0.15-mdev6634/

Sergey Petrunya psergey at askmonty.org
Wed Feb 4 15:06:19 EET 2015


At file:///home/psergey/dev2/10.0.15-mdev6634/

------------------------------------------------------------
revno: 4507
revision-id: psergey at askmonty.org-20150204130616-l0j0i0sgut2r9v5e
parent: sergii at pisem.net-20141121192039-d0lv6cj96kg5pw02
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 10.0.15-mdev6634
timestamp: Wed 2015-02-04 16:06:16 +0300
message:
  Backport fixes from 10.1 into 10.0:
  378878e1e929982a829aba27470e4b153cf7970b
    MDEV-6634: Wrong estimates for ref(const) and key IS NULL predicate
      
    IS [NOT] NULL predicate is sargable within an outer join. Range
    analysis only uses predicates from ON expressions, which have
    regular semantics (without null-complemented rows, etc).
    There is no reason not use IS [NOT] NULL predicates.
  
  9534fd83ce6dc402132cc304c121c9205b430dda
    MDEV-6634: Wrong estimates for ref(const): Update test result
=== modified file 'mysql-test/r/join_outer.result'
--- a/mysql-test/r/join_outer.result	2014-10-29 12:20:46 +0000
+++ b/mysql-test/r/join_outer.result	2015-02-04 13:06:16 +0000
@@ -2245,4 +2245,32 @@ SELECT * FROM t1 INNER JOIN t2 ON c = b
 WHERE b IN (1,2,3) OR b = d;
 a	b	c	d
 DROP TABLE t1,t2,t3;
+#
+# MDEV-6634: Wrong estimates for ref(const) and key IS NULL predicate
+#
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (a int, b int, c int, key(b), key(c));
+insert into t2 select 
+ at a:=A.a + 10*B.a+100*C.a,
+IF(@a<900, NULL, @a),
+IF(@a<500, NULL, @a)
+from t1 A, t1 B, t1 C;
+delete from t1 where a=0;
+# Check that there are different #rows of NULLs for b and c, both !=10:
+explain select * from t2 force index (b) where b is null;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ref	b	b	5	const	780	Using index condition
+explain select * from t2 force index (c) where c is null;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ref	c	c	5	const	393	Using index condition
+explain select * from t1 left join t2 on t2.b is null;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	9	
+1	SIMPLE	t2	ref	b	b	5	const	780	Using where
+explain select * from t1 left join t2 on t2.c is null;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	9	
+1	SIMPLE	t2	ref	c	c	5	const	393	Using where
+drop table t1,t2;
 SET optimizer_switch=@save_optimizer_switch;

=== modified file 'mysql-test/r/join_outer_jcl6.result'
--- a/mysql-test/r/join_outer_jcl6.result	2014-11-03 16:47:37 +0000
+++ b/mysql-test/r/join_outer_jcl6.result	2015-02-04 13:06:16 +0000
@@ -2256,6 +2256,34 @@ SELECT * FROM t1 INNER JOIN t2 ON c = b
 WHERE b IN (1,2,3) OR b = d;
 a	b	c	d
 DROP TABLE t1,t2,t3;
+#
+# MDEV-6634: Wrong estimates for ref(const) and key IS NULL predicate
+#
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (a int, b int, c int, key(b), key(c));
+insert into t2 select 
+ at a:=A.a + 10*B.a+100*C.a,
+IF(@a<900, NULL, @a),
+IF(@a<500, NULL, @a)
+from t1 A, t1 B, t1 C;
+delete from t1 where a=0;
+# Check that there are different #rows of NULLs for b and c, both !=10:
+explain select * from t2 force index (b) where b is null;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ref	b	b	5	const	780	Using index condition
+explain select * from t2 force index (c) where c is null;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ref	c	c	5	const	393	Using index condition
+explain select * from t1 left join t2 on t2.b is null;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	9	
+1	SIMPLE	t2	ref	b	b	5	const	780	Using where
+explain select * from t1 left join t2 on t2.c is null;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	9	
+1	SIMPLE	t2	ref	c	c	5	const	393	Using where
+drop table t1,t2;
 SET optimizer_switch=@save_optimizer_switch;
 set join_cache_level=default;
 show variables like 'join_cache_level';

=== modified file 'mysql-test/t/join_outer.test'
--- a/mysql-test/t/join_outer.test	2014-10-29 12:20:46 +0000
+++ b/mysql-test/t/join_outer.test	2015-02-04 13:06:16 +0000
@@ -1801,4 +1801,28 @@ SELECT * FROM t1 INNER JOIN t2 ON c = b
 DROP TABLE t1,t2,t3;
 
 
+--echo #
+--echo # MDEV-6634: Wrong estimates for ref(const) and key IS NULL predicate
+--echo #
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (a int, b int, c int, key(b), key(c));
+
+insert into t2 select 
+  @a:=A.a + 10*B.a+100*C.a,
+  IF(@a<900, NULL, @a),
+  IF(@a<500, NULL, @a)
+from t1 A, t1 B, t1 C;
+
+delete from t1 where a=0;
+
+--echo # Check that there are different #rows of NULLs for b and c, both !=10:
+explain select * from t2 force index (b) where b is null;
+explain select * from t2 force index (c) where c is null;
+
+explain select * from t1 left join t2 on t2.b is null;
+explain select * from t1 left join t2 on t2.c is null;
+
+drop table t1,t2;
+
 SET optimizer_switch=@save_optimizer_switch;

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2014-11-19 14:14:49 +0000
+++ b/sql/opt_range.cc	2015-02-04 13:06:16 +0000
@@ -8214,8 +8214,15 @@ get_mm_leaf(RANGE_OPT_PARAM *param, COND
   param->thd->mem_root= param->old_root;
   if (!value)					// IS NULL or IS NOT NULL
   {
-    if (field->table->maybe_null)		// Can't use a key on this
-      goto end;
+    /*
+      No check for field->table->maybe_null. It's perfecly fine to use range
+      access for cases like 
+
+        SELECT * FROM t1 LEFT JOIN t2 ON t2.key IS [NOT] NULL
+
+      ON expression is evaluated before considering NULL-complemented rows, so
+      IS [NOT] NULL has regular semantics.
+    */
     if (!maybe_null)				// Not null field
     {
       if (type == Item_func::ISNULL_FUNC)



More information about the commits mailing list