Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

order by for multiple (two) columns fails for large tables when second ordering is 'desc': "mat.refine_reverse' undefined" #2898

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant