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及实现兼容性管理

mysql=null的优雅解决方法

　　除此之外，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)