Menu
▾
▴
firebird-devel
|
From: Dmitry S. <dmi...@re...> - 2020-10-20 10:08:40
|
Hello.
Some string to date conversions that work in FB3 fail with an error in FB4:
SELECT cast('01 jan 1900' as timestamp) FROM rdb$database;
Statement failed, SQLSTATE = 22018
conversion error from string "01 jan 1900"
SELECT cast('01,jan,1900' as timestamp) FROM rdb$database;
Statement failed, SQLSTATE = 22018
conversion error from string "01,jan,1900"
AFAIU the error appears after fix for CORE-5750. It's expected that
whitespace and comma are no longer considered as date separators?
Because documentantion here
(https://firebirdsql.org/en/firebird-date-literals/) says they are valid
separators.
Best regards,
Dmitry Starodubov
|
|
From: Mark R. <ma...@la...> - 2020-10-20 10:27:26
|
On 20-10-2020 11:51, Dmitry Starodubov wrote:
> Some string to date conversions that work in FB3 fail with an error in FB4:
>
> SELECT cast('01 jan 1900' as timestamp) FROM rdb$database;
>
> Statement failed, SQLSTATE = 22018
> conversion error from string "01 jan 1900"
>
>
> SELECT cast('01,jan,1900' as timestamp) FROM rdb$database;
>
> Statement failed, SQLSTATE = 22018
>
> conversion error from string "01,jan,1900"
>
> AFAIU the error appears after fix for CORE-5750. It's expected that
> whitespace and comma are no longer considered as date separators?
>
> Because documentantion here
> (https://firebirdsql.org/en/firebird-date-literals/) says they are valid
> separators.
Keep in mind, that link is an excerpt from Helen Borrie's Firebird Book
from 2004 (Firebird 1.5 era). It is descriptive of what worked at the
time, it is not prescriptive as to how Firebird is supposed to behave.
That said, I think allowing spaces between date elements would be fine.
Personally, I think Firebird should add support for a cast with a cast
template, CAST(<cast operand> AS <cast target> [FORMAT <cast template>])
as defined in SQL:2016 6.13 <cast specification> and 9.44 Datetime
templates, which would allow to do away with some ambiguity.
Mark
--
Mark Rotteveel
|
|
From: Roman S. <rom...@gm...> - 2020-10-20 10:40:33
|
вт, 20 окт. 2020 г. в 13:28, Mark Rotteveel <ma...@la...>: > > Because documentantion here > > (https://firebirdsql.org/en/firebird-date-literals/) says they are valid > > separators. > > Keep in mind, that link is an excerpt from Helen Borrie's Firebird Book > from 2004 (Firebird 1.5 era). It is descriptive of what worked at the > time, it is not prescriptive as to how Firebird is supposed to behave. Why not? For me and lots of other developers, who have been using such code, it's exactly how Firebird is supposed to behave :) In other words, what are the reasons to break the backward compatibility? -- Roman Simakov |
|
From: Mark R. <ma...@la...> - 2020-10-20 11:47:58
|
On 20-10-2020 12:40, Roman Simakov wrote: > вт, 20 окт. 2020 г. в 13:28, Mark Rotteveel <ma...@la...>: >>> Because documentantion here >>> (https://firebirdsql.org/en/firebird-date-literals/) says they are valid >>> separators. >> >> Keep in mind, that link is an excerpt from Helen Borrie's Firebird Book >> from 2004 (Firebird 1.5 era). It is descriptive of what worked at the >> time, it is not prescriptive as to how Firebird is supposed to behave. > > Why not? For me and lots of other developers, who have been using such > code, it's exactly how Firebird is supposed to behave :) > In other words, what are the reasons to break the backward compatibility? I'm just saying that the quote is an excerpt of a book that describes how Firebird worked at that time (probably reverse-engineered using a lot of trial and error). It is not a prescription (requirements document) how Firebird is supposed to work. As such, it shouldn't be taken as the holy truth to which Firebird must conform. I prefer backwards compatibility, but there are a lot of oddities in that described behaviour, some of which are not worth to preserve IMHO. Mark -- Mark Rotteveel |
|
From: Dmitry Y. <fir...@ya...> - 2020-10-20 10:43:54
|
20.10.2020 13:40, Roman Simakov wrote: > >>> Because documentantion here >>> (https://firebirdsql.org/en/firebird-date-literals/) says they are valid >>> separators. >> >> Keep in mind, that link is an excerpt from Helen Borrie's Firebird Book >> from 2004 (Firebird 1.5 era). It is descriptive of what worked at the >> time, it is not prescriptive as to how Firebird is supposed to behave. > > Why not? For me and lots of other developers, who have been using such > code, it's exactly how Firebird is supposed to behave :) > In other words, what are the reasons to break the backward compatibility? AFAIU, it was discussed here in February 2018, thread "Valid date or not". Dmitry |
|
From: Vlad K. <hv...@us...> - 2020-10-20 11:14:45
|
20.10.2020 13:43, Dmitry Yemanov wrote: > 20.10.2020 13:40, Roman Simakov wrote: >> >>>> Because documentantion here >>>> (https://firebirdsql.org/en/firebird-date-literals/) says they are valid >>>> separators. >>> >>> Keep in mind, that link is an excerpt from Helen Borrie's Firebird Book >>> from 2004 (Firebird 1.5 era). It is descriptive of what worked at the >>> time, it is not prescriptive as to how Firebird is supposed to behave. >> >> Why not? For me and lots of other developers, who have been using such >> code, it's exactly how Firebird is supposed to behave :) >> In other words, what are the reasons to break the backward compatibility? > > AFAIU, it was discussed here in February 2018, thread "Valid date or not". I've re-read that thread quickly and I saw nor final decision, nor proposition to change (or break) rules for traditional (legacy) date\time types (without TZ). Regards, Vlad |
|
From: Roman S. <rom...@gm...> - 2020-10-20 11:19:34
|
вт, 20 окт. 2020 г. в 14:15, Vlad Khorsun via Firebird-devel <fir...@li...>: > I've re-read that thread quickly and I saw nor final decision, nor proposition > to change (or break) rules for traditional (legacy) date\time types (without TZ). Me too. Generally that discussion was focused on other formats. Here I'd focus on the broken backward compatibility. I see no problem to keep it possible since we have had it already. -- Roman Simakov |
|
From: Dmitry Y. <fir...@ya...> - 2020-10-20 11:43:16
|
20.10.2020 13:58, Vlad Khorsun via Firebird-devel wrote: >> AFAIU, it was discussed here in February 2018, thread "Valid date or >> not". > > I've re-read that thread quickly and I saw nor final decision, nor > proposition > to change (or break) rules for traditional (legacy) date\time types > (without TZ). The tracker ticket (CORE-5750) mentions the problem with spaces inside the literal. If time parts are separated by spaces, it's hard to guess whether the final part is TZ or milliseconds, AFAIU. The final decision is also documented in that ticket. While I agree that breaking things is usually bad, time '10 20 30' or time '10,20,30' look so terribly wrong to me, so personally I support breaking them ;-) The same for date with commas. The only "broken" format I agree to consider useful is '20 Oct 2020'. Given that space-parsing problems appear inside the time part, perhaps it could be re-allowed for dates (only). Dmitry |
|
From: Roman S. <rom...@gm...> - 2020-10-20 12:09:07
|
вт, 20 окт. 2020 г. в 14:43, Dmitry Yemanov <fir...@ya...>: > > 20.10.2020 13:58, Vlad Khorsun via Firebird-devel wrote: > > >> AFAIU, it was discussed here in February 2018, thread "Valid date or > >> not". > > > > I've re-read that thread quickly and I saw nor final decision, nor > > proposition > > to change (or break) rules for traditional (legacy) date\time types > > (without TZ). > > The tracker ticket (CORE-5750) mentions the problem with spaces inside > the literal. If time parts are separated by spaces, it's hard to guess > whether the final part is TZ or milliseconds, AFAIU. The final decision > is also documented in that ticket. > > While I agree that breaking things is usually bad, time '10 20 30' or > time '10,20,30' look so terribly wrong to me, so personally I support > breaking them ;-) The same for date with commas. The only "broken" > format I agree to consider useful is '20 Oct 2020'. Given that > space-parsing problems appear inside the time part, perhaps it could be > re-allowed for dates (only). Yes! I agree. -- Roman Simakov |
|
From: Adriano d. S. F. <adr...@gm...> - 2020-10-22 11:09:35
|
On 20/10/2020 08:42, Dmitry Yemanov wrote: > 20.10.2020 13:58, Vlad Khorsun via Firebird-devel wrote: > >>> AFAIU, it was discussed here in February 2018, thread "Valid date or >>> not". >> >> I've re-read that thread quickly and I saw nor final decision, nor >> proposition >> to change (or break) rules for traditional (legacy) date\time types >> (without TZ). > > The tracker ticket (CORE-5750) mentions the problem with spaces inside > the literal. If time parts are separated by spaces, it's hard to guess > whether the final part is TZ or milliseconds, AFAIU. The final > decision is also documented in that ticket. > > While I agree that breaking things is usually bad, time '10 20 30' or > time '10,20,30' look so terribly wrong to me, so personally I support > breaking them ;-) The same for date with commas. The only "broken" > format I agree to consider useful is '20 Oct 2020'. Given that > space-parsing problems appear inside the time part, perhaps it could > be re-allowed for dates (only). This is stil ambiguous. This is valid in v3: ---- SQL> select timestamp '22 oct' from rdb$database; CONSTANT ========================= 2020-10-22 00:00:00.0000 SQL> select timestamp '22 oct 20' from rdb$database; CONSTANT ========================= 2020-10-22 00:00:00.0000 ---- But now you can't easily know if 20 is a time zone or a year. Adriano |
|
From: Alex P. <pes...@ma...> - 2020-10-22 11:16:08
|
On 10/22/20 2:09 PM, Adriano dos Santos Fernandes wrote: > SQL> select timestamp '22 oct 20' from rdb$database; > > CONSTANT > ========================= > 2020-10-22 00:00:00.0000 > ---- > > But now you can't easily know if 20 is a time zone or a year. In this particular case that's known - time is not provided at all, i.e. 20 is a year. |
|
From: Mark R. <ma...@la...> - 2020-10-22 12:33:49
|
On 22-10-2020 13:09, Adriano dos Santos Fernandes wrote:
> On 20/10/2020 08:42, Dmitry Yemanov wrote:
> This is stil ambiguous.
>
> This is valid in v3:
>
> ----
> SQL> select timestamp '22 oct' from rdb$database;
>
> CONSTANT
> =========================
> 2020-10-22 00:00:00.0000
>
> SQL> select timestamp '22 oct 20' from rdb$database;
>
> CONSTANT
> =========================
> 2020-10-22 00:00:00.0000
> ----
>
> But now you can't easily know if 20 is a time zone or a year.
Neither of those are technically valid timestamp literals if you look at
the SQL standard*; the minimum valid literal for that timestamp would be
2020-10-22 00:00:00.
The problem is that Firebird is handling timestamp literals as a form of
casting, which it shouldn't do, and thus allows to full gamut of
ambiguous date formats Firebird allows in CAST(...) (which, BTW, it also
shouldn't do according to the standard). I would prefer if the timestamp
literal were restricted to what is defined in the SQL standard, but that
is a ship that has sailed.
In my opinion, the 20 in the string 22 oct 20 refers to the year, and
definitely not a time zone. Expecting a time zone IMHO only makes sense
if a time value has been specified, which should require at minimum the
hour and minutes to be specified as well.
On a related note, given time zones are new in Firebird 4, I think we
should be more strict about them and not introduce ambiguity in their
syntax, and we should follow the SQL standard requirements, and always
expect:
<sign> <hours value> <colon> <minutes value>
(which, to be clear, requires + or -, and both hours and minutes, just
having '20' should not be sufficient, that should be +20:00).
* The SQL standard defines the datetime literals as:
<sign> ::=
<plus sign>
| <minus sign>
<datetime literal> ::=
<date literal>
| <time literal>
| <timestamp literal>
<date literal> ::=
DATE <date string>
<time literal> ::=
TIME <time string>
<timestamp literal> ::=
TIMESTAMP <timestamp string>
<date string> ::=
<quote> <unquoted date string> <quote>
<time string> ::=
<quote> <unquoted time string> <quote>
<timestamp string> ::=
<quote> <unquoted timestamp string> <quote>
<time zone interval> ::=
<sign> <hours value> <colon> <minutes value>
<date value> ::=
<years value> <minus sign> <months value> <minus sign> <days value>
<time value> ::=
<hours value> <colon> <minutes value> <colon> <seconds value>
<unquoted date string> ::=
<date value>
<unquoted time string> ::=
<time value> [ <time zone interval> ]
<unquoted timestamp string> ::=
<unquoted date string> <space> <unquoted time string>
<years value> ::=
<datetime value>
<months value> ::=
<datetime value>
<days value> ::=
<datetime value>
<hours value> ::=
<datetime value>
<minutes value> ::=
<datetime value>
<seconds value> ::=
<seconds integer value> [ <period> [ <seconds fraction> ] ]
<seconds integer value> ::=
<unsigned integer>
<seconds fraction> ::=
<unsigned integer>
<datetime value> ::=
<unsigned integer>
--
Mark Rotteveel
|
|
From: Adriano d. S. F. <adr...@gm...> - 2020-10-22 14:49:54
|
On 22/10/2020 09:33, Mark Rotteveel wrote: > > On a related note, given time zones are new in Firebird 4, I think we > should be more strict about them and not introduce ambiguity in their > syntax, and we should follow the SQL standard requirements, and always > expect: > <sign> <hours value> <colon> <minutes value> Agree. Adriano |
|
From: Adriano d. S. F. <adr...@gm...> - 2020-10-24 21:52:24
|
Em qui, 22 de out de 2020 09:34, Mark Rotteveel <ma...@la...> escreveu: > On a related note, given time zones are new in Firebird 4, I think we > should be more strict about them and not introduce ambiguity in their > syntax, and we should follow the SQL standard requirements, and always > expect: > <sign> <hours value> <colon> <minutes value> > (which, to be clear, requires + or -, and both hours and minutes, just > having '20' should not be sufficient, that should be +20:00). > Please create a ticket for this. Adriano |
|
From: Mark R. <ma...@la...> - 2020-10-25 06:50:11
|
On 24-10-2020 23:51, Adriano dos Santos Fernandes wrote: > Please create a ticket for this. Done: http://tracker.firebirdsql.org/browse/CORE-6429 -- Mark Rotteveel |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X