Teradata优化器为何为NOT NULL列生成IS NOT NULL检查？

我在测试中发现一个现象：Teradata优化器会为已标记为NOT NULL的列生成NOT(COLUMN_NAME IS NULL)条件。即使测试表的所有列都明确设置了NOT NULL约束，且不会产生NULL值，在两个不同系统上测试时，尽管整体执行计划不同，但这个空值检查条件始终存在。

测试案例SQL语句 #

CREATE VOLATILE TABLE Test (
    SomeInteger INTEGER NOT NULL,
    SomeOtherInteger INTEGER NOT NULL,
    SomeDate DATE FORMAT 'YYYY-MM-DD' NOT NULL,
    SomeValue VARCHAR(255) NOT NULL)
PRIMARY INDEX (SomeInteger, SomeOtherInteger)
ON COMMIT PRESERVE ROWS;

INSERT INTO Test VALUES (1,1,'2022-11-08','VBAZVVVZDVB');

SELECT
    MAIN.SomeInteger,
    MAIN.SomeOtherInteger,
    MAIN.SomeDate,
    MAIN.SomeValue,
    Coalesce(SJOIN.SomeIdentifier, 'Y') AS SomeIdentifier
FROM
    Test MAIN
    
    LEFT OUTER JOIN
        (SELECT
            SomeInteger,
            SomeOtherInteger,
            'X' SomeIdentifier
        FROM
            (SELECT DISTINCT
                SomeInteger,
                SomeOtherInteger,
                SomeValue
            FROM
                TEST) AS SUBQ
        HAVING
            Count(*) > 1
        GROUP BY SomeInteger, SomeOtherInteger) AS SJOIN
    ON SJOIN.SomeInteger = MAIN.SomeInteger
    AND SJOIN.SomeOtherInteger = MAIN.SomeOtherInteger;

TD Vantage Express 17.20的执行计划片段 #

Explanation                                                              
 ------------------------------------------------------------------------ 
   1) First, we do an all-AMPs SUM step in TD_DATADICTIONARYMAP to       
      aggregate from DBC.TEST by way of an all-rows scan with no         
      residual conditions, grouping by field1 (DBC.TEST.SomeInteger      
      ,DBC.TEST.SomeOtherInteger ,DBC.TEST.SomeValue).  Aggregate        
      intermediate results are computed locally, then placed in Spool 5  
      in TD_DataDictionaryMap.  The size of Spool 5 is estimated with    
      high confidence to be 1 row (293 bytes).  The estimated time for   
      this step is 0.00 seconds.                                          
   2) Next, we do an all-AMPs RETRIEVE step in TD_DataDictionaryMap from 
      Spool 5 (Last Use) by way of an all-rows scan into Spool 1 (used   
      to materialize view, derived table, table function or table        
      operator SUBQ) (all_amps), which is built locally on the AMPs.     
      The size of Spool 1 is estimated with high confidence to be 1 row  
      (29 bytes).  The estimated time for this step is 0.00 seconds.     
   3) We do an all-AMPs SUM step in TD_DataDictionaryMap to aggregate    
      from Spool 1 (Last Use) by way of an all-rows scan with a          
      condition of ("(NOT (SUBQ.SOMEOTHERINTEGER IS NULL )) AND (NOT     
      (SUBQ.SOMEINTEGER IS NULL ))"), grouping by field1 (               
      DBC.TEST.SOMEINTEGER ,DBC.TEST.SOMEOTHERINTEGER).  Aggregate       
      intermediate results are computed locally, then placed in Spool 8  
      in TD_DataDictionaryMap.  The size of Spool 8 is estimated with    
      low confidence to be 1 row (37 bytes).  The estimated time for     
      this step is 0.00 seconds.                                            
[..]

原因分析 #

• 通用规则的设计逻辑：Teradata优化器的规则偏向通用化，不会为表级NOT NULL约束单独裁剪通用检查逻辑。即便源表列是NOT NULL，优化器也会保留这类空值检查作为基础逻辑的一部分，无需针对特定约束做特殊调整。
• 派生表的约束传递限制：查询中的SUBQ是通过SELECT DISTINCT生成的派生表，优化器不会自动将源表的NOT NULL约束传递到派生表的列上。对于优化器而言，派生表的列属性需要重新推导，它不会主动做这么细粒度的约束继承，因此会默认添加空值检查。
• 聚合步骤的防御性设计：在后续GROUP BY聚合环节，这个空值检查是一种防御性机制——即使实际数据不会出现NULL，优化器也会加入该条件，避免因意外NULL值导致聚合逻辑异常，保证执行的安全性。
• 无实际性能影响：虽然执行计划中显示了这个条件，但实际执行时，因为数据本身没有NULL值，该条件会被快速过滤，不会产生额外的性能开销，只是优化器通用规则的体现。

内容的提问来源于stack exchange，提问作者Castro