Column type for aggregate column created from select does not match MySQL

[angelamayxie]

MySQL

mysql> create table t1 (i int);
Query OK, 0 rows affected (0.01 sec)

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

mysql> create table t2 select sum(i) from t1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table t2;
+-------+------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                 |
+-------+------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `sum(i)` decimal(32,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Dolt

tmp/main*> create table t1 (i int);
Query OK, 0 rows affected (0.01 sec)
tmp/main*> insert into t1 values (1);
Query OK, 1 row affected (0.01 sec)
tmp/main*> create table t2 select sum(i) from t1;
Query OK, 1 row affected (0.01 sec)
tmp/main*> show create table t2;
+-------+------------------------------------------------------------------+
| Table | Create Table                                                     |
+-------+------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (                                              |
|       |   `sum(t1.i)` double NOT NULL                                    |
|       | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin |
+-------+------------------------------------------------------------------+
1 row in set (0.00 sec) 

Column is decimal(32,0) DEFAULT NULL in MySQL but double NOT NULL in Dolt. TODO added in dolthub/go-mysql-server#3182

Column name is fixed in dolthub/vitess#430

[angelamayxie]

mysql

mysql> create table t2 select sum(i), max(i), min(i), avg(i) from t1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table t2;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                  |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `sum(i)` decimal(32,0) DEFAULT NULL,
  `max(i)` int DEFAULT NULL,
  `min(i)` int DEFAULT NULL,
  `avg(i)` decimal(14,4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

dolt

tmp/main*> create table t2 select sum(i), max(i), min(i), avg(i) from t1;
Query OK, 1 row affected (0.01 sec)
tmp/main*> show create table t2;
+-------+------------------------------------------------------------------+
| Table | Create Table                                                     |
+-------+------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (                                              |
|       |   `sum(t1.i)` double NOT NULL,                                   |
|       |   `max(t1.i)` int NOT NULL,                                      |
|       |   `min(t1.i)` int NOT NULL,                                      |
|       |   `avg(t1.i)` double                                             |
|       | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin |
+-------+------------------------------------------------------------------+
1 row in set (0.00 sec)