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

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

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

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

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

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

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.

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

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

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

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 😉

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:

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:

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: