Skip to content
  • Praveenkumar Hulakund's avatar
    8eacffb1
    Bug#29706689 - CHECK CONSTRAINT COMPARING COLUMN WITH DEFAULT VALUE IS NOT ENFORCED · 8eacffb1
    Praveenkumar Hulakund authored
    When a check constraint expression contains a column using function
    defaults (Like CURTIME(), CURRENT_TIMESTAMP, CURDATE()), for a
    operation if default value is used for such column then constraint
    was evaluated incorrectly.
    
    Currently default value for columns using function defaults or
    default expression is set very late, after CHECK OPTION and
    check constraints evaluation. For a DML operation if value for a
    column is supposed to come from the function defaults then check
    constraint expression uses NULL as a value for column instead
    of correct default value. As result check constraint is evaluated
    incorrectly.
    
    The sequence of execution should be 1) set function defaults
    2) invoke before triggers 3) CHECK OPTION 4) CHECK CONSTRAINTS.
    In the current code, this sequence is followed only when BEFORE
    triggers are defined on the table. Otherwise 1 is executed
    after 4. To fix this issue, corrected the execution sequence.
    
    Correcting sequence of execution fixes even Bug29834066 "DEFAULT
    EXPRESSION CAN BREAK VIEW CHECK OPTION". Issue here is, INSERT
    into views evaluate default expression / function after CHECK OPTION
    is performed. As result it is possible to insert rows into view
    with CHECK OPTION which won't be visible in the view, which
    CHECK OPTION is supposed to prevent.
    
    Reviewed by : Dmitry Lenev <dmitry.lenev@oracle.com>
    
    Change-Id: Ib16f7c32d597b80cc78506a7ace53dea6dcb0088
    8eacffb1
    Bug#29706689 - CHECK CONSTRAINT COMPARING COLUMN WITH DEFAULT VALUE IS NOT ENFORCED
    Praveenkumar Hulakund authored
    When a check constraint expression contains a column using function
    defaults (Like CURTIME(), CURRENT_TIMESTAMP, CURDATE()), for a
    operation if default value is used for such column then constraint
    was evaluated incorrectly.
    
    Currently default value for columns using function defaults or
    default expression is set very late, after CHECK OPTION and
    check constraints evaluation. For a DML operation if value for a
    column is supposed to come from the function defaults then check
    constraint expression uses NULL as a value for column instead
    of correct default value. As result check constraint is evaluated
    incorrectly.
    
    The sequence of execution should be 1) set function defaults
    2) invoke before triggers 3) CHECK OPTION 4) CHECK CONSTRAINTS.
    In the current code, this sequence is followed only when BEFORE
    triggers are defined on the table. Otherwise 1 is executed
    after 4. To fix this issue, corrected the execution sequence.
    
    Correcting sequence of execution fixes even Bug29834066 "DEFAULT
    EXPRESSION CAN BREAK VIEW CHECK OPTION". Issue here is, INSERT
    into views evaluate default expression / function after CHECK OPTION
    is performed. As result it is possible to insert rows into view
    with CHECK OPTION which won't be visible in the view, which
    CHECK OPTION is supposed to prevent.
    
    Reviewed by : Dmitry Lenev <dmitry.lenev@oracle.com>
    
    Change-Id: Ib16f7c32d597b80cc78506a7ace53dea6dcb0088
Loading