Bug #22587 | Bug on view using function | ||
---|---|---|---|
Submitted: | 22 Sep 2006 13:12 | Modified: | 13 Nov 2006 14:30 |
Reporter: | [ name withheld ] | Email Updates: | |
Status: | No Feedback | Impact on me: | |
Category: | MySQL Server | Severity: | S1 (Critical) |
Version: | 5.0.24a | OS: | MacOS (mac os x 10.3.9) |
Assigned to: | CPU Architecture: | Any | |
Tags: | VIEW |
[22 Sep 2006 13:12]
[ name withheld ]
[1 Oct 2006 1:08]
Ed Dawley
We just updated a production server and ran across this same issue. Here is a simple case demonstrating the bug. Notice that the value for utc_timestamp() is being inserted as a constant if used with the between syntax. mysql> create table `table` (`column` text, start datetime, end datetime); Query OK, 0 rows affected (0.02 sec) mysql> CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW test as select `column` from `table` where utc_timestamp() between start and end; Query OK, 0 rows affected (0.00 sec) mysql> show create view test; +------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | View | Create View | +------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test | CREATE ALGORITHM=UNDEFINED DEFINER=`admin`@`localhost` SQL SECURITY DEFINER VIEW `test` AS select `table`.`column` AS `column` from `table` where (20061001010553 between `table`.`start` and `table`.`end`) | +------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW test2 as select `column` from `table` where utc_timestamp() > start and utc_timestamp() < end; Query OK, 0 rows affected (0.01 sec) mysql> show create view test2; +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | View | Create View | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test2 | CREATE ALGORITHM=UNDEFINED DEFINER=`admin`@`localhost` SQL SECURITY DEFINER VIEW `test2` AS select `table`.`column` AS `column` from `table` where ((utc_timestamp() > `table`.`start`) and (utc_timestamp() < `table`.`end`)) | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
[13 Oct 2006 14:30]
Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat with a newer version, 5.0.26, and inform about the results.
[14 Nov 2006 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".