I stumbled across this while trying to update a table with a timestamp type column. At the time, I didn't know that the timstamp column would update itself when a row was changed. A kind gentleman in another group pointed that out to me.
I added a column to a table and wanted to update the rows to populate the new column. When doing something like this I usually will create a temporary table and perform a dry run, just to make sure I am producing the results I want. In this case, it did exactly what I wanted to the temporary table, but gave different results to the real table.
It appears that the timestamp field does not automatically update in a temporary table, but does in the real table. Have I found a MySQL bug, or does it behave this way by design?
JM
----------------------------------------------------------------
(MySQL server version 3.23.55-Max)
mysql> create temporary table jim select * from MonthEnd;
Query OK, 39 rows affected (0.00 sec)
Records: 39 Duplicates: 0 Warnings: 0
mysql> describe jim;
+-----------+--------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+------------+-------+
| YYMM | timestamp(4) | YES | | NULL | |
| BeginDate | date | | | 0000-00-00 | |
| EndDate | date | | | 0000-00-00 | |
| Weeks | decimal(1,0) | YES | | NULL | |
+-----------+--------------+------+-----+------------+-------+
4 rows in set (0.00 sec)
mysql> select * from jim;
+------+------------+------------+-------+
| YYMM | BeginDate | EndDate | Weeks |
+------+------------+------------+-------+
| 0110 | 2001-09-30 | 2001-10-27 | NULL |
| 0111 | 2001-10-28 | 2001-12-01 | NULL |
| 0112 | 2001-12-02 | 2001-12-28 | NULL |
| 0201 | 2001-12-29 | 2002-01-26 | NULL |
| 0202 | 2002-01-27 | 2002-02-23 | NULL |
| 0203 | 2002-02-24 | 2002-03-30 | NULL |
| 0204 | 2002-03-31 | 2002-04-27 | NULL |
| 0205 | 2002-04-28 | 2002-05-25 | NULL |
| 0206 | 2002-05-26 | 2002-06-29 | NULL |
| 0207 | 2002-06-30 | 2002-08-03 | NULL |
| 0208 | 2002-08-04 | 2002-08-31 | NULL |
| 0209 | 2002-09-01 | 2002-10-05 | NULL |
| 0210 | 2002-10-06 | 2002-11-02 | NULL |
| 0211 | 2002-11-03 | 2002-11-30 | NULL |
| 0212 | 2002-12-01 | 2003-01-03 | NULL |
| 0301 | 2003-01-04 | 2003-02-01 | NULL |
| 0302 | 2003-02-02 | 2003-03-01 | NULL |
| 0303 | 2003-03-02 | 2003-04-05 | NULL |
| 0304 | 2003-04-06 | 2003-05-03 | NULL |
| 0305 | 2003-05-04 | 2003-05-31 | NULL |
| 0306 | 2003-06-01 | 2003-07-05 | NULL |
| 0307 | 2003-07-06 | 2003-08-02 | NULL |
| 0308 | 2003-08-03 | 2003-08-30 | NULL |
| 0309 | 2003-08-31 | 2003-10-04 | NULL |
| 0310 | 2003-10-05 | 2003-11-01 | NULL |
| 0311 | 2003-11-02 | 2003-11-29 | NULL |
| 0312 | 2003-11-30 | 2004-01-02 | NULL |
| 0401 | 2004-01-03 | 2004-01-31 | 4 |
| 0402 | 2004-02-01 | 2004-02-28 | 4 |
| 0403 | 2004-02-29 | 2004-04-03 | 5 |
| 0404 | 2004-04-04 | 2004-05-01 | 4 |
| 0405 | 2004-05-02 | 2004-05-29 | 4 |
| 0406 | 2004-05-30 | 2004-07-03 | 5 |
| 0407 | 2004-07-04 | 2004-07-31 | 4 |
| 0408 | 2004-08-01 | 2004-08-28 | 4 |
| 0409 | 2004-08-29 | 2004-10-02 | 5 |
| 0410 | 2004-10-03 | 2004-10-30 | 4 |
| 0411 | 2004-10-31 | 2004-11-27 | 4 |
| 0412 | 2004-11-28 | 2004-12-31 | 5 |
+------+------------+------------+-------+
39 rows in set (0.01 sec)
mysql> update jim set Weeks = round((TO_DAYS(EndDate)-TO_DAYS(BeginDate))/7);
Query OK, 27 rows affected (0.00 sec)
Rows matched: 39 Changed: 27 Warnings: 0
mysql> select * from jim;
+------+------------+------------+-------+
| YYMM | BeginDate | EndDate | Weeks |
+------+------------+------------+-------+
| 0110 | 2001-09-30 | 2001-10-27 | 4 |
| 0111 | 2001-10-28 | 2001-12-01 | 5 |
| 0112 | 2001-12-02 | 2001-12-28 | 4 |
| 0201 | 2001-12-29 | 2002-01-26 | 4 |
| 0202 | 2002-01-27 | 2002-02-23 | 4 |
| 0203 | 2002-02-24 | 2002-03-30 | 5 |
| 0204 | 2002-03-31 | 2002-04-27 | 4 |
| 0205 | 2002-04-28 | 2002-05-25 | 4 |
| 0206 | 2002-05-26 | 2002-06-29 | 5 |
| 0207 | 2002-06-30 | 2002-08-03 | 5 |
| 0208 | 2002-08-04 | 2002-08-31 | 4 |
| 0209 | 2002-09-01 | 2002-10-05 | 5 |
| 0210 | 2002-10-06 | 2002-11-02 | 4 |
| 0211 | 2002-11-03 | 2002-11-30 | 4 |
| 0212 | 2002-12-01 | 2003-01-03 | 5 |
| 0301 | 2003-01-04 | 2003-02-01 | 4 |
| 0302 | 2003-02-02 | 2003-03-01 | 4 |
| 0303 | 2003-03-02 | 2003-04-05 | 5 |
| 0304 | 2003-04-06 | 2003-05-03 | 4 |
| 0305 | 2003-05-04 | 2003-05-31 | 4 |
| 0306 | 2003-06-01 | 2003-07-05 | 5 |
| 0307 | 2003-07-06 | 2003-08-02 | 4 |
| 0308 | 2003-08-03 | 2003-08-30 | 4 |
| 0309 | 2003-08-31 | 2003-10-04 | 5 |
| 0310 | 2003-10-05 | 2003-11-01 | 4 |
| 0311 | 2003-11-02 | 2003-11-29 | 4 |
| 0312 | 2003-11-30 | 2004-01-02 | 5 |
| 0401 | 2004-01-03 | 2004-01-31 | 4 |
| 0402 | 2004-02-01 | 2004-02-28 | 4 |
| 0403 | 2004-02-29 | 2004-04-03 | 5 |
| 0404 | 2004-04-04 | 2004-05-01 | 4 |
| 0405 | 2004-05-02 | 2004-05-29 | 4 |
| 0406 | 2004-05-30 | 2004-07-03 | 5 |
| 0407 | 2004-07-04 | 2004-07-31 | 4 |
| 0408 | 2004-08-01 | 2004-08-28 | 4 |
| 0409 | 2004-08-29 | 2004-10-02 | 5 |
| 0410 | 2004-10-03 | 2004-10-30 | 4 |
| 0411 | 2004-10-31 | 2004-11-27 | 4 |
| 0412 | 2004-11-28 | 2004-12-31 | 5 |
+------+------------+------------+-------+
39 rows in set (0.00 sec) (* Notice the YYMM column didn't automatically update *)
mysql> update MonthEnd set Weeks = round((TO_DAYS(EndDate)-TO_DAYS(BeginDate))/7);
Query OK, 27 rows affected (0.00 sec)
Rows matched: 39 Changed: 27 Warnings: 0
mysql> select * from MonthEnd;
+------+------------+------------+-------+
| YYMM | BeginDate | EndDate | Weeks |
+------+------------+------------+-------+
| 0310 | 2003-11-02 | 2003-11-29 | 4 |
| 0310 | 2003-10-05 | 2003-11-01 | 4 |
| 0310 | 2003-08-31 | 2003-10-04 | 5 |
| 0310 | 2003-08-03 | 2003-08-30 | 4 |
| 0310 | 2003-07-06 | 2003-08-02 | 4 |
| 0310 | 2003-06-01 | 2003-07-05 | 5 |
| 0310 | 2003-05-04 | 2003-05-31 | 4 |
| 0310 | 2003-04-06 | 2003-05-03 | 4 |
| 0310 | 2003-03-02 | 2003-04-05 | 5 |
| 0310 | 2003-02-02 | 2003-03-01 | 4 |
| 0310 | 2003-01-04 | 2003-02-01 | 4 |
| 0310 | 2002-12-01 | 2003-01-03 | 5 |
| 0310 | 2002-11-03 | 2002-11-30 | 4 |
| 0310 | 2002-10-06 | 2002-11-02 | 4 |
| 0310 | 2002-09-01 | 2002-10-05 | 5 |
| 0310 | 2002-08-04 | 2002-08-31 | 4 |
| 0310 | 2002-06-30 | 2002-08-03 | 5 |
| 0310 | 2002-05-26 | 2002-06-29 | 5 |
| 0310 | 2002-04-28 | 2002-05-25 | 4 |
| 0310 | 2002-03-31 | 2002-04-27 | 4 |
| 0310 | 2002-02-24 | 2002-03-30 | 5 |
| 0310 | 2002-01-27 | 2002-02-23 | 4 |
| 0310 | 2001-12-29 | 2002-01-26 | 4 |
| 0310 | 2001-12-02 | 2001-12-28 | 4 |
| 0310 | 2001-10-28 | 2001-12-01 | 5 |
| 0310 | 2001-09-30 | 2001-10-27 | 4 |
| 0310 | 2003-11-30 | 2004-01-02 | 5 |
| 0401 | 2004-01-03 | 2004-01-31 | 4 |
| 0402 | 2004-02-01 | 2004-02-28 | 4 |
| 0403 | 2004-02-29 | 2004-04-03 | 5 |
| 0404 | 2004-04-04 | 2004-05-01 | 4 |
| 0405 | 2004-05-02 | 2004-05-29 | 4 |
| 0406 | 2004-05-30 | 2004-07-03 | 5 |
| 0407 | 2004-07-04 | 2004-07-31 | 4 |
| 0408 | 2004-08-01 | 2004-08-28 | 4 |
| 0409 | 2004-08-29 | 2004-10-02 | 5 |
| 0410 | 2004-10-03 | 2004-10-30 | 4 |
| 0411 | 2004-10-31 | 2004-11-27 | 4 |
| 0412 | 2004-11-28 | 2004-12-31 | 5 |
+------+------------+------------+-------+
39 rows in set (0.00 sec)
mysql>
