|
The workaround for me is to add a CONVERT:
CONVERT(
GREATEST( COALESCE(`inspection`.`ts`, 0), COALESCE(`inspection_details`.`ts`, 0), COALESCE(`open_time_reason`.`ts`, 0)
), datetime
) AS `changed_at`
|
|
MariaDB [test]> select * from inspection;
|
+---------------------+
|
| ts |
|
+---------------------+
|
| 2015-08-19 00:21:47 |
|
+---------------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> select * from inspection_details;
|
+---------------------+
|
| ts |
|
+---------------------+
|
| 2015-08-19 00:22:18 |
|
+---------------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> select * from open_time_reason;
|
+---------------------+
|
| ts |
|
+---------------------+
|
| 2015-08-19 00:22:46 |
|
+---------------------+
|
1 row in set (0.00 sec)
|
MariaDB [test]> INSERT INTO t1 SELECT GREATEST( COALESCE(`inspection`.`ts`, 0), COALESCE(`inspection_details`.`ts`, 0), COALESCE(`open_time_reason`.`ts`, 0) ) AS `changed_at` FROM inspection, inspection_details, open_time_reason ON DUPLICATE KEY UPDATE `changed_at` = VALUES(`changed_at`);
|
Query OK, 1 row affected (0.03 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> select * from t1;
|
+---------------------+
|
| changed_at |
|
+---------------------+
|
| 2015-08-19 00:22:46 |
|
+---------------------+
|
1 row in set (0.00 sec)
|
MariaDB [test]> select @@version;
|
+----------------------+
|
| @@version |
|
+----------------------+
|
| 5.5.44-MariaDB-debug |
|
+----------------------+
|
1 row in set (0.00 sec)
|
Works all right, so it's not that obvious.
Please provide SHOW CREATE TABLE for all tables, data sample which shows the problem, the complete query, and your cnf files or output of SHOW VARIABLES.
|
|
Hi Elena,
Thanks for the reply. I've attached the schema dump with sample data, `show variables` results and the full INSERT query. I can duplicate the error with the above on my local MariaDB 10.0.17.
Cheers,
Ray
|
|
Thanks a lot, it was very helpful. Now I can actually reproduce it.
The problem boils down to the test case below, so ON DUPLICATE KEY and VALUES are not actually needed:
SET NAMES utf8;
|
|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (id1 int, dt datetime);
|
|
DROP TABLE IF EXISTS t2;
|
CREATE TABLE t2 (id2 int, ts timestamp);
|
|
INSERT INTO t2 VALUES (1,'2012-06-11 15:17:34'),(2,'2012-06-11 15:18:24');
|
|
INSERT INTO t1 (dt)
|
SELECT GREATEST(COALESCE(ts, 0), COALESCE(ts, 0)) AS dt
|
FROM t2 LEFT JOIN t1 ON id1 = id2
|
;
|
|
select * from t1;
|
MariaDB 5.5 and above returns
+---------------------+
|
| changed_at |
|
+---------------------+
|
| 0000-00-00 00:00:00 |
|
| 0000-00-00 00:00:00 |
|
+---------------------+
|
while of course t2.ts values are expected.
Reproducible both with InnoDB and MyISAM.
Not reproducible with NAMES latin1, which is why I am assigning it for bar for analysis. If it turns out to be unrelated to character sets and all work you do on cleaning up functions behavior, please reassign it appropriately.
|
|
No worries Elena, glad it's confirmed.
Cheers,
Ray
|
|
Another script demonstrating a related problem:
SET NAMES utf8;
|
DROP TABLE IF EXISTS t1, t2;
|
CREATE TABLE t1 (id2 int, ts timestamp);
|
INSERT INTO t1 VALUES (1,'2012-06-11 15:17:34'),(2,'2012-06-11 15:18:24');
|
CREATE TABLE t2 AS SELECT GREATEST(COALESCE(ts, 0), COALESCE(ts, 0)) AS dt FROM t1;
|
SHOW WARNINGS;
|
SHOW CREATE TABLE t2;
|
SELECT * FROM t2;
|
returns warnings after CREATE:
+---------+------+-----------------------------------------+
|
| Level | Code | Message |
|
+---------+------+-----------------------------------------+
|
| Warning | 1265 | Data truncated for column 'dt' at row 1 |
|
| Warning | 1265 | Data truncated for column 'dt' at row 2 |
|
+---------+------+-----------------------------------------+
|
The values were in fact truncated:
+---------+
|
| dt |
|
+---------+
|
| 2012-06 |
|
| 2012-06 |
|
+---------+
|
Tthe expected result is strings with full datetime values. e.g.'2012-06-11 15:17:34'.
And the created column data type is too short:
+-------+---------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+---------------------------------------------------------------------------------------------------------------------+
|
| t2 | CREATE TABLE `t2` (
|
`dt` varchar(7) CHARACTER SET utf8 NOT NULL DEFAULT ''
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+---------------------------------------------------------------------------------------------------------------------+
|
The expected data type is VARCHAR(19) rather than VARCHAR(7).
|
|
A workaround is to use TIMESTAMP(0) instead of 0:
INSERT INTO ...
SELECT ...
GREATEST( COALESCE(`inspection`.`ts`, TIMESTAMP(0)), COALESCE(`inspection_details`.`ts`, TIMESTAMP(0)), COALESCE(`open_time_reason`.`ts`, 0)
) AS `changed_at`
ON DUPLICATE KEY UPDATE ...
`changed_at` = VALUES(`changed_at`)
|
|
The same problems is repeatable with CASE:
SET NAMES utf8;
|
DROP TABLE IF EXISTS t1, t2;
|
CREATE TABLE t1 (id2 int, ts timestamp) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES (1,'2012-06-11 15:17:34'),(2,'2012-06-11 15:18:24');
|
DROP TABLE IF EXISTS t2;
|
CREATE TABLE t2 ENGINE=MyISAM AS SELECT GREATEST(CASE WHEN 1 THEN ts ELSE 0 END, CASE WHEN 1 THEN ts ELSE 0 END) AS t2 FROM t1;
|
SHOW WARNINGS;
|
SHOW CREATE TABLE t2;
|
SELECT * FROM t2;
|
with IFNULL:
SET NAMES utf8;
|
DROP TABLE IF EXISTS t1, t2;
|
CREATE TABLE t1 (id2 int, ts timestamp) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES (1,'2012-06-11 15:17:34'),(2,'2012-06-11 15:18:24');
|
DROP TABLE IF EXISTS t2;
|
CREATE TABLE t2 ENGINE=MyISAM AS SELECT GREATEST(IFNULL(ts,0), IFNULL(ts,0)) AS t2 FROM t1;
|
SHOW WARNINGS;
|
SHOW CREATE TABLE t2;
|
SELECT * FROM t2;
|
and IF:
SET NAMES utf8;
|
DROP TABLE IF EXISTS t1, t2;
|
CREATE TABLE t1 (id2 int, ts timestamp) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES (1,'2012-06-11 15:17:34'),(2,'2012-06-11 15:18:24');
|
DROP TABLE IF EXISTS t2;
|
CREATE TABLE t2 ENGINE=MyISAM AS SELECT GREATEST(IF(1,ts,0), IF(1,ts,0)) AS dt FROM t1;
|
SHOW WARNINGS;
|
SHOW CREATE TABLE t2;
|
SELECT * FROM t2;
|
|
test.query.sql
test.variables.txt