-- @owner: li-xin12345
-- @date: 2024/10/17
-- @testpoint: is [not] nan入参_数值类型字符串_转换数值后在float8范围内

@conn PrimaryDbAmode;

--step1: 准备数据;expect: 成功
drop table if exists t_is_nan_0004;
create table t_is_nan_0004(v1 text, v2 varchar(100));
insert into t_is_nan_0004 values ('-3.402E+38', 'test');
insert into t_is_nan_0004 values ('3.402E+38', 'test');
insert into t_is_nan_0004 values ('1.79E+308', 'test');
insert into t_is_nan_0004 values ('-1.79E+308', '666');

--step2:作为select字段使用;expect:根据转换后的数值判断，成功
select (v1+'1') is nan from t_is_nan_0004;
select (v1+'1') is not nan from t_is_nan_0004;
select v1 is nan from t_is_nan_0004;
select v1 is not nan from t_is_nan_0004;
select v1 is not nan as test from t_is_nan_0004;

--step3:作为where条件使用;expect:根据转换后的数值判断，成功
select * from t_is_nan_0004 where v1 is nan;
select * from t_is_nan_0004 where v1 is nan or v2 = 'test';
select * from t_is_nan_0004 where v1 is not nan;
select * from t_is_nan_0004 where v1 is not nan and v2 = 'test';

--step4:既做select字段，又做where条件使用;expect:根据转换后的数值判断，成功
select v1 is nan as test from t_is_nan_0004 where v1 is nan;
select v1 is nan from t_is_nan_0004 where v1 is not nan;
select v1 is not nan from t_is_nan_0004 where v1 is nan;
select v1 is not nan from t_is_nan_0004 where v1 is not nan;

--step5:清理环境;expect:成功
drop table if exists t_is_nan_0004;