[MDEV-8630] Datetime value dropped in "INSERT ... SELECT ... ON DUPLICATE KEY" - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 5.5.41, 10.0.17, 5.5(EOL), 10.0(EOL), 10.1(EOL)
Fix Version/s: 5.5.47
Component/s: Character Sets, Data Manipulation - Update
Labels:
None
Environment:
Debian Squeeze x64

Sprint:
5.5.47-1

Description

INSERT INTO ...

 SELECT ...

  GREATEST( COALESCE(`inspection`.`ts`, 0), COALESCE(`inspection_details`.`ts`, 0), COALESCE(`open_time_reason`.`ts`, 0)

 ) AS `changed_at`

 ON DUPLICATE KEY UPDATE ...

 `changed_at` = VALUES(`changed_at`)

In MySQL the changed_at will be the expected value from the greatest function, but in MariaDB it will be '0000-00-00 00:00:00'

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

test.query.sql
2 kB
2015-08-19 02:28
test.sql.gz
3.14 MB
2015-08-19 02:25
test.variables.txt
61 kB
2015-08-19 02:27

Activity

Ascending order - Click to sort in descending order

Raymond Xu created issue - 2015-08-17 01:42

Raymond Xu added a comment - 2015-08-17 01:43

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`

Raymond Xu added a comment - 2015-08-17 01:43 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`

Sergei Golubchik made changes - 2015-08-18 20:48

Field	Original Value	New Value
Description	INSERT INTO ... SELECT ... GREATEST( COALESCE(`inspection`.`ts`, 0), COALESCE(`inspection_details`.`ts`, 0), COALESCE(`open_time_reason`.`ts`, 0) ) AS `changed_at` ON DUPLICATE KEY UPDATE ... `changed_at` = VALUES(`changed_at`) In MySQL the changed_at will be the expected value from the greatest function, but in MariaDB it will be '0000-00-00 00:00:00'	{code:sql} INSERT INTO ... SELECT ... GREATEST( COALESCE(`inspection`.`ts`, 0), COALESCE(`inspection_details`.`ts`, 0), COALESCE(`open_time_reason`.`ts`, 0) ) AS `changed_at` ON DUPLICATE KEY UPDATE ... `changed_at` = VALUES(`changed_at`) {code} In MySQL the changed_at will be the expected value from the greatest function, but in MariaDB it will be '0000-00-00 00:00:00'

Sergei Golubchik made changes - 2015-08-18 20:48

Fix Version/s		10.0 [ 16000 ]
Fix Version/s		5.5 [ 15800 ]

Sergei Golubchik made changes - 2015-08-18 20:49

Priority

Minor [ 4 ]

Major [ 3 ]

Sergei Golubchik made changes - 2015-08-18 20:49

Fix Version/s	10.0 [ 16000 ]
Fix Version/s	5.5 [ 15800 ]

Elena Stepanova added a comment - 2015-08-19 00:27

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.

Elena Stepanova added a comment - 2015-08-19 00:27 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 .

Elena Stepanova made changes - 2015-08-19 00:27

Labels

need_feedback

Raymond Xu made changes - 2015-08-19 02:25

Attachment

test.sql.gz [ 39206 ]

Raymond Xu made changes - 2015-08-19 02:27

Attachment

test.variables.txt [ 39207 ]

Raymond Xu made changes - 2015-08-19 02:28

Attachment

test.query.sql [ 39208 ]

Raymond Xu added a comment - 2015-08-19 02:29

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

Raymond Xu added a comment - 2015-08-19 02:29 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

Elena Stepanova made changes - 2015-08-19 02:31

Labels

need_feedback

Elena Stepanova added a comment - 2015-08-19 19:07

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.

Elena Stepanova added a comment - 2015-08-19 19:07 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.

Elena Stepanova made changes - 2015-08-19 19:07

Fix Version/s		10.1 [ 16100 ]
Fix Version/s		10.0 [ 16000 ]
Fix Version/s		5.5 [ 15800 ]
Affects Version/s		10.1 [ 16100 ]
Affects Version/s		10.0 [ 16000 ]
Affects Version/s		5.5 [ 15800 ]
Assignee		Alexander Barkov [ bar ]

Elena Stepanova made changes - 2015-08-19 19:51

Status

Open [ 1 ]

Confirmed [ 10101 ]

Raymond Xu added a comment - 2015-08-20 01:22

No worries Elena, glad it's confirmed.

Cheers,
Ray

Raymond Xu added a comment - 2015-08-20 01:22 No worries Elena, glad it's confirmed. Cheers, Ray

Alexander Barkov added a comment - 2015-08-20 10:10 - edited

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).

Alexander Barkov added a comment - 2015-08-20 10:10 - edited 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).

Alexander Barkov made changes - 2015-08-20 11:33

Rank

Ranked higher

Rasmus Johansson (Inactive) made changes - 2015-12-03 14:33

Sprint

5.5.47-1 [ 22 ]

Alexander Barkov added a comment - 2015-12-03 15:28

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`)

Alexander Barkov added a comment - 2015-12-03 15:28 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`)

Alexander Barkov added a comment - 2015-12-03 16:00 - edited

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;

Alexander Barkov added a comment - 2015-12-03 16:00 - edited 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;

Alexander Barkov made changes - 2015-12-03 19:04

Component/s		Character Sets [ 10801 ]
Fix Version/s		5.5.47 [ 20300 ]
Fix Version/s	5.5 [ 15800 ]
Fix Version/s	10.0 [ 16000 ]
Fix Version/s	10.1 [ 16100 ]
Resolution		Fixed [ 1 ]
Status	Confirmed [ 10101 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:41

Workflow

MariaDB v3 [ 71055 ]

MariaDB v4 [ 149481 ]

People

Assignee:: Alexander Barkov

Reporter:: Raymond Xu

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2015-08-17 01:42

Updated:: 2015-12-03 19:04

Resolved:: 2015-12-03 19:04

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Attachments

Activity

People

Dates

Git Integration