On associativity, transitivity and reflexivity

Addition is supposed to be associative.
a+(b+c)=(a+b)+c

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)
select
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;
A=B B=C A=C
--- --- ---
YES YES NO

The equality operator is also supposed to be reflexive
a=a

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;
A=A
---
NO

:mrgreen:

11 thoughts on “On associativity, transitivity and reflexivity

  1. APC

    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
    — — —
    YES YES NO

    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
    — — —
    YES YES YES

    SQL>

    Cheers, APC

  2. Laurent Schneider Post author

    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
    --------------
    YES

  3. Laurent Schneider Post author

    note that with interval, it is a dangerous mission to deal with months, because adding one month to 31-JAN result in ORA-01839: date not valid for month specified

  4. APC

    >> 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

  5. Hector Gabriel Ulloa Ligarius

    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

    Brief….

    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!

    😀

    Regards
    http://ligarius.wordpress.com

  6. Laurent Schneider Post author

    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.

  7. Sokrates

    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;

    A=A
    —————
    NO or undefined

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

    month / day – arithmetic
    NEVER WAS ASSOCIATIVE IN REAL LIFE
    (where did you hear that ?)

  8. Laurent Schneider Post author

    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

  9. Nigel Thomas

    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

  10. Laurent Schneider Post author

    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 😉

  11. KevinMiles

    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 (
    SELECT
    interval '1' month a,
    interval '7' day c
    FROM dual )
    SELECT b, a+(b+c),(a+b)+c FROM t,
    (SELECT
    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,
    (SELECT
    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:

Comments are closed.