MySQL Bugs: #96521: on duplicate key update [1048] [23000]: Column 'NAME' cannot be null

Bug #96521	on duplicate key update [1048] [23000]: Column 'NAME' cannot be null
Submitted:	13 Aug 2019 12:10	Modified:	14 Aug 2019 14:09
Reporter:	zhongxuchen chen	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.17	OS:	Any
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
execute:on duplicate key  update 
result:SQL 错误 [1048] [23000]: Column 'NAME' cannot be null

How to repeat:
step 1: create table for test
CREATE TABLE TEST1 (
ID varchar(100) NOT NULL,
NAME varchar(100) NOT NULL,
STATUS varchar(100) NOT NULL,
PRIMARY KEY (ID)
)

step 2、insert record:
insert into TEST1 (ID, NAME,STATUS ) values('4','test','6') ;

step 3、execute insert on duplicate update （same PK）

insert into TEST1 (ID, NAME,STATUS ) values('4',null,'6') 
on duplicate key update TEST1.NAME=ifnull(values(NAME),TEST1.NAME),
TEST1.STATUS=ifnull(values(STATUS),TEST1.STATUS);

Result:
SQL 错误 [1048] [23000]: Column 'NAME' cannot be null

Hi,

Thanks for submitting a bug. Verified both on latest 5.7 and 8.0.
It looks like optimizer is not even trying to execute when the NAME is NULL as part of query optimization. I will verify the behavior but optimizer team might reject the bug depending on how the specification treats this case.

best regards
Bogdan

Hi,

I'm reclassifying this as not a bug as I did more research and checked also how other SQL servers behave. For e.g. 

test=# insert into TEST1 (ID, NAME,STATUS ) values('4',null,'6')
test-# ON CONFLICT (ID) DO UPDATE
test-# SET NAME   = COALESCE(excluded.NAME, TEST1.NAME),
test-#     STATUS = COALESCE(excluded.STATUS, TEST1.STATUS);
ERROR:  null value in column "name" violates not-null constraint
DETAIL:  Failing row contains (4, null, 6).
test=#

this is a bug!(postgresql has too)

but sqlserver\ oracle\db2 is use merge into !so no this problem;

in sqltoy-orm(https://github.com/chenrenfei/sagacity-sqltoy),Use this feature to achieve saveOrUpdate！

now only mysql and postgresql not support this feature!Just because of this bug！

       //mysql with insert into ON DUPLICATE KEY UPDATE (now has bug for not null field)
       public Long saveOrUpdateAll(SqlToyContext sqlToyContext, List<?> entities, final int batchSize,
			ReflectPropertyHandler reflectPropertyHandler, final String[] forceUpdateFields, Connection conn,
			final Boolean autoCommit, final String tableName) throws Exception {
		// mysql是否支持原生saveOrUpdate
		// if (SqlToyConstants.mysqlSupportSaveOrUpdate()) {
		// return saveOrUpdateAllBySelf(sqlToyContext, entities, batchSize,
		// reflectPropertyHandler, forceUpdateFields,
		// conn, autoCommit, tableName);
		// }
		//目前mysql不支持,则先update，然后saveIgnore 两步操作实现同样功能
		Long updateCnt = DialectUtils.updateAll(sqlToyContext, entities, batchSize, forceUpdateFields,
				reflectPropertyHandler, NVL_FUNCTION, conn, autoCommit, tableName, true);
		logger.debug("修改记录数为:{}", updateCnt);
		// 如果修改的记录数量跟总记录数量一致,表示全部是修改
		if (updateCnt >= entities.size())
			return updateCnt;
		Long saveCnt = this.saveAllIgnoreExist(sqlToyContext, entities, batchSize, reflectPropertyHandler, conn,
				autoCommit, tableName);
		logger.debug("新建记录数为:{}", saveCnt);
		return updateCnt + saveCnt;
	}

        //sqlserver with merge into 
        public Long saveOrUpdateAll(SqlToyContext sqlToyContext, List<?> entities, final int batchSize,
			final ReflectPropertyHandler reflectPropertyHandler, final String[] forceUpdateFields, Connection conn,
			final Boolean autoCommit, final String tableName) throws Exception {
		return SqlServerDialectUtils.saveOrUpdateAll(sqlToyContext, entities, batchSize, reflectPropertyHandler,
				forceUpdateFields, conn, DBType.SQLSERVER, autoCommit);
	}