Date: 2010-11-29 12:33:19 +0100
From: Alexander Ulrich <>
To: MonetDB5 devs <>
Version: 11.3.3 (Apr2011-SP1) [obsolete]
CC: alfred.nordman, mitja, @drstmane, @skinkie

Last updated: 2011-07-29 10:52:37 +0200

Comment 15234

Date: 2010-11-29 12:33:19 +0100
From: Alexander Ulrich <>

User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10_6_5; en-US) AppleWebKit/534.7 (KHTML, like Gecko) Chrome/7.0.517.44 Safari/534.7
Build Identifier:

Running the following query results in an MALException. It executes just fine on PostgreSQL 9.0. I'm not sure if this query counts as 'complex'. The original one was much larger, but I wasn't successful in reducing this even further.

$ cat foo.sql
WITH
-- binding due to rownum operator
t0000 (iter4_nat, iter5_nat) AS
(SELECT a0000.iter4_nat, ROW_NUMBER () OVER () AS iter5_nat
FROM (VALUES (1)) AS a0000(iter4_nat)),

-- binding due to rownum operator
t0001 (iter4_nat, iter5_nat, item1_int, item2_int, item3_int,
iter23_nat) AS
(SELECT a0001.iter4_nat, a0001.iter5_nat, a0002.col AS item1_int,
a0002.row AS item2_int, a0002.val AS item3_int,
ROW_NUMBER () OVER
(ORDER BY a0001.iter5_nat ASC, a0002.row ASC, a0002.col ASC) AS
iter23_nat
FROM t0000 AS a0001,
foo AS a0002),

-- binding due to aggregate
t0002 (iter11_nat, pos12_nat) AS
(SELECT a0003.iter5_nat AS iter11_nat, MIN (a0003.iter23_nat) AS pos12_nat
FROM t0001 AS a0003
GROUP BY a0003.iter5_nat, a0003.item2_int),

-- binding due to rownum operator
t0003 (iter11_nat, pos12_nat, iter4_nat, iter5_nat, item1_int,
item2_int, item3_int, iter23_nat, pos25_bool, pos26_nat) AS
(SELECT a0004.iter11_nat, a0004.pos12_nat, a0005.iter4_nat, a0005.iter5_nat,
a0005.item1_int, a0005.item2_int, a0005.item3_int, a0005.iter23_nat,
CASE WHEN a0005.iter23_nat = a0004.pos12_nat THEN 1 ELSE 0 END AS
pos25_bool,
ROW_NUMBER () OVER
(PARTITION BY a0004.iter11_nat ORDER BY a0005.iter23_nat ASC) AS
pos26_nat
FROM t0002 AS a0004,
t0001 AS a0005
WHERE a0004.iter11_nat = a0005.iter5_nat
AND a0005.iter23_nat = a0004.pos12_nat)

SELECT 1 AS iter20_nat, a0006.item2_int AS item8_int
FROM t0003 AS a0006
WHERE a0006.pos26_nat = 2
ORDER BY a0006.iter11_nat ASC, a0006.pos26_nat ASC;

$ mclient -l sql -d test foo.sql
MALException:mkey.bulk_rotate_xor_hash:operation failed Operands not synced on head

The table foo was created as follows:
$ cat create.sql
CREATE TABLE foo(row int not null, col int not null, val int not null, primary key (row, col));
INSERT INTO foo(row, col, val) VALUES (1,1,1), (1,2,2), (2,1,3), (2,2,4);
$ mclient -l sql -d test create.sql
operation successful
4 affected rows

Reproducible: Always

Steps to Reproduce:

1. Installed MonetDB from changeset 38323:decb8b46e1ec of the Oct2010 branch
2. created the table foo
3. executed SQL query

Actual Results:

$ mclient -l sql -d test foo.sql
MALException:mkey.bulk_rotate_xor_hash:operation failed Operands not synced on head

Expected Results:

$ psql90 test < foo.sql
iter20_nat | item8_int
------------+-----------
1 | 2
(1 row)

$ mserver5 --version
MonetDB server v5.22.2 (64-bit), based on kernel v1.40.2 (64-bit oids)
Not released
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2010 MonetDB B.V., all rights reserved
Visit http://monetdb.cwi.nl/ for further information
Found 5.5GiB available memory, 2 available cpu cores
Configured for prefix: /opt/monetdb
Libraries:
libpcre: 8.10 2010-06-25 (compiled with 8.10)
openssl: OpenSSL 1.0.0a 1 Jun 2010 (compiled with OpenSSL 1.0.0a 1 Jun 2010)
libxml2: 2.7.7 (compiled with 2.7.7)
Compiled by: ulricha@u-081-c089.eap.uni-tuebingen.de (x86_64-apple-darwin10.5.0)
Compilation: gcc -Wall -Wextra -g -Werror-implicit-function-declaration -Werror -Wpointer-arith -Wdeclaration-after-statement -Wundef -Wp,-D_FORTIFY_SOURCE=2
Linking : /usr/libexec/gcc/i686-apple-darwin10/4.2.1/ld

Comment 15236

Date: 2010-11-30 09:30:29 +0100
From: Alexander Ulrich <>

One additional information: when the table foo is replaced by a literal table in the SQL statement, the query executes and delivers the expected result.

Comment 15291

Date: 2010-12-05 20:49:58 +0100
From: @skinkie

Also happens on not so complex query:

insert into anbikvk select id, kvks from myanbi, kvk_suggest where myanbi.naam = upper(bedrijfsnaam) and vestigingsplaats = upper(plaats) and kvks not in (select kvks from anbikvk);

Comment 15671

Date: 2011-03-28 17:35:55 +0200
From: @sjoerdmullender

The Mar2011 version has been released.

Comment 15739

Date: 2011-04-21 17:58:08 +0200
From: Alfred Nordman <<alfred.nordman>>

Another way to reproduce this exception on Mar2011 build is:

CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1);
SELECT * FROM (SELECT a, 1 AS n FROM t1 UNION SELECT a, 2 AS n FROM t1) AS abc INNER JOIN (SELECT a, 1 AS n FROM t1 UNION SELECT a, 2 AS n FROM t1) AS cba ON abc.a = cba.a AND abc.n = cba.n;

Regards,
Alfred

Comment 15889

Date: 2011-07-05 11:39:21 +0200
From: @sjoerdmullender

Changeset 977f36bdf105 made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=977f36bdf105

Changeset description:

Added test for bug #2734.
Test fails as described in the report.  I hope I got the expected
output right.

Comment 15904

Date: 2011-07-07 15:28:59 +0200
From: @drstmane

Changeset dba439fbebd8 made by Stefan Manegold Stefan.Manegold@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=dba439fbebd8

Changeset description:

fixed bug #2734:
Like with BATjoin(), also with BATleftjoin()
run (simple) BATpropcheck(b, BATPROPS_QUICK)
also on result head, not only on result tail.

Extended test with larger example from bug report.
Fixed stable output.

Comment 15995

Date: 2011-07-29 10:52:37 +0200
From: @sjoerdmullender

The Apr2011-SP2 bugfix release is out.