[Bug]: Issue with PIVOT on parameterized queries

[scoquelin]

What happened?

We are in the process of migrating from SQL Server to Babelfish and we noticed that some existing parameterized queries that contains the PIVOT function which are working fine in SQL Server are not interpreted correctly with Babelfish.

Steps to reproduce :

-- Create the SalesData table
CREATE TABLE SalesData (
    Product VARCHAR(50),
    Year INT,
    Sales INT
);

-- Insert sample data into SalesData table
INSERT INTO SalesData (Product, Year, Sales)
VALUES 
    ('Apple', 2023, 100),
    ('Orange', 2023, 150),
    ('Apple', 2024, 120),
    ('Orange', 2024, 130);

-- Simple Pivot query (WORKS on SQL Server and Babelfish)
SELECT Product, [2023], [2024]
FROM 
    (SELECT Product, Year, Sales
     FROM SalesData) AS SourceTable
PIVOT
    (SUM(Sales) FOR Year IN ([2023], [2024])) AS PivotTable;

-- Parameterized Pivot query (WORKS on SQL Server, but FAILS on Babelfish)
EXEC sp_executesql
N'
SELECT Product, [2023], [2024]
FROM 
    (SELECT Product, Year, Sales
     FROM SalesData WHERE PRODUCT = @product) AS SourceTable
PIVOT
    (SUM(Sales) FOR Year IN ([2023], [2024])) AS PivotTable;
',
N'@product VARCHAR(50)',
N'Orange'

The error is:

column "@product" does not exist

Note: I used sp_executesql to troubleshoot/reproduce the issue we are actually having when wrapping query/parameters in SqlCommand in C#

Also : if the PIVOT part is removed from the query and we just keep the intermediate query :

EXEC sp_executesql
N'
--SELECT Product, [2023], [2024]
--FROM 
    (SELECT Product, Year, Sales
     FROM SalesData WHERE PRODUCT = @product) --AS SourceTable
--PIVOT
--    (SUM(Sales) FOR Year IN ([2023], [2024])) AS PivotTable;
',
N'@product VARCHAR(50)',
N'Orange'

query is properly parsed and output is :

Product	Year	Sales
Orange	2023	150
Orange	2024	130

so the usage of the PIVOT function seems to be introducing the issue when used in conjunction with query parameters

Version

BABEL_4_X_DEV

Extension

babelfishpg_tsql (Default)

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

No response

Relevant log output

2025-02-24 22:06:26.582 UTC [13275] CONTEXT:  PL/tsql function inline_code_block line 2 at EXEC_SP
        TDS Protocol: Message Type: SQL BATCH, Phase: TDS_REQUEST_PHASE_PROCESS. Preparing to Send Back the Tds response
2025-02-24 22:06:26.582 UTC [13275] LOG:  Unmapped error found. Code: 50360452, Message: column "@product" does not exist, File: parse_relation.c, Line: 3769, Context: babelfishpg_tsql
2025-02-24 22:06:26.582 UTC [13275] CONTEXT:  TDS Protocol: Message Type: SQL BATCH, Phase: TDS_REQUEST_PHASE_PROCESS. Preparing to Send Back the Tds response
2025-02-24 22:06:26.582 UTC [13275] ERROR:  column "@product" does not exist at character 110
2025-02-24 22:06:26.582 UTC [13275] QUERY:  SELECT Product, [2023], [2024]
        FROM
            (SELECT Product, Year, Sales
             FROM SalesData WHERE PRODUCT = "@product") AS SourceTable
        PIVOT
            (SUM(Sales) FOR Year IN ([2023], [2024])) AS PivotTable
2025-02-24 22:06:26.582 UTC [13275] CONTEXT:  PL/tsql function inline_code_block line 2 at SQL statement
        PL/tsql function inline_code_block line 2 at EXEC_SP
        TDS Protocol: Message Type: SQL BATCH, Phase: TDS_REQUEST_PHASE_PROCESS. Preparing to Send Back the Tds response
2025-02-24 22:06:26.582 UTC [13275] LOG:  Unmapped error found. Code: 50360452, Message: column "@product" does not exist, File: parse_relation.c, Line: 3769, Context: TDS

Code of Conduct

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

[rishabhtanwar29]

Thanks @scoquelin for reporting this issue, I am also able to reproduce this in the latest version and I have created internal task to track the fix.