UI: change column's default property to nullable (NULL)

[waynexia]

What type of enhancement is this?

API improvement

What does the enhancement do?

If a column's nullability is not specified in CREATE TABLE clause it will be defaulted to NON-NULL. But other system like postgresql is default to NULL.

Ours:

mysql> CREATE TABLE monitor (
    ->   host STRING,
    ->   ts TIMESTAMP DEFAULT current_timestamp,
    ->   cpu DOUBLE DEFAULT 0,
    ->   memory DOUBLE NOT NULL,
    ->   TIME INDEX (ts),
    ->   PRIMARY KEY(host)) ENGINE=mito WITH(regions=1);
Query OK, 1 row affected (0.07 sec)

mysql> desc table monitor;
+--------+-----------+------+---------------------+---------------+
| Field  | Type      | Null | Default             | Semantic Type |
+--------+-----------+------+---------------------+---------------+
| host   | String    | NO   |                     | PRIMARY KEY   |
| ts     | Timestamp | NO   | current_timestamp() | TIME INDEX    |
| cpu    | Float64   | NO   | 0                   | VALUE         |
| memory | Float64   | NO   |                     | VALUE         |
+--------+-----------+------+---------------------+---------------+
4 rows in set (0.01 sec)

A nullable column should be explicit declared:

mysql> CREATE TABLE monitor3 (
    ->   host STRING,
    ->   ts TIMESTAMP DEFAULT current_timestamp,
    ->   cpu DOUBLE DEFAULT 0,
    ->   memory DOUBLE NULL,
    ->   TIME INDEX (ts),
    ->   PRIMARY KEY(host)) ENGINE=mito WITH(regions=1);
Query OK, 1 row affected (0.06 sec)

mysql> desc table monitor3
    -> ;
+--------+-----------+------+---------------------+---------------+
| Field  | Type      | Null | Default             | Semantic Type |
+--------+-----------+------+---------------------+---------------+
| host   | String    | NO   |                     | PRIMARY KEY   |
| ts     | Timestamp | NO   | current_timestamp() | TIME INDEX    |
| cpu    | Float64   | NO   | 0                   | VALUE         |
| memory | Float64   | YES  |                     | VALUE         |
+--------+-----------+------+---------------------+---------------+
4 rows in set (0.01 sec)

Implementation challenges

No response

[azhsmesos]

Do you know what caused the mistake？

mysql> desc table monitor;
ERROR 1815 (HY000): Failed to execute query: desc table monitor, source: Failed to parse SQL, source: SQL statement is not supported: desc table monitor, keyword: desc

[waynexia]

Do you know what caused the mistake？

mysql> desc table monitor;
ERROR 1815 (HY000): Failed to execute query: desc table monitor, source: Failed to parse SQL, source: SQL statement is not supported: desc table monitor, keyword: desc

The example I wrote uses an un-merged function (DESC TABLE) in #534 .

[waynexia]

It is now merged. Your SQL should run as expect.

[azhsmesos]

Please describe this issue again. I verified and found that it seems to meet the expectationPlease describe this issue again. I just verified and found that it seems to meet the expectation...

[waynexia]

Please describe this issue again. I verified and found that it seems to meet the expectationPlease describe this issue again. I just verified and found that it seems to meet the expectation...

Sorry for the unclear description. In short, column's nullability property is current default to NOT NULL. And this issue request it to default to NULL.

[azhsmesos]

I have completed the coding, can you assign it to me? I will mention pr later