MySQL Bugs: #9979: Use of CONVERT_TZ in multiple-table UPDATE causes bogus privilege error

Bug #9979	Use of CONVERT_TZ in multiple-table UPDATE causes bogus privilege error
Submitted:	18 Apr 2005 17:58	Modified:	21 Jul 2005 4:43
Reporter:	Dylan Weed	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.10/4.1.12.BK	OS:	Linux (SuSe 9.2 on AMD64)
Assigned to:	Jim Winstead	CPU Architecture:	Any

Description:
When the CONVERT_TZ() function is used as part of a multiple-table update, the update fails with an error similar to this:

# ERROR 1143 (42000): UPDATE command denied 
# to user 'agg'@'localhost' for column 'START_TIME' in table 'MEALS'

If the same multiple-table update is run without including the CONVERT_TZ() function, it completes successfully, even though the same tables are present in the query.  Are the timezone tables required to have certain permissions set if they are implicitly included in a multi-table update?

How to repeat:
DROP TABLE IF EXISTS MEALS;
CREATE TABLE MEALS (
  EVENT VARCHAR(40) NOT NULL,
  CHEF_ID INT UNSIGNED NOT NULL,
  START_TIME DATETIME NOT NULL
);

DROP TABLE IF EXISTS CHEFS;
CREATE TABLE CHEFS (
  CHEF_ID INT UNSIGNED NOT NULL,
  NAME VARCHAR(40)
);

INSERT INTO MEALS VALUES ('Breakfast', 1, '2005-01-01 08:00');
INSERT INTO MEALS VALUES ('Lunch', 1, '2005-01-01 12:00');
INSERT INTO MEALS VALUES ('Dinner', 2, '2005-01-01 18:00');

INSERT INTO CHEFS VALUES (1, 'Joe');
INSERT INTO CHEFS VALUES (2, 'Bob');

# Updating a datetime column works
UPDATE MEALS M SET M.START_TIME = '2005-01-01 10:00';

# Updating a datetime to a timezone conversion works also
UPDATE MEALS M SET M.START_TIME = CONVERT_TZ(M.START_TIME, 'EST', 'EST');

# Updating a datetime column in a multitable update works.
UPDATE MEALS M JOIN CHEFS C ON (M.CHEF_ID = C.CHEF_ID)
SET M.START_TIME = '2005-01-01 11:00' WHERE C.NAME='Joe';

# BUG:  Updating a datetime column in a multitable update using
# a timezone conversion fails due to permissions.  (Note that this
# is virtually the same as the statement that ran successfully above.)
# ERROR 1143 (42000): UPDATE command denied 
# to user 'agg'@'localhost' for column 'START_TIME' in table 'MEALS'

UPDATE MEALS M JOIN CHEFS C ON (M.CHEF_ID = C.CHEF_ID) 
SET M.START_TIME = CONVERT_TZ('2005-01-01 11:00', 'EST', 'EST') 
WHERE C.NAME='Joe';

Verified with latest BK.

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

Approved

Fixed in 4.1.14, and will be fixed in 5.0.10.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented in 4.1.4 and 5.0.10 changelogs. Closed.