|
This script returns from results:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DATE) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23');
|
SELECT a, FROM_UNIXTIME(CONCAT(a,'10')) AS f1, FROM_UNIXTIME(CONCAT(a,'10'))+0 AS f2 FROM t1;
|
SELECT * FROM t1 GROUP BY FROM_UNIXTIME(CONCAT(a,'10'))+0;
|
The first SELECT returns:
+------------+---------------------+------------------------------------------------+
|
| a | f1 | f2 |
|
+------------+---------------------+------------------------------------------------+
|
| 2005-05-04 | 1970-01-01 03:33:25 | 19700101033325.0000000000000000000000000000000 |
|
| 2000-02-23 | 1970-01-01 03:33:20 | 19700101033320.0000000000000000000000000000000 |
|
+------------+---------------------+------------------------------------------------+
|
Notice, the number of fractional digits in "f2" looks excessive.
The second SELECT returns:
+------------+
|
| a |
|
+------------+
|
| 2005-05-04 |
|
+------------+
|
This is wrong, because the two values in "f2" in the previous SELECT
are unique. The expected result in this SELECT should return two rows.
More similar failures:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DATE) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23');
|
SELECT * FROM t1 GROUP BY FROM_UNIXTIME(concat(a,'10'))/1;
|
A related problem:
DROP TABLE IF EXISTS t1,t2;
|
CREATE TABLE t1 (a DATE);
|
INSERT INTO t1 VALUES ('2005-05-04');
|
SELECT CHAR_LENGTH(CONCAT(FROM_UNIXTIME(CONCAT(a,'10')) MOD FROM_UNIXTIME(CONCAT(a,'10')))) AS f2 FROM t1;
|
CREATE TABLE t2 AS SELECT CONCAT(FROM_UNIXTIME(CONCAT(a,'10')) MOD FROM_UNIXTIME(CONCAT(a,'10'))) AS f2 FROM t1;
|
SHOW WARNINGS;
|
SHOW COLUMNS IN t2;
|
returns warnings:
+---------+------+-----------------------------------------+
|
| Level | Code | Message |
|
+---------+------+-----------------------------------------+
|
| Warning | 1265 | Data truncated for column 'f2' at row 1 |
|
+---------+------+-----------------------------------------+
|
which happens because the column is too short (26),
while CHAR_LENGTH() in the above query returns 27:
+-------+---------------+------+-----+---------+-------+
|
| Field | Type | Null | Key | Default | Extra |
|
+-------+---------------+------+-----+---------+-------+
|
| f2 | varbinary(26) | YES | | NULL | |
|
+-------+---------------+------+-----+---------+-------+
|
|