Wrong message about invalid time zone in CAST FORMAT

[aafemt]

Value is truncated or missed:

SQL> select cast ('2005-03-16 01:02:03.1234 +01:00' as timestamp with time zone format 'YYYY-MM-DD HH24:MI:SS.FF4 TZR') from rdb$database;

                                                     CAST
=========================================================
Statement failed, SQLSTATE = 22009
Invalid time zone region:
SQL> select cast ('2005-03-16 01:02:03.1234 abcd' as timestamp with time zone format 'YYYY-MM-DD HH24:MI:SS.FF4 TZR') from rdb$database;

                                                     CAST
=========================================================
Statement failed, SQLSTATE = 22009
Invalid time zone region: A

BTW, Oracle and encodeTimeStampTz() are fine with the first value. Perhaps FORMAT also should accept displacement for TZR element.

[TreeHunter9]

Perhaps FORMAT also should accept displacement for TZR element.

Okey, we stole this pattern from Oracle anyway, so why not

[pavel-zotov]

QA note.
Still see weird outcome for some totally pointless values:

select cast ('00:2147483647' as time with time zone format 'TZH:TZM') from rdb$database;

Statement failed, SQLSTATE = HY000
Value for TZM pattern is out of range [0, 59] // OK, expected

select cast ('00:2147483648' as time with time zone format 'TZH:TZM') from rdb$database;

00:00:00.0000 +00:00 // ??

show version;
ISQL Version: WI-T6.0.0.710 Firebird 6.0 Initial
Server version:
Firebird/Windows/AMD/Intel/x64 (access method), version "WI-T6.0.0.710 Firebird 6.0 Initial"
Firebird/Windows/AMD/Intel/x64 (remote server), version "WI-T6.0.0.710 Firebird 6.0 Initial/tcp (PZ)/P20:C"
Firebird/Windows/AMD/Intel/x64 (remote interface), version "WI-T6.0.0.710 Firebird 6.0 Initial/tcp (PZ)/P20:C"
on disk structure version 14.0

[TreeHunter9]

I'll fix it

[TreeHunter9]

QA note. Still see weird outcome for some totally pointless values:

select cast ('00:2147483647' as time with time zone format 'TZH:TZM') from rdb$database;

Statement failed, SQLSTATE = HY000
Value for TZM pattern is out of range [0, 59] // OK, expected

select cast ('00:2147483648' as time with time zone format 'TZH:TZM') from rdb$database;

00:00:00.0000 +00:00 // ??

show version;
ISQL Version: WI-T6.0.0.710 Firebird 6.0 Initial
Server version:
Firebird/Windows/AMD/Intel/x64 (access method), version "WI-T6.0.0.710 Firebird 6.0 Initial"
Firebird/Windows/AMD/Intel/x64 (remote server), version "WI-T6.0.0.710 Firebird 6.0 Initial/tcp (PZ)/P20:C"
Firebird/Windows/AMD/Intel/x64 (remote interface), version "WI-T6.0.0.710 Firebird 6.0 Initial/tcp (PZ)/P20:C"
on disk structure version 14.0

Created PR #8501, which fixes this overflow and also fixes cases where there is a missing value in the input string for pattern, for example:

SELECT CAST('A.M. 8' as time format 'MI A.M. HH12') FROM RDB$DATABASE;

Where value for MI pattern is missing.