postgresql与lightdb中的null行为及oracle、mysql的兼容性
null首先跟char、varchar、bpchar（blank padded character）、text有关系。在oracle中，则与char、varchar2、clob有关。
其次，涉及的范围比较广，''、null、=、!=、is null、is not null、替换、计算长度、类型强转如cast(null as date)，以及它们之间的组合。它又涉及到transform_null_equals参数的影响。
zjh@postgres=# select null = ''; ?column? ---------- f (1 row) zjh@postgres=# select null = null; # transform_null_equals=on ?column? ---------- t (1 row)
zjh@postgres=# set transform_null_equals=off;
SET
zjh@postgres=# select null=null;
?column?
----------
(1 row)
zjh@postgres=# select null is null; ?column? ---------- t (1 row)
zjh@postgres=# select '' is null;
?column?
----------
f
(1 row)
zjh@postgres=# select '' = ''; ?column? ---------- t (1 row) zjh@postgres=# select '' = ' '; ?column? ---------- f (1 row) zjh@postgres=# select '' = ' '::bpchar; ?column? ---------- t (1 row) zjh@postgres=# select '' = ' '::char; ?column? ---------- t (1 row) zjh@postgres=# select '' = ' '::varchar; ?column? ---------- f (1 row)
zjh@postgres=# select '' = ' '::text; ?column? ---------- f (1 row) zjh@postgres=# select '' = trim(' ')::text; ?column? ---------- t (1 row) zjh@postgres=# select '' = trim(' '::text); ?column? ---------- t (1 row)
zjh@postgres=# select 1 from t where '' = null; ?column? ---------- (0 rows) zjh@postgres=# select sum(id) from t where '' = null; sum ----- (1 row) zjh@postgres=# select max(id) from t where '' = null; max ----- (1 row)
zjh@postgres=# select 'abc' = 'abc '::text; ?column? ---------- f (1 row) zjh@postgres=# select 'abc' = 'abc '::bpchar; ?column? ---------- t (1 row) zjh@postgres=# select 'abc' = ' abc'::bpchar; ?column? ---------- f (1 row) zjh@postgres=# select 'abc' = ' abc'::varchar; ?column? ---------- f (1 row) zjh@postgres=# select 'abc' = ' abc'::text; ?column? ---------- f (1 row)
zjh@postgres=# select trim('abc ') = 'abc '::bpchar; ?column? ---------- t (1 row)
zjh@postgres=# select replace('12345','4',''); replace --------- 1235 (1 row) zjh@postgres=# select replace('12345','4',null); replace --------- (1 row) zjh@postgres=# zjh@postgres=# select replace('12345',null,null); replace --------- (1 row) zjh@postgres=# select replace('12345','',''); replace --------- 12345 (1 row)
zjh@postgres=# select length(null); length -------- (1 row) zjh@postgres=# select length(''); length -------- 0 (1 row)
zjh@postgres=# select 1 from t where cast('' AS DATE) is null; ERROR: invalid input syntax for type date: "" LINE 1: select 1 from t where cast('' AS DATE) is null; ^ zjh@postgres=# select 1 from t where cast(null AS DATE) is null; ?column? ---------- 1 (1 row) zjh@postgres=# select 1 from t where cast('' AS bpchar(10)) is null; ?column? ---------- (0 rows) zjh@postgres=# select 1,cast('' AS bpchar(10)) from t; ?column? | bpchar ----------+------------ 1 | (1 row) zjh@postgres=# select 1,length(cast('' AS bpchar(10))) from t; ?column? | length ----------+-------- 1 | 0 (1 row) zjh@postgres=# select 1,length(cast(null AS bpchar(10))) from t; ?column? | length ----------+-------- 1 | (1 row)
-- 虽然pg中null和''都被诊断为unkown，但是内部处理貌似仍然不同 zjh@postgres=# select pg_typeof(''); pg_typeof ----------- unknown (1 row) zjh@postgres=# select pg_typeof(null); pg_typeof ----------- unknown (1 row)
======================下面是不等于=====================
https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL
https://linuxhint.com/bpchar-data-type-postgres/
https://www.postgresql.org/docs/current/datatype-character.html
https://www.geeksforgeeks.org/postgresql-difference-between-char-varchar-and-text/
oracle中null的行为
SQL> select 1 from dual where '' = ''; no rows selected SQL> select 1 from dual where null = null; no rows selected SQL> select 1 from dual where null is null; 1 ---------- 1 SQL> select 1 from dual where '' is null; 1 ---------- 1
SQL> select sum(id) from t_null where '' = null; SUM(ID) ---------- SQL> select * from t_null where '' = null; no rows selected SQL> select max(id) from t_null where '' = null; MAX(ID) ---------- SQL> select count(id) from t_null where '' = null; COUNT(ID) ---------- 0
SQL> select replace('12345','4','') from dual; REPL ---- 1235 SQL> select replace('12345','4',null) from dual; REPL ---- 1235 SQL> select replace('12345',null,null) from dual; REPLA ----- 12345 SQL> select replace('12345','','') from dual; REPLA ----- 12345
SQL> select length('') from dual; LENGTH('') ---------- SQL> select length(null) from dual; LENGTH(NULL) ------------ SQL>
SQL> select 1 from t where cast('' AS DATE) is null; 1 ---------- 1 SQL> select 1 from t where cast(null AS DATE) is null; 1 ---------- 1 SQL> select 1 from t where cast('' AS char(10)) is null; 1 ---------- 1 SQL> select 1,cast('' AS char(10)) from t; 1 CAST(''ASC ---------- ---------- 1 SQL> select 1,length(cast('' AS char(10))) from t; 1 LENGTH(CAST(''ASCHAR(10))) ---------- -------------------------- 1 SQL> select 1,length(cast(null AS char(10))) from t; 1 LENGTH(CAST(NULLASCHAR(10))) ---------- ---------------------------- 1
==============================下面不等于===================
https://community.oracle.com/tech/developers/discussion/1053012/difference-between-null-and
https://cloud.tencent.com/developer/article/1052571
https://blog.csdn.net/liangmengbk/article/details/124211692
https://blog.csdn.net/Beijing_L/article/details/122619636
https://blog.csdn.net/longcccvv/article/details/54376015
https://blog.csdn.net/weixin_34536454/article/details/116312493
论lightdb/postgresql中的search_path及实现兼容性管理
除此之外，null的行为还受到参数standard_conforming_strings的影响。
总结
null代表未知，''实际上代表空。不应该相同才对。
- 在原生pg中，''和null的实现是不一样的，虽然都是unknown，但是内部仍然不是相同的，但是‘’ = ‘’成立。在lightdb的oracle兼容模式下，'' is null成立。
- 在oracle里面，除了存储层面（包括PL/SQL）的char类型外，可以认为字符串中''几乎就是当做null来处理的，''可以当做null用在其它类型（如SELECT * FROM t WHERE to_number('') IS null;是成立的，在其它数据库中如pg的orafce中就会报错ERROR: invalid input syntax for type numeric: ""），is null/is not null都成立。但是!=/=/in/not in操作外（因为判断null是否成立只能用操作符 is null。而本质上in、not in也是调用=/!=操作符实现的），导致了'' = ''，oracle也是不成立的。
- mysql方面，null =''不成立，'' is null也不成立。如果pg transform_null_equals=off（也是默认值），和mysql/oracle行为相同，也就是null不等于null，必须null is null，但是‘’ = ‘’成立。如果=on，则多了(null = null) == true（是把双刃剑）。
所以，lightdb兼容null的规则为：
- 如果db在oracle模式下，不管transform_null_equals如何设置，都会解析为off，强制 null = null不成立，'' = ''不会成立，'' is null成立。当前'' is null仅限于字符串体系。
- 如果db在mysql模式下，不管transform_null_equals如何设置，都会解析为off，强制 null = null不成立。
- 如果db在pg模式下，遵从transform_null_equals的设置。
在一个lightdb实例中。对于下列数据： create table t(id text, v text); insert into t values(‘’,’v1’),(null,‘v2’); 在oracle模式下： zjh@oracle_db=# select * from t where id = ''; id | v ----+--- (0 rows) zjh@oracle_db=# select * from t where id = null; id | v ----+--- (0 rows) zjh@oracle_db=# select * from t where id is null; id | v ----+---- | v1 | v2 (2 rows) 在mysql模式下： zjh@mysql_db=# select * from t where id = ''; id | v ----+---- | v1
| v2 (2 row) zjh@mysql_db=# select * from t where id = null; id | v ----+----
| v1
| v2 (2 row)
