bk commit into 5.0 tree (gkodinov:1.2433) BUG#26672

[kgeorge]

Below is the list of changes that have just been committed into a local
5.0 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-07 14:51:45+02:00, gkodinov@stripped +3 -0
  Bug #26672:
   DATE/DATETIME values are out of the currently supported
   4 basic value types (INT,STRING,REAL and DECIMAL).
   So expressions (not fields) of compile type DATE/DATETIME are 
   generally considered as STRING values. This is not so
   when they are compared : then they are compared as 
   INTEGER values.
   But the rule for comparison as INTEGERS must be checked
   explicitly each time when a comparison is to be performed.
   filesort is one such place. However there the check was 
   not done and hence the expressions (not fields) of type 
   DATE/DATETIME were sorted by their string representation.
   Fixed to compare them as INTEGER values for filesort.

  mysql-test/r/order_by.result@stripped, 2007-03-07 14:51:39+02:00, gkodinov@stripped +22 -0
    Bug #26672: test case

  mysql-test/t/order_by.test@stripped, 2007-03-07 14:51:40+02:00, gkodinov@stripped +15 -0
    Bug #26672: test case

  sql/filesort.cc@stripped, 2007-03-07 14:51:40+02:00, gkodinov@stripped +4 -1
    Bug #26672: sort dates/times as integers

# 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:	macbook.gmz
# Root:	/Users/kgeorge/mysql/work/B26672-5.0-opt

--- 1.116/sql/filesort.cc	2007-02-22 16:59:55 +02:00
+++ 1.117/sql/filesort.cc	2007-03-07 14:51:40 +02:00
@@ -1298,7 +1298,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.60/mysql-test/r/order_by.result	2007-03-06 22:49:17 +02:00
+++ 1.61/mysql-test/r/order_by.result	2007-03-07 14:51:39 +02:00
@@ -934,3 +934,25 @@ 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;

--- 1.43/mysql-test/t/order_by.test	2007-03-06 22:49:15 +02:00
+++ 1.44/mysql-test/t/order_by.test	2007-03-07 14:51:40 +02:00
@@ -648,3 +648,18 @@ create table t1 (a int, b int, c int);
 insert into t1 values (1,2,3), (9,8,3), (19,4,3), (1,4,9);
 select a,(sum(b)/sum(c)) as ratio from t1 group by a order by sum(b)/sum(c) asc;
 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;