[Bug]: Unexpected behaviour with Aggregate Concatenation Queries

[gutosantos82]

Contact Details

[email protected]

What happened?

Unexpected behaviour with Aggregate Concatenation Queries

use tempdb

drop table if exists CharsTable
 
create table CharsTable (letter char(1))
 
insert into CharsTable select 'a'
insert into CharsTable select 'b'
insert into CharsTable select 'c'
insert into CharsTable select 'd'
insert into CharsTable select 'e'
insert into CharsTable select 'f'

declare @string2 as varchar(1000)
set @string2 = ''
select @string2 = @string2 + letter from CharsTable
select @string2

Result:

@string2
f

Expected result:

@string2
abcdef

References: https://social.technet.microsoft.com/wiki/contents/articles/28585.t-sql-safely-concatenate-values-into-a-scalar-variable.aspx https://www.betaarchive.com/wiki/index.php/Microsoft_KB_Archive/287515

Version

BABEL_1_X_DEV (Default)

Extension

babelfishpg_tsql (Default)

What flavour of Linux are you running into the bug?

Ubuntu (Default)

Relevant log output

No response

Code of Conduct

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

[robverschoor]

There are multiple issues here. First, Babelfish does not currently support variable assignments whereby the target variable also occurs in the assignment expression. This is a known issue. Incidentally, the same applies to UPDATE where a variable is assigned in the SET clause.

Second, observe that this query actually does not have a guaranteed result in SQL Server. The order of the concatenation is determined by the order in which rows are accessed while scanning the table. That order depends on the query plan (and is not dependent on an ORDER BY clause since that only affects the final result set). Unless you would force the order of row access, for example with hints and a clustered index, the order of row access could vary, and with that, the final result.

[gutosantos82]

Hi @rcv-aws, thanks for the feedback. Is there a ticket for this known issue? So I can track the development. Yes, I read in the Microsoft KB that in MSSQL the order is also not guaranteed. I'm not specifically interested in the order, I just would like to have the same values disregarding the order.

[robverschoor]

We are tracking this internally at AWS.

[3manuek]

@JimFinnerty I checked this and the f returned by the code block is not related to the bool data type. It returns the last iterated letter.

1> select false;
2> go
bool
0

1> select true;
2> go
bool
1

Test within e as the last letter:

2> drop table if exists CharsTable
4> create table CharsTable (letter char(1))
6> insert into CharsTable select 'a'
7> insert into CharsTable select 'b'
8> insert into CharsTable select 'c'
9> insert into CharsTable select 'd'
10> insert into CharsTable select 'e'

12> declare @string2 as varchar(1000)
13> set @string2 = ''
14> select @string2 = @string2 + letter from CharsTable
15> select @string2
16> GO
@string2
e

[tanscorpio7]

Fixed in https://github.com/babelfish-for-postgresql/babelfish_extensions/pull/3011,

[PauloHMattos]

Fixed in https://github.com/babelfish-for-postgresql/babelfish_extensions/pull/3011,

Should this issue be closed?