Обсуждение: recent ECPG 7.1->7.2 incompatibility ( interval('0sec') = syntax error )

Hello,

while switching to the 7.2 ecpg on a 7.1 database for it's new features,
we recognized several incompatibilities:

date ( some_timestamp_var ),  timestamp ( something ), timestamp
(date_var, time_var ), interval('0sec')   are not longer possible.

I don't know whether these constructs are legal in a 7.2 database, but
we used them a lot in our code.

cast ( some_timestamp_var as date), '0sec'::interval   and   "timestamp"
(date_var, time_var)  still work but if this is just an oversight, I'd
recommend to fix it.

Yours
    Christof

Michael Meskes wrote:

> > cast ( some_timestamp_var as date), '0sec'::interval   and   "timestamp"
> > (date_var, time_var)  still work but if this is just an oversight, I'd
>
> You mean it works with quotes but not without?

Yes:

exec sql select "interval"('0sec');  // accepted by ecpg
exec sql select interval('0sec');     // not accepted by ecpg (yesterday's
CVS)

Christof

PS:
exec sql select date("timestamp"('now'));
is the same

On Fri, Nov 02, 2001 at 04:58:00PM +0100, Christof Petig wrote:
> > > cast ( some_timestamp_var as date), '0sec'::interval   and   "timestamp"
> > > (date_var, time_var)  still work but if this is just an oversight, I'd
> >
> > You mean it works with quotes but not without?
>
> Yes:
>
> exec sql select "interval"('0sec');  // accepted by ecpg
> exec sql select interval('0sec');     // not accepted by ecpg (yesterday's
> CVS)

Not really surprising I guess since "interval" is accepted as the string
>interval< while interval is regarded a token.

Michael
--
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

On Fri, Nov 02, 2001 at 11:49:03AM +0100, Christof Petig wrote:
> date ( some_timestamp_var ),  timestamp ( something ), timestamp
> (date_var, time_var ), interval('0sec')   are not longer possible.

Yes, there were some changes to this part of the parser.

> I don't know whether these constructs are legal in a 7.2 database, but
> we used them a lot in our code.

If they still work with the backend there's a bug in ecpg, but looking at
the sources suggests that it's working correctly.

> cast ( some_timestamp_var as date), '0sec'::interval   and   "timestamp"
> (date_var, time_var)  still work but if this is just an oversight, I'd

You mean it works with quotes but not without?

Michael

--
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

...
> > > You mean it works with quotes but not without?
> > exec sql select "interval"('0sec');  // accepted by ecpg
> > exec sql select interval('0sec');     // not accepted by ecpg (yesterday's
> > CVS)
> Not really surprising I guess since "interval" is accepted as the string
> >interval< while interval is regarded a token.

This change was forced by new features to support SQL9x INTERVAL syntax.
Since the full syntax allows constants of the form "INTERVAL 'val' DAY
TO MINUTE" I had to change the main parser to make INTERVAL a reserved
word, as is specified in SQL9x.

Using internal conversion functions to force type is not recommended (or
at least shouldn't be). You probably know that CAST('string' AS
INTERVAL) should work for you, and is SQL9x compliant.

                        - Thomas

Thomas Lockhart wrote:

> ...
> > > > You mean it works with quotes but not without?
> > > exec sql select "interval"('0sec');  // accepted by ecpg
> > > exec sql select interval('0sec');     // not accepted by ecpg (yesterday's
> > > CVS)
> > Not really surprising I guess since "interval" is accepted as the string
> > >interval< while interval is regarded a token.
>
> This change was forced by new features to support SQL9x INTERVAL syntax.
> Since the full syntax allows constants of the form "INTERVAL 'val' DAY
> TO MINUTE" I had to change the main parser to make INTERVAL a reserved
> word, as is specified in SQL9x.
>
> Using internal conversion functions to force type is not recommended (or
> at least shouldn't be). You probably know that CAST('string' AS
> INTERVAL) should work for you, and is SQL9x compliant.

I didn't know this to be SQL compliant (), so we'll switch to the cast syntax.

I was used to the type conversion syntax back from the old days we used other
DBMSs - so this kind of abuse might be common. A friend of mine which is used to
oracle was shocked to see this syntax no longer supported.

PS: Do you know a standard compliant way to combine a date and a time to a
timestamp?

  Christof