Thread: bug or change in functionality in 7.2?

bug or change in functionality in 7.2?

From
Barry Lind
Date:
I have in my code a SQL statement that does the following:

select period_start + interval('1 hour') from periods;

This worked in 7.1, but in 7.2 I am getting the following error:

ERROR:  parser: parse error at or near "'"

If I remove the quotes then I get the following error:

ERROR:  parser: parse error at or near "hour"

Was this change from 7.1 to 7.2 intentional?  If so, how should this be
coded in 7.2?

thanks,
--Barry


Re: bug or change in functionality in 7.2?

From
"Christopher Kings-Lynne"
Date:
Well, the way I've always constructed these queries is:

select period_start + interval '1 hour' from periods;

Try that.  In fact, I believe the above is the correct SQL standard syntax?

Chris

> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Barry Lind
> Sent: Friday, 16 November 2001 9:52 AM
> To: pgsql-general@postgresql.org
> Cc: PostgreSQL-development
> Subject: [HACKERS] bug or change in functionality in 7.2?
> 
> 
> I have in my code a SQL statement that does the following:
> 
> select period_start + interval('1 hour') from periods;
> 
> This worked in 7.1, but in 7.2 I am getting the following error:
> 
> ERROR:  parser: parse error at or near "'"
> 
> If I remove the quotes then I get the following error:
> 
> ERROR:  parser: parse error at or near "hour"
> 
> Was this change from 7.1 to 7.2 intentional?  If so, how should this be 
> coded in 7.2?
> 
> thanks,
> --Barry
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 



Re: bug or change in functionality in 7.2?

From
Tom Lane
Date:
Barry Lind <barry@xythos.com> writes:
> select period_start + interval('1 hour') from periods;
> This worked in 7.1, but in 7.2 I am getting the following error:
> ERROR:  parser: parse error at or near "'"

"interval" is a more reserved word than it used to be ("timestamp"
is too).  This is because interval(n) is now a type name, not a
function name, because we now support SQL92's notion of precision
specs for intervals and timestamps.  That means using "interval"
as an unquoted function name doesn't work anymore.

I concur with Christopher's recommendation: use the syntax
    interval '1 hour'
Other possibilities are
    cast('1 hour' as interval)
    "interval"('1 hour')
    '1 hour'::interval
The last two are Postgres-isms, the first two are SQL92 standard
notations that we'll try not to break in future.

            regards, tom lane

Re: bug or change in functionality in 7.2?

From
"Ross J. Reedstrom"
Date:
This needs to be highlighted in the release notes/history/migration
docs, whatever. both interval() and timestamp(), since that was a
(wrong) way to do casts, in the past.

Ross

On Fri, Nov 16, 2001 at 12:26:40AM -0500, Tom Lane wrote:
> Barry Lind <barry@xythos.com> writes:
> > select period_start + interval('1 hour') from periods;
> > This worked in 7.1, but in 7.2 I am getting the following error:
> > ERROR:  parser: parse error at or near "'"
> 
> "interval" is a more reserved word than it used to be ("timestamp"
> is too).  This is because interval(n) is now a type name, not a
> function name, because we now support SQL92's notion of precision
> specs for intervals and timestamps.  That means using "interval"
> as an unquoted function name doesn't work anymore.
> 
> I concur with Christopher's recommendation: use the syntax
>     interval '1 hour'
> Other possibilities are
>     cast('1 hour' as interval)
>     "interval"('1 hour')
>     '1 hour'::interval
> The last two are Postgres-isms, the first two are SQL92 standard
> notations that we'll try not to break in future.
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html


Re: bug or change in functionality in 7.2?

From
Barry Lind
Date:
Thanks for the quick help.  I have changed my code accordingly.

--Barry

Tom Lane wrote:

> Barry Lind <barry@xythos.com> writes:
>
>>select period_start + interval('1 hour') from periods;
>>This worked in 7.1, but in 7.2 I am getting the following error:
>>ERROR:  parser: parse error at or near "'"
>>
>
> "interval" is a more reserved word than it used to be ("timestamp"
> is too).  This is because interval(n) is now a type name, not a
> function name, because we now support SQL92's notion of precision
> specs for intervals and timestamps.  That means using "interval"
> as an unquoted function name doesn't work anymore.
>
> I concur with Christopher's recommendation: use the syntax
>     interval '1 hour'
> Other possibilities are
>     cast('1 hour' as interval)
>     "interval"('1 hour')
>     '1 hour'::interval
> The last two are Postgres-isms, the first two are SQL92 standard
> notations that we'll try not to break in future.
>
>             regards, tom lane
>
>



Re: bug or change in functionality in 7.2?

From
F Harvell
Date:
On Fri, 16 Nov 2001 00:26:40 EST, Tom Lane wrote:
> Barry Lind <barry@xythos.com> writes:
> > select period_start + interval('1 hour') from periods;
> > This worked in 7.1, but in 7.2 I am getting the following error:
> > ERROR:  parser: parse error at or near "'"
>
> "interval" is a more reserved word than it used to be ("timestamp"
> is too).  This is because interval(n) is now a type name, not a
> function name, because we now support SQL92's notion of precision
> specs for intervals and timestamps.  That means using "interval"
> as an unquoted function name doesn't work anymore.
>
> I concur with Christopher's recommendation: use the syntax
>     interval '1 hour'
> Other possibilities are
>     cast('1 hour' as interval)
>     "interval"('1 hour')
>     '1 hour'::interval
> The last two are Postgres-isms, the first two are SQL92 standard
> notations that we'll try not to break in future.

  In my readings on the standard, the first one is _not_ SQL92
standard notation.  Indeed, I may be incorrect since I do not have an
actual copy of the SQL92 standard.  I am basing my statements on Date/
Darwin's "A guide to the SQL Standard", fourth edition.  In that tome,
they state:

----- cut -----
day-time:

  Written as the key word INTERVAL, followed by a (day-time) interval
string consisting of an opening single quote, an optional sign, a
continuous nonempty subsequence of dd, hh, mm, and ss[.[nnnnnn]] (with
a space separator between dd and the rest, if dd is specified, and
colon separators elsewhere), and a closing single quote, followed by
the appropriate "start [TO end]" specification.

  Examples:

  INTERVAL '1'     MINUTE
  INTERVAL '2 12' DAY TO HOUR
  INTERVAL '2:12:35' HOUR TO SECOND
  INTERVAL '-4.50' SECOND

----- cut -----

  In my experiences with other databases, the notations indicated in
the Date/Darwin book do indeed work whereas the PostgreSQL notation
(with the closing single quote following the start to end
specification) do not work.

Thanks,
F Harvell

--
Mr. F Harvell                          Phone: +1.407.673.2529
FTS International Data Systems, Inc.    Cell: +1.407.467.1919
7457 Aloma Ave, Suite 302                Fax: +1.407.673.4472
Winter Park, FL 32792                 mailto:fharvell@fts.net



Re: bug or change in functionality in 7.2?

From
Tom Lane
Date:
F Harvell <fharvell@fts.net> writes:
>   In my experiences with other databases, the notations indicated in
> the Date/Darwin book do indeed work whereas the PostgreSQL notation
> (with the closing single quote following the start to end
> specification) do not work.

In current sources:

regression=# select INTERVAL '2:12:35' HOUR TO SECOND;
 interval
----------
 02:12:35
(1 row)

regression=# select INTERVAL '2:12:35 HOUR TO SECOND';
ERROR:  Bad interval external representation '2:12:35 HOUR TO SECOND'
regression=#

Looks like Lockhart agrees with you ;-)

            regards, tom lane

Re: bug or change in functionality in 7.2?

From
F Harvell
Date:
On Mon, 19 Nov 2001 11:24:08 EST, Tom Lane wrote:
> F Harvell <fharvell@fts.net> writes:
> >   In my experiences with other databases, the notations indicated in
> > the Date/Darwin book do indeed work whereas the PostgreSQL notation
> > (with the closing single quote following the start to end
> > specification) do not work.
>
> In current sources:
>
> regression=# select INTERVAL '2:12:35' HOUR TO SECOND;
>  interval
> ----------
>  02:12:35
> (1 row)
>
> regression=# select INTERVAL '2:12:35 HOUR TO SECOND';
> ERROR:  Bad interval external representation '2:12:35 HOUR TO SECOND'
> regression=#
>
> Looks like Lockhart agrees with you ;-)
>

If the above is true (i.e., errors on the second interval literal), it
should probably be mentioned in the release notes (HISTORY file?).
While I eagerly anticipate the change and agree with it, it will break
a lot of my current code.  I think this is (potentially) correct,
however, it should be told to people who are using interval literals
and anticipating to make the upgrade to 7.2.

--
Mr. F Harvell                          Phone: +1.407.673.2529
FTS International Data Systems, Inc.    Cell: +1.407.467.1919
7457 Aloma Ave, Suite 302                Fax: +1.407.673.4472
Winter Park, FL 32792                 mailto:fharvell@fts.net



Re: bug or change in functionality in 7.2?

From
Bruce Momjian
Date:
> If the above is true (i.e., errors on the second interval literal), it
> should probably be mentioned in the release notes (HISTORY file?).
> While I eagerly anticipate the change and agree with it, it will break
> a lot of my current code.  I think this is (potentially) correct,
> however, it should be told to people who are using interval literals
> and anticipating to make the upgrade to 7.2.

Can I have some text for HISTORY?

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  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, Pennsylvania 19026

Re: bug or change in functionality in 7.2?

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> If the above is true (i.e., errors on the second interval literal), it
>> should probably be mentioned in the release notes (HISTORY file?).

> Can I have some text for HISTORY?

Thomas would be the authority, but AFAIK this is new stuff; it doesn't
break anything that worked before.

            regards, tom lane

Re: bug or change in functionality in 7.2?

From
Bruce Momjian
Date:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> If the above is true (i.e., errors on the second interval literal), it
> >> should probably be mentioned in the release notes (HISTORY file?).
>
> > Can I have some text for HISTORY?
>
> Thomas would be the authority, but AFAIK this is new stuff; it doesn't
> break anything that worked before.

Oh, OK.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  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, Pennsylvania 19026

Re: bug or change in functionality in 7.2?

From
F Harvell
Date:
On Mon, 19 Nov 2001 16:41:11 EST, Bruce Momjian wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > >> If the above is true (i.e., errors on the second interval literal), it
> > >> should probably be mentioned in the release notes (HISTORY file?).
> >
> > > Can I have some text for HISTORY?
> >
> > Thomas would be the authority, but AFAIK this is new stuff; it doesn't
> > break anything that worked before.
>
> Oh, OK.

  Well, since I started this, I figured that I had best verify if
there is an issue.  There appears to be _no_ issue.  The exiting (7.1)
functionality still works in 7.2.  Sorry for the confusion.

  It might be reasonable, though, to mention in the types or
enhancements section that the SQL92 interval literal syntax is now
supported.  (It's implied but not spelled out as "Add INTERVAL() YEAR
TO MONTH (etc) syntax (Thomas)".)

  BTW, many thanks to Thomas.  This is a compatibility that I really
appreciate.

--
Mr. F Harvell                          Phone: +1.407.673.2529
FTS International Data Systems, Inc.    Cell: +1.407.467.1919
7457 Aloma Ave, Suite 302                Fax: +1.407.673.4472
Winter Park, FL 32792                 mailto:fharvell@fts.net