Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Whitespace as date separator causes conversion error [CORE6427] #6665

Closed
firebird-issue-importer opened this issue Oct 22, 2020 · 14 comments
Closed

Comments

@firebird-issue-importer

Submitted by: @dmitry-starodubov

Such string to date conversion that work in pre-FB4 versions fails in FB4:

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 CORE5750 and it breaks backward compatibility.

Commits: ff37d44

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 22, 2020

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 22, 2020

Commented by: @asfernandes

Should space separator also be allowed when month is in numeric format (01 01 1900)?

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 22, 2020

Commented by: @asfernandes

Also, '02oct20' (without spaces) is valid in v3. Should it be?

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 23, 2020

Commented by: @mrotteveel

Maybe better to ask that on firebird-devel to get more eyes on it. Personally, I'd say: allow spaces in 01 01 1900, but do not accept the version without separators (02oct20), however that could potentially lead to a backwards comptibility issue.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 23, 2020

Commented by: @dyemanov

I agree with Mark.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 23, 2020

Commented by: @dmitry-starodubov

Our developers usually use formats "DD-MM-YYYY", "YYYY-MM-DD", "DD.MM.YYYY".
So "DD MMM YYYY" is some kind of abberation). But it's used and causes problem to migrate our application to v4.
As for me, I agree with Mark and Dmitry. "DD MM YYYY" looks usable. Date without separators looks weird.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 26, 2020

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 4.0 RC 1 [ 10930 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 26, 2020

Modified by: @asfernandes

Version: 4.0 Beta 2 [ 10888 ]

Version: 4.0 RC 1 [ 10930 ] =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 28, 2020

Commented by: @pavel-zotov

Something wrong occurs when delimiter is space and FIRST token (i.e. day number) is greater than 12:

select cast('01 01 2000' as timestamp) from rdb$database;

2000-01-01 00:00:00.0000 -- OK, expected

select cast('12 01 2000' as timestamp) from rdb$database;

2000-12-01 00:00:00.0000 -- OK, expected

select cast('13 01 2000' as timestamp) from rdb$database;

Statement failed, SQLSTATE = 22018
conversion error from string "13 01 2000"

PS
Checked on 4.0.0.2238

PPS.
The same result in FB 2.5 and 3.x, so this is not regression. But anyway parser could be more smarter here.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 28, 2020

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Deferred

Test Details: See note 28/Oct/20 07:03 AM.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 28, 2020

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Deferred => No test

Test Details: See note 28/Oct/20 07:03 AM. =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 28, 2020

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 28, 2020

Modified by: @pavel-zotov

status: Closed [ 6 ] => Closed [ 6 ]

QA Status: No test => Done successfully

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 28, 2020

Commented by: @mrotteveel

@pavel In your example, the first token is taken as the month, not the day (ie American order). This is simply one of the problem with the entirely ambiguous parsing Firebird does. Following the SQL standard to the letter, it should by default only accept something akin to yyyy-MM-dd HH:mm:ss.ffff, all other formats should be handled through CAST( ... as ... FORMAT <format-string>), but that is no longer an option.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants