From: igor
Date: April 8 2006 6:42pm
Subject: bk commit into 5.0 tree (igor:1.2148) BUG#16069
List-Archive: http://lists.mysql.com/commits/4675
X-Bug: 16069
Message-Id: <20060408184222.3C56B260D56@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
  1.2148 06/04/08 11:42:09 igor@stripped +3 -0
  Fixed bug #16069.
  The function agg_cmp_type in item_cmpfunc.cc neglected the fact that 
  the first argument in a BETWEEN/IN predicate could be a field of a view.
  As a result in the case when the retrieved table was hidden by a view 
  over it and the arguments in the BETWEEN/IN predicates are of 
  the date/time type the function did not perform conversion of 
  the constant arguments to the same format as the first field argument.
  If formats of the arguments differed it caused wrong a evaluation of
  the predicates.

  sql/item_cmpfunc.cc
    1.198 06/04/08 11:42:00 igor@stripped +2 -2
    Fixed bug #16069.
    The function agg_cmp_type in item_cmpfunc.cc neglected the fact that 
    the first argument in a BETWEEN/IN predicate could be a field of a view.
    As a result in the case when the retrieved table was hidden by a view 
    over it and the arguments in the BETWEEN/IN predicates are of 
    the date/time type the function did not perform conversion of 
    the constant arguments to the same format as the first field argument.
    If formats of the arguments differed it caused wrong a evaluation of
    the predicates. 

  mysql-test/t/view.test
    1.140 06/04/08 11:42:00 igor@stripped +20 -0
    Added a test case for bug #16069.

  mysql-test/r/view.result
    1.151 06/04/08 11:42:00 igor@stripped +21 -0
    Added a test case for bug #16069.

# 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/mysql-5.0-0

--- 1.197/sql/item_cmpfunc.cc	2006-04-03 21:02:34 -07:00
+++ 1.198/sql/item_cmpfunc.cc	2006-04-08 11:42:00 -07:00
@@ -55,8 +55,8 @@
   bool all_constant= TRUE;
 
   /* If the first argument is a FIELD_ITEM, pull out the field. */
-  if (items[0]->type() == Item::FIELD_ITEM)
-    field=((Item_field *)items[0])->field;
+  if (items[0]->real_item()->type() == Item::FIELD_ITEM)
+    field=((Item_field *)(items[0]->real_item()))->field;
   /* But if it can't be compared as a longlong, we don't really care. */
   if (field && !field->can_be_compared_as_longlong())
     field= NULL;

--- 1.150/mysql-test/r/view.result	2006-04-04 12:54:56 -07:00
+++ 1.151/mysql-test/r/view.result	2006-04-08 11:42:00 -07:00
@@ -2579,3 +2579,24 @@
 2
 DROP VIEW v2;
 DROP TABLE t1, t2;
+CREATE TABLE t1 (id int NOT NULL PRIMARY KEY,
+td date DEFAULT NULL, KEY idx(td));
+INSERT INTO t1 VALUES 
+(1, '2005-01-01'), (2, '2005-01-02'), (3, '2005-01-02'),
+(4, '2005-01-03'), (5, '2005-01-04'), (6, '2005-01-05'),
+(7, '2005-01-05'), (8, '2005-01-05'), (9, '2005-01-06');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT * FROM t1 WHERE td BETWEEN '2005.01.02' AND '2005.01.04';
+id	td
+2	2005-01-02
+3	2005-01-02
+4	2005-01-03
+5	2005-01-04
+SELECT * FROM v1 WHERE td BETWEEN '2005.01.02' AND '2005.01.04';
+id	td
+2	2005-01-02
+3	2005-01-02
+4	2005-01-03
+5	2005-01-04
+DROP VIEW v1;
+DROP TABLE t1;

--- 1.139/mysql-test/t/view.test	2006-04-04 12:54:56 -07:00
+++ 1.140/mysql-test/t/view.test	2006-04-08 11:42:00 -07:00
@@ -2434,3 +2434,23 @@
 DROP VIEW v2;
 
 DROP TABLE t1, t2;
+
+#
+# Bug #16069: VIEW does return the same results as underlying SELECT
+#             with WHERE condition containing BETWEEN over dates 
+
+CREATE TABLE t1 (id int NOT NULL PRIMARY KEY,
+                 td date DEFAULT NULL, KEY idx(td));
+
+INSERT INTO t1 VALUES 
+ (1, '2005-01-01'), (2, '2005-01-02'), (3, '2005-01-02'),
+ (4, '2005-01-03'), (5, '2005-01-04'), (6, '2005-01-05'),
+ (7, '2005-01-05'), (8, '2005-01-05'), (9, '2005-01-06');
+
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+SELECT * FROM t1 WHERE td BETWEEN '2005.01.02' AND '2005.01.04';
+SELECT * FROM v1 WHERE td BETWEEN '2005.01.02' AND '2005.01.04';
+
+DROP VIEW v1;
+DROP TABLE t1;