From: Date: August 13 2006 8:22pm
Subject: bk commit into 5.0 tree (evgen:1.2252) BUG#16377
List-Archive: http://lists.mysql.com/commits/10328
X-Bug: 16377
Message-Id: <20060813182230.AEB4322DCDA@moonbone.moonbone.local>

Below is the list of changes that have just been committed into a local
5.0 repository of evgen. When evgen 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-13 22:22:27+04:00, evgen@stripped +3 -0
  Fixed bug#15950: NOW() optimized away in VIEWs
  
  This bug is a side-effect of fix for bug#16377. NOW() is optimized in
  BETWEEN to integer constants to speed up query execution. When view is being
  created it saves already modified query and thus became wrong.
  
  The agg_cmp_type() function now substitutes constant result DATE/TIME functions 
  with their result only if the current query isn't CREATE VIEW or SHOW CREATE
  VIEW.

  mysql-test/r/view.result@stripped, 2006-08-13 22:10:48+04:00, evgen@stripped +7 -0
    Added test case for bug#15950: NOW() optimized away in VIEWs

  mysql-test/t/view.test@stripped, 2006-08-13 22:10:46+04:00, evgen@stripped +9 -0
    Added test case for bug#15950: NOW() optimized away in VIEWs

  sql/item_cmpfunc.cc@stripped, 2006-08-13 22:10:59+04:00, evgen@stripped +28 -20
    Fixed bug#15950: NOW() optimized away in VIEWs
    The agg_cmp_type() function now substitutes constant result DATE/TIME functions 
    with their result only if the current query isn't CREATE VIEW or SHOW CREATE
    VIEW.

# 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:	evgen
# Host:	moonbone.local
# Root:	/work/15950-bug-5.0-opt-mysql

--- 1.213/sql/item_cmpfunc.cc	2006-08-13 22:22:30 +04:00
+++ 1.214/sql/item_cmpfunc.cc	2006-08-13 22:22:30 +04:00
@@ -125,31 +125,39 @@
   uchar null_byte;
   Field *field= NULL;
 
-  /* Search for date/time fields/functions */
-  for (i= 0; i < nitems; i++)
+  /*
+    Don't convert items while creating or showing a view to make
+    original query to be stored/displayed.
+  */
+  if (thd->lex->sql_command != SQLCOM_CREATE_VIEW &&
+      thd->lex->sql_command != SQLCOM_SHOW_CREATE)
   {
-    if (!items[i]->result_as_longlong())
+    /* Search for date/time fields/functions */
+    for (i= 0; i < nitems; i++)
     {
-      /* Do not convert anything if a string field/function is present */
-      if (!items[i]->const_item() && items[i]->result_type() == STRING_RESULT)
+      if (!items[i]->result_as_longlong())
       {
-        i= nitems;
+        /* Do not convert anything if a string field/function is present */
+        if (!items[i]->const_item() && items[i]->result_type() == STRING_RESULT)
+        {
+          i= nitems;
+          break;
+        }
+        continue;
+      }
+      if ((res= items[i]->real_item()->type()) == Item::FIELD_ITEM &&
+          items[i]->result_type() != INT_RESULT)
+      {
+        field= ((Item_field *)items[i]->real_item())->field;
+        break;
+      }
+      else if (res == Item::FUNC_ITEM)
+      {
+        field= items[i]->tmp_table_field_from_field_type(0);
+        if (field)
+          field->move_field(buff, &null_byte, 0);
         break;
       }
-      continue;
-    }
-    if ((res= items[i]->real_item()->type()) == Item::FIELD_ITEM &&
-        items[i]->result_type() != INT_RESULT)
-    {
-      field= ((Item_field *)items[i]->real_item())->field;
-      break;
-    }
-    else if (res == Item::FUNC_ITEM)
-    {
-      field= items[i]->tmp_table_field_from_field_type(0);
-      if (field)
-        field->move_field(buff, &null_byte, 0);
-      break;
     }
   }
   if (field)

--- 1.169/mysql-test/r/view.result	2006-08-13 22:22:30 +04:00
+++ 1.170/mysql-test/r/view.result	2006-08-13 22:22:30 +04:00
@@ -2850,3 +2850,10 @@
 t1
 DROP TABLE t1;
 DROP VIEW IF EXISTS v1;
+create table t1 (f1 datetime);
+create view v1 as select * from t1 where f1 between now() and now() + interval 1 minute;
+show create view v1;
+View	Create View
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1` where (`t1`.`f1` between now() and (now() + interval 1 minute))
+drop view v1;
+drop table t1;

--- 1.154/mysql-test/t/view.test	2006-08-13 22:22:30 +04:00
+++ 1.155/mysql-test/t/view.test	2006-08-13 22:22:30 +04:00
@@ -2718,3 +2718,12 @@
 --disable_warnings
 DROP VIEW IF EXISTS v1;
 --enable_warnings
+
+#
+# Bug #15950: NOW() optimized away in VIEWs
+#
+create table t1 (f1 datetime);
+create view v1 as select * from t1 where f1 between now() and now() + interval 1 minute;
+show create view v1;
+drop view v1;
+drop table t1;