bk commit into 4.1 tree (gkodinov:1.2485) BUG#4981

[kgeorge]

Below is the list of changes that have just been committed into a local
4.1 repository of kgeorge. When kgeorge 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
  1.2485 06/06/02 12:04:03 gkodinov@stripped +3 -0
  Bug #4981: 4.x and 5.x produce non-optimal execution path, 
          3.23 regression test failure
  
  The member SEL_ARG::min_flag was not initialized, 
  due to which the condition for no GEOM_FLAG in function 
  key_or did not choose "Range checked for each record" as 
  the correct access method.

  sql/opt_range.cc
    1.145 06/06/02 12:03:59 gkodinov@stripped +1 -1
    All of the class members initialized

  mysql-test/t/select.test
    1.55 06/06/02 12:03:59 gkodinov@stripped +14 -0
    testcase for 'Range checked' access method

  mysql-test/r/select.result
    1.72 06/06/02 12:03:59 gkodinov@stripped +14 -0
    testcase for 'Range checked' access method

# 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:	gkodinov
# Host:	rakia.(none)
# Root:	/home/kgeorge/mysql/4.1/B4981

--- 1.144/sql/opt_range.cc	2005-10-18 13:04:07 +03:00
+++ 1.145/sql/opt_range.cc	2006-06-02 12:03:59 +03:00
@@ -64,7 +64,7 @@
 	  uint8 min_flag, uint8 max_flag, uint8 maybe_flag);
   SEL_ARG(enum Type type_arg)
     :elements(1),use_count(1),left(0),next_key_part(0),color(BLACK),
-     type(type_arg)
+     type(type_arg),min_flag(0)
   {}
   inline bool is_same(SEL_ARG *arg)
   {

--- 1.71/mysql-test/r/select.result	2005-11-24 18:15:05 +02:00
+++ 1.72/mysql-test/r/select.result	2006-06-02 12:03:59 +03:00
@@ -2714,3 +2714,17 @@
 f1	f2
 1	1
 drop table t1,t2;
+CREATE TABLE t1(a int, b int, c int, KEY b(b), KEY c(c));
+insert into t1 values (1,0,0),(2,0,0);
+CREATE TABLE t2 (a int, b varchar(2), c varchar(2), PRIMARY KEY(a));
+insert into t2 values (1,'',''), (2,'','');
+CREATE TABLE t3 (a int, b int, PRIMARY KEY (a,b), KEY a (a), KEY b (b));
+insert into t3 values (1,1),(1,2);
+explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2 
+where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and 
+t2.b like '%%' order by t2.b limit 0,1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	b,c	b	5	const	1	Using where; Using temporary; Using filesort
+1	SIMPLE	t3	index	PRIMARY,a,b	PRIMARY	8	NULL	2	Using index
+1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	2	Range checked for each record (index map: 0x1)
+DROP TABLE t1,t2,t3;

--- 1.54/mysql-test/t/select.test	2005-11-24 18:14:52 +02:00
+++ 1.55/mysql-test/t/select.test	2006-06-02 12:03:59 +03:00
@@ -2248,4 +2248,18 @@
 select * from t1 where f1 in (select f3 from t2 where (f3,f4)= (select f3,f4 from t2)); 
 drop table t1,t2;
 
+#
+# Bug #4981: 4.x and 5.x produce non-optimal execution path, 3.23 regression test failure
+#
+CREATE TABLE t1(a int, b int, c int, KEY b(b), KEY c(c));
+insert into t1 values (1,0,0),(2,0,0);
+CREATE TABLE t2 (a int, b varchar(2), c varchar(2), PRIMARY KEY(a));
+insert into t2 values (1,'',''), (2,'','');
+CREATE TABLE t3 (a int, b int, PRIMARY KEY (a,b), KEY a (a), KEY b (b));
+insert into t3 values (1,1),(1,2);
+# must have "range checked" for t2
+explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2 
+ where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and 
+       t2.b like '%%' order by t2.b limit 0,1;
+DROP TABLE t1,t2,t3;
 # End of 4.1 tests