GBASE 8C——SQL参考3 类型转换

1. 1. 类型转换

SQL语句可能会在同一表达式中混合不同的数据类型。在大多数情况下，用户不需要了解类型转换机制的细节，但是数据库进行的隐式类型转换可能会影响查询的结果，可能会需要显式的类型转换进行调整。

本章介绍GBase 8c类型转换的机制和习惯。

1. 1. 1. 概述

SQL是一种强类型语言，即每个数据项都有一个相关的数据类型，数据类型决定其行为和允许的用法。GBase 8c提供一种可扩展的通用规则来管理类型转换，这种做法允许使用混合类型的表达式。

语法解析器将语法元素分解为五个基本种类：整数、非整数数字、字符串、标识符、关键字。大多数非数字类型常量首先被分类为字符串，例如查询：

SELECT text 'Origin' AS "label", point '(0,0)' AS "value";

 label  | value

--------+-------

 Origin | (0,0)

(1 row)

文字常量text和point没有被指定类型，初始会分别分配一个占位符类型unknown。

在SQL解析器里，有四种基本的SQL结构有独立的类型转换规则：

1. 函数调用：PostgreSQL允许函数重载，函数名本身并不唯一的标识将要被调用的函数，解析器必须根据提供的参数类型选择正确的的函数。
2. 操作符：PostgreSQL允许带有前缀和后缀的单参数（一元）操作符表达式，也允许两个参数（二元）操作符。像函数一样，操作符也可以被重载，因此解析器必须根据提供的参数类型选择正确的操作符。
3. 值存储：INSERT和UPDATE语句将表达式的结果插入表中，语句中表达式类型必须和目标列的类型一致（或可以被转换为一致）。
4. UNION、CASE和相关结构：因为一个UNION的SELECT语句中的所有查询结果必须在一个列集中显示，所以每个SELECT子句的结果类型必须能项目匹配并被转换成一个统一的集合。CASE、ARRAY、GREATEST和LEAST函数要求与UNION相同。

关于哪些数据类型之间存在哪种转换类型以及如何执行这些转换的信息，都存储在系统目录中。其他转换用户可以通过CREATE CAST命令增加。

解析器负责在具有隐式转换的类型组中对其类型进行恰当的隐式转换。数据类型可以分为以下几个基本的类型分类：boolean、numeric、string、bitstring、datetime、timespan、geometric、network和用户自定义。每个分类中，可以有一个或多个首选类型。

类型转换的基本规则：

1. 隐式转换不能有意外或不可预见的输出
2. 不需要隐式转换的查询，解析器不能有额外的开销。
3. 如果查询通常要求为某个函数进行隐式类型转换，而用户定义了一个有正确参数类型的新函数，解析器应使用新函数而不再做隐式转换来使用旧函数。
   1. 1. 操作符

操作符的类型转换规则和优先级如下：

1. 从系统目录pg_operator中选出要考虑的操作符：如果是不带模式限定的操作符，则该操作符被认定为在当前搜索路径中可见并有匹配名字和参数个数的操作符（如果有多个匹配，则使用第一个）；如果是带模式限定的操作符，则以模式限定为准。
2. 查找一个正好接受输入参数类型的操作符：
   1. 如果二元操作符调用中有一个参数是unknown类型，则默认其与另外一个参数类型相同；如果全部为unknown类型，则将无法找到匹配；
   2. 如果二元操作符调用中一个参数时unknown类型而另一个是域类型，下一次检查会查看是否有操作符可以两边都接受该域类型，如果有则使用它。
3. 寻找最优匹配：
   1. 将输入类型不匹配且不能转换为匹配类型的操作符抛弃；
   2. 如果输入的参数时域类型，则后续所有步骤都为该域的基类型；
   3. 遍历所有候选操作符，保留在输入类型上匹配最准确的；如果没有操作符能准确匹配，则保留所有候选；如果只能下一个候选操作符，则直接使用，否则继续下一步；
   4. 遍历所有候选操作符，保留在最多个需要类型转换的位置上接受首选类型的操作符；
   5. 如果有任何输入参数是unknown类型，检查被剩余候选操作符在那些参数位置上接受的类型分类；
   6. 如果既有unknown参数也有已知类型的参数，并且所有已知类型参数具有相同的类型，则假定该unknown参数也是那种类型的，并且检查哪些候选操作符可以在该unknown参数的位置上接受那个类型。如果正好有一个候选者通过了这个测试，则使用之；否则失败。
      1. 函数

函数类型转换规则和优先级如下：

1. 从系统表pg_proc中选择所有可能被选到的函数。如果使用了一个不带模式修饰的函数名字，那么认为该函数是那些在当前搜索路径中的函数。如果给出一个带修饰的函数名，那么只考虑指定模式中的函数。

1. 如果搜索路径中找到了多个不同参数类型的函数，将选择搜索路径中最早出现的函数；

1. 查找和输入参数类型完全匹配的函数。如果找到一个，则用之。如果输入的实参类型都是unknown类型，则不会找到匹配的函数。
2. 如果未找到完全匹配，请查看该函数是否为一个特殊的类型转换函数。
3. 寻找最优匹配。

1. 抛弃那些输入类型不匹配并且也不能隐式转换成匹配的候选函数。unknown文本在这种情况下可以转换成任何东西。如果只剩下一个候选项，则用之，否则继续下一步。
2. 如果输入时域类型，则在后续所有步骤中都将其作为该域的基本类型。
3. 遍历所有候选函数，保留那些输入类型匹配最准确的。此时，如果没有一个函数能准确匹配，则保留所有候选。如果只剩下一个候选项，则用之，否则继续下一步。
4. 遍历所有候选函数，保留那些需要类型转换时接受首选类型位置最多的函数。如果没有接受首选类型的函数，则保留所有候选。如果只剩下一个候选项，则用之，否则继续下一步。
5. 如果有任何输入参数是unknown类型，检查剩余的候选函数对应参数位置的类型范畴。在每一个能够接受字符串类型范畴的位置使用string类型（这种对字符串的偏爱是合适的，因为unknown文本确实像字符串）。另外，如果所有剩下的候选函数都接受相同的类型范畴，则选择该类型范畴，否则抛出一个错误（因为在没有更多线索的条件下无法作出正确的选择）。现在抛弃不接受选定的类型范畴的候选函数，然后，如果任意候选函数在那个范畴接受一个首选类型，则抛弃那些在该参数位置接受非首选类型的候选函数。如果没有一个候选符合这些测试则保留所有候选。如果只有一个候选函数符合，则使用它；否则，继续下一步。
6. 如果同时有unknown和已知类型的参数，并且所有已知类型的参数有相同的类型，假设unknown参数也是这种类型，检查哪个候选函数可以在unknown参数位置接受这种类型。如果正好一个候选符合，那么使用它。否则，产生一个错误。
   1. 1. 值存储

1. 查找与目标字段准确的匹配。
2. 试着将表达式直接转换成目标类型。如果已知这两种类型之间存在一个已注册的转换函数，那么直接调用该转换函数即可。如果表达式是一个未知类型文本，该文本字符串的内容将交给目标类型的输入转换过程。
3. 检查一下看目标类型是否有长度转换。长度转换是一个从某类型到自身的转换。如果在pg_cast表里面找到一个，那么在存储到目标字段之前先在表达式上应用。这样的转换函数总是接受一个额外的类型为integer的参数，它接收目标字段的atttypmod值（实际上是其声明长度，atttypmod的解释随不同的数据类型而不同），并且它可能接受一个boolean类型的第三个参数，表示转换是显式的还是隐式的。转换函数负责施加那些长度相关的语义，比如长度检查或者截断。
   1. 1. UNION、CASE和相关结构

SQL UNION构造必须把那些可能不太相似的类型匹配起来成为一个结果集。解析算法分别应用于联合查询的每个输出字段。INTERSECT和EXCEPT构造对不相同的类型使用和UNION相同的算法进行解析。CASE、ARRAY、VALUES、GREATEST和LEAST构造也使用同样的算法匹配它的部件表达式并且选择一个结果数据类型。

1. UNION，CASE 和相关构造解析：

1. 如果所有输入都是相同的类型，并且不是unknown类型，那么解析成这种类型。
2. 如果所有输入都是域类型，则所有后续步骤中都将其当做该域的基本类型。
3. 如果所有输入都是unknown类型则解析成text类型（字符串类型范畴的首选类型）。否则，忽略unknown输入。
4. 如果输入不属于同一个类型范畴，失败。（unknown类型除外）
5. 如果有的话，选择第一个在其分类中作为首选类型的非位置输入类型。
6. 否则，选择最后的非未知输入类型，其允许所有在前面的非位置输入被隐式转换为该类型。
7. 把所有输入转换为所选的类型（对于字符串保持原有长度）。如果从给定的输入到所选的类型没有隐式转换则失败。
   1. 1. SELECT 输出列

前面几节中的规则将导致SQL查询中所有表达式被分配非unknown数据类型。但是将未指定类型的文字显示为SELECT的简单输出列时，数据库会将文本类型解析为text。

例如：

SELECT 'Hello World';