MySQL Bugs: #41584: Inconsistent behaviour when comparing system variable "timestamp".

Bug #41584	Inconsistent behaviour when comparing system variable "timestamp".
Submitted:	18 Dec 2008 10:20	Modified:	18 Dec 2008 16:12
Reporter:	Horst Hunger	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S3 (Non-critical)
Version:	Ver 5.1.31-debug	OS:	Linux (suse-linux on x86_64)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	disabled

Description:
On a 64 bit platform the following statement

"SELECT @@timestamp = VARIABLE_VALUE
FROM INFORMATION_SCHEMA.SESSION_VARIABLES
WHERE VARIABLE_NAME='timestamp';"

delivers true and false depending on the value of timestamp. Especially if it is negative or has more than 19 digits. 

How to repeat:
execute the attached test.

Thank you for the bug report. Verified as described on Ubuntu 8.10 64-bit and Windows XP 32-bit:

miguel@hegel:~/dbs$ 5.1/bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.31-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SET @@timestamp = -1000000000;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@timestamp;
+----------------------+
| @@timestamp          |
+----------------------+
| 18446744072709551616 | 
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT VARIABLE_VALUE
    -> FROM INFORMATION_SCHEMA.SESSION_VARIABLES
    -> WHERE VARIABLE_NAME='timestamp';
+----------------------+
| VARIABLE_VALUE       |
+----------------------+
| 18446744072709551616 | 
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT @@timestamp = VARIABLE_VALUE
    -> FROM INFORMATION_SCHEMA.SESSION_VARIABLES
    -> WHERE VARIABLE_NAME='timestamp';
+------------------------------+
| @@timestamp = VARIABLE_VALUE |
+------------------------------+
|                            0 | 
+------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> SET @@timestamp = 511000000000;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@timestamp;
+--------------+
| @@timestamp  |
+--------------+
| 511000000000 | 
+--------------+
1 row in set (0.00 sec)

mysql> SELECT VARIABLE_VALUE
    -> FROM INFORMATION_SCHEMA.SESSION_VARIABLES
    -> WHERE VARIABLE_NAME='timestamp';
+----------------+
| VARIABLE_VALUE |
+----------------+
| 511000000000   | 
+----------------+
1 row in set (0.01 sec)

mysql> SELECT @@timestamp = VARIABLE_VALUE
    -> FROM INFORMATION_SCHEMA.SESSION_VARIABLES
    -> WHERE VARIABLE_NAME='timestamp';
+------------------------------+
| @@timestamp = VARIABLE_VALUE |
+------------------------------+
|                            1 | 
+------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> SET @@timestamp = -1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@timestamp;
+----------------------+
| @@timestamp          |
+----------------------+
| 18446744073709551615 | 
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT VARIABLE_VALUE
    -> FROM INFORMATION_SCHEMA.SESSION_VARIABLES
    -> WHERE VARIABLE_NAME='timestamp';
+----------------------+
| VARIABLE_VALUE       |
+----------------------+
| 18446744073709551615 | 
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT @@timestamp = VARIABLE_VALUE
    -> FROM INFORMATION_SCHEMA.SESSION_VARIABLES
    -> WHERE VARIABLE_NAME='timestamp';
+------------------------------+
| @@timestamp = VARIABLE_VALUE |
+------------------------------+
|                            0 | 
+------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> SET @@timestamp = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@timestamp;
+-------------+
| @@timestamp |
+-------------+
|  1229615852 | 
+-------------+
1 row in set (0.00 sec)

mysql> SELECT VARIABLE_VALUE
    -> FROM INFORMATION_SCHEMA.SESSION_VARIABLES
    -> WHERE VARIABLE_NAME='timestamp';
+----------------+
| VARIABLE_VALUE |
+----------------+
| 1229615857     | 
+----------------+
1 row in set (0.00 sec)

mysql> SELECT @@timestamp = VARIABLE_VALUE
    -> FROM INFORMATION_SCHEMA.SESSION_VARIABLES
    -> WHERE VARIABLE_NAME='timestamp';
+------------------------------+
| @@timestamp = VARIABLE_VALUE |
+------------------------------+
|                            1 | 
+------------------------------+
1 row in set (0.00 sec)

mysql> 

c:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.31-nt-debug-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql 5.1 >SET @@timestamp = -1000000000;
Query OK, 0 rows affected (0.01 sec)

mysql 5.1 >SELECT @@timestamp;
+-------------+
| @@timestamp |
+-------------+
|  3294967296 |
+-------------+
1 row in set (0.02 sec)

mysql 5.1 >SELECT VARIABLE_VALUE
    -> FROM INFORMATION_SCHEMA.SESSION_VARIABLES
    -> WHERE VARIABLE_NAME='timestamp';
+----------------+
| VARIABLE_VALUE |
+----------------+
| 3294967296     |
+----------------+
1 row in set (0.09 sec)

mysql 5.1 >
mysql 5.1 >SELECT @@timestamp = VARIABLE_VALUE
    -> FROM INFORMATION_SCHEMA.SESSION_VARIABLES
    -> WHERE VARIABLE_NAME='timestamp';
+------------------------------+
| @@timestamp = VARIABLE_VALUE |
+------------------------------+
|                            1 |
+------------------------------+
1 row in set (0.01 sec)

mysql 5.1 >
mysql 5.1 >SET @@timestamp = 511000000000;
Query OK, 0 rows affected (0.00 sec)

mysql 5.1 >SELECT @@timestamp;
+-------------+
| @@timestamp |
+-------------+
|  4193859072 |
+-------------+
1 row in set (0.00 sec)

mysql 5.1 >SELECT VARIABLE_VALUE
    -> FROM INFORMATION_SCHEMA.SESSION_VARIABLES
    -> WHERE VARIABLE_NAME='timestamp';
+----------------+
| VARIABLE_VALUE |
+----------------+
| 4193859072     |
+----------------+
1 row in set (0.00 sec)

mysql 5.1 >
mysql 5.1 >SELECT @@timestamp = VARIABLE_VALUE
    -> FROM INFORMATION_SCHEMA.SESSION_VARIABLES
    -> WHERE VARIABLE_NAME='timestamp';
+------------------------------+
| @@timestamp = VARIABLE_VALUE |
+------------------------------+
|                            1 |
+------------------------------+
1 row in set (0.00 sec)

mysql 5.1 >
mysql 5.1 >SET @@timestamp = -1;
Query OK, 0 rows affected (0.00 sec)

mysql 5.1 >SELECT @@timestamp;
+-------------+
| @@timestamp |
+-------------+
|  4294967295 |
+-------------+
1 row in set (0.00 sec)

mysql 5.1 >SELECT VARIABLE_VALUE
    -> FROM INFORMATION_SCHEMA.SESSION_VARIABLES
    -> WHERE VARIABLE_NAME='timestamp';
+----------------+
| VARIABLE_VALUE |
+----------------+
| 4294967295     |
+----------------+
1 row in set (0.02 sec)

mysql 5.1 >
mysql 5.1 >SELECT @@timestamp = VARIABLE_VALUE
    -> FROM INFORMATION_SCHEMA.SESSION_VARIABLES
    -> WHERE VARIABLE_NAME='timestamp';
+------------------------------+
| @@timestamp = VARIABLE_VALUE |
+------------------------------+
|                            1 |
+------------------------------+
1 row in set (0.00 sec)

mysql 5.1 >
mysql 5.1 >SET @@timestamp = 0;
Query OK, 0 rows affected (0.00 sec)

mysql 5.1 >SELECT @@timestamp;
+-------------+
| @@timestamp |
+-------------+
|  1229619896 |
+-------------+
1 row in set (0.00 sec)

mysql 5.1 >SELECT VARIABLE_VALUE
    -> FROM INFORMATION_SCHEMA.SESSION_VARIABLES
    -> WHERE VARIABLE_NAME='timestamp';
+----------------+
| VARIABLE_VALUE |
+----------------+
| 1229619896     |
+----------------+
1 row in set (0.00 sec)

mysql 5.1 >
mysql 5.1 >SELECT @@timestamp = VARIABLE_VALUE
    -> FROM INFORMATION_SCHEMA.SESSION_VARIABLES
    -> WHERE VARIABLE_NAME='timestamp';
+------------------------------+
| @@timestamp = VARIABLE_VALUE |
+------------------------------+
|                            1 |
+------------------------------+
1 row in set (0.00 sec)