文档首页/ 数据仓库服务 GaussDB(DWS)/ SQL语法参考/ SQL语法参考(9.1.0.x)/ 类型转换/ UNION，CASE和相关构造
更新时间：2024-09-02 GMT+08:00
查看PDF
分享

UNION，CASE和相关构造

SQL UNION构造把不相同的数据类型进行匹配输出为统一的数据类型结果集。因为SELECT UNION语句中的所有查询结果必须在一列里显示出来，所以每个SELECT子句中的元素类型必须相互匹配并转换成一个统一的数据类型。类似地，一个CASE构造的结果表达式必须转换成统一的类型，这样整个case表达式会有一个统一的输出类型。同样的要求也存在于ARRAY构造以及GREATEST和LEAST函数中。

UNION，CASE和相关构造解析

  • 如果所有输入都是相同的数据类型，不包括unknown类型（即输入的字符串文本未声明类型，该文本首先被定义成一个未知类型），那么解析成所输入的相同数据类型。
  • 如果所有输入都是unknown类型则解析成text类型（字符串类型范畴的首选类型）。否则，忽略unknown输入。
  • 如果输入不属于同一个类型范畴，查询失败（unknown类型除外）。
  • 如果输入类型是同一个类型范畴，则选择该类型范畴的首选类型（union操作会选择第一个分支的类型作为所选类型的情况除外）。

    系统表pg_type中typcategory表示数据类型范畴， typispreferred表示是否是typcategory分类中的首选类型。

  • 把所有输入转换为所选的类型（对于字符串保持原有长度）。如果从给定的输入到所选的类型没有隐式转换则失败。
  • 若输入中含json、txid_snapshot、sys_refcursor或几何类型，则不能进行union。

对于CASE、COALESCE、IF和IFNULL，在TD兼容模式下的处理

  • 如果所有输入都是相同的数据类型，不包括unknown类型，那么解析成所输入的相同数据类型。
  • 如果所有输入都是unknown类型则解析成text类型。
  • 如果输入字符串（包括unknown，unknown当text来处理）和数字类型，那么解析成字符串类型，如果是其他不同的类型范畴，则报错。
  • 如果输入类型是同一个类型范畴，则选择该类型的优先级较高的类型。
  • 把所有输入转换为所选的类型。如果从给定的输入到所选的类型没有隐式转换则失败。

对于CASE、COALESCE、IF和IFNULL，在MySQL兼容模式下的处理

  • 如果所有输入都是相同的类型，不包括unknown类型，那么解析成所输入的相同数据类型。
  • 如果所有输入都是unknown类型则解析成text类型。
  • 如果输入是unknown类型和某一非unknown类型，则解析成该非unknown类型。
  • 如果存在多种非unknown类型，将enum类型当做text类型，再进行比较。
  • 如果输入类型是同一个类型范畴，则选择该类型的优先级较高的类型。如果是不同的类型范畴，则解析成text类型。
  • 把所有输入转换为所选的类型。如果从给定的输入到所选的类型没有隐式转换则失败。

示例

示例1：Union中的未知类型解析。示例中未知类型文本'b'将被解析成text类型。

1
2
3
4
5
6
 
SELECT text 'a' AS "text" UNION SELECT 'b';
 text
------
 a
 b
(2 rows)

示例2：简单Union中的类型解析。文本1.2是numeric类型，且integer类型值1可以隐式地转换为numeric，因此使用numeric类型。

1
2
3
4
5
6
 
SELECT 1.2 AS "numeric" UNION SELECT 1;
 numeric
---------
       1
     1.2
(2 rows)

示例3：转换Union中的类型解析。示例中由于类型real不能被隐式地转换为integer，而integer可以隐式地转换成real类型，那么联合结果类型被系统决定为real。

1
2
3
4
5
6
 
SELECT 1 AS "real" UNION SELECT CAST('2.2' AS REAL);
 real
------
    1
  2.2
(2 rows)

示例4：COALESCE函数输入int和varchar类型，ORA模式下会报错，TD模式下解析为varchar类型，MySQL模式下解析为text类型。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
 
--指定兼容模式创建数据库ora_db、td_db、mysql_db。
CREATE DATABASE ora_db dbcompatibility = 'ORA';
CREATE DATABASE td_db dbcompatibility = 'TD';
CREATE DATABASE mysql_db dbcompatibility = 'MySQL';

--切换数据库为ora_db。
\c ora_db

--创建表t1。
ora_db=# CREATE TABLE t1(a int, b varchar(10));

--查看coalesce参数输入int和varchar类型的查询语句的执行计划。                 
ora_db=# EXPLAIN SELECT coalesce(a, b) FROM t1;
ERROR:  COALESCE types integer and character varying cannot be matched
CONTEXT:  referenced column: coalesce

--删除表。
ora_db=# DROP TABLE t1;

--切换数据库为td_db。
ora_db=# \c td_db

--创建表t2。
td_db=# CREATE TABLE t2(a int, b varchar(10));

--查看coalesce参数输入int和varchar类型的查询语句的执行计划。
td_db=# EXPLAIN VERBOSE select coalesce(a, b) from t2;
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
  id |                  operation                   | E-rows | E-distinct | E-width | E-costs
 ----+----------------------------------------------+--------+------------+---------+---------
   1 | ->  Data Node Scan on "__REMOTE_FQS_QUERY__" |      0 |            |       0 | 0.00

                       Targetlist Information (identified by plan id)
 -------------------------------------------------------------------------------------------
   1 --Data Node Scan on "__REMOTE_FQS_QUERY__"
         Output: (COALESCE((t2.a)::character varying, t2.b))
         Node/s: All datanodes
         Remote query: SELECT COALESCE(a::character varying, b) AS "coalesce" FROM public.t2
(10 rows)

--删除表。
td_db=# DROP TABLE t2;

--切换数据库为mysql_db。
td_db=# \c mysql_db

--创建表t3。
mysql_db=# CREATE TABLE t3(a int, b varchar(10));

--查看coalesce参数输入int和varchar类型的查询语句的执行计划。
mysql_db=# EXPLAIN VERBOSE select coalesce(a, b) from t3;
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
  id |                  operation                   | E-rows | E-distinct | E-width | E-costs
 ----+----------------------------------------------+--------+------------+---------+---------
   1 | ->  Data Node Scan on "__REMOTE_FQS_QUERY__" |      0 |            |       0 | 0.00

                    Targetlist Information (identified by plan id)
 ------------------------------------------------------------------------------------
   1 --Data Node Scan on "__REMOTE_FQS_QUERY__"
         Output: (COALESCE((t3.a)::text, (t3.b)::text))
         Node/s: All datanodes
         Remote query: SELECT COALESCE(a::text, b::text) AS "coalesce" FROM public.t3
(10 rows)

--删除表。
mysql_db=# DROP TABLE t3;

--切换数据库为gaussdbgaussdb
mysql_db=# \c gaussdbgaussdb

--删除数据库。
DROP DATABASE ora_db;
DROP DATABASE td_db;
DROP DATABASE mysql_db;
父主题： 类型转换