|
>Edward Rayl wrote:
>I have not used EXCEPT, but I am assuming, as well, that it maps to
MINUS.
Oracle's MINUS is the equivalent of EXCEPT and returns unique (distinct) rows. Oracle uses the same precedence for UNION,
INTERSECT and MINUS. So what precedence should Derby use?
I agree with previous comments that a strategy
should be discussed and adopted. A starting point might be to see where folks
stand on the relative importance of various aspects of the issue:
1. Rank the importance of being compatible with
each of the other databases of concern.
A. Which DB is it most important
to be compatible with? My vote here is DB2 since Derby would not exist as
opensource without IBM's generosity. Since there is every indication that they
intend to provide continued support and cooperation to the project it makes
sense to me to make that as attractive as possible unless there is a compelling
reason to diverge.
2. How important (on a scale of 1-10) is
portability in migrating FROM Derby to other databases?
A. Derby's use of UNION DISTINCT
would be non-portable to ORACLE and DB2 but the non-portability would not go
unnoticed; it would be simple to find and fix the problem.
B. Derby's use of higher
precedence for INTERSECT would be non-portable to ORACLE since oracle uses the
same precedence. This could easily go unnoticed since there would be no
compile error but a difference in the result set. This type of non-visible
portability issue can be very hard to detect.
3. How important is portability in migrating TO
Derby from other databases?
A. Derby's use of UNION DISTINCT
would be portable since the word DISTINCT is not mandatory.
4. Should Derby
adopt the Least Common Denominator standard?
My own experience is that, in practice, portability
is over-hyped. Every Oracle-to-DB2 (and vice versa) project I have worked on
involved examining every important query manually to optimize it. I would much
rather have a query not work, perhaps because of the use of UNION DISTINCT
rather than just UNION, than work but give different results.
Oracle allows embedded hints making such queries
non-portable to almost every other database. For years DB2 supported TABLE
expressions while other database did not; all of those queries were
non-portable.
Unless we plan to maintain a table like that in
'http://www.dbazine.com/gulutzan3.shtml' of differences the closer we stick to
the SQL standard the easier it will be for integrator's to know how to work with
Derby in their environment.
|
