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

[22 Nov 2005 22:47] Beat Vontobel
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.
[23 Nov 2005 6:34] Jorge del Conde
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>
[19 Apr 2006 9:46] 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/5134
[20 Apr 2006 18:18] Konstantin Osipov
Approved by email.
[22 Apr 2006 7:56] 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/5335
[25 Apr 2006 21:37] Dmitry Lenev
Fixed in 5.0.21 and 5.1.10
[2 May 2006 1:24] Paul Dubois
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)