MySQL SELECT query works but an UPDATE query with same WHERE clause does not-CodePudding

When rows are excluded in an UPDATE query, why does the database engine try to set an incorrect datetime first and stops execution?

CREATE TABLE `test`(
  `name` VARCHAR(255) NOT NULL,
  `order_date` DATETIME NULL
)

INSERT INTO `test` (`name`, `order_date`) VALUES
("test nodate", NULL),
("test 2021-10-27", NULL);

-- Both rows are selected with a formatted date taken from the `name`
SELECT *, DATE_FORMAT(RIGHT(`name`, 10), "%Y-%m-%d") AS `date part from name` FROM `test`;

-- The first row with an invalid date in the name is correctly excluded
SELECT *, DATE_FORMAT(RIGHT(`name`, 10), "%Y-%m-%d") AS `date part from name` FROM `test`
WHERE DATE_FORMAT(RIGHT(`name`, 10), "%Y-%m-%d") IS NOT NULL;

-- Error: invalid datetime "est nodate", but that row should be excluded
UPDATE `test` SET
`order_date` = DATE_FORMAT(RIGHT(`name`, 10), "%Y-%m-%d")
WHERE DATE_FORMAT(RIGHT(`name`, 10), "%Y-%m-%d") IS NOT NULL;

DB Fiddle: https://www.db-fiddle.com/f/iUmjxZz3u5bpZesfiD4pzY/1

When trying for example this update query to set an invalid datetime, the engine doesnt throw the same error, while I expect it to be the same of the errornous update statement above:

UPDATE `test`
SET `order_date` = "invalid"
WHERE `name` <> `name`;

This update is succesful and no rows are updated, but I tried to force the invalid datetime error.

Are functions like DATE_FORMAT() special case in order of execution or something? Is a workaround possible to make the UPDATE query succesfully execute?

CodePudding user response：

Error is detected in WHERE, not in SET.

UPDATE `test`
SET `order_date` = NOW()
WHERE DATE_FORMAT(RIGHT(`name`, 10), "%Y-%m-%d") IS NOT NULL;

produces the same error.

And the error occurence is defined by SQL Server mode.

See Strict SQL Mode.

Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e0bcb8862b10fb8af16b8e2ffb1ae46c

CodePudding user response：

The operation:

DATE_FORMAT('foo bar baz', '%Y-%m-%d')

results in the following warning:

1292-Incorrect datetime value: '...'

Now, depending on SQL mode, the warning will be treated as error in data-change statements. This explains why your SELECT statement works (with warnings) but INSERT does not (the warnings are treated as errors).

Full demo on db<>fiddle