bug: (Primary/Secondary)After an integer column is added,InnoDB defaults to 0, but tianmu defaults to null.

[haitaoguan]

Have you read the Contributing Guidelines on issues?

• I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

• I confirm there is no existing issue for this

Describe the problem

###MASTER
create table ttt(id int,name varchar(5));
insert into ttt values(1,'AAA'),(2,'BBB');
alter table ttt add column age int not null;

mysql> show create table ttt\G
*************************** 1. row ***************************
       Table: ttt
Create Table: CREATE TABLE `ttt` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(5) DEFAULT NULL,
  `age` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> select * from ttt;
+------+------+-----+
| id   | name | age |
+------+------+-----+
|    1 | AAA  |   0 |
|    2 | BBB  |   0 |
+------+------+-----+
2 rows in set (0.00 sec)


###SLAVE
mysql> show create table ttt\G
*************************** 1. row ***************************
       Table: ttt
Create Table: CREATE TABLE `ttt` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(5) DEFAULT NULL,
  `age` int(11) NOT NULL
) ENGINE=TIANMU DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> select * from ttt;
+------+------+-----+
| id   | name | age |
+------+------+-----+
|    1 | AAA  | NULL |
|    2 | BBB  | NULL |
+------+------+-----+
2 rows in set (0.00 sec)

Expected behavior

mysql> select * from ttt;
+------+------+-----+
| id   | name | age |
+------+------+-----+
|    1 | AAA  |   0 |
|    2 | BBB  |   0 |
+------+------+-----+
2 rows in set (0.00 sec)

How To Reproduce

No response

Environment

./mysqld Ver 5.7.36-StoneDB for Linux on x86_64 (build-)
build information as follow:
Repository address: https://github.com/stoneatom/stonedb.git:stonedb-5.7-dev
Branch name: stonedb-5.7-dev
Last commit ID: 0bc2cbe
Last commit time: Date: Fri Dec 30 17:46:55 2022 +0800
Build time: Date: Tue Jan 3 09:59:43 CST 2023

Are you interested in submitting a PR to solve the problem?

• Yes, I will!

[lujiashun]

ACK

[wisehead]

I move this issue to 1.0.3 in order not to blocking 1.0.2 release. but this bug has a big imapct on SQL result, please fix it ASAP.

[lujiashun]

It seems "MANDATORY_TIANMU" of SQL_mode effects the result.

If SQL_mode is 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'， the result is:

mysql> select * from ttt;
+------+------+-----+
| id   | name | age |
+------+------+-----+
|    1 | AAA  | NULL |
|    2 | BBB  | NULL |
+------+------+-----+
2 rows in set (0.00 sec)

If SQL_MODE add "MANDATORY_TIANMU" , "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,MANDATORY_TIANMU", the result is :

mysql> select * from ttt;
+------+------+-----+
| id   | name | age |
+------+------+-----+
|    1 | AAA  |   0 |
|    2 | BBB  |   0 |
+------+------+-----+
2 rows in set (0.01 sec)

[konghaiya]

This problem looks like #1074

[lujiashun]

in function enum_alter_inplace_result ha_tianmu::check_if_supported_inplace_alter
1 if MANDATORY_TIANMU is added, ha_alter_info->handler_flags is 0x2000000080;
return value is HA_ALTER_INPLACE_NOT_SUPPORTED,
and SQL layer dealed with it (use copy mode), slow but the result is correct;
2 if MANDATORY_TIANMU is not added, ha_alter_info->handler_flags is 0x80,
return value is HA_ALTER_INPLACE_EXCLUSIVE_LOCK,
and tianmu layer will deal with it(use inplace mode), quic but the result is not correct,
3 What we should to do is
3.1 to remove the 0x200000000 flags(RECREATE_TABLE) even in MANDATORY_TIANMU mode;
3.2 to fix the bug that the tianmu mode does not support the NOT NULL value;

[lujiashun]

1 For the scenario to add interger column not default NULL, We can modify the TianmuAttr::Create to fix the bug；
2 But If we want to enhance the feature to support add string column not defaut NULL ，We had to find another way to support it；
3 For now，Only to deal with interger， because it‘s the most frequency scenario, but implementation seems to be avaialble now;
4 If in the future, we need support string column not default null , implementation for interger need not to be adjusted.