2023-09-12 mysql-代号m-添加字段且字段非空出错-问题分析

摘要:

2023-09-12 mysql-代号m-添加字段且字段非空出错-问题分析

上下文:

2023-09-11 mysql-代号m-0930阶段目标-任务列表-记录_财阀悟世的博客-CSDN博客

https://devops.aliyun.com/projex/project/36ed2d8a9a29e7f8407c6f5498/bug/7398227ae8b85743d94335b698

DDL:

表结构和数据:

CREATE TABLE t_test(
  id INT NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(10) NOT NULL,
  last_name VARCHAR(10) NOT NULL,
  sex VARCHAR(5) NOT NULL,
  score INT NOT NULL,
  copy_id INT NOT NULL,
  PRIMARY KEY (`id`)
);
 
insert into t_test values(1,'张','三','男',10,1),(2,'李','四','女',20,2),(3,'王','五','男',30,3);
 
select * from t_test;
 
show create table t_test\G

alter语句:

alter table t_test add column age smallint not null;

问题现象:

错误日志:

SQLException:assert:40002!UPDATE: NOT NULL constraint violated for column 't_test.age'

monetdb的处理:

直接结果:

sql>alter table t_test add column age int not null;
UPDATE: NOT NULL constraint violated for column 't_test.age'

对应的函数:

static void
sql_update_check_null(backend *be, sql_table *t, stmt **updates)
{
	mvc *sql = be->mvc;
	node *n;
	sql_subfunc *cnt = sql_bind_func(sql, "sys", "count", sql_bind_localtype("void"), NULL, F_AGGR, true);
 
	for (n = ol_first_node(t->columns); n; n = n->next) {
		sql_column *c = n->data;
 
		if (updates[c->colnr] && !c->null) {
			stmt *s = updates[c->colnr];
			char *msg = NULL;
 
			if (!(s->key && s->nrcols == 0)) {
				s = stmt_selectnil(be, updates[c->colnr]);
				s = stmt_aggr(be, s, NULL, NULL, cnt, 1, 0, 1);
			} else {
				sql_subfunc *isnil = sql_bind_func(sql, "sys", "isnull", &c->type, NULL, F_FUNC, true);
 
				s = stmt_unop(be, updates[c->colnr], NULL, isnil);
			}
			msg = sa_message(sql->sa, SQLSTATE(40002) "UPDATE: NOT NULL constraint violated for column '%s.%s'", c->t->base.name, c->base.name);
			(void)stmt_exception(be, s, msg, 00001);
		}
	}
}

调用堆栈:

#0  sql_update_check_null (be=0x7f71d40e75b0, t=0x7f71a7dddc70, updates=0x7f71a7ddfa90) at /root/work/trunk/mysql-server-mysql-8.0.33/storage/monetdb/sql/backends/monet5/rel_bin.c:5183
#1  0x00007f73c85d4c3d in rel2bin_update (be=0x7f71d40e75b0, rel=0x7f71a7ddec20, refs=0x7f71a7ddf380) at /root/work/trunk/mysql-server-mysql-8.0.33/storage/monetdb/sql/backends/monet5/rel_bin.c:5309
#2  0x00007f73c85d9c52 in subrel_bin (be=0x7f71d40e75b0, rel=0x7f71a7ddec20, refs=0x7f71a7ddf380) at /root/work/trunk/mysql-server-mysql-8.0.33/storage/monetdb/sql/backends/monet5/rel_bin.c:6392
#3  0x00007f73c85d9efc in output_rel_bin (be=0x7f71d40e75b0, rel=0x7f71a7ddec20, top=1) at /root/work/trunk/mysql-server-mysql-8.0.33/storage/monetdb/sql/backends/monet5/rel_bin.c:6449
#4  0x00007f73c8607269 in sql_relation2stmt (be=0x7f71d40e75b0, r=0x7f71a7ddec20, top=1) at /root/work/trunk/mysql-server-mysql-8.0.33/storage/monetdb/sql/backends/monet5/sql_gencode.c:762
#5  0x00007f73c860751c in backend_dumpstmt (be=0x7f71d40e75b0, mb=0x7f71d40bb590, r=0x7f71a7ddec20, top=1, add_end=0, query=0x7f71a7dddae0 "alter table t_test add column age3 int not null;")
    at /root/work/trunk/mysql-server-mysql-8.0.33/storage/monetdb/sql/backends/monet5/sql_gencode.c:813
#6  0x00007f73c85a7bf9 in SQLparser (c=0x7f71d415ade0) at /root/work/trunk/mysql-server-mysql-8.0.33/storage/monetdb/sql/backends/monet5/sql_scenario.c:1214
#7  0x00007f73ca9101ae in monetdbe_query_internal (mdbe=0x7f71d40fb640, query=0x7f71d410eb60 "alter table t_test add column age3 int not null;", result=0x0, affected_rows=0x7f73741f1778, 
    prepare_id=0x0, language=83 'S') at /root/work/trunk/mysql-server-mysql-8.0.33/storage/monetdb/tools/monetdbe/monetdbe.c:409
#8  0x00007f73ca91586c in monetdbe_query (dbhdl=0x7f71d40fb640, query=0x7f71d410eb60 "alter table t_test add column age3 int not null;", result=0x0, affected_rows=0x7f73741f1778)
    at /root/work/trunk/mysql-server-mysql-8.0.33/storage/monetdb/tools/monetdbe/monetdbe.c:1521
#9  0x000000000389487a in Mondetdb_Adaptor::execute_query (this=0xc0aa1a0, dbname=0x7f71d400e170 "test_join", sql=0x7f71d40a5c30 "alter table t_test add column age3 int not null", set_status=false)
    at /root/work/trunk/mysql-server-mysql-8.0.33/sql/monetdb_adaptor/monetdb_adaptor.cc:321
#10 0x0000000003e45e1b in Sql_cmd_alter_table::execute (this=0x7f71d40a7670, thd=0x7f71d40150e0) at /root/work/trunk/mysql-server-mysql-8.0.33/sql/sql_alter.cc:367
#11 0x00000000036c06cb in mysql_execute_command (thd=0x7f71d40150e0, first_level=true) at /root/work/trunk/mysql-server-mysql-8.0.33/sql/sql_parse.cc:4714
#12 0x00000000036c2987 in dispatch_sql_command (thd=0x7f71d40150e0, parser_state=0x7f73741f38b0) at /root/work/trunk/mysql-server-mysql-8.0.33/sql/sql_parse.cc:5363
#13 0x00000000036b86d0 in dispatch_command (thd=0x7f71d40150e0, com_data=0x7f73741f49a0, command=COM_QUERY) at /root/work/trunk/mysql-server-mysql-8.0.33/sql/sql_parse.cc:2050
#14 0x00000000036b6618 in do_command (thd=0x7f71d40150e0) at /root/work/trunk/mysql-server-mysql-8.0.33/sql/sql_parse.cc:1439
#15 0x000000000393b45a in handle_connection (arg=0xc03c9e0) at /root/work/trunk/mysql-server-mysql-8.0.33/sql/conn_handler/connection_handler_per_thread.cc:302
#16 0x0000000005949155 in pfs_spawn_thread (arg=0xc168e60) at /root/work/trunk/mysql-server-mysql-8.0.33/storage/perfschema/pfs.cc:3042
#17 0x00007f73ca62b1ca in start_thread () from /lib64/libpthread.so.0
#18 0x00007f73c8bdbe73 in clone () from /lib64/libc.so.6