| 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: | |
| 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 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.
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).