db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "RPost" <rp0...@pacbell.net>
Subject Re: Overly conservative on reserved words?
Date Tue, 01 Mar 2005 23:52:17 GMT
>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

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.
View raw message