Re: Overly conservative on reserved words?

[Jack Klebanoff <kl...@sbcglobal.net>]

Jeremy Boynes wrote:

> Daniel John Debrunner wrote:
>
>> Jeremy Boynes wrote:
>>
>>
>>> Reserving additional words from the second group poses a bigger 
>>> issue as
>>> users' may have databases out there already using these words as
>>> identifiers. The smoothest path is probably to give people an 
>>> indication
>>> of which words will need to be reserved at some point and hence should
>>> be avoided; it is better for us to do this earlier than later.
>>
>>
>>
>> Actually having even keywords defined as reserved by the SQL Standard
>> reserved in Derby has caused problems. I recently changed LOCAL not to
>> be a reserved word as other databases do not enforce it. We probably
>> need some set rules, but reserving because the SQL standard says so it
>> not the approach taken by other products.
>>
>
> 'The nice thing about standards is there are so many to choose from' :-)
> Especially true with 4 versions of ISO SQL and N vendor-specific 
> dialects. One of the issues users face is that the spec evolves and 
> products implement newer versions, words that were not reserved before 
> now need to be.
>
> Ideally we would not need to reserve anything, giving users complete 
> freedom on how they name their things; however, that would make the 
> parser, lets say, challenging. Short of this ideal, I think we should 
> compormise and only reserve words needed to resolve ambiguity in the 
> parser; that lets users decide how much portability they need. To help 
> them do that I think it's useful for us to indicate direction and what 
> is likely to be reserved (SAVEPOINT) vs. what isn't (PERCENTILE_DISC).
>
> Is it worth raising a warning on DDL operations that define objects 
> that conflict with SQL's reservation list?
>
> -- 
> Jeremy
>
I am uneasy about offering extensions to the SQL standard. Every 
extension is a "minnow trap" making it difficult for developers to port 
applications from Derby to other relational databases. Many, perhaps 
most, developers are not that vigilant about all the fine points of 
standards and portability. Often they do not consciously decide how much 
portability they need, but use whatever gets their application going and 
find out later whether it is portable.

We have to decide what Derby's goals are. Currently part of our charter 
says "developers can later migrate to other databases if they so 
choose". As long as this remains an important part of Derby's charter we 
should try to keep Derby a subset of the latest SQL standard, and we 
should avoid features that are incompatible with leading enterprise 
relational database management systems.

(A real minnow trap is a device used to catch minnows, small bait fish. 
The trap is a cylindrical cage with funnel like openings at either end. 
You put some food in the cage. Minnows find it easy to swim in but hard 
to swim out).

Jack

Compatibility, was: Overly conservative on reserved words?

[Jeremy Boynes <jb...@apache.org>]

RPost wrote:
> 
> 1. Rank the importance of being compatible with each of the other
> databases of concern. 
> 

Most important to me is the SQL standard.

If we focus on compatibility with that, then I think DB2 will be a close 
second as IBM seem to have a tendency of being closer to the standard 
that other vendors (disclaimer, my opinion and I do *not* work for IBM).

Assuming we can avoid conflicts with the standard then the next two 
platforms for me are Oracle and Microsoft SQL Server due to their broad 
enterprise adoption, and MySQL due to its dominance in the LAMP arena.

Let me add, I really really appreciate IBM's contribution but, as with 
all children, it is essentialy that Derby develops its own identity and 
learns to fly on its own.

> 2. How important (on a scale of 1-10) is portability in migrating
> FROM Derby to other databases? 


If you look at the embedded arena, I don't think this that important an 
issue because of the close relationship between the application and the 
database and that often the database is not really visible to the end user.

If you look at the bundled arena where an ISV is distributing Derby with 
a product (perhaps as a seed/low-end offering) but also wants to allow 
end-users to use an existing production database then portability 
becomes much more important; the ISV wants to minimize the discrepancies 
between the platforms they support.

If you look at the application arena, then portability may not matter 
that much because the user is primarily interested in one database 
platform. What becomes important here is that Derby's dialect is 
supported by the tools (EJB, JDO, O/R mapping) that people are using.

> 
> 3. How important is portability in migrating TO Derby from other
> databases? 

I don't see this as that important as I don't think SQL portability will 
be the major decision factor in such a migration decision, tooling will.

> 4. Should Derby adopt the Least Common Denominator standard?
> 

Absolutely not.

In an ideal world, the SQL standard would be the LCD and implementations 
would be offering features over and beyond. With the current state of 
the market, *none* of the implementations, commercial or open source, is 
anywhere near implementing even that baseline.

I would like to see Derby as the innovator here, offering features not 
available even in the leading commercial alternatives. Let Oracle and 
IBM play catch up ;-)

--
Jeremy

Re: Overly conservative on reserved words?

[RPost <rp...@pacbell.net>]

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

Re: Overly conservative on reserved words?

[Edward Rayl <er...@bellsouth.net>]

Oracle 10g supports
UNION
UNION ALL
INTERSECT
MINUS

It does not support UNION DISTINCT, although UNION behaves as if it were 
UNION DISTINCT.

I have not used EXCEPT, but I am assuming, as well, that it maps to MINUS.



Satheesh Bandaram wrote:

>Yes, I meant Oracle 10g... Does it support UNION DISTINCT? I thought
>not, based on their documentation. They seem to have a MINUS, which may
>map to EXCEPT.
>
>Satheesh
>
>Edward Rayl wrote:
>
>  
>
>>Satheesh Bandaram wrote:
>>
>>    
>>
>>>Take for example, the recently
>>>added UNION/INTERSECT DISTINCT clause. This doesn't add any new
>>>functionality to Derby, since the default is the DISTINCT. Having this
>>>syntax will create problems for migrating to DB2, Oracle, Informix. (I
>>>only briefly searched for support in Oracle 10i database. They don't
>>>seem to have it.)
>>>
>>>      
>>>
>>Oracle 10g does not support the ANSI EXCEPT operator. It does support
>>UNION and INTERSECT. However, it does not support the use of the ALL
>>keyword with those set operators. However, Oracle does intend to
>>support all the ANSI set operators and their keywords in a future
>>release.
>>
>>A minor note -- you really meant Oracle 10g, not 10i
>>
>>
>>
>>    
>>
>
>
>
>  
>

Re: Overly conservative on reserved words?

[Satheesh Bandaram <sa...@Sourcery.Org>]

Yes, I meant Oracle 10g... Does it support UNION DISTINCT? I thought
not, based on their documentation. They seem to have a MINUS, which may
map to EXCEPT.

Satheesh

Edward Rayl wrote:

> Satheesh Bandaram wrote:
>
>> Take for example, the recently
>> added UNION/INTERSECT DISTINCT clause. This doesn't add any new
>> functionality to Derby, since the default is the DISTINCT. Having this
>> syntax will create problems for migrating to DB2, Oracle, Informix. (I
>> only briefly searched for support in Oracle 10i database. They don't
>> seem to have it.)
>>
> Oracle 10g does not support the ANSI EXCEPT operator. It does support
> UNION and INTERSECT. However, it does not support the use of the ALL
> keyword with those set operators. However, Oracle does intend to
> support all the ANSI set operators and their keywords in a future
> release.
>
> A minor note -- you really meant Oracle 10g, not 10i
>
>
>

Re: Overly conservative on reserved words?

[Edward Rayl <er...@bellsouth.net>]

Satheesh Bandaram wrote:

>Take for example, the recently
>added UNION/INTERSECT DISTINCT clause. This doesn't add any new
>functionality to Derby, since the default is the DISTINCT. Having this
>syntax will create problems for migrating to DB2, Oracle, Informix. (I
>only briefly searched for support in Oracle 10i database. They don't
>seem to have it.)
>
Oracle 10g does not support the ANSI EXCEPT operator. It does support 
UNION and INTERSECT. However, it does not support the use of the ALL 
keyword with those set operators. However, Oracle does intend to support 
all the ANSI set operators and their keywords in a future release.

A minor note -- you really meant Oracle 10g, not 10i

Re: Overly conservative on reserved words?

[Daniel John Debrunner <dj...@debrunners.com>]

Satheesh Bandaram wrote:


> I am tempted to call for a vote to rollback the change to add DISTINCT
> clause to set operators, but will wait for community input before doing
> so. 

I think we vote to accept patches, not to revoke them.

At any time you can veto a patch (before or after it is committed) by
voting -1 (with a reason). You don't need to call for a vote.

Dan.

Re: Overly conservative on reserved words?

[Satheesh Bandaram <sa...@Sourcery.Org>]

I couldn't agree more. Being a non-commercial database, Apache Derby
shouldn't be laying "minnow traps". Derby should provide easy migration
path to leading commercial databases. Take for example, the recently
added UNION/INTERSECT DISTINCT clause. This doesn't add any new
functionality to Derby, since the default is the DISTINCT. Having this
syntax will create problems for migrating to DB2, Oracle, Informix. (I
only briefly searched for support in Oracle 10i database. They don't
seem to have it.) I haven't looked at Sybase and others.  Why have this
syntax when it doesn't add any value but creates hurdles to applications?

I am tempted to call for a vote to rollback the change to add DISTINCT
clause to set operators, but will wait for community input before doing
so. I do hope Derby will remain "non-proprietary" in a common sense way.
API compatibility in SQL and JDBC is a great asset to have...

Satheesh

Jack Klebanoff wrote:

> Jeremy Boynes wrote:
>
>> Daniel John Debrunner wrote:
>>
>>> Jeremy Boynes wrote:
>>>
>>>
>>>> Reserving additional words from the second group poses a bigger
>>>> issue as
>>>> users' may have databases out there already using these words as
>>>> identifiers. The smoothest path is probably to give people an
>>>> indication
>>>> of which words will need to be reserved at some point and hence should
>>>> be avoided; it is better for us to do this earlier than later.
>>>
>>>
>>>
>>>
>>> Actually having even keywords defined as reserved by the SQL Standard
>>> reserved in Derby has caused problems. I recently changed LOCAL not to
>>> be a reserved word as other databases do not enforce it. We probably
>>> need some set rules, but reserving because the SQL standard says so it
>>> not the approach taken by other products.
>>>
>>
>> 'The nice thing about standards is there are so many to choose from' :-)
>> Especially true with 4 versions of ISO SQL and N vendor-specific
>> dialects. One of the issues users face is that the spec evolves and
>> products implement newer versions, words that were not reserved
>> before now need to be.
>>
>> Ideally we would not need to reserve anything, giving users complete
>> freedom on how they name their things; however, that would make the
>> parser, lets say, challenging. Short of this ideal, I think we should
>> compormise and only reserve words needed to resolve ambiguity in the
>> parser; that lets users decide how much portability they need. To
>> help them do that I think it's useful for us to indicate direction
>> and what is likely to be reserved (SAVEPOINT) vs. what isn't
>> (PERCENTILE_DISC).
>>
>> Is it worth raising a warning on DDL operations that define objects
>> that conflict with SQL's reservation list?
>>
>> -- 
>> Jeremy
>>
> I am uneasy about offering extensions to the SQL standard. Every
> extension is a "minnow trap" making it difficult for developers to
> port applications from Derby to other relational databases. Many,
> perhaps most, developers are not that vigilant about all the fine
> points of standards and portability. Often they do not consciously
> decide how much portability they need, but use whatever gets their
> application going and find out later whether it is portable.
>
> We have to decide what Derby's goals are. Currently part of our
> charter says "developers can later migrate to other databases if they
> so choose". As long as this remains an important part of Derby's
> charter we should try to keep Derby a subset of the latest SQL
> standard, and we should avoid features that are incompatible with
> leading enterprise relational database management systems.
>
> (A real minnow trap is a device used to catch minnows, small bait
> fish. The trap is a cylindrical cage with funnel like openings at
> either end. You put some food in the cage. Minnows find it easy to
> swim in but hard to swim out).
>
> Jack
>
>
>