檢測矛盾

我發現了我 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

以下是一些 DBMS 如何處理問題中的約束的總結。還測試了其他 3 個查詢：

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

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

3.  select * 
   from emp 
   where title = 'BOSS' and salary <= 100 
     and ( NOT (title = 'BOSS' AND salary <= 100) )

4. select *  
  from emp 
  where title = 'BOSS' and salary <= 100 
    and ( (title <> 'BOSS' OR salary > 100) )

Db2 V11.5.4.0

未能辨識 q1 中的矛盾，成功辨識 q2、q3、q4

db2 "explain plan for select ..."
db2exfmt -d nya -1
[...]

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

2,3,4.

Optimized Statement:
-------------------
SELECT 
 NULL AS "EMP_NO",
 NULL AS "TITLE",
 NULL AS "SALARY" 
FROM 
 (VALUES
 ) AS Q1 
WHERE 
 (FALSE = TRUE)

Access Plan:
-----------
   Total Cost:             7.58112e-05
   Query Degree:           1


  Rows 
 RETURN
 (   1)
  Cost 
   I/O 
   |
    0 
 TBSCAN
 (   2)
1.27533e-05 
    0 
   |
    0 
TABFNC: SYSIBM  
 GENROW

瑪麗亞數據庫 10.5

未能辨識查詢 1、3、4 中的矛盾，查詢 2 成功

小提琴 MariaDB

1,3,4.

   Extra
   Using where

Extra Impossible WHERE

**MySQL 8.0**


未能辨識查詢 1、3、4 中的矛盾，查詢 2 成功


[小提琴 MySQL](https://dbfiddle.uk/?rdbms=mysql_8.0&sample=sakila&fiddle=9aea30b374cdd1b576bc3f5b500200dc)


1,3,4.

Extra Using where

2. ```
Extra
Impossible WHERE

甲骨文 18c

不是 100% 確定如何解釋該計劃，但它表示所有 4 個查詢的 TABLE ACCESS Full 所以我猜這意味著它失敗了 1,2,3,4

小提琴甲骨文 18c

Postgres 13

未能辨識 q1、q3、q4 中的矛盾，成功用於 q2

1,3,4。查詢計劃 Seq Scan on emp (cost=0.00..26.50 rows=2 width=46) Filter: ( …

2. QUERY PLAN Result (cost=0.00..0.00 rows=0 width=0) One-Time Filter: false

小提琴 Postgres

SQL 伺服器 2019

未能辨識 q1、q3、q4 中的矛盾，成功用於 q2

小提琴 SQL 伺服器

結論

測試的 DBMS 都沒有成功使用來自約束的資訊。

如果將資訊注入查詢，則 Db2 是唯一使用該資訊的數據庫。

正如它接縫一樣，Oracle 是唯一一個對所有 4 個查詢都失敗的查詢。

你的問題很好，但它跳過了一步。您假設查詢計劃器（或優化器，取決於您喜歡的術語）可以考慮所有 CHECK，並合併它們。

所以你有了：

check (title in ('BOSS','WORK'))

和：

CHECK ( (title <> 'BOSS' OR salary > 100) )
CHECK ( (title <> 'WORK' OR salary <= 100) )

你假設規劃者應該能夠知道：

CHECK ((title = 'BOSS' and salary > 100) or title = 'WORK' AND salary <= 100)

這可能是這種情況，也可能不是。

一般來說，您的答案取決於您使用的技術。MySQL 和 MariaDB 不考慮 CHECK。其他一些技術可能會這樣做。您必須測試您感興趣的技術。

引用自：https://dba.stackexchange.com/questions/278835