MySQL Bugs: #15153: CONVERT_TZ() not allowed in all places in VIEWs

Bug #15153	CONVERT_TZ() not allowed in all places in VIEWs
Submitted:	22 Nov 2005 22:47	Modified:	2 May 2006 1:24
Reporter:	Beat Vontobel (Silver Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S2 (Serious)
Version:	5.0 BK	OS:	Any (All)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
CONVERT_TZ() raises an "ERROR 1356 (HY000): View references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them" if used in some places inside a VIEW, even if nothing mentioned in the error message holds.

How to repeat:
First create a simple table with a DATETIME column, a VIEW on it using CONVERT_TZ() in the WHERE clause and SELECT from it:

mysql> CREATE TABLE t (dt DATETIME);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE OR REPLACE VIEW v AS SELECT dt FROM t WHERE CONVERT_TZ(dt, 'UTC', 'Europe/Zurich') > NOW();
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM v;
Empty set (0.00 sec)

This works as intended. Now change the VIEW to use the CONVERT_TZ in the SELECT part:

mysql> CREATE OR REPLACE VIEW v AS SELECT CONVERT_TZ(dt, 'UTC', 'Europe/Zurich') FROM t;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM v;
ERROR 1356 (HY000): View 'meteonews.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Just to check, execute the exactly same query from the view standalone:

mysql> SELECT CONVERT_TZ(dt, 'UTC', 'Europe/Zurich') FROM t;
Empty set (0.01 sec)

This works again. The error is only triggered if the CONVERT_TZ() is used in the SELECT part of a VIEW, not in any other part or in a standalone query.

Thanks for your bug report.  I tested this under FC4 w/5.0.16:

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

mysql> CREATE OR REPLACE VIEW v AS SELECT dt FROM t WHERE CONVERT_TZ(dt, 'UTC',
    -> 'Europe/Zurich') > NOW();
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM v;
Empty set (0.00 sec)

mysql> CREATE OR REPLACE VIEW v AS SELECT CONVERT_TZ(dt, 'UTC', 'Europe/Zurich')
    -> FROM t;
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT * FROM v;
ERROR 1356 (HY000): View 'test.v' references invalid table(s) or column(s) or function(s)
mysql> SELECT CONVERT_TZ(dt, 'UTC', 'Europe/Zurich') FROM t;
Empty set (0.00 sec)

mysql>

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/5134

Approved by email.

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/5335

Fixed in 5.0.21 and 5.1.10

Noted in 5.0.21, 5.1.10 changelogs.

Use of <literal>CONVERT_TZ()</literal> in a view definition
could result in spurious syntax or access errors. (Bug #15153)