From: Date: August 31 2006 4:27pm
Subject: bk commit into 5.0 tree (igor:1.2272) BUG#16249
List-Archive: http://lists.mysql.com/commits/11167
X-Bug: 16249
Message-Id: <20060831142743.597755BA7B@rurik.mysql.com>

Below is the list of changes that have just been committed into a local
5.0 repository of igor. When igor 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, 2006-08-31 07:27:34-07:00, igor@stripped +4 -0
  Fixed bug #16249: different results for a range with an without index 
  when a range condition use an invalid DATETIME constant.
  Now we do not use invalid DATETIME constants to form end keys for
  range intervals: range analysis just ignores predicates with such
  constants.  

  mysql-test/r/query_cache.result@stripped, 2006-08-31 07:27:29-07:00, igor@stripped +6 -0
    Adjusted result warnings when adding a fix for bug #16249.

  mysql-test/r/range.result@stripped, 2006-08-31 07:27:29-07:00, igor@stripped +45 -0
    Added a test case for bug #16249.

  mysql-test/t/range.test@stripped, 2006-08-31 07:27:29-07:00, igor@stripped +29 -0
    Added a test case for bug #16249.

  sql/opt_range.cc@stripped, 2006-08-31 07:27:29-07:00, igor@stripped +8 -1
    Fixed bug #16249: different results for a range with an without index 
    when a range condition use an invalid DATETIME constant.
    Now we do not use invalid DATETIME constants to form end keys for
    range intervals: range analysis just ignores predicates with such
    constants.  

# 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:	igor
# Host:	rurik.mysql.com
# Root:	/home/igor/dev-opt/mysql-5.0-opt-bug16249

--- 1.222/sql/opt_range.cc	2006-08-31 07:27:43 -07:00
+++ 1.223/sql/opt_range.cc	2006-08-31 07:27:43 -07:00
@@ -4129,6 +4129,7 @@
   MEM_ROOT *alloc= param->mem_root;
   char *str;
   ulong orig_sql_mode;
+  int err;
   DBUG_ENTER("get_mm_leaf");
 
   /*
@@ -4280,7 +4281,13 @@
       (field->type() == FIELD_TYPE_DATE ||
        field->type() == FIELD_TYPE_DATETIME))
     field->table->in_use->variables.sql_mode|= MODE_INVALID_DATES;
-  if (value->save_in_field_no_warnings(field, 1) < 0)
+  err= value->save_in_field_no_warnings(field, 1);
+  if (err > 0 && field->cmp_type() != value->result_type())
+  {
+    tree= 0;
+    goto end;
+  } 
+  if (err < 0)
   {
     field->table->in_use->variables.sql_mode= orig_sql_mode;
     /* This happens when we try to insert a NULL field in a not null column */

--- 1.48/mysql-test/r/range.result	2006-08-31 07:27:43 -07:00
+++ 1.49/mysql-test/r/range.result	2006-08-31 07:27:43 -07:00
@@ -896,3 +896,48 @@
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index_merge	idx1,idx2	idx1,idx2	4,4	NULL	4	Using sort_union(idx1,idx2); Using where
 DROP TABLE t1;
+CREATE TABLE t1 (                                      
+item char(20) NOT NULL default '',                          
+started datetime NOT NULL default '0000-00-00 00:00:00', 
+price decimal(16,3) NOT NULL default '0.000',                 
+PRIMARY KEY (item,started)                     
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+('A1','2005-11-01 08:00:00',1000),
+('A1','2005-11-15 00:00:00',2000),
+('A1','2005-12-12 08:00:00',3000),
+('A2','2005-12-01 08:00:00',1000);
+EXPLAIN SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	PRIMARY	PRIMARY	20	const	2	Using where
+Warnings:
+Warning	1292	Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1
+Warning	1292	Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1
+SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00';
+item	started	price
+A1	2005-11-01 08:00:00	1000.000
+A1	2005-11-15 00:00:00	2000.000
+Warnings:
+Warning	1292	Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1
+Warning	1292	Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1
+SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-02 00:00:00';
+item	started	price
+A1	2005-11-01 08:00:00	1000.000
+A1	2005-11-15 00:00:00	2000.000
+DROP INDEX `PRIMARY` ON t1;
+EXPLAIN SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+Warnings:
+Warning	1292	Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1
+SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00';
+item	started	price
+A1	2005-11-01 08:00:00	1000.000
+A1	2005-11-15 00:00:00	2000.000
+Warnings:
+Warning	1292	Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1
+SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-02 00:00:00';
+item	started	price
+A1	2005-11-01 08:00:00	1000.000
+A1	2005-11-15 00:00:00	2000.000
+DROP TABLE t1;

--- 1.41/mysql-test/t/range.test	2006-08-31 07:27:43 -07:00
+++ 1.42/mysql-test/t/range.test	2006-08-31 07:27:43 -07:00
@@ -709,5 +709,34 @@
 
 DROP TABLE t1;
 
+#
+# Bug #16249: different results for a range with an without index 
+#             when a range condition use an invalid datetime constant 
+#
+
+CREATE TABLE t1 (                                      
+  item char(20) NOT NULL default '',                          
+  started datetime NOT NULL default '0000-00-00 00:00:00', 
+  price decimal(16,3) NOT NULL default '0.000',                 
+  PRIMARY KEY (item,started)                     
+) ENGINE=MyISAM;   
+
+INSERT INTO t1 VALUES
+('A1','2005-11-01 08:00:00',1000),
+('A1','2005-11-15 00:00:00',2000),
+('A1','2005-12-12 08:00:00',3000),
+('A2','2005-12-01 08:00:00',1000);
+
+EXPLAIN SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00';
+SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00';
+SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-02 00:00:00';
+
+DROP INDEX `PRIMARY` ON t1;
+
+EXPLAIN SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00';
+SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00';
+SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-02 00:00:00';
+
+DROP TABLE t1;
 
 # End of 5.0 tests

--- 1.72/mysql-test/r/query_cache.result	2006-08-31 07:27:43 -07:00
+++ 1.73/mysql-test/r/query_cache.result	2006-08-31 07:27:43 -07:00
@@ -947,18 +947,24 @@
 Warnings:
 Warning	1292	Incorrect datetime value: '20050327 invalid' for column 'date' at row 1
 Warning	1292	Incorrect datetime value: '20050327 invalid' for column 'date' at row 1
+Warning	1292	Truncated incorrect DOUBLE value: '20050327 invalid'
+Warning	1292	Truncated incorrect DOUBLE value: '20050327 invalid'
 SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050328 invalid';
 COUNT(*)
 0
 Warnings:
 Warning	1292	Incorrect datetime value: '20050328 invalid' for column 'date' at row 1
 Warning	1292	Incorrect datetime value: '20050328 invalid' for column 'date' at row 1
+Warning	1292	Truncated incorrect DOUBLE value: '20050328 invalid'
+Warning	1292	Truncated incorrect DOUBLE value: '20050328 invalid'
 SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050327 invalid';
 COUNT(*)
 0
 Warnings:
 Warning	1292	Incorrect datetime value: '20050327 invalid' for column 'date' at row 1
 Warning	1292	Incorrect datetime value: '20050327 invalid' for column 'date' at row 1
+Warning	1292	Truncated incorrect DOUBLE value: '20050327 invalid'
+Warning	1292	Truncated incorrect DOUBLE value: '20050327 invalid'
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
 Qcache_queries_in_cache	0