Queries/Procedures with DECOMPRESS frequently return defaulted result erroneously

[LouisGordon]

Type: Bug

1. Create a table with a [Content] VARBINARY(MAX) NULL column
2. Insert data into the table and using the built in COMPRESS function for the [Content] column
3. Query the value using the DECOMPRESS function ex: SELECT DECOMPRESS(Content) FROM dbo.MyTable
4. Witness that sometimes this works, and frequently it does not with a value of 0x (empty varbinary). Interestingly not null, which might be a clue.

If you add additional columns to the table with data for the query, those are also returned as NULL incorrectly when this happens, even though the data is there.

Azure Data Studio version: azuredatastudio 1.50.0 (49c73a8, 2024-11-17T22:25:36.863Z)
OS version: Darwin arm64 24.1.0
Restricted Mode: No
Preview Features: Enabled
Modes:

System Info

Item	Value
CPUs	Apple M4 Pro (14 x 2400)
GPU Status	2d_canvas: enabled canvas_oop_rasterization: enabled_on direct_rendering_display_compositor: disabled_off_ok gpu_compositing: enabled multiple_raster_threads: enabled_on opengl: enabled_on rasterization: enabled raw_draw: disabled_off_ok skia_graphite: disabled_off video_decode: enabled video_encode: enabled webgl: enabled webgl2: enabled webgpu: enabled webnn: disabled_off
Load (avg)	3, 4, 5
Memory (System)	48.00GB (0.06GB free)
Process Argv
Screen Reader	no
VM	0%

Extensions (4)

Extension	Author (truncated)	Version
profiler	Mic	0.14.0
schema-compare	Mic	1.21.0
sql-database-projects	Mic	1.4.3
powershell	ms-	2024.3.2

[kburtram]

I haven't been able to reproduce using a simple SQL test script (like the one below). Could you please provide some additional details that would be helpful to repro, if you're still impacted by this bug?

-- Example test repro script
-- Step 1: Create a table with a VARBINARY(MAX) column and additional columns
CREATE TABLE dbo.TestTable (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Content VARBINARY(MAX) NULL,
    ExtraColumn NVARCHAR(100) NOT NULL
);

-- Step 2: Insert compressed data and populate the additional column
INSERT INTO dbo.TestTable (Content, ExtraColumn)
VALUES
    (COMPRESS(N'This is some test data'), N'Extra Data 1'),
    (COMPRESS(N'Another piece of test data'), N'Extra Data 2'),
    (NULL, N'Extra Data 3'); -- Testing with NULL content


-- Step 3: Query the table using the DECOMPRESS function
-- Observe if the DECOMPRESS function returns unexpected results
SELECT 
    ID,
    DECOMPRESS(Content) AS DecompressedContent,
    ExtraColumn
FROM dbo.TestTable;


-- Step 4: Add variations for additional testing
-- Insert more rows to simulate different scenarios
INSERT INTO dbo.TestTable (Content, ExtraColumn)
VALUES
    (COMPRESS(N''), N'Empty Content Test'), -- Compressed empty string
    (COMPRESS(N'Special Characters: !@#$%^&*()'), N'Special Characters'),
    (COMPRESS(N''), N'Empty Content Test'), -- Compressed empty string
    (COMPRESS(N'Special Characters: !@#$%^&*()'), N'Special Characters');


-- Query again to check behavior
SELECT 
    ID,
    DECOMPRESS(Content) AS DecompressedContent,
    ExtraColumn
FROM dbo.TestTable;

Final Query resultset consistently looks fine. The 0x field are for empty strings in this example.