[Bug report] Create nullable column with value 'ture', return 'false' about mysql table

[LauraXia123]

Version

main branch

Describe what's wrong

1. Create nullable column with value 'true', return 'false' about mysql table
2. If autoIncrement column can not be null, need to throw an exception

Error message and/or stacktrace

N/A

How to reproduce

1. Create nullable column with value 'true', autoIncrement with value true
curl 'http://0.0.0.0:8090/api/metalakes/metalake_demo/catalogs/catalog_mysql/schemas/db/tables' \ -H 'Accept: application/vnd.gravitino.v1+json' \ -H 'Accept-Language: zh-CN,zh;q=0.9' \ -H 'Cache-Control: no-cache' \ -H 'Connection: keep-alive' \ -H 'Content-Type: application/json;charset=UTF-8' \ -H 'Pragma: no-cache' \ --data-raw '{"name":"table4","comment":"","columns":[{"name":"col","type":"integer","nullable":true,"comment":"","autoIncrement":true}],"properties":{"engine":"InnoDB"},"indexes":[{"indexType":"unique_key","name":"col","fieldNames":[["col"]]}]}' \ --insecure

2. Return nullable column with value 'false'
   { "code": 0, "table": { "name": "table4", "comment": "", "columns": [ { "name": "col", "type": "integer", "nullable": false, "autoIncrement": true } ], "properties": { "engine": "InnoDB" }, "audit": { "creator": "anonymous", "createTime": "2024-12-02T07:56:50.306454916Z", "lastModifier": "anonymous", "lastModifiedTime": "2024-12-02T07:56:51.937079781Z" }, "distribution": { "strategy": "none", "number": 0, "funcArgs": [] }, "sortOrders": [], "partitioning": [], "indexes": [ { "indexType": "UNIQUE_KEY", "name": "col", "fieldNames": [ [ "col" ] ] } ] } }

Additional context

No response

[LauraXia123]

@mchades Can you help to have a check, please?

[mchades]

I have experimented with this in MySQL, and this behavior aligns with the standards of MySQL.

mysql> CREATE TABLE t1 (
    ->   c1 INT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (c1)
    -> );
ERROR 1171 (42000): All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead

mysql> CREATE TABLE t1 (
    ->   c1 INT NULL AUTO_INCREMENT,
    ->   UNIQUE KEY (c1)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| view1          |
+----------------+
2 rows in set (0.00 sec)

mysql> desc t1;
+-------+------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra          |
+-------+------+------+-----+---------+----------------+
| c1    | int  | NO   | PRI | NULL    | auto_increment |
+-------+------+------+-----+---------+----------------+
1 row in set (0.02 sec)

mysql> show create table t1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                        |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `c1` int NOT NULL AUTO_INCREMENT,
  UNIQUE KEY `c1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

But I think we should throw an error at creating a table through Gravitino as you mentioned

[jerryshao]

@mchades can you please work on this?