操作符运算兼容
在将参数开关dolphin.b_compatibility_mode设置为on时,表示启用四则运算的MySQL兼容。相比于原始的openGauss,dolphin对于四则运算的修改主要为:
-
支持更多类型参与四则运算,具体如下:
- 数字类型:tinyint(unsigned)、smallint(unsigned)、integer(unsigned)、bigint(unsigned)、float4、float8、decimal/numeric以及bit。
- 字符串类型:char、varchar、binary、varbinary、tinyblob、blob、mediumblob、longblob、enum、set、json以及text(目前openGauss没有实际上的tinytext、mediumtext和longtext,因此不考虑)。
- 时间日期类型:date、datetime、timestamp、time、year、INTERVAL表达式。
-
一些原操作符返回值兼容MySQL,具体的兼容规则如下:
- 整型 X 整型:针对"+"、"-"、"*“这三个操作符,如果两个都是有符号的整型,则返回结果也是有符号的整型,否则返回无符号整型;针对”/",其返回值为定点型(numeric类型)。
- 整型 X 定点型:针对"+"、"-"、"*"、"/“四则运算,返回定点型。注:openGauss定点数未实现无符号,所以无符号整型参与运算返回的结果均为有符号的。
- 整型 X 浮点型:针对”+"、"-"、"*"、"/“四则运算,返回浮点型。注:openGauss浮点数未实现无符号,所以无符号整型参与运算返回的结果均为有符号的。
- 定点型 X 定点型:针对”+"、"-"、"*"、"/“四则运算,返回定点型。
- 定点型 X 浮点型:针对”+"、"-"、"*"、"/“四则运算,返回浮点型。
- 浮点型 X 浮点型:针对”+"、"-"、"*"、"/“四则运算,返回浮点型。
- 数字类型/字符串类型/时间日期类型 X INTERVAL表达式:针对”+"、"-“这两个运算符,返回TEXT类型。
基于上述规则,只需要运用字符串类型、时间类型等的类型转换规则,就可以推算出这些类型进行混合运算时的返回值,类型转换规则如下:
- 字符串类型:在进行四则运算是统一转换为浮点类型
- 时间日期类型:
- date + INTERVAL expr unit或date - INTERVAL expr unit分别是函数DATE_ADD(date, INTERVAL expr unit)或DATE_SUB(date, INTERVAL expr unit)的同义词。此外date + INTERVAL expr unit允许左右两边操作数交换而date - INTERVAL expr unit不允许交换。
- 当操作数中没有INTERVAL表达式时,date固定转换为有符号整型,year固定转换为无符号整型;针对datetime、timestamp、time三个类型,如果没有指定typmod(表示毫秒和微秒)的话,就转换为有符号整型,否则会转换为一个定点数,其小数位数与指定的typmod相同。
具体的一些表达式返回值可以参考下面的表格:
表达式 参数为on时 参数为off时 int1+int1 integer tinyint int1-int1 integer tinyint int1*int1 integer tinyint int1/int1 numeric double precision int1+uint1 uint4 uint1 int1-uint1 uint4 uint1 int1*uint1 uint4 uint1 int1/uint1 numeric double precision int1+int2 integer bigint int1-int2 integer bigint int1*int2 integer bigint int1/int2 numeric double precision int1+uint2 uint4 uint2 int1-uint2 uint4 uint2 int1*uint2 uint4 uint2 int1/uint2 numeric double precision int1/int4 numeric double precision int1+uint4 uint8 uint4 int1-uint4 uint8 uint4 int1*uint4 uint8 uint4 int1/uint4 numeric double precision int1/int8 numeric double precision int1/uint8 numeric double precision int1+bit1 uint4 double precision int1-bit1 uint4 double precision int1*bit1 uint4 double precision int1/bit1 numeric double precision int1+bit64 uint8 double precision int1-bit64 uint8 double precision int1*bit64 uint8 double precision int1/bit64 numeric double precision uint1+int1 uint4 uint1 uint1-int1 uint4 uint1 uint1*int1 uint4 uint1 uint1/int1 numeric double precision uint1+uint1 uint4 uint1 uint1-uint1 uint4 uint1 uint1*uint1 uint4 uint1 uint1/uint1 numeric double precision uint1+int2 uint4 uint2 uint1-int2 uint4 uint2 uint1*int2 uint4 uint2 uint1/int2 numeric double precision uint1+uint2 uint4 uint2 uint1-uint2 uint4 uint2 uint1*uint2 uint4 uint2 uint1/uint2 numeric double precision uint1+int4 uint8 uint4 uint1-int4 uint8 uint4 uint1*int4 uint8 uint4 uint1/int4 numeric double precision uint1+uint4 uint8 uint4 uint1-uint4 uint8 uint4 uint1*uint4 uint8 uint4 uint1/uint4 numeric double precision uint1/int8 numeric double precision uint1/uint8 numeric double precision uint1+bit1 uint4 uint8 uint1-bit1 uint4 uint8 uint1*bit1 uint4 uint8 uint1/bit1 numeric double precision uint1/bit64 numeric double precision int2+int1 integer bigint int2-int1 integer bigint int2*int1 integer bigint int2/int1 numeric double precision int2+uint1 uint4 uint2 int2-uint1 uint4 uint2 int2*uint1 uint4 uint2 int2/uint1 numeric double precision int2+int2 integer smallint int2-int2 integer smallint int2*int2 bigint smallint int2/int2 numeric double precision int2+uint2 uint4 uint2 int2-uint2 uint4 uint2 int2*uint2 uint8 uint2 int2/uint2 numeric double precision int2+int4 bigint integer int2-int4 bigint integer int2*int4 bigint integer int2/int4 numeric double precision int2+uint4 uint8 uint4 int2-uint4 uint8 uint4 int2*uint4 uint8 uint4 int2/uint4 numeric double precision int2/int8 numeric double precision int2/uint8 numeric double precision int2+bit1 uint4 double precision int2-bit1 uint4 double precision int2*bit1 uint4 double precision int2/bit1 numeric double precision int2+bit64 uint8 double precision int2-bit64 uint8 double precision int2*bit64 uint8 double precision int2/bit64 numeric double precision uint2+int1 uint4 uint2 uint2-int1 uint4 uint2 uint2*int1 uint4 uint2 uint2/int1 numeric double precision uint2+uint1 uint4 uint2 uint2-uint1 uint4 uint2 uint2*uint1 uint4 uint2 uint2/uint1 numeric double precision uint2+int2 uint4 uint2 uint2-int2 uint4 uint2 uint2*int2 uint8 uint2 uint2/int2 numeric double precision uint2+uint2 uint4 uint2 uint2-uint2 uint4 uint2 uint2*uint2 uint8 uint2 uint2/uint2 numeric double precision uint2+int4 uint8 uint4 uint2-int4 uint8 uint4 uint2*int4 uint8 uint4 uint2/int4 numeric double precision uint2+uint4 uint8 uint4 uint2-uint4 uint8 uint4 uint2*uint4 uint8 uint4 uint2/uint4 numeric double precision uint2/int8 numeric double precision uint2/uint8 numeric double precision uint2+bit1 uint4 uint8 uint2-bit1 uint4 uint8 uint2*bit1 uint4 uint8 uint2/bit1 numeric double precision uint2/bit64 numeric double precision int4/int1 numeric double precision int4+uint1 uint8 uint4 int4-uint1 uint8 uint4 int4*uint1 uint8 uint4 int4/uint1 numeric double precision int4+int2 bigint integer int4-int2 bigint integer int4*int2 bigint integer int4/int2 numeric double precision int4+uint2 uint8 uint4 int4-uint2 uint8 uint4 int4*uint2 uint8 uint4 int4/uint2 numeric double precision int4+int4 bigint integer int4-int4 bigint integer int4*int4 bigint integer int4/int4 numeric double precision int4+uint4 uint8 uint4 int4-uint4 uint8 uint4 int4*uint4 uint8 uint4 int4/uint4 numeric double precision int4/int8 numeric double precision int4/uint8 numeric double precision int4+bit1 uint8 uint4 int4-bit1 uint8 uint4 int4*bit1 uint8 uint4 int4/bit1 numeric double precision int4+bit64 uint8 uint4 int4-bit64 uint8 uint4 int4*bit64 uint8 uint4 int4/bit64 numeric double precision uint4+int1 uint8 uint4 uint4-int1 uint8 uint4 uint4*int1 uint8 uint4 uint4/int1 numeric double precision uint4+uint1 uint8 uint4 uint4-uint1 uint8 uint4 uint4*uint1 uint8 uint4 uint4/uint1 numeric double precision uint4+int2 uint8 uint4 uint4-int2 uint8 uint4 uint4*int2 uint8 uint4 uint4/int2 numeric double precision uint4+uint2 uint8 uint4 uint4-uint2 uint8 uint4 uint4*uint2 uint8 uint4 uint4/uint2 numeric double precision uint4+int4 uint8 uint4 uint4-int4 uint8 uint4 uint4*int4 uint8 uint4 uint4/int4 numeric double precision uint4+uint4 uint8 uint4 uint4-uint4 uint8 uint4 uint4*uint4 uint8 uint4 uint4/uint4 numeric double precision uint4/int8 numeric double precision uint4/uint8 numeric double precision uint4+bit1 uint8 uint4 uint4-bit1 uint8 uint4 uint4*bit1 uint8 uint4 uint4/bit1 numeric double precision uint4+bit64 uint8 uint4 uint4-bit64 uint8 uint4 uint4*bit64 uint8 uint4 uint4/bit64 numeric double precision int8/int1 numeric double precision int8/uint1 numeric double precision int8/int2 numeric double precision int8/uint2 numeric double precision int8/int4 numeric double precision int8/uint4 numeric double precision int8/int8 numeric double precision int8/uint8 numeric double precision int8/bit1 numeric double precision int8/bit64 numeric double precision uint8/int1 numeric double precision uint8/uint1 numeric double precision uint8/int2 numeric double precision uint8/uint2 numeric double precision uint8/int4 numeric double precision uint8/uint4 numeric double precision uint8/int8 numeric double precision uint8/uint8 numeric double precision uint8/bit1 numeric double precision uint8/bit64 numeric double precision float4+float4 double precision real float4-float4 double precision real float4*float4 double precision real float4/float4 double precision real bit1+int1 uint4 double precision bit1-int1 uint4 double precision bit1*int1 uint4 double precision bit1/int1 numeric double precision bit1+uint1 uint4 uint8 bit1-uint1 uint4 uint8 bit1*uint1 uint4 uint8 bit1/uint1 numeric double precision bit1+int2 uint4 double precision bit1-int2 uint4 double precision bit1*int2 uint4 double precision bit1/int2 numeric double precision bit1+uint2 uint4 uint8 bit1-uint2 uint4 uint8 bit1*uint2 uint4 uint8 bit1/uint2 numeric double precision bit1+int4 uint8 uint4 bit1-int4 uint8 uint4 bit1*int4 uint8 uint4 bit1/int4 numeric double precision bit1+uint4 uint8 uint4 bit1-uint4 uint8 uint4 bit1*uint4 uint8 uint4 bit1/uint4 numeric double precision bit1/int8 numeric double precision bit1/uint8 numeric double precision bit1+bit1 uint4 numeric bit1-bit1 uint4 numeric bit1*bit1 uint4 numeric bit1+bit64 uint8 numeric bit1-bit64 uint8 numeric bit1*bit64 uint8 numeric bit64+int1 uint8 double precision bit64-int1 uint8 double precision bit64*int1 uint8 double precision bit64/int1 numeric double precision bit64/uint1 numeric double precision bit64+int2 uint8 double precision bit64-int2 uint8 double precision bit64*int2 uint8 double precision bit64/int2 numeric double precision bit64/uint2 numeric double precision bit64+int4 uint8 uint4 bit64-int4 uint8 uint4 bit64*int4 uint8 uint4 bit64/int4 numeric double precision bit64+uint4 uint8 uint4 bit64-uint4 uint8 uint4 bit64*uint4 uint8 uint4 bit64/uint4 numeric double precision bit64/int8 numeric double precision bit64/uint8 numeric double precision bit64+bit64 uint8 numeric bit64-bit64 uint8 numeric bit64*bit64 uint8 numeric