Date: 2010-08-05 14:29:51 +0200
From: @drstmane
To: SQL devs <>
Version: 2.40.1 (Oct2010) [obsolete]
CC: @njnes

Last updated: 2011-03-28 17:31:26 +0200

Comment 14612

Date: 2010-08-05 14:29:51 +0200
From: @drstmane

When run multi-threaded with 64-bit MonetDB, test sql/src/test/VOC/Tests/VOC.SQL.sh fails, as query

select count(*) from craftsmen c, passengers p
where c.trip = p.trip and exists
(select 1 from voyages v
where c.trip = v.trip and v.boatname = 'AMSTERDAM'
and v.departure_harbour ='Texel');

then produces

+---------+
| L3 |
+=========+
| 4846720 |
+---------+
1 row

instead of

+---------+
| L3 |
+=========+
| 1305366 |
+---------+
1 row

cf., http://monetdb.cwi.nl/testing/projects/monetdb/Stable/sql/.mTests103/GNU.64.64.d.1-Fedora10/src_test_VOC/VOC.out.00.html

On the (currently) sole working (for this test) 32-bit multi-core platform (32-bit Darwin), it works fine:
http://monetdb.cwi.nl/testing/projects/monetdb/Stable/sql/.mTests103/GNU.32.32.d.1-Darwin9.8.0/src_test_VOC/VOC.out.00.html

Also on Windows (i.e., single-threaded) it works fine:
http://monetdb.cwi.nl/testing/projects/monetdb/Stable/sql/.mTests103/Int.32.32.d.1-Windows5.1/src_test_VOC/VOC.out.00.html

Tests by hand show that also on other platforms, single-threaded execution (Mtest.py -n1) works fine.

Note also that query

select count(*) from craftsmen c, passengers p
where c.trip = p.trip and exists (select 1) ;

produces

+---------+
| L4 |
+=========+
| 1308285 |
+---------+
1 row

in all cases.

Comment 14614

Date: 2010-08-05 15:23:45 +0200
From: @drstmane

disabling the "mitosis" optimizer makes the test work fine also 64-bit multi-threaded

Comment 14618

Date: 2010-08-05 20:30:04 +0200
From: @drstmane

The reason that it works on 32-bit (and possibly / sometimes on 64-bit with 32-bit OIDs) is mainly that mitosis does not necessarily get triggered in these cases, as there are typing problems with the "rows" property ("rowsProp") in MonetDB5 & sql.

Basically, the "rows" property seems to allow different types (bte, sht, int, lng) is some places, but in others the code that uses it silently assumes the "rows" property is of type lng --- reading lval of a ValRecord that has been initialized as TYPE_int usually results in (at least) unexprected results and behavior.

I do not yet(?) understand the intentions of using sometimes multiple types and sometimes only type for "rowsProp", and hence, cannot yet(/) fix this problem ...

Obviously, this discovery is only a side-effect of and not causually realted to the bug reported here ...

Comment 14620

Date: 2010-08-06 10:44:34 +0200
From: @drstmane

With the rowsProp type problems fixed, mitosis not also fires on 32-bit systems (and 64-bit systems with 32-bit OIDs), and thus, this test fails in all multi-threaded cases.

Comment 14832

Date: 2010-08-30 09:22:11 +0200
From: @sjoerdmullender

The Jun2010-SP2 version has been released.

Comment 15048

Date: 2010-10-18 11:19:59 +0200
From: @sjoerdmullender

The problem seems to be the semijoin:
When not using mitosis/mergetable, towards the end of the plan there is a semijoin, the result of which provides the final result.
In the mitosis/mergetable case, there are a bunch of semijoins whose results are combined. All these semijoins have the same left-hand side, which was also the same as in the case where mitosis/mergetable was switched off.
The results of the semijoins cannot simply be appended to each other, which is what is effectively done in the mitosis/mergetable case. The results should be combined intelligently, so that results from the left-hand side are not duplicated. This is not done, hence the incorrect result.

Comment 15265

Date: 2010-12-03 22:20:17 +0100
From: @njnes

is fixed. Tests return correct results again.

Comment 15620

Date: 2011-03-28 17:31:26 +0200
From: @sjoerdmullender

The Mar2011 version has been released.