Azure Data Studio seems to be corrupting results for a SELECT query from Notebook

241 views Asked by At

This morning I ran a query against one of my tables and panicked when I got back results that looked like the data had been manipulated or was possibly corrupted. With investigation I found that running the query again resulted in different records being corrupted and previously corrupted records were fine. Using a WHERE clause and ORDER BY I would get records that were corrupted in the columns used for filtering and ordering, which should have been missing if the underlying data was corrupt.

Here's an example of the results: enter image description here Things to note, on most of the corrupted results, the primary key seems to be replaced by the ID from another column. The other columns in a given row all have the same random data prepended to the value. Sometimes, but not always, that value partially comes from another one of the columns. On one of the rows you can see that the ID in the first column is null, which is not allowed since that's the primary key. Sometimes the query comes back with multiple rows with a null ID column.

Looking at the primary index, the first column, I can query for the record based on the id that would fill in the gap and I get back a clean record. For instance, I see that the sequence goes from 176977, 176976 is corrupt but 176975 is ok. I will query specifically for 176976 and the record is returned normally.

I don't have it showing in the screenshot but there are date columns too. Those date columns come back corrupted in those rows as well, with dates before the year 1000 typically, yet if my where clause is for a recent date, those records still get returned so the underlying data is intact.

I switched to SSMS to try to figure out what was going on and SSMS returns the data clean every time I've used it today, making me think this is a problem with Azure Data Studio or my current connection via Azure Data Studio. This is not something I've ever seen before.

So far I have only noticed this issue with this one table. These queries were all through a Notebook in Azure Data Studio. I just tried running that query a couple times as just a query, rather than out of the Notebook, and it returned clean records. I do not have any extensions installed in Data Studio. Data Studio is version 1.47.0 which is up to date according to Data Studio's check for updates feature.

Update: I can successfully run the query from a dedicated sql pane in Data Studio. It seems to only corrupt things when run from the Notebook.

Edit: The query is one I've run hundreds of times over a span of years without ever seeing this before. Sorry, I should have thought to include this from the start. Collation is SQL_Latin1_General_CP1_CI_AS.

select top(100) * from WebhookEvents order by Id desc

The table is:

CREATE TABLE [dbo].[WebhookEvents](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [EventId] [int] NOT NULL,
    [EventName] [nvarchar](35) NOT NULL,
    [EventBody] [nvarchar](max) NOT NULL,
    [WebhookEventResult] [nvarchar](15) NOT NULL,
    [ResultMessage] [nvarchar](500) NULL,
    [CreatedDateTimeUtc] [datetime] NOT NULL,
    [ModifiedDateTimeUtc] [datetime] NOT NULL,
    [CustomerEmail] [nvarchar](255) NULL,
    [SubscriptionId] [int] NULL,
 CONSTRAINT [PK_dbo.WebhookEvents] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
0

There are 0 answers