Forum Discussion

Copper Contributor

Nov 14, 2023

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

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 ...

Dixus

Copper Contributor

Nov 14, 2023

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?

LainRobertson

Silver Contributor

Nov 15, 2023

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

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

Dixus
Copper Contributor
Nov 15, 2023
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...

I also copied all Ids from the response and made a text search. 9952 is not being returned..
- LainRobertson
  Silver Contributor
  Nov 15, 2023
  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
  - Dixus
    Copper Contributor
    Nov 15, 2023
    LainRobertson yes it's included:
    
    But it's also included without the cast:
    
    And it's included using two conditions:
    
    But not using only one condition:
    
    Either it's not there without any conditions: