Bug #8795 CONVERT_TZ returns results from previous calls on occasion

• Submitted: 24 Feb 2005 22:09
• Modified: 21 Jul 2005 11:26
• Reporter: Chad Clark
• Status: Can't repeat
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.9
• OS: Linux (Slackware 10.1)
• CPU Architecture: Any

[24 Feb 2005 22:09] Chad Clark

Description:
I have not been able to reduce this to a simple reproducable test case (I have tied) but wish to report the problem regardless because queries are returning incorrect data.  I am supposed to have the project done today but during a final once-over I found a weird bug and traced it to some query results.  If I find more time I will try to generate a small reproducable proceedure.  In the mean time I have to work around this problem.

The problem looks like what would happen if a global variable were being re-used by a function that does not clear it every time the function is called.  What I mean is to me it looks like CREATE_TZ might expect it's temporary return variable to changed each time the function is called but instead only returns the previously returned value when the input time is NULL.  Unfortunatly this does not happen all of the time.

Here are the queries that are being run.  They are large and I have not included all of the table information but I hope they give someone some idea of where the problem is.

The first query shows that when one row is selected then convert_tz() returns NULL (as I expect).

The second query shows that when multple rows are selected then convert_tz() returns something completely unexpected.

The third query shows that if the ORDER BY is removed then NULL is returned (as expected).

The fourth query shows that if the INNER JOIN is removed then NULL is returned (as expected).

Thanks,
Chad

mysql> SELECT  PurchasedGreetings.ID, PurchasedGreetings.SendChrono , CONVERT_TZ(PurchasedGreetings.SendChrono, 'Canada/Mountain', PurchasedGreetings.DisplayTZ) AS SendChrono_2  FROM PurchasedGreetings  INNER JOIN Greetings ON PurchasedGreetings.GreetingID = Greetings.ID  WHERE UserID = '19'  AND SendRequested = 'No'  AND SentToDBS = 'No' AND PurchasedGreetings.ID = 170 ORDER BY PurchasedGreetings.ID ASC ;
+-----+---------------------+--------------+
| ID  | SendChrono          | SendChrono_2 |
+-----+---------------------+--------------+
| 170 | 0000-00-00 00:00:00 | NULL         |
+-----+---------------------+--------------+
1 row in set (0.00 sec)

mysql> SELECT  PurchasedGreetings.ID, PurchasedGreetings.SendChrono , CONVERT_TZ(PurchasedGreetings.SendChrono, 'Canada/Mountain', PurchasedGreetings.DisplayTZ) AS SendChrono_2  FROM PurchasedGreetings  INNER JOIN Greetings ON PurchasedGreetings.GreetingID = Greetings.ID  WHERE UserID = '19'  AND SendRequested = 'No'  AND SentToDBS = 'No' ORDER BY PurchasedGreetings.ID ASC ;
+-----+---------------------+---------------------+
| ID  | SendChrono          | SendChrono_2        |
+-----+---------------------+---------------------+
| 134 | 2005-02-02 14:45:00 | 2005-02-02 14:45:00 |
| 170 | 0000-00-00 00:00:00 | 2005-02-02 14:45:00 |
| 178 | 2005-02-09 18:00:00 | 2005-02-09 18:00:00 |
| 180 | 0000-00-00 00:00:00 | 2005-02-09 18:00:00 |
| 183 | 0000-00-00 00:00:00 | 2005-02-25 18:00:00 |
| 185 | 0000-00-00 00:00:00 | 2005-02-25 18:00:00 |
| 186 | 0000-00-00 00:00:00 | 2005-02-09 18:00:00 |
| 204 | 2005-02-21 03:00:00 | 2005-02-21 03:00:00 |
| 213 | 0000-00-00 00:00:00 | 2005-02-09 18:00:00 |
| 214 | 2005-02-16 18:00:00 | 2005-02-16 18:00:00 |
| 217 | 0000-00-00 00:00:00 | 2005-02-09 18:00:00 |
| 219 | 2005-02-25 15:00:00 | 2005-02-25 18:00:00 |
+-----+---------------------+---------------------+
12 rows in set (0.00 sec)

mysql> SELECT  PurchasedGreetings.ID, PurchasedGreetings.SendChrono , CONVERT_TZ(PurchasedGreetings.SendChrono, 'Canada/Mountain', PurchasedGreetings.DisplayTZ) AS SendChrono_2  FROM PurchasedGreetings  INNER JOIN Greetings ON PurchasedGreetings.GreetingID = Greetings.ID  WHERE UserID = '19'  AND SendRequested = 'No'  AND SentToDBS = 'No' ;
+-----+---------------------+---------------------+
| ID  | SendChrono          | SendChrono_2        |
+-----+---------------------+---------------------+
| 134 | 2005-02-02 14:45:00 | 2005-02-02 14:45:00 |
| 170 | 0000-00-00 00:00:00 | NULL                |
| 178 | 2005-02-09 18:00:00 | 2005-02-09 18:00:00 |
| 180 | 0000-00-00 00:00:00 | NULL                |
| 186 | 0000-00-00 00:00:00 | NULL                |
| 213 | 0000-00-00 00:00:00 | NULL                |
| 217 | 0000-00-00 00:00:00 | NULL                |
| 219 | 2005-02-25 15:00:00 | 2005-02-25 18:00:00 |
| 183 | 0000-00-00 00:00:00 | NULL                |
| 185 | 0000-00-00 00:00:00 | NULL                |
| 204 | 2005-02-21 03:00:00 | 2005-02-21 03:00:00 |
| 214 | 2005-02-16 18:00:00 | 2005-02-16 18:00:00 |
+-----+---------------------+---------------------+
12 rows in set (0.00 sec)

mysql> SELECT  PurchasedGreetings.ID, PurchasedGreetings.SendChrono , CONVERT_TZ(PurchasedGreetings.SendChrono, 'Canada/Mountain', PurchasedGreetings.DisplayTZ) AS SendChrono_2  FROM PurchasedGreetings  WHERE UserID = '19'  AND SendRequested = 'No'  AND SentToDBS = 'No' ORDER BY PurchasedGreetings.ID ASC ;
+-----+---------------------+---------------------+
| ID  | SendChrono          | SendChrono_2        |
+-----+---------------------+---------------------+
|  11 | 2004-12-25 18:00:00 | 2004-12-25 18:00:00 |
|  21 | 0000-00-00 00:00:00 | NULL                |
|  23 | 2005-01-13 18:00:00 | 2005-01-13 18:00:00 |
|  26 | 0000-00-00 00:00:00 | NULL                |
|  27 | 0000-00-00 00:00:00 | NULL                |
|  29 | 2005-01-20 18:45:00 | 2005-01-20 18:45:00 |
|  31 | 0000-00-00 00:00:00 | NULL                |

   ... several rows removed ...

| 165 | 0000-00-00 00:00:00 | NULL                |
| 170 | 0000-00-00 00:00:00 | NULL                |
| 171 | 0000-00-00 00:00:00 | NULL                |

   ... several rows removed ...

How to repeat:
Not included (yet).

Suggested fix:
Unknown.

[24 Feb 2005 22:26] Chad Clark

It looks like a work around exists thanks to the IF function.

/Chad

mysql> SELECT
  PurchasedGreetings.ID,
  PurchasedGreetings.SendChrono ,
  IF(SendChrono , CONVERT_TZ(PurchasedGreetings.SendChrono, 'Canada/Mountain', PurchasedGreetings.DisplayTZ) , NULL ) AS SendChrono_2
FROM PurchasedGreetings
INNER JOIN Greetings ON PurchasedGreetings.GreetingID = Greetings.ID
WHERE UserID = '19'
AND SendRequested = 'No'
AND SentToDBS = 'No'
ORDER BY PurchasedGreetings.ID ASC ;
+-----+---------------------+---------------------+
| ID  | SendChrono          | SendChrono_2        |
+-----+---------------------+---------------------+
| 134 | 2005-02-02 14:45:00 | 2005-02-02 14:45:00 |
| 170 | 0000-00-00 00:00:00 | NULL                |
| 178 | 2005-02-09 18:00:00 | 2005-02-09 18:00:00 |
| 180 | 0000-00-00 00:00:00 | NULL                |
| 183 | 0000-00-00 00:00:00 | NULL                |
| 185 | 0000-00-00 00:00:00 | NULL                |
| 186 | 0000-00-00 00:00:00 | NULL                |
| 204 | 2005-02-21 03:00:00 | 2005-02-21 03:00:00 |
| 213 | 0000-00-00 00:00:00 | NULL                |
| 214 | 2005-02-16 18:00:00 | 2005-02-16 18:00:00 |
| 217 | 0000-00-00 00:00:00 | NULL                |
| 219 | 2005-02-25 15:00:00 | 2005-02-25 18:00:00 |
+-----+---------------------+---------------------+
12 rows in set (0.00 sec)

[26 Feb 2005 18:27] Aleksey Kishkin

Hi could you please attach your tables description (output of 'show create table' command for your tables)? And (if possible) what the values are in the PurchasedGreetings.DisplayTZ column.

[28 Feb 2005 17:02] Chad Clark

I don't know what changed over the weekend but now the query seems to be returning the NULL results like I expected.  I do have a mysqldump from Friday but it will have to wait before I could look at trying the data from it and I'm not 100% sure it contains the data that breaks the queries.  Also the create table output is below.

If you would like a copy of the old mysqldump I can arange it.  The live database will probably be changed quite a bit today but I will make a dump right away before the changes get made.

/Chad

mysql> SELECT  PurchasedGreetings.ID, PurchasedGreetings.SendChrono , CONVERT_TZ(PurchasedGreetings.SendChrono, 'Canada/Mountain', PurchasedGreetings.DisplayTZ) AS SendChrono_2 , PurchasedGreetings.DisplayTZ FROM PurchasedGreetings  INNER JOIN Greetings ON PurchasedGreetings.GreetingID = Greetings.ID  WHERE UserID = '19'  AND SendRequested = 'No'  AND SentToDBS = 'No' ORDER BY PurchasedGreetings.ID ASC;
+-----+---------------------+---------------------+-----------------+
| ID  | SendChrono          | SendChrono_2        | DisplayTZ       |
+-----+---------------------+---------------------+-----------------+
| 134 | 2005-02-02 14:45:00 | 2005-02-02 14:45:00 | Canada/Mountain |
| 178 | 2005-02-09 18:00:00 | 2005-02-09 18:00:00 | Canada/Mountain |
| 180 | 0000-00-00 00:00:00 | NULL                | Canada/Mountain |
| 183 | 0000-00-00 00:00:00 | NULL                | Canada/Mountain |
| 185 | 0000-00-00 00:00:00 | NULL                | Canada/Mountain |
| 186 | 0000-00-00 00:00:00 | NULL                |                 |
| 204 | 2005-02-21 03:00:00 | 2005-02-21 03:00:00 | Canada/Mountain |
| 213 | 0000-00-00 00:00:00 | NULL                |                 |
| 214 | 2005-02-16 18:00:00 | 2005-02-16 18:00:00 | Canada/Mountain |
| 217 | 0000-00-00 00:00:00 | NULL                | Canada/Mountain |
| 219 | 2005-02-25 15:00:00 | 2005-02-25 18:00:00 | Canada/Atlantic |
| 223 | 0000-00-00 00:00:00 | NULL                |                 |
+-----+---------------------+---------------------+-----------------+
12 rows in set (0.00 sec)

mysql> SELECT  PurchasedGreetings.ID, PurchasedGreetings.SendChrono , CONVERT_TZ(PurchasedGreetings.SendChrono, 'Canada/Mountain', PurchasedGreetings.DisplayTZ) AS SendChrono_2  FROM PurchasedGreetings  INNER JOIN Greetings ON PurchasedGreetings.GreetingID = Greetings.ID  WHERE UserID = '19'  AND SendRequested = 'No'  AND SentToDBS = 'No' ORDER BY PurchasedGreetings.ID ASC;
+-----+---------------------+---------------------+
| ID  | SendChrono          | SendChrono_2        |
+-----+---------------------+---------------------+
| 134 | 2005-02-02 14:45:00 | 2005-02-02 14:45:00 |
| 178 | 2005-02-09 18:00:00 | 2005-02-09 18:00:00 |
| 180 | 0000-00-00 00:00:00 | NULL                |
| 183 | 0000-00-00 00:00:00 | NULL                |
| 185 | 0000-00-00 00:00:00 | NULL                |
| 186 | 0000-00-00 00:00:00 | NULL                |
| 204 | 2005-02-21 03:00:00 | 2005-02-21 03:00:00 |
| 213 | 0000-00-00 00:00:00 | NULL                |
| 214 | 2005-02-16 18:00:00 | 2005-02-16 18:00:00 |
| 217 | 0000-00-00 00:00:00 | NULL                |
| 219 | 2005-02-25 15:00:00 | 2005-02-25 18:00:00 |
| 223 | 0000-00-00 00:00:00 | NULL                |
+-----+---------------------+---------------------+
12 rows in set (0.01 sec)

mysql>

mysql> show create table PurchasedGreetings \G
*************************** 1. row ***************************
       Table: PurchasedGreetings
Create Table: CREATE TABLE `PurchasedGreetings` (
  `ID` bigint(20) unsigned NOT NULL auto_increment,
  `UserID` bigint(20) unsigned NOT NULL default '0',
  `GreetingID` bigint(20) unsigned NOT NULL default '0',
  `SenderName` varchar(31) NOT NULL default '',
  `SendChrono` datetime NOT NULL default '0000-00-00 00:00:00',
  `SendRequested` enum('Yes','No') NOT NULL default 'No',
  `SentToDBS` enum('Yes','No') NOT NULL default 'No',
  `WaveDbs1` varchar(4) NOT NULL default '',
  `WaveDbs2` varchar(4) NOT NULL default '',
  `Variable1` varchar(31) NOT NULL default '',
  `Variable2` varchar(31) NOT NULL default '',
  `Charged` int(10) unsigned NOT NULL default '0',
  `DisplayTZ` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`ID`),
  KEY `UserID` (`UserID`,`GreetingID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> show create table Greetings \G
*************************** 1. row ***************************
       Table: Greetings
Create Table: CREATE TABLE `Greetings` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `CategoryID` int(10) unsigned NOT NULL default '0',
  `ExclusiveUserID` int(10) unsigned NOT NULL default '0',
  `CreditCost` int(10) unsigned NOT NULL default '1',
  `Name` varchar(125) NOT NULL default '',
  `Description` text NOT NULL,
  `DbsMode` char(2) NOT NULL default '',
  `MessageDbsCode` varchar(4) NOT NULL default '',
  `Variable1Name` varchar(50) NOT NULL default 'Sender',
  `Variable2Name` varchar(50) NOT NULL default 'Recipient',
  `Status` enum('Active','Inactive') NOT NULL default 'Inactive',
  PRIMARY KEY  (`ID`),
  KEY `CategoryID` (`CategoryID`),
  KEY `Status` (`Status`),
  KEY `CategoryID_2` (`CategoryID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>

[28 Mar 2005 23: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".

[29 Mar 2005 8:53] Sergei Golubchik

reopened

[5 Apr 2005 23:01] MySQL Verification Team

Could you please provide a dump file of the tables.
You can upload it at:

ftp://ftp.mysql.com/pub/mysql/upload/

using a file name which identifies this bug report.

Thanks in advance.

[6 May 2005 23: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".