[#MDEV-16365] Setting a column NOT NULL fails to return error for NULL values when there is no DEFAULT

~~MDEV-14168~~ changed behaviour in an undesired way. If the test of the feature is executed on MariaDB Server 10.2 or earlier, it will fail:

CURRENT_TEST: innodb.alter_not_null

mysqltest: At line 8: query 'ALTER TABLE t1 CHANGE f1 f1 INT NOT NULL' failed: 1138: Invalid use of NULL value

If I change the statement to use ALGORITHM=COPY, it will fail with a different message (in both 10.2 and 10.3):

CURRENT_TEST: innodb.alter_not_null

mysqltest: At line 8: query 'ALTER TABLE t1 CHANGE f1 f1 INT NOT NULL, ALGORITHM=COPY' failed: 1265: Data truncated for column 'f1' at row 1

The invariant should be that ALGORITHM=INPLACE and ALGORITHM=COPY produce the same result. After ~~MDEV-14168~~, ALGORITHM=INPLACE would incorrectly convert the NULL value to the implicit default value 0 of the INT data type.

With SQL_MODE='' (not STRICT_TRANS_TABLES), warnings would be reported for each row in ALGORITHM=COPY:

+ALTER TABLE t1 CHANGE f1 f1 INT NOT NULL, ALGORITHM=COPY;

+affected rows: 1

+info: Records: 1  Duplicates: 0  Warnings: 1

+Warnings:

+Warning	1265	Data truncated for column 'f1' at row 1

Similarly, warnings will be reported by ALTER IGNORE TABLE, also for SQL_MODE='STRICT_TRANS_TABLES' values.

I think that ALGORITHM=INPLACE should be fixed so that it will report errors or warnings for the affected rows.

As part of the fix, the following code (which I expected ~~MDEV-14168~~ to remove) should be removed:

diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc

index 769eced242c..94545321052 100644

--- a/storage/innobase/handler/handler0alter.cc

+++ b/storage/innobase/handler/handler0alter.cc

@@ -913,18 +913,6 @@ ha_innobase::check_if_supported_inplace_alter(

 		DBUG_RETURN(HA_ALTER_INPLACE_INSTANT);

-	/* Only support NULL -> NOT NULL change if strict table sql_mode

-	is set. Fall back to COPY for conversion if not strict tables.

-	In-Place will fail with an error when trying to convert

-	NULL to a NOT NULL value. */

-	if ((ha_alter_info->handler_flags

-	     & ALTER_COLUMN_NOT_NULLABLE)

-	    && !thd_is_strict_mode(m_user_thd)) {

-		ha_alter_info->unsupported_reason = my_get_err_msg(

-			ER_ALTER_OPERATION_NOT_SUPPORTED_REASON_NOT_NULL);

-		DBUG_RETURN(HA_ALTER_INPLACE_NOT_SUPPORTED);

-	}

 	/* DROP PRIMARY KEY is only allowed in combination with ADD

 	PRIMARY KEY. */

 	if ((ha_alter_info->handler_flags

[MDEV-16365] Setting a column NOT NULL fails to return error for NULL values when there is no DEFAULT Created: 2018-05-31 Updated: 2019-03-04 Resolved: 2018-06-26
Status:	Closed
Project:	MariaDB Server
Component/s:	Storage Engine - InnoDB
Affects Version/s:	10.3.7
Fix Version/s:	10.3.8

[MDEV-16365] Setting a column NOT NULL fails to return error for NULL values when there is no DEFAULT Created: 2018-05-31 Updated: 2019-03-04 Resolved: 2018-06-26