MySQL Bugs: #4663: constant function in WHERE clause evaluated in view definition

Bug #4663	constant function in WHERE clause evaluated in view definition
Submitted:	21 Jul 2004 0:40	Modified:	27 Jul 2005 23:06
Reporter:	Dean Ellis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.3	OS:
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
CURRENT_USER() is evaluated rather than being stored literally in a view definition.

How to repeat:
CREATE VIEW v1 (uname) AS SELECT CURRENT_USER();
SHOW CREATE VIEW v1;

Suggested fix:
Store the literal function call.

ChangeSet 
  1.1729 04/08/24 18:46:27 bell@sanja.is.com.ua +4 -0 
  items for functions which is converted to constants internally to support its correct 
printing added (BUG#4663)

Thank you for bugreport! Patch is pushed into source repository.

This previous test case was corrected, but the problem remains:

DROP TABLE IF EXISTS t1;
DROP VIEW IF EXISTS v1;
CREATE TABLE t1 ( a varchar(50) );
CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = CURRENT_USER();
SHOW CREATE VIEW v1;
DROP TABLE t1;
DROP VIEW v1;

Also exists in 5.0.6 - and is (seemingly) for all functions.

e.g.

mysql> create table t1 (a timestamp default now());
Query OK, 0 rows affected (0.01 sec)

mysql> create table t2 (b timestamp default now());
Query OK, 0 rows affected (0.02 sec)

mysql> create view v1 as select * from t1,t2 where t1.a < now(); Query OK, 0 rows affected (0.01 sec)

mysql> select * from v1;
Empty set (0.01 sec)

mysql> insert into t1 values();
Query OK, 1 row affected (0.01 sec)

mysql> select * from v1;
Empty set (0.02 sec)
mysql> show create table v1;
+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| View | Create View                                       |
+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| v1   | CREATE ALGORITHM=UNDEFINED VIEW `test`.`v1` AS select sql_no_cache `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`a` < 20050605174304) |
+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

Note that 20050605174304 should be 'now()'

I am using mysql 5.0.7 on Mac OS X 10.3

I need row level security, and am attempting to use views as described in the whitepaper

MySQL 5.0 Views    MySQL 5.0 New Features Series – Part 3  

I seem to be having the same problem as other users.  I guess it has not been fixed in
release 5.0.7.  Below is an example of how to reproduce what I did.  I have two users,
root, and publicuser.

logged in as the root user 

mysql> CREATE TABLE Employees (    name CHAR(20),     wage DECIMAL(5,2) , note CHAR(20));  

mysql> INSERT INTO Employees VALUES ('root@localhost',3.25,'reliable');

mysql> INSERT INTO Employees VALUES ('publicuser@localhost',4.00,'worthy');

mysql> create view employee_name_and_wage_2 as
    -> select name, wage from employees
    -> where
    -> current_user = name;

mysql> grant select on employees_name_and_wage_2 to publicuser@localhost;

logging in as publicuser@localhost

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13 to server version: 5.0.7-beta-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test

Database changed
mysql> select current_user;
+----------------------+
| current_user         |
+----------------------+
| publicuser@localhost |
+----------------------+
1 row in set (0.00 sec)

mysql> select * from employee_name_and_wage_2;
+----------------+------+
| name           | wage |
+----------------+------+
| root@localhost | 3.25 |
+----------------+------+
1 row in set (0.00 sec)

That last select should return the information for the publicuser.

CURRENT_USER() bug is already fixed

but now() in WHERE clause in comparison with integer value can be evaluated now

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/internals/26382

http://lists.mysql.com/internals/27055
(the same patch as for bug#7291)

pushed to 5.0.10

Documented in the 5.0.10 changelog.