Oracle 模式下 NULL 与 DATE 运算报错，类型不一致

```Shell
SQL> CREATE TABLE t1 (c1 date); 
Table created. 
```

```shell
SQL> create table t2 (c1 number);
Table created
```

```shell
SQL> select * from t1;
 no rows selected
```

```shell
SQL> declare  
    fee1 date;
    fee2 number(14, 2);
    begin
    select min(c1) into fee1 from t1;
    insert into t2 values(fee1 - to_date('2020-01-01', 'yyyy-mm-dd'));
    commit;
    end;
    /
PL/SQL procedure successfully completed.
```

```shell
SQL> select* from t2;
```

输出结果如下。

```shell
C1
----------
1 rows selected
```

```shell
SQL> declare  
    fee1 date;
    fee2 number(14, 2);
    begin
    select min(c1) into fee1 from t1;
    insert into t2 values(fee1 - to_date('2020-01-01', 'yyyy-mm-dd'));
    commit;
    end;
    /
PL/SQL procedure successfully completed.

4. 查询 t2 表。

 ```shell
 SQL> select * from t2;
 ```

 输出结果如下。

 ```shell  
 C1
 ----------
 1307.41314
 2 rows selected.

```Shell
obclient> CREATE TABLE t1 (c1 date); 
Query OK, 0 rows affected (0.668 sec)
```

```shell
obclient> create table t2(c1 number);
Query OK, 0 rows affected (0.172 sec)
```

```shell
obclient >  select *from t1;
Empty set (0.004 sec)
```

```shell
obclient > declare  
        fee1 date;
        fee2 number(14, 2);
        begin
        select min(c1) into fee1 from t1;
        insert into t2 values(fee1 - to_date('2020-01-01', 'yyyy-mm-dd'));
        commit;
        end;
        /
```

 输出结果如下。

 ```shell
 ORA-00932: inconsistent datatypes
 at anonymous block , line : 6, col : 3
 ```

```shell
obclient [CHUER]> select*from t2;
Empty set (0.040 sec)
```

```shell
obclient >  insert into t1 values(sysdate);
Query OK, 1 row affected (0.059 sec)
```

提交插入数据。

```shell
obclient > commit;
Query OK, 0 rows affected (0.005 sec)
```

obclient > select*from t1;

 +-----------+
 | C1        |
 +-----------+
 | 31-JUL-23 |
 +-----------+
 1 row in set (0.004 sec)

```shell
 obclient > declare  
        fee1 date;
        fee2 number(14, 2);
        begin
        select min(c1) into fee1 from t1;
        insert into t2 values(fee1 - to_date('2020-01-01', 'yyyy-mm-dd'));
        commit;
        end;
        /
Query OK, 1 row affected (0.112 sec)

4. 查询 t2 表。

  ```shell
  obclient > select * from t2;
  ```

   输出结果如下。

 ```shell
 +-------------------------------------------+
 | C1                                        |
 +-------------------------------------------+
 | 1307.410474537037037037037037037037037037 |
 +-------------------------------------------+
 1 row in set (0.026 sec)
 ```

## 适用版本

OceanBase V2.x 和 V3.x 版本。

## 问题原因

这个上面的例子在 OceanBase 数据库中会报错，insert 中的 fee1 会被抽为参数 0，它的值是 NULL，减法类型推导时，NULL类型 - DATE类型预期会报错。

在 Oracle 中不会报错，Oracle 中会用 fee1 的静态类型 date 进行类型推导。

## 解决方法

暂时没有好的解决方式。如果允许 null 和 date 做运算，会影响执行计划评估。ps 中参数是 NULL 和不是 NULL 的时候会命中不同的计划。