bk commit into 5.1 tree (gkodinov:1.2485)

[kgeorge]

Below is the list of changes that have just been committed into a local
5.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@stripped, 2007-03-14 17:04:45+02:00, gkodinov@stripped +15 -0
  Merge magare.gmz:/home/kgeorge/mysql/autopush/B26794-5.0-opt
  into  magare.gmz:/home/kgeorge/mysql/autopush/B26794-merge-5.1-opt
  MERGE: 1.1810.2373.155

  mysql-test/r/alter_table.result@stripped, 2007-03-14 17:04:42+02:00, gkodinov@stripped +4
-38
    merged 5.0-opt -> 5.1-opt
    MERGE: 1.49.4.1

  mysql-test/r/gis-rtree.result@stripped, 2007-03-14 17:04:43+02:00, gkodinov@stripped +0 -2
    merged 5.0-opt -> 5.1-opt
    MERGE: 1.14.5.1

  mysql-test/r/order_by.result@stripped, 2007-03-14 17:04:43+02:00, gkodinov@stripped +0 -0
    merged 5.0-opt -> 5.1-opt
    MERGE: 1.55.1.8

  mysql-test/r/select.result@stripped, 2007-03-14 16:42:49+02:00, gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.121.1.32

  mysql-test/r/subselect.result@stripped, 2007-03-14 17:04:43+02:00, gkodinov@stripped +13
-13
    merged 5.0-opt -> 5.1-opt
    MERGE: 1.134.1.45

  mysql-test/t/alter_table.test@stripped, 2007-03-14 17:04:43+02:00, gkodinov@stripped +0
-23
    merged 5.0-opt -> 5.1-opt
    MERGE: 1.36.5.1

  mysql-test/t/order_by.test@stripped, 2007-03-14 17:04:43+02:00, gkodinov@stripped +0 -0
    merged 5.0-opt -> 5.1-opt
    MERGE: 1.41.1.5

  mysql-test/t/select.test@stripped, 2007-03-14 16:42:49+02:00, gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.102.1.25

  mysql-test/t/subselect.test@stripped, 2007-03-14 17:04:43+02:00, gkodinov@stripped +12
-13
    merged 5.0-opt -> 5.1-opt
    MERGE: 1.129.1.15

  sql/field.cc@stripped, 2007-03-14 16:42:49+02:00, gkodinov@stripped +0 -1
    Auto merged
    MERGE: 1.256.1.87

  sql/filesort.cc@stripped, 2007-03-14 16:42:49+02:00, gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.105.1.12

  sql/item.cc@stripped, 2007-03-14 16:42:50+02:00, gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.113.1.143

  sql/item.h@stripped, 2007-03-14 16:42:50+02:00, gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.183.1.39

  sql/sql_show.cc@stripped, 2007-03-14 16:42:50+02:00, gkodinov@stripped +0 -1
    Auto merged
    MERGE: 1.253.47.1

  sql/sql_table.cc@stripped, 2007-03-14 16:42:50+02:00, gkodinov@stripped +0 -2
    Auto merged
    MERGE: 1.239.48.2

# 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:	magare.gmz
# Root:	/home/kgeorge/mysql/autopush/B26794-merge-5.1-opt/RESYNC

--- 1.126/sql/filesort.cc	2007-02-23 13:23:38 +02:00
+++ 1.127/sql/filesort.cc	2007-03-14 16:42:49 +02:00
@@ -1374,7 +1374,10 @@ sortlength(THD *thd, SORT_FIELD *sortord
     }
     else
     {
-      switch ((sortorder->result_type=sortorder->item->result_type())) {
+      sortorder->result_type= sortorder->item->result_type();
+      if (sortorder->item->result_as_longlong())
+        sortorder->result_type= INT_RESULT;
+      switch (sortorder->result_type) {
       case STRING_RESULT:
 	sortorder->length=sortorder->item->max_length;
         set_if_smaller(sortorder->length, thd->variables.max_sort_length);

--- 1.254/sql/item.cc	2007-03-14 13:32:10 +02:00
+++ 1.255/sql/item.cc	2007-03-14 16:42:50 +02:00
@@ -4127,7 +4127,9 @@ bool Item_field::set_no_const_sub(byte *
   DESCRIPTION
     The function returns a pointer to an item that is taken from
     the very beginning of the item_equal list which the Item_field
-    object refers to (belongs to).  
+    object refers to (belongs to) unless item_equal contains  a constant
+    item. In this case the function returns this constant item, 
+    (if the substitution does not require conversion).   
     If the Item_field object does not refer any Item_equal object
     'this' is returned 
 
@@ -4136,7 +4138,8 @@ bool Item_field::set_no_const_sub(byte *
     of the thransformer method.  
 
   RETURN VALUES
-    pointer to a replacement Item_field if there is a better equal item;
+    pointer to a replacement Item_field if there is a better equal item or
+    a pointer to a constant equal item;
     this - otherwise.
 */
 
@@ -4144,6 +4147,14 @@ Item *Item_field::replace_equal_field(by
 {
   if (item_equal)
   {
+    Item *const_item= item_equal->get_const();
+    if (const_item)
+    {
+      if (cmp_context != (Item_result)-1 &&
+          const_item->cmp_context != cmp_context)
+        return this;
+      return const_item;
+    }
     Item_field *subst= item_equal->get_first();
     if (subst && !field->eq(subst->field))
       return subst;

--- 1.229/sql/item.h	2007-03-10 23:23:11 +02:00
+++ 1.230/sql/item.h	2007-03-14 16:42:50 +02:00
@@ -1811,7 +1811,11 @@ public:
     			   str_value.length(), collation.collation);
   }
   Item *safe_charset_converter(CHARSET_INFO *tocs);
-  inline void append(char *str, uint length) { str_value.append(str, length); }
+  inline void append(char *str, uint length)
+  {
+    str_value.append(str, length);
+    max_length= str_value.numchars() * collation.collation->mbmaxlen;
+  }
   void print(String *str);
   // to prevent drop fixed flag (no need parent cleanup call)
   void cleanup() {}

--- 1.173/mysql-test/r/subselect.result	2007-03-10 23:23:11 +02:00
+++ 1.174/mysql-test/r/subselect.result	2007-03-14 17:04:43 +02:00
@@ -3873,3 +3873,16 @@ id_1
 DROP TABLE t1;
 DROP TABLE t2;
 DROP TABLE t1xt2;
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (3), (1), (2);
+SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1;
+col1	col2
+this is a test.	3
+this is a test.	1
+this is a test.	2
+SELECT * FROM (SELECT 'this is ' 'a test.' AS col1, a AS t2 FROM t1) t;
+col1	t2
+this is a test.	3
+this is a test.	1
+this is a test.	2
+DROP table t1;

--- 1.147/mysql-test/t/subselect.test	2007-03-06 18:24:46 +02:00
+++ 1.148/mysql-test/t/subselect.test	2007-03-14 17:04:43 +02:00
@@ -2736,3 +2736,15 @@ DROP TABLE t1;
 DROP TABLE t2;
 DROP TABLE t1xt2;
 
+#
+# Bug #26728: derived table with concatanation of literals in select list
+#  
+
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (3), (1), (2);           
+
+SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1;
+SELECT * FROM (SELECT 'this is ' 'a test.' AS col1, a AS t2 FROM t1) t;
+
+DROP table t1;
+

--- 1.75/mysql-test/r/alter_table.result	2007-03-14 12:20:33 +02:00
+++ 1.76/mysql-test/r/alter_table.result	2007-03-14 17:04:42 +02:00
@@ -886,7 +886,7 @@ ALTER TABLE t1 ADD b GEOMETRY NOT NULL, 
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `a` varchar(500) DEFAULT NULL,
+  `a` varchar(500) default NULL,
   `b` geometry NOT NULL,
   SPATIAL KEY `b` (`b`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
@@ -894,7 +894,7 @@ ALTER TABLE t1 ADD KEY(b(50));
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `a` varchar(500) DEFAULT NULL,
+  `a` varchar(500) default NULL,
   `b` geometry NOT NULL,
   SPATIAL KEY `b` (`b`),
   KEY `b_2` (`b`(50))
@@ -903,9 +903,9 @@ ALTER TABLE t1 ADD c POINT;
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `a` varchar(500) DEFAULT NULL,
+  `a` varchar(500) default NULL,
   `b` geometry NOT NULL,
-  `c` point DEFAULT NULL,
+  `c` point default NULL,
   SPATIAL KEY `b` (`b`),
   KEY `b_2` (`b`(50))
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1

--- 1.65/mysql-test/r/order_by.result	2007-03-09 11:39:37 +02:00
+++ 1.66/mysql-test/r/order_by.result	2007-03-14 17:04:43 +02:00
@@ -958,6 +958,28 @@ a	ratio
 19	1.3333
 9	2.6667
 drop table t1;
+CREATE TABLE t1 (a INT UNSIGNED NOT NULL, b TIME);
+INSERT INTO t1 (a) VALUES (100000), (0), (100), (1000000),(10000), (1000), (10);
+UPDATE t1 SET b = SEC_TO_TIME(a);
+SELECT a, b FROM t1 ORDER BY b DESC;
+a	b
[phone]:46:40
[phone]:46:40
+10000	02:46:40
+1000	00:16:40
+100	00:01:40
+10	00:00:10
+0	00:00:00
+SELECT a, b FROM t1 ORDER BY SEC_TO_TIME(a) DESC;
+a	b
[phone]:46:40
[phone]:46:40
+10000	02:46:40
+1000	00:16:40
+100	00:01:40
+10	00:00:10
+0	00:00:00
+DROP TABLE t1;
 CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), UNIQUE KEY b (b));
 INSERT INTO t1 VALUES (1,1),(2,2);
 CREATE TABLE t2 (a INT, b INT, KEY a (a,b));

--- 1.154/mysql-test/r/select.result	2007-03-09 16:37:14 +02:00
+++ 1.155/mysql-test/r/select.result	2007-03-14 16:42:49 +02:00
@@ -3933,4 +3933,42 @@ cc 	cc 	7
 aa  	aa	2
 aa	aa	2
 DROP TABLE t1,t2;
+CREATE TABLE t1 (
+access_id int NOT NULL default '0',
+name varchar(20) default NULL,
+rank int NOT NULL default '0',
+KEY idx (access_id)
+);
+CREATE TABLE t2 (
+faq_group_id int NOT NULL default '0',
+faq_id int NOT NULL default '0',
+access_id int default NULL,
+UNIQUE KEY idx1 (faq_id),
+KEY idx2 (faq_group_id,faq_id)
+);
+INSERT INTO t1 VALUES 
+(1,'Everyone',2),(2,'Help',3),(3,'Technical Support',1),(4,'Chat User',4);
+INSERT INTO t2 VALUES
+(261,265,1),(490,494,1);
+SELECT t2.faq_id 
+FROM t1 INNER JOIN t2 IGNORE INDEX (idx1)
+ON (t1.access_id = t2.access_id)
+LEFT JOIN t2 t
+ON (t.faq_group_id = t2.faq_group_id AND
+find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))
+WHERE
+t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
+faq_id
+265
+SELECT t2.faq_id 
+FROM t1 INNER JOIN t2
+ON (t1.access_id = t2.access_id)
+LEFT JOIN t2 t
+ON (t.faq_group_id = t2.faq_group_id AND
+find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))
+WHERE
+t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
+faq_id
+265
+DROP TABLE t1,t2;
 End of 5.0 tests

--- 1.48/mysql-test/t/order_by.test	2007-03-09 11:39:37 +02:00
+++ 1.49/mysql-test/t/order_by.test	2007-03-14 17:04:43 +02:00
@@ -679,6 +679,21 @@ select a,(sum(b)/sum(c)) as ratio from t
 drop table t1;
 
 #
+# Bug#26672: Incorrect SEC_TO_TIME() casting in ORDER BY
+#
+CREATE TABLE t1 (a INT UNSIGNED NOT NULL, b TIME);
+INSERT INTO t1 (a) VALUES (100000), (0), (100), (1000000),(10000), (1000), (10);
+UPDATE t1 SET b = SEC_TO_TIME(a);
+
+-- Correct ORDER
+SELECT a, b FROM t1 ORDER BY b DESC;
+
+-- must be ordered as the above
+SELECT a, b FROM t1 ORDER BY SEC_TO_TIME(a) DESC;
+
+DROP TABLE t1;
+
+#
 # BUG#16590: Optimized does not do right "const" table pre-read
 #
 CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), UNIQUE KEY b (b));

--- 1.124/mysql-test/t/select.test	2007-03-09 16:37:38 +02:00
+++ 1.125/mysql-test/t/select.test	2007-03-14 16:42:49 +02:00
@@ -3299,4 +3299,51 @@ SELECT * FROM t1 LEFT JOIN t2 ON t1.name
 
 DROP TABLE t1,t2;
 
+
+#
+# Bug #26963: join with predicates that contain fields from equalities evaluated
+#             to constants after constant table substitution
+#
+
+CREATE TABLE t1 (
+ access_id int NOT NULL default '0',
+ name varchar(20) default NULL,
+ rank int NOT NULL default '0',
+ KEY idx (access_id)
+);
+
+CREATE TABLE t2 (
+  faq_group_id int NOT NULL default '0',
+  faq_id int NOT NULL default '0',
+  access_id int default NULL,
+  UNIQUE KEY idx1 (faq_id),
+  KEY idx2 (faq_group_id,faq_id)
+);
+
+INSERT INTO t1 VALUES 
+  (1,'Everyone',2),(2,'Help',3),(3,'Technical Support',1),(4,'Chat User',4);
+INSERT INTO t2 VALUES
+  (261,265,1),(490,494,1);
+
+
+SELECT t2.faq_id 
+  FROM t1 INNER JOIN t2 IGNORE INDEX (idx1)
+       ON (t1.access_id = t2.access_id)
+       LEFT JOIN t2 t
+       ON (t.faq_group_id = t2.faq_group_id AND
+           find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))
+   WHERE
+     t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
+
+SELECT t2.faq_id 
+  FROM t1 INNER JOIN t2
+       ON (t1.access_id = t2.access_id)
+       LEFT JOIN t2 t
+       ON (t.faq_group_id = t2.faq_group_id AND
+           find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))
+   WHERE
+     t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
+
+DROP TABLE t1,t2;
+
 --echo End of 5.0 tests