I get different results in PostgreSQL/Babelfish on AWS Aurora than I do with SQL Server for the following:

DECLARE @template NVARCHAR(MAX) = ' {@a} and {@b} and {@c}';

DECLARE @params AS TABLE 
(
    k VARCHAR(255),
    v VARCHAR(MAX)
)
 
INSERT INTO @params VALUES ('b','second')
INSERT INTO @params VALUES ('a','first')
INSERT INTO @params VALUES ('c','more')

SELECT 
    @template = REPLACE(@template, '{@' + k + '}', ISNULL(v,'NULL'))
FROM @params;

SELECT @template;

SQL Server: "first and second and more"

Babelfish: "{@a} and {@b} and more"

Is this a PostgreSQL difference, a Babelfish difference, my error?

I get different results in PostgreSQL/Babelfish on AWS Aurora than I do with SQL Server for the following:

DECLARE @template NVARCHAR(MAX) = ' {@a} and {@b} and {@c}';

DECLARE @params AS TABLE 
(
    k VARCHAR(255),
    v VARCHAR(MAX)
)
 
INSERT INTO @params VALUES ('b','second')
INSERT INTO @params VALUES ('a','first')
INSERT INTO @params VALUES ('c','more')

SELECT 
    @template = REPLACE(@template, '{@' + k + '}', ISNULL(v,'NULL'))
FROM @params;

SELECT @template;

SQL Server: "first and second and more"

Babelfish: "{@a} and {@b} and more"

Is this a PostgreSQL difference, a Babelfish difference, my error?

• sql-server
• postgresql
• t-sql
• babelfish

• 1 @DaleK Babelfish is a translation layer whose explicit aim is to run the Microsoft SQL Server "dialect" on a PostgreSQL (or AWS Aurora) backend. – IMSoP Commented Mar 4 at 20:42
• I don't have a direct answer, but I suspect the actual difference here is in how assignments are handled when referencing the same variable on each row of a SELECT. I can't test it right now, but an even simpler example would probably be SELECT @foo = @foo + SomeTable.X FROM SomeTable – IMSoP Commented Mar 4 at 20:45
• 3 Actually so the fact that SQL Server does that is an anti-pattern that is recommended not to be used, and the behaviour is not deterministic. You should not rely on the fact that it automatically concatenates those rows into 1. You should use a proper mechanism to concat them. I can't find a reference off the top of my head for not using that approach, but there are loads around. – Dale K Commented Mar 4 at 20:59
• 4 This is an example of the recursive variable assignment anti pattern learn.microsoft/en-us/sql/t-sql/language-elements/… - for recursive replace there isn't a straight forward replacement that I can immediately think of – Martin Smith Commented Mar 4 at 21:10
• 1 Thank you @MartinSmith, I didn't know that. I rewrote it as a recursive CTE. – Salah Commented Mar 4 at 23:53

1 Answer 1

Per Martin Smith I learned that a recursive select can't be relied on. I rewrote it as a recursive CTE and it works on both platforms:

DECLARE @template NVARCHAR(MAX) = ' {@a} and {@b} and {@c}';

DECLARE @params AS TABLE (
  k VARCHAR(255),
  v VARCHAR(MAX)
);
INSERT INTO @params VALUES ('b', 'second'), ('a', 'first'), ('c', 'more');

;
WITH params AS (
  SELECT 
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn, 
    k, 
    v
  FROM @params
),
replacer (repl, paramrow) AS (
  SELECT @template, CAST(0 AS BIGINT) 
  UNION ALL
  SELECT 
    REPLACE(r.repl, '{@' + p.k + '}', p.v),
    p.rn
  FROM replacer r
  INNER JOIN params p ON r.paramrow + 1 = p.rn
)

SELECT TOP 1 repl 
FROM replacer
ORDER BY paramrow DESC;