Hi,

I can reproduce a MonetDB 2013 - SP5 database which gets in such a state the following first query does not return a result with a record containing '20130510199500000099' as value for the field "ida" whereas the second query (with only a little extra at the end) does while clearly this should never be the case. Moreover: the problem disappears when I dump, drop and reload the table analysis_04_1 !

testdpe99.sql :

SELECT A.identificatierecord AS idA, B.identificatierecord AS idB, A.identificatierecord||'-'||B.identificatierecord AS ctxid, 'D.P.E.99.99' AS testcode FROM analysis_04_1 AS A INNER JOIN analysis_04_1 AS B ON A.instellingscode = B.instellingscode AND A.verzekerdennummer = B.verzekerdennummer WHERE NOT A.identificatierecord = B.identificatierecord

AND (B.Zorgtypecode = '11' OR B.Zorgtypecode = '21')

AND ( (A.BegindatumPrestatie <= B.EinddatumPrestatie AND A.EinddatumPrestatie >= B.BegindatumPrestatie AND NOT ( A.EinddatumPrestatie > A.BegindatumPrestatie AND B.EinddatumPrestatie > B.BegindatumPrestatie AND ( A.BegindatumPrestatie = B.EinddatumPrestatie OR B.BegindatumPrestatie = A.EinddatumPrestatie ) )) OR (A.BegindatumPrestatie = B.EinddatumPrestatie OR B.BegindatumPrestatie = A.EinddatumPrestatie OR A.BegindatumPrestatie=(B.EinddatumPrestatie + INTERVAL '1' day) OR B.BegindatumPrestatie=(A.EinddatumPrestatie + INTERVAL '1' day) ) );

testdpe99-2.sql :

SELECT A.identificatierecord AS idA, B.identificatierecord AS idB, A.identificatierecord||'-'||B.identificatierecord AS ctxid, 'D.P.E.99.99' AS testcode FROM analysis_04_1 AS A INNER JOIN analysis_04_1 AS B ON A.instellingscode = B.instellingscode AND A.verzekerdennummer = B.verzekerdennummer WHERE NOT A.identificatierecord = B.identificatierecord

AND (B.Zorgtypecode = '11' OR B.Zorgtypecode = '21')

AND ( (A.BegindatumPrestatie <= B.EinddatumPrestatie AND A.EinddatumPrestatie >= B.BegindatumPrestatie AND NOT ( A.EinddatumPrestatie > A.BegindatumPrestatie AND B.EinddatumPrestatie > B.BegindatumPrestatie AND ( A.BegindatumPrestatie = B.EinddatumPrestatie OR B.BegindatumPrestatie = A.EinddatumPrestatie ) )) OR (A.BegindatumPrestatie = B.EinddatumPrestatie OR B.BegindatumPrestatie = A.EinddatumPrestatie OR A.BegindatumPrestatie=(B.EinddatumPrestatie + INTERVAL '1' day) OR B.BegindatumPrestatie=(A.EinddatumPrestatie + INTERVAL '1' day) ) )

AND A.identificatierecord = '20130510199500000099';

The table analysis_04_1 is the result of a SELECT query after which it is altered and updated quite some times adding extra fields etc. No errors seem to be reported in that process.

Before reloading the table analysis_04_1 I get:

bkaptijn@DevRef1:~/DDDB$ mclient -d cacis < ../testdpe99-2.sql | grep 20130510199500000099 | 20130510199500000099 | 20130313587700000003 | 20130510199500000099-20130313587700000003 | D.P.E.99.99 | bkaptijn@DevRef1:~/DDDB$ mclient -d cacis < ../testdpe99.sql | grep 20130510199500000099 bkaptijn@DevRef1:~/DDDB$

But after reloading the table (using in mclient: > dumpfile, \D analysis_04_1, DROP TABLE analysis_04_1;, < dumpfile):

bkaptijn@DevRef1:~/DDDB$ mclient -d cacis < ../testdpe99.txt | grep 20130510199500000099 | 20130510199500000099 | 20130313587700000003 | 20130510199500000099-20130313587700000003 | D.P.E.99.99 | bkaptijn@DevRef1:~/DDDB$ mclient -d cacis < ../testdpe99-2.txt | grep 20130510199500000099 | 20130510199500000099 | 20130313587700000003 | 20130510199500000099-20130313587700000003 | D.P.E.99.99 | bkaptijn@DevRef1:~/DDDB$

Is there a known issue which does play a role here or what can I do to investigate this further / give more information?

Sincerely,

Bas Kaptijn