Fix of newly introduced bug in ORDER BY when one has a constant in the select list.

3 files changed, 78 insertions, 3 deletions

diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index 9bc716ee8b9..9ac88b42436 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -471,3 +471,38 @@ 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
 drop table t1,t2,t3;
+CREATE TABLE t1 (
+`titre` char(80) NOT NULL default '',
+`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
+`date` datetime NOT NULL default '0000-00-00 00:00:00',
+`auteur` char(35) NOT NULL default '',
+`icone` tinyint(2) unsigned NOT NULL default '0',
+`lastauteur` char(35) NOT NULL default '',
+`nbrep` smallint(6) unsigned NOT NULL default '0',
+`dest` char(35) NOT NULL default '',
+`lu` tinyint(1) unsigned NOT NULL default '0',
+`vue` mediumint(8) unsigned NOT NULL default '0',
+`ludest` tinyint(1) unsigned NOT NULL default '0',
+`ouvert` tinyint(1) unsigned NOT NULL default '1',
+PRIMARY KEY  (`numeropost`),
+KEY `date` (`date`),
+KEY `dest` (`dest`,`ludest`),
+KEY `auteur` (`auteur`,`lu`),
+KEY `auteur_2` (`auteur`,`date`),
+KEY `dest_2` (`dest`,`date`)
+) CHECKSUM=1;
+CREATE TABLE t2 (
+`numeropost` mediumint(8) unsigned NOT NULL default '0',
+`pseudo` char(35) NOT NULL default '',
+PRIMARY KEY  (`numeropost`,`pseudo`),
+KEY `pseudo` (`pseudo`)
+);
+INSERT INTO t1 (titre,auteur,dest) VALUES ('test','joce','bug');
+INSERT INTO t2 (numeropost,pseudo) VALUES (1,'joce'),(1,'bug');
+SELECT titre,t1.numeropost,auteur,icone,nbrep,0,date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30;
+titre	numeropost	auteur	icone	nbrep	0	date	vue	ouvert	lastauteur	dest
+test	1	joce	0	0	0	0000-00-00 00:00:00	0	1		bug
+SELECT titre,t1.numeropost,auteur,icone,nbrep,'0',date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30;
+titre	numeropost	auteur	icone	nbrep	0	date	vue	ouvert	lastauteur	dest
+test	1	joce	0	0	0	0000-00-00 00:00:00	0	1		bug
+drop table t1,t2;
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index baa3fe67f0b..0ee6f901aae 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -292,3 +292,41 @@ EXPLAIN SELECT t1.gid, t2.sid, t3.uid from t2, t1, t3 where t2.gid = t1.gid and
 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;
+
+#
+# Test of bug when doing an ORDER BY with const items
+#
+
+CREATE TABLE t1 (
+  `titre` char(80) NOT NULL default '',
+  `numeropost` mediumint(8) unsigned NOT NULL auto_increment,
+  `date` datetime NOT NULL default '0000-00-00 00:00:00',
+  `auteur` char(35) NOT NULL default '',
+  `icone` tinyint(2) unsigned NOT NULL default '0',
+  `lastauteur` char(35) NOT NULL default '',
+  `nbrep` smallint(6) unsigned NOT NULL default '0',
+  `dest` char(35) NOT NULL default '',
+  `lu` tinyint(1) unsigned NOT NULL default '0',
+  `vue` mediumint(8) unsigned NOT NULL default '0',
+  `ludest` tinyint(1) unsigned NOT NULL default '0',
+  `ouvert` tinyint(1) unsigned NOT NULL default '1',
+  PRIMARY KEY  (`numeropost`),
+  KEY `date` (`date`),
+  KEY `dest` (`dest`,`ludest`),
+  KEY `auteur` (`auteur`,`lu`),
+  KEY `auteur_2` (`auteur`,`date`),
+  KEY `dest_2` (`dest`,`date`)
+) CHECKSUM=1;
+
+CREATE TABLE t2 (
+  `numeropost` mediumint(8) unsigned NOT NULL default '0',
+  `pseudo` char(35) NOT NULL default '',
+  PRIMARY KEY  (`numeropost`,`pseudo`),
+  KEY `pseudo` (`pseudo`)
+);
+
+INSERT INTO t1 (titre,auteur,dest) VALUES ('test','joce','bug');
+INSERT INTO t2 (numeropost,pseudo) VALUES (1,'joce'),(1,'bug');
+SELECT titre,t1.numeropost,auteur,icone,nbrep,0,date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30;
+SELECT titre,t1.numeropost,auteur,icone,nbrep,'0',date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30;
+drop table t1,t2;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index b63f3e08911..37ee18a78d5 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -639,7 +639,8 @@ mysql_select(THD *thd,TABLE_LIST *tables,List<Item> &fields,COND *conds,
     DBUG_PRINT("info",("Creating tmp table"));
     thd->proc_info="Creating tmp table";
 
-    join.tmp_table_param.hidden_field_count=  all_fields.elements- fields.elements;
+    join.tmp_table_param.hidden_field_count= (all_fields.elements -
+					      fields.elements);
     if (!(tmp_table =
 	  create_tmp_table(thd,&join.tmp_table_param,all_fields,
 			   ((!simple_group && !procedure &&
@@ -3598,7 +3599,7 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
   List_iterator_fast<Item> li(fields);
   Item *item;
   Field **tmp_from_field=from_field;
-  for(uint field_no=0; ((item=li++)); field_no++)
+  while ((item=li++))
   {
     Item::Type type=item->type();
     if (not_all_columns)
@@ -3613,7 +3614,7 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
 	param->using_indirect_summary_function=1;
 	continue;
       }
-      if (item->const_item() && field_no >= hidden_field_count)
+      if (item->const_item() && (int) hidden_field_count <= 0)
         continue; // We don't have to store this
     }
     if (type == Item::SUM_FUNC_ITEM && !group && !save_sum_fields)
@@ -3669,6 +3670,7 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
     if (!--hidden_field_count)
       hidden_null_count=null_count;
   }
+  DBUG_ASSERT(field_count >= (uint) (reg_field - table->field));
   field_count= (uint) (reg_field - table->field);
 
   /* If result table is small; use a heap */