示例 示例1:所有参数为同一类型,expr 不在min 和max 中。 gbase> SELECT 1 BETWEEN 2 AND 3 FROM t; +-------------------+ | 1 BETWEEN 2 AND 3 | +-------------------+ | 0 | +-------------------+ 1 row in set 示例2:所有参数为同一类型,expr 在min 和max 中。 gbase> SELECT 'b' BETWEEN 'a' AND 'c' FROM t; +-------------------------+ | 'b' BETWEEN 'a' AND 'c' | +-------------------------+ | 1 | +-------------------------+ 1 row in set 示例3:参数中包含数字和字符串。 gbase> SELECT 2 BETWEEN 2 AND '3' FROM t; +---------------------+ | 2 BETWEEN 2 AND '3' | +---------------------+ | 1 | +---------------------+ 1 row in set 示例4:针对不同类型参数,HIVE 引擎返回结果会有所不同。例如: gbase> SELECT 'b' BETWEEN 'a' AND 1 FROM test.t8a;--8a返回1
GBase UP 产品手册 5 数据库管理指南 文档版本04(2021-04-21) 南大通用数据技术股份有限公司 371 +-----------------------+ | 'b' BETWEEN 'a' AND 1 | +-----------------------+ | 1 | warnings, truncated incorrect DOUBLE value: 'b'
gbase> SELECT 'b' BETWEEN 'a' AND 1 FROM test_hive.tuph;--UP HIVE 返回NULL +-----------------------+ | 'b' BETWEEN 'a' AND 1 | +-----------------------+ | NULL | 原因:gbase 有隐式类型转换 select cast('a' as integer) ; -- 返回 0 select cast('b' as integer) ; --返回0 SELECT 'b' BETWEEN 'a' AND 1 FROM test.t8a; 相当于: select 0 between 0 and 1 from test.t8a; 所以返回 1。