Bug #1512 Timestamp not updated in temporary tables
Submitted: 9 Oct 2003 13:09 Modified: 16 Oct 2003 22:07
Reporter: [ name withheld ] Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:all OS:Linux (linux)
Assigned to: CPU Architecture:Any

[9 Oct 2003 13:09] [ name withheld ] 
Description:
Timestamp values are not updated when updating temporary tables.

How to repeat:
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 YYMM values did not change even though the records were updated.  I didn;t really WANT them to change, but they should have, and they did when I updated the actual MonthEnd table.
[14 Oct 2003 6:23] Alexander Keremidarski 
Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Please tell us what MySQL version you use.

I wasn't able to repeat this bug with 4.0.15
[15 Oct 2003 6:01] [ name withheld ] 
This bug is repeatable on my systems (v3.23.58 and v3.23.55-Max), and has been repeated on others (in the alt.php.sql newsgroup) systems as well.  I believe it has something to do with the create...select process.  Run this script to recreate:

create database bug_test;
use bug_test;
create table real_table(ts timestamp(4), i integer);
insert into real_table values('2001-01-01',100);
insert into real_table values('2001-02-02',200);
insert into real_table values('2001-03-03',300);
select * from real_table;
create temporary table temp_table select * from real_table;
update temp_table set i = i * 2;
select * from temp_table;
[16 Oct 2003 22:07] Alexander Keremidarski 
Thanks.
I was able to get same result under 3.23 with your queries.

Relevant part:

mysql> select * from temp_table;
+------+------+
| ts   | i    |
+------+------+
| 0101 |  200 |
| 0102 |  400 |
| 0103 |  600 |
+------+------+
3 rows in set (0.00 sec)
 
mysql> update temp_table set i = i * 2;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0
 
mysql> select * from temp_table;
+------+------+
| ts   | i    |
+------+------+
| 0101 |  400 |
| 0102 |  800 |
| 0103 | 1200 |
+------+------+

Howevere this 3.23 only behaviour.
Under 4.0 result is:
mysql> select * from real_table;
+------+------+
| ts   | i    |
+------+------+
| 0101 |  100 |
| 0102 |  200 |
| 0103 |  300 |
+------+------+
3 rows in set (0.00 sec)
 
mysql> update temp_table set i = i * 2;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0
 
mysql> select * from real_table;
+------+------+
| ts   | i    |
+------+------+
| 0101 |  100 |
| 0102 |  200 |
| 0103 |  300 |
+------+------+
3 rows in set (0.00 sec)

As 3.23 is in phasing out stage we fix only critical bugs there like server crashes or security related. 

Consider upgrading to 4.0