检测矛盾

我找到了我 2011 年的一些旧笔记，并且我还重新阅读了 @lukas-eder 的优秀文章10 Cool SQL Optimisations That do not Depending the Cost Model。所以我想我会再记笔记。场景很简单Fiddle

create table emp ( 
    emp_no int not null primary key, 
    title varchar(10) not null, 
    salary int not null, 
        check (emp_no > 0), 
        check (title in ('BOSS','WORK'))
);

insert into emp with recursive t (n) as (
    values (1) 
    union all 
    select n+1 from t where n+1 < 1000
) select n, case when mod(n,10) = 0 then 'BOSS' else 'WORK' end, 
            case when mod(n,10) = 0 then 110 else 0 end + mod(n,90) 
from t;

现在，给定规则：

 (TITLE = BOSS) implies (SALARY > 100)

和

 (TITLE = WORK) implies (SALARY <= 100)

可以实现为：

-- (TITLE = BOSS) => (SALARY > 100)
alter table emp add constraint cc1 
    CHECK ( (title <> 'BOSS' OR salary > 100) );
-- (TITLE = WORK) => (SALARY <= 100)
alter table emp add constraint cc2
    CHECK ( (title <> 'WORK' OR salary <= 100) );

和查询：

select * 
from emp 
where title = 'BOSS' and salary <= 100

DBMS 能否发现矛盾，并在不接触数据的情况下返回空结果集？

Let A = ( title = ’BOSS’ ), B = ( salary > 100 )

select * from emp where (A ^ ~B)                    
select * from emp where (A ^ ~B) ^ (~A v B)           # by cc1 
select * from emp where (A ^ ~B ^ ~A) v (A ^ ~B ^ B)    
select * from emp where (FALSE ^ ~B) v (A ^ FALSE)      
select * from emp where (FALSE) v (FALSE)           
select * from emp where (FALSE)

我试过 Postgres 13（见上面的小提琴）

Seq Scan on emp  (cost=0.00..26.50 rows=2 width=46) (actual time=0.134..0.134 rows=0 loops=1)
  Filter: ((salary <= 100) AND ((title)::text = 'BOSS'::text))
  Rows Removed by Filter: 999
Planning Time: 0.312 ms
Execution Time: 0.149 ms

和 DB2 11.5.4.0：

Optimized Statement:
-------------------
SELECT 
  Q1.EMP_NO AS "EMP_NO",
  Q1.TITLE AS "TITLE",
  Q1.SALARY AS "SALARY" 
FROM 
  DB2INST1.EMP AS Q1 
WHERE 
  (Q1.SALARY <= 100) AND 
  (Q1.TITLE = 'BOSS')

Access Plan:
-----------
        Total Cost:             51.8267
        Query Degree:           1


      Rows 
     RETURN
     (   1)
      Cost 
       I/O 
       |
     90.2441 
     TBSCAN
     (   2)
     51.8267 
        4 
       |
       999 
 TABLE: DB2INST1
       EMP
       Q1

但两者都没有这样做。任何其他可以发现矛盾并采取行动的 DBMS？这当然比现实世界的问题更有趣，但仍然如此。

编辑：@federico-razzoli 在他的回答中建议的约束也不起作用：

alter table emp add constraint cc1
    check ((title = 'BOSS' and salary > 100) or 
           (title = 'WORK' AND salary <= 100));

并且相同的查询仍然会导致表访问

 Rows 
 RETURN
 (   1)
  Cost 
   I/O 
   |
 90.2441 
 TBSCAN
 (   2)
 51.8267 
    4 
   |
   999 

表：DB2INST1 EMP Q1