1

I have an interesting issue on which Google has failed me.

Background

I am working with medical records managed with Ingres SQL. The other day I wrote a simple query in Excel to pull all patient rows that have died in a set date range and output them as a report. This query works. I happened to notice (because of my date range) that I was missing a recent row. Upon further inspection I realized I am missing more rows, not just in this report.

Details of issue

When I query specifically for these missing rows (by selecting their specific patient ID from its column) they exist. When looking at Visual DBA at the table in which they reside and were just queried from successfully, they do not appear.

Similarly, these missing rows do not appear in the raw data pulled by my Excel query, whereas other rows meeting the same criteria do appear. I have looked for inconsistencies between rows that are showing up in Visual DBA and rows that are not. They are all consistent (null values, format, etc) and have differences only in value, not type. ie, last name is 'Jones' instead of 'Smith'.

My question

Why are rows missing from Ingres Visual DBA (table 'x' let's say) but when queried specifically in Ingres Visual SQL from table 'x' they appear?

This is a serious issue for us as rows are missing from automatically generated medical reports and vital information could be missed.

Additional Details

When in the Object Manager in Visual DBA I navigate to my table in question (say d1_17, which is sorted by ascending values of d1_17.xpid) xpid = '27245' is not there. When I query specifically in Visual SQL for d1_17.xpid = '27245' it outputs the row correctly.

They are connected to same database. Excel and Visual DBA are also in sync (both missing the same rows). The discrepancy arises when I query specifically d1_17.xpid = '27245' in Visual SQL and the row prints, and yet, when browsing in object manager, in d1_17 in Visual DBA the row with xpid = '27245' isn't there.

Every value in d1_17.xpid is non-null. Other columns may have null values, but every row is a patient and every patient has an xpid (patient ID).

While we are admins on the network for our database we unfortunately did not create it, our software team for our EHR GUI did that part as well.

  • It's an old question so hopefully resolved by now, but this may be a Visual DBA bug. I recently saw a problem similar to that described here where expected rows did not appear in VDBA but did appear using other query tools. It was resolved by using a later version of Ingres (client side). Suspect the cause to have been bug 128591, it's worth checking the release notes for your current patch level to see if the fix for this is included. – G Jones Feb 28 '17 at 11:06
  • Unsolved as a matter of fact, but this is our assumption. Thanks for pointing me to the bug. Struggling to find both release notes on Ingres 10.1.0 (my current client-side version) or any info on bug 128591. If you happen have a second, could you please post me your findings? – Jackson Mar 1 '17 at 12:27
2

(Please forgive the SQL Server test case - the problem is common to all SQL implementations because that of common semantics mandated by the SQL Standard.)

Even though you have used a LEFT OUTER JOIN, the semantics of SQL can convert this to an implied INNER JOIN if you improperly put constant-test conditions in a WHERE clause instead of the JOIN clause. The example below ilustrates this.

Preliminaries to create test data:

if object_id('Address') is not null drop table Address;
if object_id('Person') is not null drop table Person;
create table Person (
    ID          int identity    not null primary key clustered,
    Name        varchar(10)     not null unique nonclustered
);

create table Address (
    ID          int identity    not null primary key clustered,
    PersonID    int             not null references Person(ID),
    SequenceNo  int             not null,
    Address     varchar(40)     not null

    ,constraint NK unique nonclustered (PersonID,SequenceNo)
);

insert Person(Name) values
     ('Fred')
    ,('George')
    ,('Ron')
    ,('Ginny')
;

with data as(
    select * from(values
            ('Ron',   1,'Attic bedroom, The Burrows')
           ,('Ron',   2,'Junior Boys Dorm, Gryffindor House')
           ,('Fred',  1,'Blue bedroom, The Burrows')
           ,('Fred',  2,'Senior Boys Dorm, Gryffindor House')
           ,('George',1,'Green bedroom, The Burrows')
           ,('George',2,'Senior Boys Dorm, Gryffindor House')
           ,('Ginny', 1,'Pink bedroom, The Burrows')
    )data(PersonName,SequenceNo,Address)
)
insert Address(PersonID,SequenceNo,Address)
select 
    Person.ID,
    SequenceNo,
    Address
from data join Person on data.PersonName = Person.Name
;

Now these two queries

select *
from Person
left join Address on Address.PersonID = Person.ID
               where SequenceNo       = 2
;
select *
from Person
left join Address on Address.PersonID = Person.ID
                 and SequenceNo       = 2
;

respectively return

ID          Name       ID          PersonID    SequenceNo  Address
----------- ---------- ----------- ----------- ----------- ----------------------------------------
3           Ron        2           3           2           Junior Boys Dorm, Gryffindor House
1           Fred       4           1           2           Senior Boys Dorm, Gryffindor House
2           George     6           2           2           Senior Boys Dorm, Gryffindor House

ID          Name       ID          PersonID    SequenceNo  Address
----------- ---------- ----------- ----------- ----------- ----------------------------------------
1           Fred       4           1           2           Senior Boys Dorm, Gryffindor House
2           George     6           2           2           Senior Boys Dorm, Gryffindor House
3           Ron        2           3           2           Junior Boys Dorm, Gryffindor House
4           Ginny      NULL        NULL        NULL        NULL

Note how, despite the LEFT OUTER JOIN which one expects to ensure that all Person rows are returned, the placement of the condition SequenceNo = 2 in the WHERE clause instead of the JOIN clause coerces the join into an INNER JOIN; so that Ginny is dropped from the first result set.

This is a specific example of how, more generally, the occurrence of a NULL value in a field being tested violates intuition. One loses the Excluded Middle, so that when a predicate A may be NULL it is no longer tautologically true that A or NOT A will give you all rows; all rows with a NULL value for A will be silently dropped.

| improve this answer | |
  • This may still be the issue with the structure of the tables in the database but the issue is not superficial enough for this be a solution for the query, which is sort of expected if it's a problem with the table. After my LEFT JOIN lines I did have a "WHERE bdeceased = 1". I changed this to a "AND bdeceased = 1". I then had every row from the table taken into my raw data sheet, except still missing the same rows. – Jackson Dec 1 '15 at 15:21
  • I do think the logic of your solution is correct in being the issue with the structure of the tables in this database. At least one of these troubled tables is new in a recent patch and I expect that it was constructed poorly. I will pass this along to the devs and let you know if your intuitions prevail as expected. – Jackson Dec 1 '15 at 15:21
  • I pulled up the SQL for the creation of the table. It looks good. It's not a lot of code and I've scoured it, taking into account your info about how server SQL reads join statements. It is known that Ingres has trouble reading lines that in other flavors of SQL are basic and straightforward. We understand that once we are able to upgrade our version of Ingres many of these issues will iron themselves out, and we hope that is the case with this one. Thanks for your help, Pieter. If/when I have found the cause of this issue I will update my question. – Jackson Dec 8 '15 at 15:31
0

I suspect that the date values are the issue. Formatting datetime values in excel does not remove the time portion, only hides it. One query is truncating the time the other isn't. If you are using BETWEEN for the date ranges, add 1 day to the end date and try this:

and Timestamp >= @StartDate and Timestamp < @EndDate
| improve this answer | |
  • Thanks for the info. Some of the missing rows are dates in the middle of the date range as well, this was my first thought too, but I nixed it when I saw the rows were missing in Visual DBA. Once I determine why these rows are missing in Visual DBA I'll look into Excel's reading of timestamps to make sure they are ordered correctly and all needed values are present in this and future reports. Appreciate the heads up. – Jackson Dec 1 '15 at 14:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.