Date: 2010-07-30 03:21:29 +0200
From: Mark Bucciarelli <>
To: SQL devs <>
Version: -- development
CC: @njnes, @sjoerdmullender, @skinkie

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

Comment 14558

Date: 2010-07-30 03:21:29 +0200
From: Mark Bucciarelli <>

User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.4; en-US; rv:1.9.2.8) Gecko/20100722 Firefox/3.6.8
Build Identifier:

Full error:

MALException:group.refine:Operation failed
!ERROR: CTrefine: both BATs must have the same cardinality and their heads must form a 1-1 match

This just was raised in my Django application, which talks to MonetDB through the SQL client.

Reproducible: Always

Steps to Reproduce:

For my schema the following SQL generates the error:

SELECT
"tbl1"."id",
"tbl1"."id1",
"tbl1"."id2"
FROM
"tbl1" INNER JOIN "tbl2" ON ("tbl1"."id1" = "tbl2"."id")
ORDER BY
"tbl2"."title" ASC,
"tbl1"."id1" ASC
LIMIT 21
;

If I remove the LIMIT clause, the query executes without problem.

Comment 14559

Date: 2010-07-30 03:22:34 +0200
From: Mark Bucciarelli <>

Changing to SQL component, since removing the LIMIT clause suggests that's where it belongs.

Comment 14571

Date: 2010-07-30 19:54:11 +0200
From: @njnes

mark do you have example data ? I have a small patch which I want to test. Also this is needed for the testweb later on.

Comment 14572

Date: 2010-07-30 19:57:51 +0200
From: Mark Bucciarelli <>

(In reply to comment 2)

mark do you have example data ? I have a small patch which I want to test. Also
this is needed for the testweb later on.

i don't think i will be able to get this done today.

it is on my todo list, but i'm behind.
i'm also on vacation next week. i can do it when i
get back.

if the patch applies to feb-sp2, and you think it is
low risk, i can try in production.

Comment 14575

Date: 2010-07-30 20:10:12 +0200
From: @njnes

Changeset e134609ff6ec made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

fix bug in order by with multiple expressions and limit (bug #2634)

Comment 14982

Date: 2010-10-05 17:59:14 +0200
From: Mark Bucciarelli <>

The following SQL triggers the bug. Now I will apply your patch and rebuild to see if it fixes the problem.

Did you patch get in 2nd June release?

-- Bug #2634, dev.monetdb.org/bugzilla
-- Tue Oct 5 11:46:49 EDT 2010

-- To run:
-- 1. Create MonetDB datbase test with user test.
-- 2. mclient -l sql -u test -d test < bug.2634.sql

CREATE TABLE "test"."tbl1" (
"id" int NOT NULL,
"id1" int NOT NULL,
"id2" int NOT NULL
)
;

CREATE TABLE "test"."tbl2" (
"id" int NOT NULL,
"title" varchar(10)
)
;

INSERT INTO test.tbl1 VALUES (1,1,1)
;
INSERT INTO test.tbl1 VALUES (2,2,2)
;
INSERT INTO test.tbl2 VALUES (1, 'one')
;
INSERT INTO test.tbl2 VALUES (2, 'two')
;
INSERT INTO test.tbl2 VALUES (3, 'three')
;

--
-- ERROR = !MALException:group.refine:Operation failed
-- !ERROR: CTrefine: both BATs must have the same cardinality and their heads must form a 1-1 match.

SELECT
tbl1.id,
tbl1.id1,
tbl1.id2
FROM
test.tbl1 INNER JOIN test.tbl2
ON (tbl1.id1 = tbl2.id)
ORDER BY
tbl2.title ASC,
tbl1.id1 ASC
LIMIT 1
;

DROP TABLE "test"."tbl2";
DROP TABLE "test"."tbl1";

Comment 14983

Date: 2010-10-05 18:18:21 +0200
From: Mark Bucciarelli <>

Yes, applying your patch makes the test script run fine.

Thanks!

Comment 15008

Date: 2010-10-07 16:38:55 +0200
From: @sjoerdmullender

Changeset 77001de7db5e 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=77001de7db5e

Changeset description:

Added test for bug #2634.

Comment 15009

Date: 2010-10-07 16:39:57 +0200
From: @sjoerdmullender

This was fixed in the Jun2010-SP2 release.
Since we now also have a working test, the ticket can be closed.

Comment 15189

Date: 2010-11-11 22:07:48 +0100
From: @skinkie

Currently having the same issue with the latest head.

copy select distinct kvk, bedrijfsnaam, plaats from kvk order by kvks, sub, bedrijfsnaam into '/home/projects/openkvk/download/www/kvk_bedrijfsnaam_plaats.tsv' using delimiters '\t', '\n' null as '';

MALException:group.refine:operation failed
ERROR: CTrefine: both BATs must have the same cardinality and their heads must form a 1-1 match.

sql>select distinct kvk, bedrijfsnaam, plaats from kvk order by kvks, sub, bedrijfsnaam limit 1;
+-------------+--------------------------------------------+------------+
| kvk | bedrijfsnaam | plaats |
+=============+============================================+============+
| 10000400000 | C.H. van Duijsen Bouwmaterialenhandel B.V. | Leeuwarden |
+-------------+--------------------------------------------+------------+
1 tuple (37.487ms)

Comment 15264

Date: 2010-12-03 22:07:19 +0100
From: @njnes

problem was fixed and test was added (some time ago)

Comment 15619

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

The Mar2011 version has been released.