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