更新时间:2022-07-29 GMT+08:00

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类型。

指定兼容模式创建数据库ora_db、td_db、mysql_db。

1
2
3
CREATE DATABASE ora_db dbcompatibility = 'ORA';
CREATE DATABASE td_db dbcompatibility = 'TD';
CREATE DATABASE mysql_db dbcompatibility = 'MySQL';
  • 切换数据库为ora_db。
    1
    postgres=# \c ora_db
    

    创建表t1,并查看coalesce参数输入int和varchar类型的查询语句的执行计划。

    1
    2
    3
    4
    ora_db=# CREATE TABLE t1(a int, b varchar(10));      
    ora_db=# EXPLAIN SELECT coalesce(a, b) FROM t1;
    ERROR:  COALESCE types integer and character varying cannot be matched
    CONTEXT:  referenced column: coalesce
    
  • 切换数据库为td_db。
    1
    ora_db=# \c td_db
    

    创建表t2,并查看coalesce参数输入int和varchar类型的查询语句的执行计划。

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    td_db=# CREATE TABLE t2(a int, b varchar(10));
    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)
    
  • 切换数据库为mysql_db。
    1
    td_db=# \c mysql_db
    

    创建表t3,并查看coalesce参数输入int和varchar类型的查询语句的执行计划。

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    mysql_db=# CREATE TABLE t3(a int, b varchar(10));
    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)
    
  • 切换数据库为postgres
    1
    mysql_db=# \c postgres