Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 92572 invoked from network); 1 Mar 2005 23:47:08 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur-2.apache.org with SMTP; 1 Mar 2005 23:47:08 -0000 Received: (qmail 1287 invoked by uid 500); 1 Mar 2005 23:47:07 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 1054 invoked by uid 500); 1 Mar 2005 23:47:07 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Development" Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 1041 invoked by uid 99); 1 Mar 2005 23:47:06 -0000 X-ASF-Spam-Status: No, hits=2.3 required=10.0 tests=DNS_FROM_RFC_ABUSE,DNS_FROM_RFC_POST,FROM_ENDS_IN_NUMS,HTML_30_40,HTML_MESSAGE X-Spam-Check-By: apache.org Received-SPF: pass (hermes.apache.org: local policy includes SPF record at spf.trusted-forwarder.org) Received: from smtp803.mail.sc5.yahoo.com (HELO smtp803.mail.sc5.yahoo.com) (66.163.168.182) by apache.org (qpsmtpd/0.28) with SMTP; Tue, 01 Mar 2005 15:47:05 -0800 Received: from unknown (HELO rpws002) (rp0428@pacbell.net@68.125.10.130 with login) by smtp803.mail.sc5.yahoo.com with SMTP; 1 Mar 2005 23:47:02 -0000 Message-ID: <000d01c51eb9$b3b20aa0$1401a8c0@rpws002> From: "RPost" To: "Derby Development" References: <4222030C.3010600@apache.org> <42237891.4020407@debrunners.com> <422385C9.3090507@apache.org> <4224A57E.8020801@sbcglobal.net> <4224B093.5080706@Sourcery.Org> <4224D808.5090104@bellsouth.net> <4224DE40.4010909@Sourcery.Org> <4224E8FE.9060604@bellsouth.net> Subject: Re: Overly conservative on reserved words? Date: Tue, 1 Mar 2005 15:52:17 -0800 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_000A_01C51E76.A52CFB90" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Virus-Checked: Checked X-Spam-Rating: minotaur-2.apache.org 1.6.2 0/1000/N This is a multi-part message in MIME format. ------=_NextPart_000_000A_01C51E76.A52CFB90 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable >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? =20 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. ------=_NextPart_000_000A_01C51E76.A52CFB90 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
>Edward Rayl wrote:
 
>I have not used EXCEPT, but I am assuming, as well, that it = maps to=20 MINUS.

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