insert into sharding column(type timestamp) null while it has default not null value will violate sharding expectation

[irene-coming]

• dble version：
  5.6.29-dble-9.9.9.9-88bdad5-20181030102646

• preconditions ：
  no

• configs：

schema.xml

<table name="date_patch_default" dataNode="dn1,dn2,dn3,dn4" rule="date_default_rule" />

rule.xml

    <tableRule name="date_default_rule">
        <rule>
            <columns>id</columns>
            <algorithm>date_default</algorithm>
        </rule>
    </tableRule>
    <function name="date_default" class="Date">
        <property name="dateFormat">yyyy-MM-dd</property>
        <property name="sBeginDate">2016-12-01</property>
        <property name="sEndDate">2017-01-9</property>
        <property name="sPartionDay">10</property>
        <property name="defaultNode">0</property>
    </function>

• steps：
  step1. prepare table in dble:

mysql> drop table date_patch_default;
Query OK, 0 rows affected (0.03 sec)

mysql> create table date_patch_default(id timestamp, c timestamp);
Query OK, 0 rows affected (0.04 sec)
mysql> show create table date_patch_default;
+--------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table              | Create Table                                                                                                                                                                                                       |
+--------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| date_patch_default | CREATE TABLE `date_patch_default` (
  `id` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

step2. execute sql with dble:

mysql> insert into date_patch_default values (null,null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from date_patch_default;
+---------------------+---------------------+
| id                  | c                   |
+---------------------+---------------------+
| 2018-11-08 15:59:39 | 2018-11-08 15:59:39 |
+---------------------+---------------------+
1 row in set (0.01 sec)

• expect result：
  1. step2, real sharing and sharding rule should be consistent
• real result：
  1.step2, insert null make dble deciding the row sent to default sharding node, but in fact the column id value is not really null, and the sharding may differ from expect
• supplements：
  1.if change the column datatype to datetime , has no such default value deal, and no above problem

2. the following dble action is consistent with mysql:

mysql> create table aly_test(id int default 1, c int);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into aly_test values(0,0),(1,1),(2,2),(3,3),(null,null);
Query OK, 5 rows affected (0.02 sec)

mysql> select * from aly_test;
+------+------+
| id   | c    |
+------+------+
|    2 |    2 |
|    3 |    3 |
|    1 |    1 |
|    0 |    0 |
| NULL | NULL |
+------+------+
5 rows in set (0.01 sec)

mysql> drop table aly_test;
Query OK, 0 rows affected (0.03 sec)

mysql> create table aly_test(id int not null default 1, c int);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into aly_test values(0,0),(1,1),(2,2),(3,3),(null,null);
ERROR 1048 (23000): Column 'id' cannot be null

/label ~BUG

[sunsun314]

'Null' can be no longer insert into a sharding column when the column in MySQL be created with description “not null”

[FlyingMao]

verified version： 5.6.29-dble-9.9.9.9-959f576-20181113080156