with enum sharding:if insert into enum column with index value will leading error sharding action

[irene-coming]

• dble version：
  5.6.29-dble-9.9.9.9-08136d5-20181106070356

• preconditions ：
  no

• configs：

schema.xml

<table name="enum_patch_integer" dataNode="dn1,dn2,dn3,dn4" rule="enum_integer_rule"/>

rule.xml

    <tableRule name="enum_integer_rule">
        <rule>
            <columns>id</columns>
            <algorithm>enum_integer</algorithm>
        </rule>
    </tableRule>
    <function name="enum_integer" class="Enum">
        <property name="mapFile">enum-integer.txt</property>
        <property name="type">0</property>
        <property name="defaultNode">1</property>
    </function>

• steps：
  step1. execute sql with dble:

mysql>CREATE TABLE `enum_patch_integer` (
  `id` enum('1','2','3','6','7','8','12','13') DEFAULT '7'
) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysql> insert into enum_patch_integer values(1),(6),(12);
ERROR 1265 (01000): Data truncated for column 'id' at row 3
mysql>
mysql> insert into enum_patch_integer values(1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into enum_patch_integer values(6);
Query OK, 1 row affected (0.00 sec)

mysql> select * from enum_patch_integer;
+------+
| id   |
+------+
| 1    |
| 8    |
+------+
2 rows in set (0.01 sec)

step2.execute explain in dble:

mysql> explain insert into enum_patch_integer values(1),(6),(12);
+-----------+----------+--------------------------------------------+
| DATA_NODE | TYPE     | SQL/REF                                    |
+-----------+----------+--------------------------------------------+
| dn1       | BASE SQL | INSERT INTO enum_patch_integer VALUES (1)  |
| dn2       | BASE SQL | INSERT INTO enum_patch_integer VALUES (6)  |
| dn3       | BASE SQL | INSERT INTO enum_patch_integer VALUES (12) |
+-----------+----------+--------------------------------------------+
3 rows in set (0.00 sec)

• expect result：
  1. in step2 explain resultset: 12 is out of enum index, need proper error feedback
  2. with enum index, sharding should convert to the corresponding value and then decide which sharding to route
• real result：
  1. out of index enum has no error
  2. insert enum index will sharding by it.
• supplements：
  1.

/label ~BUG

[irene-coming]

another problem with enum datatype: dble version: 5.6.29-dble-9.9.9.9-a68f557-20181119123440

step1:

mysql> create table aly_test(id int, c1 enum('a','b','c'));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into aly_test values(1, 'a'),(2,'b'),(null,null),(4, 'c'),(5,'b');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

expect:

mysql> select sum(DISTINCT id),sum(DISTINCT c1) from aly_test;
+------------------+------------------+
| sum(DISTINCT id) | sum(DISTINCT c1) |
+------------------+------------------+
|               12 |                6 |
+------------------+------------------+
1 row in set (0.00 sec)

real:

mysql> select sum(DISTINCT id),sum(DISTINCT c1) from aly_test;
+------------------+------------------+
| SUM(DISTINCT id) | SUM(DISTINCT c1) |
+------------------+------------------+
|               12 |                0 |
+------------------+------------------+
1 row in set (0.02 sec)

supplement: autotest for this comment already added: aggregate.sql d196bec5a0b388fdb7308e6e90f1309a083ef172

[PanternBao]

doc : https://dev.mysql.com/doc/refman/8.0/en/enum.html If dble supports it, need higher cost. So dble doesn't support it temporarily.