On associativity, transitivity and reflexivity

Addition is supposed to be associative.

This may be wrong in Oracle when dealing with months and days

with t as (select
 interval '1' month a,
 date '2007-09-26' b,
 interval '7' day c
from dual)
select a+(b+c),(a+b)+c 
from t;

A+(B+C)     (A+B)+C
----------- -----------
03-NOV-2007 02-NOV-2007

The equality is supposed to be transitive
if (a=b and b=c) then (a=c)
However, in Oracle the comparison operator equal may imply some transformation

with t as (select '.0' a, 0 b, '0.' c from dual) 
  case when a=b then 'YES' else 'NO ' end "A=B",
  case when b=c then 'YES' else 'NO ' end "B=C",
  case when a=c then 'YES' else 'NO ' end "A=C"
from t;
--- --- ---

The equality operator is also supposed to be reflexive

This is unfortunately wrong with null

with t as (select null a from dual) 
select case when a=a then 'YES' else 'NO ' end "A=A" 
from t;


11 Replies to “On associativity, transitivity and reflexivity”

  1. The date example is interesting. I’m going to have to investigate that one further as it is relevant to something I’m coding right now.

    I think in your second example you’ve missed the fact that an implicit TO_NUMBER occurs in A+B and B+C but not in A+C:

    SQL> with t as (select ‘.0’ a, 0 b, ‘0.’ c from dual)
    2 select
    3 case when a=b then ‘YES’ else ‘NO ‘ end “A=B”,
    4 case when b=c then ‘YES’ else ‘NO ‘ end “B=C”,
    5 case when a=c then ‘YES’ else ‘NO ‘ end “A=C”
    6 from t
    7 /
    A=B B=C A=C
    — — —

    SQL> with t as (select to_number(‘.0’) a, 0 b, to_number(‘0.’) c from dual)
    2 select
    3 case when a=b then ‘YES’ else ‘NO ‘ end “A=B”,
    4 case when b=c then ‘YES’ else ‘NO ‘ end “B=C”,
    5 case when a=c then ‘YES’ else ‘NO ‘ end “A=C”
    6 from t
    7 /
    A=B B=C A=C
    — — —


    Cheers, APC

  2. No, I said : “equal may imply some transformation” (I meant casting to number).

    That is why in oracle a=b and b=c does not imply a=c, so the equal operator is not absolutely transitive. I almost included this one about neutral element, but I thought it is too special

    In addition the neutral element is supposed to be 0

    with t as (select time '12:34:56.789' t, interval '999999999' day(9) i from dual) 
    select case when t+i=t and i!=interval '0' second then 'YES           ' else 'NO ' end 
      "T+I=T AND I!=0" 
    from t;
    T+I=T AND I!=0

  3. >> No, I said : “equal may imply some transformation” (I meant casting to number).

    Oh. How stupid do I feel?

    >> note that with interval, it is a dangerous mission to deal with months

    Fortunately, having worked through the business rules I only need to work with intervals of years. But I still need to worry about leap years. Sigh.

    Cheers, APC

  4. Hi Laurent

    This example have a logical explication

    with t as (
    select interval ‘1’ month a,
    date ‘2007-09-26’ b,
    interval ‘7’ day c
    from dual)
    select a+(b+c),(a+b)+c
    from t

    A+(B+C) (A+B)+C
    ——————- ——————-
    30-10-2007 00:00:00 30-10-2007 00:00:00


    A + B is=2007-10-26 + seven days => 27 –> 28 –> 29 –> 30 –> 31 –> 01 –> 02 of October 2007

    other way

    B + C is=2007-10-03 because 2007-09-26 more(+) seven days –> 27 –> 28 –> 29 –> 30 –> 01 –> 02 –> 03 of September 2007 more(+) one month is 03 October 2007

    Another date

    SQL> with t as (
    2 select interval ‘1’ month a,
    3 date ‘2007-07-26’ b,
    4 interval ‘7’ day c
    5 from dual)
    6 select a+(b+c),(a+b)+c
    7 from t
    8 /

    A+(B+C) (A+B)+C
    ——————- ——————-
    02-09-2007 00:00:00 02-09-2007 00:00:00

    is the same!



  5. yes, and in leap years, it is common to have leap year baby to have their birthday on March 1st 🙂

    Thanks Hector, of course there is an explanation! but it breaks the associativity of the addition.

  6. you’re a great cheater !

    your reflexivity example should be

    with t as (select null a from dual)
    select case when a=a then ‘YES’ else ‘NO or undefined’ end “A=A”
    from t;

    NO or undefined

    because you know quite well SQL is using a three-valued logic
    (not ‘YES’ does NOT imply ‘NO’)

    month / day – arithmetic
    (where did you hear that ?)

  7. I know NULL=NULL is undefined. This is however an abuse of the = operator

    If you allow to add months to a date with a function, it is fine, you can define how it works. But if you use the + operator, the + does lose its intrinsic properties

  8. Laurent

    The + operator is associative over real numbers (complex ones too, come to that) but there’s nothing to say that it has to be associative in all cases. Consider languages where string concatenation is indicated by +

    stringvar := ‘Start ‘+ ‘Finish’;

    No one reading that would expect A + B equivalent to B + A, would they?

    It all comes down to defining your universe.

    Regards Nigel

  9. Hi Nigel,
    You comment makes sense. In C++, you can redefine the + operator to whatever you want. So you can say that 1 vodka + 1 orange = 1 drink. In my opinion it is a bad practice to redefine the + operator in such a way that it loses its properties. So define string:=’Start’+’Finish’ loses another property, namely the commutativity 😉

  10. Laurent,

    The date issue intrigued me, so I investigated further. I ran a query against a range of dates to see which values were equal and which were different. Here is the query and its output:

    WITH t AS (
        interval '1' month a, 
        interval '7' day c 
      FROM dual ) 
    SELECT b, a+(b+c),(a+b)+c FROM t, 
        TO_DATE('2007-09-01', 'YYYY-MM-DD') + rownum b 
      FROM ( 
        SELECT null 
        FROM dual 
        CONNECT BY LEVEL <= 31 ));
    B         A+(B+C)   (A+B)+C
    --------- --------- ---------
    02-SEP-07 09-OCT-07 09-OCT-07
    03-SEP-07 10-OCT-07 10-OCT-07
    04-SEP-07 11-OCT-07 11-OCT-07
    05-SEP-07 12-OCT-07 12-OCT-07
    06-SEP-07 13-OCT-07 13-OCT-07
    07-SEP-07 14-OCT-07 14-OCT-07
    08-SEP-07 15-OCT-07 15-OCT-07
    09-SEP-07 16-OCT-07 16-OCT-07
    10-SEP-07 17-OCT-07 17-OCT-07
    11-SEP-07 18-OCT-07 18-OCT-07
    12-SEP-07 19-OCT-07 19-OCT-07
    13-SEP-07 20-OCT-07 20-OCT-07
    14-SEP-07 21-OCT-07 21-OCT-07
    15-SEP-07 22-OCT-07 22-OCT-07
    16-SEP-07 23-OCT-07 23-OCT-07
    17-SEP-07 24-OCT-07 24-OCT-07
    18-SEP-07 25-OCT-07 25-OCT-07
    19-SEP-07 26-OCT-07 26-OCT-07
    20-SEP-07 27-OCT-07 27-OCT-07
    21-SEP-07 28-OCT-07 28-OCT-07
    22-SEP-07 29-OCT-07 29-OCT-07
    23-SEP-07 30-OCT-07 30-OCT-07  <-- Until this row, values are the same
    24-SEP-07 01-NOV-07 31-OCT-07  <-- From here on, values are different
    25-SEP-07 02-NOV-07 01-NOV-07
    26-SEP-07 03-NOV-07 02-NOV-07
    27-SEP-07 04-NOV-07 03-NOV-07
    28-SEP-07 05-NOV-07 04-NOV-07
    29-SEP-07 06-NOV-07 05-NOV-07
    30-SEP-07 07-NOV-07 06-NOV-07
    01-OCT-07 08-NOV-07 08-NOV-07  <-- From here on, values are the same again
    02-OCT-07 09-NOV-07 09-NOV-07
    31 rows selected.

    What I noticed here is that the difference between the two occurred when one of the values was on the border of a month (eg., last day or first day).

    I modified the query to show the value of the INTERVAL by modifying the SELECT clause to add in "b+a-b" as a column:

    WITH t AS ( 
      SELECT interval '1' month a, 
        interval '7' day c FROM dual ) 
    SELECT b, b+a-b 
    FROM t, 
        TO_DATE('2007-09-01', 'YYYY-MM-DD') + rownum b 
      FROM ( 
        SELECT null FROM dual CONNECT BY LEVEL <= 31 ));
    B              B+A-B
    --------- ----------
    02-SEP-07         30
    03-SEP-07         30
    04-SEP-07         30
    05-SEP-07         30
    06-SEP-07         30
    07-SEP-07         30
    08-SEP-07         30
    09-SEP-07         30
    10-SEP-07         30
    11-SEP-07         30
    12-SEP-07         30
    13-SEP-07         30
    14-SEP-07         30
    15-SEP-07         30
    16-SEP-07         30
    17-SEP-07         30
    18-SEP-07         30
    19-SEP-07         30
    20-SEP-07         30
    21-SEP-07         30
    22-SEP-07         30
    23-SEP-07         30
    24-SEP-07         30
    25-SEP-07         30
    26-SEP-07         30
    27-SEP-07         30
    28-SEP-07         30
    29-SEP-07         30
    30-SEP-07         30
    01-OCT-07         31
    02-OCT-07         31
    31 rows selected.

    What is obvious here is that the value of A changes from 30 for all September dates to 31 for the October dates. This coincides with the number of days in the respected months. Another query to demonstrate this difference in the value of INTERVAL:

Leave a Reply

Your email address will not be published.