SQL Server not returning (all) rows (Schrödingers Database)

Copper Contributor

Hi,
i haven't seen this in 20 years of my experience. We tried nearly everything to figure out what's happening here. I hope someone can explain this..

The facts:
There is a view that returns a list of  records. It has been noticed that rows are missing in the result set (at the end in powerbi reports).

To make it clear what happens, please look at those queries. For my understanding this is impossible, but this is the real result:

Dixus_16-1699952476810.png

The record with id 9952 is missing in select * query. It's returned when selected explicitly ( where id = 9952). Its not returned when using >= or <=.

What I tried:
- rebuild all indexes
- created a new table and new views even without indexes 

The behavior was still the same. 

So have you seen something like this before? This scares me and i want to understand it. 
And i need to fix our reports.

Kind regards
Holger

15 Replies

Hi @Dixus -- Can you provide some additional information such as the version number for SQL Server, database compatibility mode, and the logic within the view_churn view?  Take care.

It's just an azure sql instance running on S4 tier: Microsoft SQL Azure (RTM) - 12.0.2000.8

The view is quite complex. But should'nt that be irrelevant? I mean that what I posted cannot happen in my understanding of logic or?

@Dixus 

 

Hi, Holger.

 

What is the type of the [orgid] column from the view? (Not the underlying table.)

 

The only contrived example I can think of is where implicit type conversion is taking place where perhaps you're expecting [orgid] to be an integer type yet the view has come to a different conclusion.

 

You can see how implicit conversion in the equality example below (first query) ignores the leading space from the actual column value. Note: I'm just using the space here as an example - I'm not Unicode wizard meaning perhaps other characters could exhibit this same behaviour.

 

Example

DROP TABLE IF EXISTS [table1]
GO

CREATE TABLE [Table1]
(
	[orgid] [nvarchar](8) NOT NULL
)
GO

INSERT INTO
	[Table1]
VALUES
	(N'9943')
	, (N'9951')
	, (N' 9952')
	, (N'9974')
GO


SELECT
	*
FROM
	[Table1]
WHERE
	[orgid] = 9952;


SELECT
	*
FROM
	[Table1]
ORDER BY
	[orgid];

 

Output

LainRobertson_0-1700042359758.png

 

 

Then again, maybe my brain's on the fritz and it's got nothing to do with this at all. But it's all that came to mind.

 

Cheers,

Lain

Very intresting thoughts.. The colum is an integer. And in the view its also only used for joins with orgids in other tables that are also integers.

I do not think that this is the problem, because a "select * from .." without any condition does not contain the rows as well...

Dixus_2-1700042785278.png


I also copied all Ids from the response and made a text search. 9952 is not being returned.. 

@Dixus 

 

Hi, Holger.

 

Does the following query include 9952?

 

If not, then I'm out of ideas for now and we'd really need the table schema(s) and view definition to learn more. Otherwise, we're just blindly guessing.

 

 

SELECT
	CAST([orgid] AS [int]) AS [OrgId]
FROM
	[View_Churn]
WHERE
	CAST([orgid] AS [int]) BETWEEN 9951 AND 9974;

 

 

Cheers,

Lain

@LainRobertson yes it's included:

Dixus_0-1700055130162.png


But it's also included without the cast:

Dixus_1-1700055155472.png


And it's included using two conditions:

Dixus_2-1700055190904.png

 

But not using only one condition:

Dixus_3-1700055212613.png


Either it's not there without any conditions:

Dixus_4-1700055257341.png

 



@Dixus 

 

Yep, okay. There's some different examples in the final post to the first two posts.

 

Sticking with the type casting for a moment (there is a point to this) purely to address your concerns around using a single WHERE criteria, does the following return 9952 at all?

 

 

SELECT
	CAST([orgid] AS [int]) AS [OrgId]
FROM
	[View_Churn]
WHERE
	CAST([orgid] AS [int]) > 9951
ORDER BY
	[OrgId];

 

 

Cheers,

Lain

@Dixus 

 

By correct, you mean that 9952 shows? Or it didn't show?

 

Cheers,

Lain

@LainRobertson it did not. Look at the screenshot on it's not there..

@Dixus 

 

Okay, cool. I didn't see a screenshot in the post which is why I was checking.

 

LainRobertson_0-1700056791673.png

 

I have nothing else to add then as that rules implicit type conversion out for me. It might help to see the view definition but otherwise, I have no idea.

 

Cheers,

Lain

Hi @Dixus -- This is interesting, but I am with @LainRobertson in that without seeing the view logic there are simply too many variables.  My only other idea is to check your ANSI_NULLS setting on the connection/table/view etc. to ensure the value is not being eliminated, though without additional testing I'm not sure that would address all of your scenarios.  Take care.

Thanks for the try and the help.

At least I could figure out, that the issue is one property that is caclulated using CASE (SELECT TOP 1 ... ) 1 else 0 END

The column is 1 or 0 depending on the condition you give (WHERE orgid >= 9952 or without that). This is scary.
The problem was the error in that subquery (sort by was wrong). I think that different execution plans ran into different outputs. Depending on the conditions.

Hi @Dixus -- Glad you solved it.  Nice work.  Take care.