[Bug]: The execution order of the SELECT assignment statement is not compatible with BF and SQL Server

[vccomnet]

What happened?

SELECT assignment statement, BF and SQL Server have different execution results

Hi, I found an issue while porting the SQL Server project to the Babelfish database, which is that the behavior of BF's SELECT assignment statement is different from SQL Server, as BF does not reference the latest value for assignment. The test screenshot is as follows:

In test case 1, @ TableName was not assigned the priority calculated @ Suffix, and the final output of @ TabelName was NULL, which should be an error.

Similarly, in test case 2, the output of @ b should be 2. The output of SQL Server is expected, but the output of BF is incorrect. The root cause should be that BF did not assign the latest variable value.

Please confirm if this is a bug, thank you.

Appendix:

TEST CASE 1:

DECLARE @ TableName nvarchar (100) DECLARE @ Suffix nvarchar (100)

SELECT @Suffix=CONVERT (VARCHAR (8), Getdata(), 112) , @ TableName=@ Suffix

SELECT @ Suffix, @ TableName

TEST CASE 2:

DECLARE @ a int DECLARE @ b int

SELECT @a=1 , @ b=@ a+1

SELECT @ a, @ b

Version

BABEL_3_X_DEV (Default)

Extension

None

Which flavor of Linux are you using when you see the bug?

No response

Relevant log output

None

Code of Conduct

• [X] I agree to follow this project's Code of Conduct.

[robverschoor]

This is a known difference between Postgres/Babelfish and SQL Server. This query assumes that the assignment to the variables is performed in a particular order, i.e @Suffix is assigned first and @TableName is assigned next and will reference the value of the just-assigned @Suffix variable. PG does not work the same way, but instead uses the value of each variable before the start of the query. Solution is to split this into two assignments.

[robverschoor]

To add, the Babelfish Compass tool detects such SELECT statements where a dependency between variable assignments exists, and reports these in the "Review Semantics" category. However, this particular case was not reported by the current version of Compass. However, when the next Compass version is released (coming 'very soon'), this case will also be reported (look for "Variable assignment dependency in SELECT" in the Compass report)