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