Forum Discussion

Dixus's avatar
Dixus
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 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:

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

  • bake13's avatar
    bake13
    Icon for Microsoft rankMicrosoft
    Nov 14, 2023

    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.

    • Dixus's avatar
      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's avatar
        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

Resources