NullReferenceException when setting the Dialect to 1

[maxle5]

Problem

Getting a NullReferenceException when attempting to alter a procedure containing the DATE keyword. This could be expected for clients or servers using Dialect 3, however I am setting the Dialect to 1 via IBConnectionStringBuilder.Dialect and can confirm that the server is also set to Dialect 1 (the same statement executes properly with IBConsole and IBExpert)

Investigation

After debugging the InterBaseSql.Data.InterbaseClient package myself, I was able to determine that the underlying error is actually: "DATE data type is now called TIMESTAMP", which tells me the issue likely has something to do with the Dialect.

SQL Dialect Documentation

The DATE data type contains both time and date information and is interpreted as TIMESTAMP; the name has changed but the meaning has not. Dialect 1 clients expect the entire timestamp to be returned. In dialect 1, DATE and TIMESTAMP are identical.

https://docwiki.embarcadero.com/InterBase/2020/en/Understanding_SQL_Dialects

Sample Application

The following repository contains a simple example to help replicate the issue: https://github.com/maxle5/interbase-dialect-one-issue

Environment

• Windows 11 Professional
• Interbase 2020 (update 4)
• AMD Ryzen 9 5900HS
• 32 GB RAM

[jeffovercash]

Dialect is being respected. There actually were no Firebird unit tests for dialect 1 which I never noticed until a few months ago. I have been adding them recently and IBConnectionStringBuilder's dialect tests were already done and passes.

I can verify your issue and now have a unit test around it. There is another strange error when connecting to a dialect 1 DB with Dialect 3 or nor Dialect in the connection string and casting a numeric field defined <= (9, x) to a (10,x) or higher type. If the client is automatically scaled back from a requested dialect 3 to dialect 1 you get an exception from the server "Metadata update statement is not allowed by the current database SQL dialect 1", but this makes no sense (other than in D1 this conversion is to a double precision and in D3 this would be to a scaled int64 which does not exist in Dialect 1).

Right now the only workaround will be to use the TIMESTAMP keyword and not the DATE synonym under Dialect 1. I will continue to try and figure out this and any other dialect 1 strangeness found.

If you run your test setting Dialect = 3 and let it auto scale back to dialect 1 (assuming the DB you are connecting to is a Dialect 1 DB) on the connection you get the same type of error as the one above "Metadata update statement is not allowed by the current database SQL dialect 1". So I think these are somehow the same bug.

[maxle5]

Thank you for the quick response!

We will attempt to workaround this issue by using the TIMESTAMP keyword for now (won't be super easy for us since we have a very large number of scripts/procedures).

However, we did find something interesting recently that may be of help, the issue I reported only seems to be a problem on Interbase 2020 "update 1" (14.1.0.319) or later; my example project works without issue on Interbase XE7 and on the first release of Interbase 2020 (14.0.0.97).

[jeffovercash]

I'll pass that on to Sriram and the IB team too then. Thanks for the additional feedback. I do think it is something specific to the ADO.NET driver exposing this in the IB server as IBX (which I also write and tested through IBConsole) does not throw the same error.

[jeffovercash]

Ok, Sriram and I went over this and found a change in the status vector being returned from XE3 (I didn't have XE7 installed and Ib 2017/2020. That type of statement is supposed to give an argument warning and always has but it is no longer giving a secondary code of isc_sqlwarn. Warnings should not raise exceptions.

So this week he is going to look into why this changed and I will look at how warnings are being handled and why this change in the status vector is causing this type of warning to be interpreted as an exception.

[maxle5]

@jeffovercash, any update on this issue?

[jeffovercash]

Yes, Sriram did find it was a regression bug and has fixed it internally and will be released in a future patch. I am still hoping to find a workaround to how the ADO.NET driver interprets SQL_WARNINGS when the bug part exists so that versions with the bug work around that bug. I know what I am looking for and what the bug looks like in the status vector so hopefully I can fix this without too many problems/rewriting the error-handling code.

[jeffovercash]

I did not get a chance to rewrite the driver's error handling code about warnings in this release. I'll try to look into it in the next few weeks again time permitted (I am recovering from surgery right now which limits my time). This will be fixed on the server side in the next IB patch. Unfortunately, I do not have any timeframe on when that will be released (although if you have a support contract you can probably get an early copy through support or they have done that in the past).

[jeffovercash]

Just an update on this. I still have not had an opportunity to put in a workaround around this IB Bug where warning codes are not being properly filled out so the ADO driver is treating it as a warning and not an error. Like I said earlier, I know the bug has been fixed internally, but I did not have time in the most recent two quick releases to try and workaround this bug as those released were focused on EFCore scaffolding support and EFCore Dialect 1 support

[jeffovercash]

sorry this took so long. This has been addressed in Update 5 of InterBase 2020.