Fixed bug#15950: NOW() optimized away in VIEWs

This bug is a side-effect of bug fix #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 becomes wrong.

The agg_cmp_type() function now substitutes constant result DATE/TIME functions 
for their results only if the current query isn't CREATE VIEW or SHOW CREATE
VIEW.


mysql-test/t/view.test:
  Added a test case for bug#15950: NOW() optimized away in VIEWs
mysql-test/r/view.result:
  Added a test case for bug#15950: NOW() optimized away in VIEWs
sql/item_cmpfunc.cc:
  Fixed bug#15950: NOW() optimized away in VIEWs
  The agg_cmp_type() function now substitutes constant result DATE/TIME functions 
  for their results only if the current query isn't CREATE VIEW or SHOW CREATE
  VIEW.

3 files changed, 44 insertions, 20 deletions

diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 534065a33b6..5d217755abd 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -2850,3 +2850,10 @@ Tables_in_test
 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;
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 5cb85ca6c9b..315a61f2003 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -2718,3 +2718,12 @@ DROP TABLE t1;
 --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;
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 34170124cd7..e840cdbdd13 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -125,31 +125,39 @@ static void agg_cmp_type(THD *thd, Item_result *type, Item **items, uint nitems)
   uchar null_byte;
   Field *field= NULL;
 
-  /* Search for date/time fields/functions */
-  for (i= 0; i < nitems; i++)
+  /*
+    Do not convert items while creating a or showing a view in order
+    to store/display the original query in these cases.
+  */
+  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())
+      {
+        /* 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)
       {
-        i= nitems;
+        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)