Description:
functions in a WHERE clause for a view will be evaluated when you create a view, not when you select from the view.
This is in contrast to how some other RDBMS work (in this case, I'm porting an app from postgresql).
How to repeat:
This works as expected:
mysql> CREATE OR REPLACE VIEW v1 AS SELECT NOW();
Query OK, 0 rows affected (0.01 sec)
mysql> show create table v1;
+------+-----------------------------------------------------------------------------------------+
| View | Create View |
+------+-----------------------------------------------------------------------------------------+
| v1 | CREATE ALGORITHM=UNDEFINED VIEW `memberdb`.`v1` AS select sql_no_cache now() AS `NOW()` |
+------+-----------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> select * from v1;
+---------------------+
| NOW() |
+---------------------+
| 2005-06-10 13:19:30 |
+---------------------+
1 row in set (0.00 sec)
mysql> select * from v1;
+---------------------+
| NOW() |
+---------------------+
| 2005-06-10 13:19:31 |
+---------------------+
1 row in set (0.00 sec)
However, the following does not:
mysql> CREATE OR REPLACE VIEW v1 AS SELECT * from members where date_entered < now();
Query OK, 0 rows affected (0.01 sec)
mysql> show create table v1;
| v1 | CREATE ALGORITHM=UNDEFINED VIEW `memberdb`.`v1` AS select sql_no_cache --LOTS OF STUFF-- where (`memberdb`.`members`.`date_entered` < 20050610132017)
There is a workaround, but it's ugly:
mysql> CREATE OR RELPACE VIEW now AS SELECT now();
and then do a subquery in your vews of (SELECT * from now) instead of calling now().
ugly, but it works.
Suggested fix:
Have now() evaluated on select, not when you create the view.
at the very least, we should document this behaviour.
