Fixed optimizer bug in ORDER BY where some ORDER BY parts where ignored.

Extended default arguments to be up to 4095 characters


Docs/manual.texi:
  Update lof LOCK TABLES
mysql-test/r/order_by.result:
  Test for optimizer bug in ORDER BY
mysql-test/t/order_by.test:
  Test for optimizer bug in ORDER BY
mysys/default.c:
  Extended default arguments to be up to 4095 characters
sql/sql_select.cc:
  Fixed optimizer bug in ORDER BY

2 files changed, 78 insertions, 5 deletions

diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index 74c8bd53af2..1a292b1203c 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -111,3 +111,34 @@ DateOfAction	TransactionID
 member_id	nickname	voornaam
 1		
 2		
+gid	sid	uid
+104620	5	15
+103867	5	27
+103962	5	27
+104619	5	75
+104505	5	117
+103853	5	250
+gid	sid	uid
+104620	5	15
+103867	5	27
+103962	5	27
+104619	5	75
+104505	5	117
+103853	5	250
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	index	PRIMARY	PRIMARY	4	NULL	6	Using index
+t2	eq_ref	PRIMARY,uid	PRIMARY	4	t1.gid	1	
+t3	eq_ref	PRIMARY	PRIMARY	2	t2.uid	1	where used; Using index
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	index	PRIMARY	PRIMARY	4	NULL	6	Using index
+t3	eq_ref	PRIMARY	PRIMARY	2	t1.gid	1	where used
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	index	PRIMARY	PRIMARY	4	NULL	6	Using index; Using temporary; Using filesort
+t2	eq_ref	PRIMARY,uid	PRIMARY	4	t1.gid	1	
+t3	eq_ref	PRIMARY	PRIMARY	2	t2.uid	1	where used; Using index
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	index	PRIMARY	PRIMARY	4	NULL	6	Using index; Using temporary; Using filesort
+t3	eq_ref	PRIMARY	PRIMARY	2	t1.gid	1	where used
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
+t3	eq_ref	PRIMARY	PRIMARY	2	t1.skr	1	where used
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index 4e5cee0d0ff..16094206745 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -168,8 +168,8 @@ drop table t1,t2,t3;
 
 #bug reported by Wouter de Jong
 
-drop table if exists members;
-CREATE TABLE members (
+drop table if exists t1;
+CREATE TABLE t1 (
   member_id int(11) NOT NULL auto_increment,
   inschrijf_datum varchar(20) NOT NULL default '',
   lastchange_datum varchar(20) NOT NULL default '',
@@ -200,8 +200,50 @@ CREATE TABLE members (
   PRIMARY KEY  (member_id)
 ) TYPE=MyISAM PACK_KEYS=1;
 
-insert into members (member_id) values (1),(2),(3);
-select member_id, nickname, voornaam FROM members
+insert into t1 (member_id) values (1),(2),(3);
+select member_id, nickname, voornaam FROM t1
 ORDER by lastchange_datum DESC LIMIT 2;
-drop table members;
+drop table t1;
+
+#
+# Test optimizing bug with EQ_REF tables, where some ORDER BY parts where
+# wrongly removed.
 
+CREATE TABLE t1 (
+  gid int(10) unsigned NOT NULL auto_increment,
+  cid smallint(5) unsigned NOT NULL default '0',
+  PRIMARY KEY  (gid),
+  KEY component_id (cid)
+) TYPE=MyISAM;
+INSERT INTO t1 VALUES (103853,108),(103867,108),(103962,108),(104505,108),(104619,108),(104620,108);
+ALTER TABLE t1 add skr int(10) not null;
+
+CREATE TABLE t2 (
+  gid int(10) unsigned NOT NULL default '0',
+  uid smallint(5) unsigned NOT NULL default '1',
+  sid tinyint(3) unsigned NOT NULL default '1',
+  PRIMARY KEY  (gid),
+  KEY uid (uid),
+  KEY status_id (sid)
+) TYPE=MyISAM;
+INSERT INTO t2 VALUES (103853,250,5),(103867,27,5),(103962,27,5),(104505,117,5),(104619,75,5),(104620,15,5);
+
+CREATE TABLE t3 (
+  uid smallint(6) NOT NULL auto_increment,
+  PRIMARY KEY  (uid)
+) TYPE=MyISAM;
+INSERT INTO t3 VALUES (1),(15),(27),(75),(117),(250);
+ALTER TABLE t3 add skr int(10) not null;
+
+select t1.gid, t2.sid, t3.uid from t2, t1, t3 where t2.gid = t1.gid and t2.uid = t3.uid order by t3.uid, t1.gid;
+select t1.gid, t2.sid, t3.uid from t3, t2, t1 where t2.gid = t1.gid and t2.uid = t3.uid order by t3.uid, t1.gid;
+
+# The following ORDER BY can be optimimized
+EXPLAIN select t1.gid, t2.sid, t3.uid from t3, t2, t1 where t2.gid = t1.gid and t2.uid = t3.uid order by t1.gid, t3.uid;
+EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t1.gid,t3.skr;
+
+# The following ORDER BY can't be optimimized
+EXPLAIN SELECT t1.gid, t2.sid, t3.uid from t2, t1, t3 where t2.gid = t1.gid and t2.uid = t3.uid order by t3.uid, t1.gid;
+EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t3.skr,t1.gid;
+EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.skr = t3.uid order by t1.gid,t3.skr;
+drop table t1,t2,t3;