Date: 2011-10-07 11:00:21 +0200
From: Evert Rol <<evert.astro>>
To: SQL devs <>
Version: 11.5.3 (Aug2011-SP1) [obsolete]
CC: @njnes

Last updated: 2011-10-26 13:22:01 +0200

Comment 16385

Date: 2011-10-07 11:00:21 +0200
From: Evert Rol <<evert.astro>>

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/535.1 (KHTML, like Gecko) Chrome/14.0.835.186 Safari/535.1
Build Identifier:

When running a select with a double "order by" on a large table (200000 records for a table with 2 int columns) and the second ordering is 'DESC', the query fails with a TypeException error 'mat.refine_reverse' undefined.

Interestingly, during an mclient session, when one stepwise increases the size of the table, the problem does not occur or only at an even larger number of records, which seems because of an optimisation (caching) mechanism.
Restarting the mclient session causes the bug to reappear.

Reproducible: Always

Steps to Reproduce:

Create a table with 200000 records in it and 2 columns.

sql>\d t
CREATE TABLE "tkp"."t" (
"i" int,
"j" int
);
sql>select count(*) from t;
+--------+
| L4 |
+========+
| 200000 |
+--------+
1 tuple

Run a query, ordering on both columns:

sql>select * from t where i < 10 order by i asc, j desc;
TypeException:user.s0_1[76]:'mat.refine_reverse' undefined in: (_173:bat[:void,:oid], _174:bat[:void,:bte]) := mat.refine_reverse(_168:bat[:void,:int], _169:bat[:void,:bte], _175:bat[:oid,:int], _176:bat[:oid,:int], _177:bat[:oid,:int])
SQLException:SQLengine:Program contains errors
sql>

(Note: the where clause is not necessary, but with the aforementioned optimisation/caching result, is probably prudent.)

Actual Results:

TypeException:user.s0_1[76]:'mat.refine_reverse' undefined in: (_173:bat[:void,:oid], _174:bat[:void,:bte]) := mat.refine_reverse(_168:bat[:void,:int], _169:bat[:void,:bte], _175:bat[:oid,:int], _176:bat[:oid,:int], _177:bat[:oid,:int])
SQLException:SQLengine:Program contains errors

Expected Results:

+------+--------+
| i | j |
+======+========+
| 1 | 199999 |
| 2 | 199998 |
| 3 | 199997 |
| 4 | 199996 |
| 5 | 199995 |
| 6 | 199994 |
| 7 | 199993 |
| 8 | 199992 |
| 9 | 199991 |
+------+--------+
9 tuples (2.748ms)

This occurs on Aug2011-SP1 and Oct2010 (11.5.3 and 5.22.3); for the Aug2011-SP1, this occurs on Linux and OS X. The OS X build has asserts enabled, though no assert appears.

Comment 16421

Date: 2011-10-14 09:31:51 +0200
From: @njnes

fixed by renaming mat.refine_rev into mat.refine_reverse (similar to the group.refine and group.refine_reverse)
Added test to missing_refine_reverse.

Comment 16430

Date: 2011-10-14 13:02:18 +0200
From: @njnes

Changeset 67224c43085a 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=67224c43085a

Changeset description:

fix bug #2898 (by renaming refine_rev into refine_reverse)