search

babelfish-for-postgresql / babelfish_extensions

Babelfish for PostgreSQL provides the capability for PostgreSQL to work with applications written for Microsoft SQL Server. Babelfish understands the SQL Server wire-protocol and T-SQL, the Microsoft SQL Server query and procedural language, so you don’t have to switch database drivers or rewrite all of your application queries.
https://babelfishpg.org/
Apache License 2.0
265 stars 88 forks source link

[Bug]: segfault #2489

Open mmmcorpsvit opened 2 months ago

mmmcorpsvit commented 2 months ago

What happened?

-- Step 1: Create the stored procedure
IF OBJECT_ID('sampleproc', 'P') IS NOT NULL
    DROP PROCEDURE sampleproc;
GO

CREATE PROCEDURE sampleproc
AS
BEGIN
    SELECT 3 AS Column1, 99 AS Column2
    UNION
    SELECT 1, 2; -- Added a placeholder column for the second SELECT
END;
GO

IF OBJECT_ID('GetSampleDataJSON', 'FN') IS NOT NULL
    DROP FUNCTION GetSampleDataJSON;
GO

-- Step 2: Create a function to convert result to JSON
CREATE FUNCTION GetSampleDataJSON(@param1 INT)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Json NVARCHAR(MAX);

    DECLARE @temp TABLE (
        Column1 INT,
        Column2 INT
    );

    INSERT INTO @temp (Column1, Column2)
    EXEC sampleproc;

    SELECT @Json = (
        SELECT Column1, Column2, @param1 AS Parameter
        FROM @temp
        FOR JSON AUTO
    );

    RETURN @Json;
END;
GO

-- Step 3: Test the function
DECLARE @param1 INT = 123; -- Example parameter value
SELECT GetSampleDataJSON(@param1) AS SampleDataJSON;

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

2024-04-10 20:40:05 2024-04-10 17:40:05.922 UTC [4643] LOG:  message_type: TDS7 Prelogin Message
2024-04-10 20:40:05 2024-04-10 17:40:05.922 UTC [4643] CONTEXT:  TDS Protocol: Message Type: TDS Pre-Login, Phase: Login. 
2024-04-10 20:40:05 2024-04-10 17:40:05.923 UTC [4643] LOG:  message_type: TDS7 Login
2024-04-10 20:40:05 2024-04-10 17:40:05.923 UTC [4643] CONTEXT:  TDS Protocol: Message Type: TDS Login7, Phase: Login. 
2024-04-10 20:40:07 2024-04-10 17:40:07.024 UTC [4648] LOG:  message_type: TDS7 Prelogin Message
2024-04-10 20:40:07 2024-04-10 17:40:07.024 UTC [4648] CONTEXT:  TDS Protocol: Message Type: TDS Pre-Login, Phase: Login. 
2024-04-10 20:40:07 2024-04-10 17:40:07.025 UTC [4648] LOG:  message_type: TDS7 Login
2024-04-10 20:40:07 2024-04-10 17:40:07.025 UTC [4648] CONTEXT:  TDS Protocol: Message Type: TDS Login7, Phase: Login. 
2024-04-10 20:40:07 2024-04-10 17:40:07.422 UTC [4648] LOG:  EOF on TDS socket
2024-04-10 20:40:07 2024-04-10 17:40:07.422 UTC [4648] CONTEXT:  TDS Protocol: EOF reached on TDS socket
2024-04-10 20:40:07 2024-04-10 17:40:07.647 UTC [36] LOG:  server process (PID 4643) was terminated by signal 11: Segmentation fault
2024-04-10 20:40:07 2024-04-10 17:40:07.647 UTC [36] DETAIL:  Failed process was running: 
2024-04-10 20:40:07     -- Step 3: Test the function
2024-04-10 20:40:07     DECLARE @param1 INT = 123; -- Example parameter value
2024-04-10 20:40:07     SELECT GetSampleDataJSON(@param1) AS SampleDataJSON;
2024-04-10 20:40:07 
2024-04-10 20:40:07 2024-04-10 17:40:07.647 UTC [36] LOG:  terminating any other active server processes
2024-04-10 20:40:07 2024-04-10 17:40:07.647 UTC [36] LOG:  all server processes terminated; reinitializing
2024-04-10 20:40:07 2024-04-10 17:40:07.658 UTC [4653] LOG:  database system was interrupted; last known up at 2024-04-10 17:39:48 UTC
2024-04-10 20:40:07 2024-04-10 17:40:07.971 UTC [4653] LOG:  database system was not properly shut down; automatic recovery in progress
2024-04-10 20:40:07 2024-04-10 17:40:07.974 UTC [4653] LOG:  redo starts at 0/46C73520
2024-04-10 20:40:07 2024-04-10 17:40:07.975 UTC [4653] LOG:  invalid record length at 0/46C89E38: expected at least 24, got 0
2024-04-10 20:40:07 2024-04-10 17:40:07.975 UTC [4653] LOG:  redo done at 0/46C89D60 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2024-04-10 20:40:07 2024-04-10 17:40:07.980 UTC [4654] LOG:  checkpoint starting: end-of-recovery immediate wait
2024-04-10 20:40:08 2024-04-10 17:40:08.007 UTC [4654] LOG:  checkpoint complete: wrote 19 buffers (0.1%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.007 s, sync=0.008 s, total=0.028 s; sync files=15, longest=0.004 s, average=0.001 s; distance=90 kB, estimate=90 kB; lsn=0/46C89E38, redo lsn=0/46C89E38
2024-04-10 20:40:08 2024-04-10 17:40:08.010 UTC [36] LOG:  database system is ready to accept connections
2024-04-10 20:40:08 2024-04-10 17:40:08.506 UTC [4659] LOG:  message_type: TDS7 Prelogin Message
2024-04-10 20:40:08 2024-04-10 17:40:08.506 UTC [4659] CONTEXT:  TDS Protocol: Message Type: TDS Pre-Login, Phase: Login. 
2024-04-10 20:40:08 2024-04-10 17:40:08.507 UTC [4659] LOG:  message_type: TDS7 Login
2024-04-10 20:40:08 2024-04-10 17:40:08.507 UTC [4659] CONTEXT:  TDS Protocol: Message Type: TDS Login7, Phase: Login. 
2024-04-10 20:40:09 2024-04-10 17:40:09.252 UTC [4659] LOG:  message_type: Remote Procedure Call (3) rpc_packet_type: sp_executesql
2024-04-10 20:40:09 2024-04-10 17:40:09.252 UTC [4659] CONTEXT:  TDS Protocol: Message Type: RPC, SP Type: SP_EXECUTESQL, Phase: TDS_REQUEST_PHASE_FETCH. Fetching RPC Request
2024-04-10 20:40:09 2024-04-10 17:40:09.338 UTC [4659] LOG:  message_type: Remote Procedure Call (3) rpc_packet_type: sp_executesql
2024-04-10 20:40:09 2024-04-10 17:40:09.338 UTC [4659] CONTEXT:  TDS Protocol: Message Type: RPC, SP Type: SP_EXECUTESQL, Phase: TDS_REQUEST_PHASE_FETCH. Fetching RPC Request
2024-04-10 20:40:09 2024-04-10 17:40:09.662 UTC [4659] ERROR:  table "#sver" does not exist
2024-04-10 20:40:09 2024-04-10 17:40:09.662 UTC [4659] CONTEXT:  SQL statement "drop table #SVer"
2024-04-10 20:40:09     PL/tsql function inline_code_block line 223 at SQL statement
2024-04-10 20:40:09     TDS Protocol: Message Type: SQL BATCH, Phase: TDS_REQUEST_PHASE_PROCESS. Writing Done Token
2024-04-10 20:41:24 2024-04-10 17:41:24.280 UTC [4667] LOG:  message_type: TDS7 Prelogin Message
2024-04-10 20:41:24 2024-04-10 17:41:24.280 UTC [4667] CONTEXT:  TDS Protocol: Message Type: TDS Pre-Login, Phase: Login. 
2024-04-10 20:41:24 2024-04-10 17:41:24.281 UTC [4667] LOG:  message_type: TDS7 Login
2024-04-10 20:41:24 2024-04-10 17:41:24.281 UTC [4667] CONTEXT:  TDS Protocol: Message Type: TDS Login7, Phase: Login.

Code of Conduct

mmmcorpsvit commented 2 months ago

jonathanpotts/babelfishpg:BABEL_4_0_0__PG_16_1

mmmcorpsvit commented 2 months ago 
-- Step 1: Create the stored procedure
IF OBJECT_ID('sampleproc', 'P') IS NOT NULL
    DROP PROCEDURE sampleproc;
GO

CREATE PROCEDURE sampleproc
AS
BEGIN
    SELECT 3 AS Column1, 99 AS Column2
    UNION
    SELECT 1, 2; -- Added a placeholder column for the second SELECT
END;
GO

IF OBJECT_ID('GetSampleDataJSON', 'FN') IS NOT NULL
    DROP FUNCTION GetSampleDataJSON;
GO

-- Step 2: Create a function to convert result to JSON
CREATE FUNCTION GetSampleDataJSON()
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Json NVARCHAR(MAX);

    DECLARE @temp TABLE (
        Column1 INT,
        Column2 INT
    );

    INSERT INTO @temp (Column1, Column2)
    EXEC sampleproc;

    SELECT @Json = (
        SELECT Column1, Column2
        FROM @temp
        FOR JSON AUTO
    );

    RETURN @Json;
END;
GO

-- Step 3: Test the function
SELECT GetSampleDataJSON() AS SampleDataJSON;
staticlibs commented 2 months ago

Hi, this example works for me on latest BABEL_4_X_DEV, I assume it was fixed in #2275 .

Deepesh125 commented 2 months ago

This mentioned fix was also cherry-picked on BABEL_3_X_DEV through PR #2303. @mmmcorpsvit Can you please check if your local branch contains this change?

mmmcorpsvit commented 2 months ago

i use jonathanpotts/babelfishpg:BABEL_4_0_0__PG_16_1 (when create issue - are avaliable only 3.x in dropdown)

kuntalghosh commented 1 month ago

Note related to the issue, but fixed the issue template to add 4_x_DEV. Thanks for reporting the same.