Bug #11478 option new doesn't work as expected in 4.0
Submitted: 21 Jun 2005 11:02 Modified: 24 Jun 2005 12:32
Reporter: Victoria Reznichenko Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0 OS:Any (any)
Assigned to: Assigned Account CPU Architecture:Any

[21 Jun 2005 11:02] Victoria Reznichenko
Description:
Option --new doesn't work with timestamp columns as expected.

1. start MySQL server with --new option and create table:

mysql> create table t1(ts timestamp);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t1 values(null);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values(null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+---------------------+
| ts                  |
+---------------------+
| 2005-06-21 14:43:38 |
| 2005-06-21 14:43:39 |
+---------------------+
2 rows in set (0.00 sec)

mysql> select ts+0 from t1;
+-------------------+
| ts+0              |
+-------------------+
| 50621144338050504 |
| 50621144339050504 |
+-------------------+
2 rows in set (0.00 sec)

as you can see the latest SELECT returns wrong values (expected: '20050621144338' and '20050621144339').

2. Start MySQL server without --new option and create a table:

mysql> create table t2(ts timestamp);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t2 values(null);
Query OK, 1 row affected (0.00 sec)

mysql> select ts from t2;
+----------------+
| ts             |
+----------------+
| 20050621145009 |
+----------------+
1 row in set (0.00 sec)

mysql> set new = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t2;
+---------------------+
| ts                  |
+---------------------+
| 2005-06-21 14:50:09 |
+---------------------+
1 row in set (0.00 sec)

mysql> select ts from t2;
+----------------+
| ts             |
+----------------+
| 20050621145009 |
+----------------+
1 row in set (0.00 sec)

The latest SELECT should return timestamp value as a string.

How to repeat:
see above.
[23 Jun 2005 11:36] MySQL Verification Team
The second case is related to query cache. "SET new" doesn't flush relevant entries.

mysql> select * from t2;
+----------------+
| ts             |
+----------------+
| 20050623153446 |
+----------------+
1 row in set (0.00 sec)

mysql> set new=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t2;
+----------------+
| ts             |
+----------------+
| 20050623153446 |
+----------------+
1 row in set (0.00 sec)

mysql> reset query cache;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t2;
+---------------------+
| ts                  |
+---------------------+
| 2005-06-23 15:34:46 |
+---------------------+
1 row in set (0.00 sec)
[24 Jun 2005 12:32] MySQL Verification Team
Duplicate for #8894