南大通用GBase 8a里面null常量数据的类型指定方法

GBase 8a里，null常量数据，注意不是某个字段的数值为null,而是一个null常量，如果不指定类型，默认未varbinary。可以通过cast强制指定类型，也可以根据上下文，从其它对应字段取得类型，比如union的其它表的。

验证环境

tt1表是一个id int类型的表，就这一个字段。

gbase> desc tt1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (Elapsed: 00:00:00.00)

tt2表是2个字段的表，多了一个varchar类型的name字段。

gbase> desc tt2;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (Elapsed: 00:00:00.00)

tt3也是2个字段，但name字段故意设置成bigint类型

gbase> create table tt3(id int, name bigint);
Query OK, 0 rows affected (Elapsed: 00:00:00.25)

gbase> insert into tt3 values(1,111);
Query OK, 1 row affected (Elapsed: 00:00:00.19)

不指定null常量类型，则默认为varbinary

如下的tt2表，用 null 生成name字段，因为没有指定类型，则默认是varbinary。

gbase> create table tt6 as select id,null name from tt1;
Query OK, 9 rows affected (Elapsed: 00:00:01.18)

gbase> desc tt6;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varbinary(1) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (Elapsed: 00:00:00.00)

通过cast强制指定null的类型

gbase> create table tt8 select id,cast(null as int) name from tt1;
Query OK, 9 rows affected (Elapsed: 00:00:00.56)

gbase> desc tt8;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| id    | int(11)   | YES  |     | NULL    |       |
| name  | bigint(1) | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
2 rows in set (Elapsed: 00:00:00.00)

2表union时其中一个包含了明确类型时可以正确识别

虽然带null常量的tt1在union的前面，但结果依然正确识别了类型为tt2的varchar。

gbase> create table tt4 as select id,null name from tt1 union all select * from tt2;
Query OK, 18 rows affected (Elapsed: 00:00:00.63)

gbase> desc tt4;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (Elapsed: 00:00:00.00)

多表union时对应列的类型必须一致

如果不一致，则会报错，比如如下第3个表tt3是bigint类型，第2个表tt2是varchar类型，语句报错。

gbase> create table tt7 as select id,null name from tt1 union select * from tt2 union all select * from tt3;
ERROR 1733 (HY000): (GBA-01EX-700) Gbase general error: UNION/INTERSECT/MINUS of non-matching columns: VARCHAR UNION/INTERSECT/MINUS LONGLONG

必须通过cast强制转化指定类型

gbase> create table tt7 as select id,null name from tt1 union select * from tt2 union all select id,cast(name as char) from tt3;
Query OK, 17 rows affected (Elapsed: 00:00:00.59)

gbase> desc tt7;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (Elapsed: 00:00:00.00)