| Bug #10890 | select X, GREATEST(X,0) with strange result | ||
|---|---|---|---|
| Submitted: | 26 May 2005 18:38 | Modified: | 28 May 2005 13:43 |
| Reporter: | Andrew Shirayev | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.0.24 | OS: | Linux (Debian sarge) |
| Assigned to: | CPU Architecture: | Any | |
[28 May 2005 13:43]
Hartmut Holzgraefe
I get +------------+------------+ | res1 | res2 | +------------+------------+ | 1110693600 | 1110693600 | | 1111298400 | 1111298400 | | 1111903200 | 1111903200 | | 1112508000 | 1112508000 | | 1113109200 | 1113109200 | | 1113714000 | 1113714000 | +------------+------------+ with 4.0.24 on Debian Sarge (ppc, from apt-get) and on SuSE 9.0 (x86, self compiled)
Description: select X, GREATEST(X,0) give uncorrect result with X=UNIX_TIMESTAMP(TRUNCATE(DATE_ADD(FROM_UNIXTIME(1110142800), INTERVAL NN.ID*7 DAY),-6)) and similar. How to repeat: mysql> CREATE TABLE NN ( -> ID int(11) NOT NULL auto_increment, -> PRIMARY KEY (ID) -> ) TYPE=MyISAM; Query OK, 0 rows affected (0.13 sec) mysql> insert into NN values (NULL),(NULL),(NULL),(NULL),(NULL),(NULL); Query OK, 6 rows affected (0.01 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> select -> UNIX_TIMESTAMP(TRUNCATE(DATE_ADD(FROM_UNIXTIME(1110142800), INTERVAL NN.ID*7 DAY),-6)) -> as res1, GREATEST( -> UNIX_TIMESTAMP(TRUNCATE(DATE_ADD(FROM_UNIXTIME(1110142800), INTERVAL NN.ID*7 DAY),-6)),0) as res2 from NN; +------------+------------+ | res1 | res2 | +------------+------------+ | 1110747600 | 1110747600 | | 1111352400 | 1111352400 | | 1111953600 | 1111953600 | | 0 | 1112558400 | | 0 | 1113163200 | | 0 | 1113768000 | +------------+------------+ 6 rows in set (0.00 sec)