firebird 5.0.1 SQL Dialect 1 or 3

[2007225 ender]

Dear Firebird Developers,

I hope you can help me solve a migration issue. After upgrading my Firebird 2.5 database to version 5.0.1, I've encountered a strange situation:

When I execute the statement "update set a=1, b=a+1", and the initial value of a is 0, the final result for b is 1, instead of the expected 2.

I suspect this is due to Firebird 5.0 defaulting to use SQL Dialect 3.

I would like to know how I can resolve this problem? I do not want to modify the existing program code, but rather set the database to default to using SQL Dialect 1. Would this resolve the issue I'm experiencing?

Please provide some suggestions and assistance, I would be greatly appreciative.

Thank you!

[Mark Rotteveel]

On 16/10/2024 08:42, 2007225 ender wrote:
> I hope you can help me solve a migration issue. After upgrading my
> Firebird 2.5 database to version 5.0.1, I've encountered a strange
> situation:
>
> When I execute the statement "update set a=1, b=a+1", and the initial
> value of a is 0, the final result for b is 1, instead of the expected 2.
>
> I suspect this is due to Firebird 5.0 defaulting to use SQL Dialect 3.
>
> I would like to know how I can resolve this problem? I do not want to
> modify the existing program code, but rather set the database to default
> to using SQL Dialect 1. Would this resolve the issue I'm experiencing?

This is not related to the dialect (and as dialect 1 is deprecated, you
should not be using it at all). This was an intentional change in
Firebird 2.5. See also
https://www.firebirdsql.org/file/documentation/release_notes/html/en/2_5/rnfb25-compat-sql.html#rnfb25-compat-sql-exec
and
https://www.firebirdsql.org/file/documentation/release_notes/html/en/2_5/rnfb25-fbconf-oldset.html

Likely in Firebird 2.5, you had set the OldSetClauseSemantics setting to
1 instead of addressing this issue at that time, and this setting was
removed in Firebird 3.0

Mark
--
Mark Rotteveel

[liviuslivius]

Hi

No it is not about dialect only follow sql standard. You must rewrite such query. Values of fields used in calulations are stable now (fixed) sou you must do this 

a=1, b=1+1

If your calculation will be different

a=a+1, b=a+1

You must then rewrite it as

a=a+1, b=a+1+1

I do not know if there is some compatybility switch in fbconfig.

Regards,

Karol Bieniaszewski

-------- Oryginalna wiadomość --------

Od: 2007225 ender <kangw...@gmail.com>

Data: 16.10.2024 08:42 (GMT+01:00)

Do: firebird-support <firebird...@googlegroups.com>

Temat: [firebird-support] firebird 5.0.1 SQL Dialect 1 or 3

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/fc0f28e0-1e5a-4d0e-9f5b-f11f27604ef0n%40googlegroups.com.

[Germán B]

The right part of the assignment always uses the old value of the column, even if it has been previously updated. In other words, it always uses old.value 

In your case: 

Bad: set a=2, b=a+1 

Good: set a=2, b = 2+1

---

De: 'liviuslivius' via firebird-support <firebird...@googlegroups.com>
Enviado: miércoles, 16 de octubre de 2024 14:28
Para: firebird...@googlegroups.com <firebird...@googlegroups.com>
Asunto: RE: [firebird-support] firebird 5.0.1 SQL Dialect 1 or 3

 

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/4XTHwy6NQYzlk4Sr%40smtp.poczta.onet.pl.

[Mark Rotteveel]

On 16/10/2024 19:28, 'liviuslivius' via firebird-support wrote:
> I do not know if there is some compatybility switch in fbconfig.

There was one in Firebird 2.5 (OldSetClauseSemantics) as a temporary
measure, it was removed in Firebird 3.0 (see
https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rlsnotes30.html#rnfb30-compat-config).

Mark
--
Mark Rotteveel