Strange behavior: dumping results from SELECT query (or inserting it into a table) does not contain the proper data until table is dumped/reloaded

6 Dec 2013

      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