Thread: 7.0 release notes should call out incompatible changes more clearly

I am aware of at least three non-backward-compatible changes in 7.0,
ie, things that will break existing applications in perhaps non-obvious
ways.  I think the "Release Notes" document ought to call these out in a
separate section, rather than expecting people to examine the detailed
change list and intuit what those brief entries mean to them.

The three I can think of are:

1. If a GROUP BY item matches both an input column name and a
select-list label ("AS" name), 7.0 assumes the input column is meant.
This is compliant with the SQL92 spec.  Unfortunately older versions
made the opposite choice.

2. SELECT DISTINCT ON syntax has changed --- now need parentheses
around the item being DISTINCT'ed.

3. User-defined operator names can't end in "+" or "-" unless they
also contain ~ ! @ # % ^ & | ` ? $ or :

Any others?
        regards, tom lane

These are all pretty obscure.  How do I make them prominent without
really scaring people who don't even know what they are?

> I am aware of at least three non-backward-compatible changes in 7.0,
> ie, things that will break existing applications in perhaps non-obvious
> ways.  I think the "Release Notes" document ought to call these out in a
> separate section, rather than expecting people to examine the detailed
> change list and intuit what those brief entries mean to them.
> 
> The three I can think of are:
> 
> 1. If a GROUP BY item matches both an input column name and a
> select-list label ("AS" name), 7.0 assumes the input column is meant.
> This is compliant with the SQL92 spec.  Unfortunately older versions
> made the opposite choice.
> 
> 2. SELECT DISTINCT ON syntax has changed --- now need parentheses
> around the item being DISTINCT'ed.
> 
> 3. User-defined operator names can't end in "+" or "-" unless they
> also contain ~ ! @ # % ^ & | ` ? $ or :
> 
> Any others?
> 
>             regards, tom lane
> 


--  Bruce Momjian                        |  http://www.op.net/~candle pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

on 4/2/00 5:40 PM, Bruce Momjian at pgman@candle.pha.pa.us wrote:

>> 1. If a GROUP BY item matches both an input column name and a
>> select-list label ("AS" name), 7.0 assumes the input column is meant.
>> This is compliant with the SQL92 spec.  Unfortunately older versions
>> made the opposite choice.
>> 
>> 2. SELECT DISTINCT ON syntax has changed --- now need parentheses
>> around the item being DISTINCT'ed.
>> 
>> 3. User-defined operator names can't end in "+" or "-" unless they
>> also contain ~ ! @ # % ^ & | ` ? $ or :

Unless I've missed some earlier discussion of it, the grammar for adding a
field to a table has also changed.

In 6.5, you could do

alter table add (   id integer
);

whereas in 7.0 you must do

alter table add id integer;

and the parens will screw things up.

-Ben

> These are all pretty obscure.  How do I make them prominent without
> really scaring people who don't even know what they are?

These are not obscure if you are using the feature, and we should have
a section (just after the description of new features) which discuss
porting/upgrade issues. I was planning on putting it in; it will need
to contain info on "datetime/timespan" vs "timestamp/interval" also
(probably the least "obscure" upgrade issue in the new release).
                     - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

Ah, and if anyone ever used CREATE FUNCTION/WITH, the position of the
WITH clause has changed. Tom, was "with" useful enough for anyone to
care about this?
                   - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> Ah, and if anyone ever used CREATE FUNCTION/WITH, the position of the
> WITH clause has changed. Tom, was "with" useful enough for anyone to
> care about this?

I doubt it.  The parameters that can be specified in WITH never did
anything before 7.0 (well, I suppose some of them did back when the
"expensive functions" optimization code worked).  The only one that
does anything useful now is ISCACHABLE (permits reduction of function
during constant-folding), and that functionality is new in 7.0.
        regards, tom lane

Andrew McMillan <Andrew@catalyst.net.nz> writes:
>> 2. SELECT DISTINCT ON syntax has changed --- now need parentheses
>> around the item being DISTINCT'ed.

> I have just upgraded my development machine today and this is proving to
> be a real pain in the neck as I seem to have used this all over the
> place :-)

> Can anyone suggest any syntax that I could use in the interim which will
> be compatible with 7.0 but which will also work with 6.5.3 so that I can
> minimise the pain of upgrading?

Er ... don't use DISTINCT ON?  It's not to be found anywhere in the
SQL92 specs, so if you want to run your app on a variety of DBMSes,
that's your only choice anyway.

If you are a heavy user of DISTINCT ON, I should think you'd gladly
accept a little conversion pain for the benefit of being able to do
DISTINCT ON multiple expressions, instead of only one unadorned
column name.
        regards, tom lane