Bug #15950 NOW() optimized away to constant in BETWEEN ... AND ... in VIEWs
Submitted: 23 Dec 2005 2:43 Modified: 7 Sep 2006 0:18
Reporter: Beat Vontobel (Silver Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S3 (Non-critical)
Version:5.0.19-BK, 5.0.17-standard-log-i686-glibc23 OS:Linux (Linux)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[23 Dec 2005 2:43] Beat Vontobel
Description:
When NOW() is used in a VIEW with the BETWEEN ... AND ... comparison operator it gets optimized away and will be replaced with a constant.

(Did a boolean search on the "Views" section in "5.0" on the bugs database with keywords "+between +now" but couldn't find anything - so this might really still be an undiscoverde one...)

How to repeat:
mysql> CREATE TABLE t (dt DATETIME);
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE VIEW v AS SELECT * FROM t WHERE dt BETWEEN NOW() AND NOW() + INTERVAL 1 MINUTE;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE VIEW v\G
*************************** 1. row ***************************
       View: v
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`admin`@`192.168.110.%` SQL SECURITY DEFINER VIEW `v` AS select sql_no_cache `t`.`dt` AS `dt` from `t` where (`t`.`dt` between 20051223022931 and 20051223023031)
1 row in set (0.01 sec)

Suggested fix:
Don't replace NOW() with a constant in BETWEEN ... AND ... but keep the original function as with other comparison operators (e.g. >= and <=, which you can use as a workaround for this bug).
[23 Dec 2005 9:06] Valeriy Kravchuk
Thank you for a problem report. Verified just as described on 5.0.19-BK (ChangeSet@1.1999, 2005-12-21 18:50:06+01:00) on Linux:

mysql> create table t (dt datetime);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE VIEW v AS SELECT * FROM t WHERE dt BETWEEN NOW() AND NOW() + INTERVAL 1 MINUTE;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE VIEW v2 AS SELECT * FROM t WHERE dt >= NOW() AND dt <= NOW() + INTERVAL 1 MINUTE;
Query OK, 0 rows affected (0.01 sec)

mysql> show create view v\G
*************************** 1. row ***************************
       View: v
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY
DEFINER VIEW `v` AS select sql_no_cache `t`.`dt` AS `dt` from `t` where (`t`.`dt` between 20051223115712 and 20051223115812)
1 row in set (0.01 sec)

mysql> show create view v2\G
*************************** 1. row ***************************
       View: v2
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY
DEFINER VIEW `v2` AS select sql_no_cache `t`.`dt` AS `dt` from `t` where ((`t`.`dt` >= now()) and (`t`.`dt` <= (now() + interval 1 minute)))
1 row in set (0.01 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.19    |
+-----------+
1 row in set (0.00 sec)
[13 Aug 2006 18:25] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10329

ChangeSet@1.2252, 2006-08-13 22:25:15+04:00, evgen@moonbone.local +3 -0
  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 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.
[15 Aug 2006 16:42] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10491

ChangeSet@1.2252, 2006-08-15 20:42:33+04:00, evgen@moonbone.local +3 -0
  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 
  with their result only if the current query isn't CREATE VIEW or SHOW CREATE
  VIEW.
[15 Aug 2006 17:02] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10494

ChangeSet@1.2252, 2006-08-15 21:02:55+04:00, evgen@moonbone.local +3 -0
  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.
[29 Aug 2006 13:24] Evgeny Potemkin
Fixed in 5.0.25
[31 Aug 2006 16:01] Chad MILLER
Available in 5.0.25.
[4 Sep 2006 11:34] Evgeny Potemkin
Fixed in 5.1.12
[7 Sep 2006 0:18] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.25/5.1.12 changelogs.