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

SQL throws TypeException if aggregations and limit statements are both present #3498

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

Comments

@monetdb-team
Copy link

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

Date: 2014-06-15 02:22:17 +0200
From: George <<georgy.ramonov>>
To: MonetDB5 devs <>
Version: 11.17.17 (Jan2014-SP2)

Last updated: 2014-10-31 14:14:16 +0100

Comment 19843

Date: 2014-06-15 02:22:17 +0200
From: George <<georgy.ramonov>>

User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/34.0.1847.116 Safari/537.36
Build Identifier:

Suppose we have a table

CREATE TABLE test.simple_table("id" INTEGER);

INSERT INTO test.simple_table VALUES (1),(2),(3),(4),(5);

Let's say I want to run a simple query:

SELECT COUNT(*) AS val
FROM test.simple_table
ORDER BY val DESC;

+-------+
| v |
+=======+
| 5 |
+-------+

Runs fine.
However, if add a LIMIT clause:

SELECT COUNT(*) AS val
FROM test.simple_table
ORDER BY val DESC
LIMIT 5;

I get the following

TypeException:user.s37_7[17]:'algebra.markT' undefined in: _30:any := algebra.markT(_27:oid, _29:oid)
program contains errors

However, if I add a primitive HAVING clause

SELECT COUNT(*) AS val
FROM test.simple_table
HAVING 1 > 0
ORDER BY val DESC
LIMIT 5;

+-------+
| v |
+=======+
| 5 |
+-------+

It runs fine again. What's going on?

------ query execution plans attached below ------

-- without LIMIT

sql>explain select count() as v from simple_table order by v;
+------------------------------------------------------------------------------+
| mal |
+==============================================================================+
| function user.s3_7{autoCommit=true}():void; |
| barrier X_36 := language.dataflow(); |
| X_2 := sql.mvc(); |
| X_3:bat[:oid,:oid] := sql.tid(X_2,"medical","simple_table"); |
| X_6 := sql.bind(X_2,"medical","simple_table","id",0); |
| (X_9,r1_9) := sql.bind(X_2,"medical","simple_table","id",2); |
| X_12 := sql.bind(X_2,"medical","simple_table","id",1); |
| X_14 := sql.delta(X_6,X_9,r1_9,X_12); |
| X_15 := algebra.leftfetchjoin(X_3,X_14); |
| X_16 := aggr.count(X_15); |
| exit X_36; |
| sql.exportValue(1,"medical.L1","v","wrd",64,0,6,X_16,""); |
| end s3_7; |
| querylog.define("explain select count(
) as v from simple_table order by v |
: ;","default_pipe")

====================

-- with LIMIT

explain select count() as v from simple_table order by v limit 5;
TypeException:user.s4_7[17]:'algebra.markT' undefined in: _30:any := algebra.markT(_27:oid, _29:oid)
+------------------------------------------------------------------------------+
| mal |
+==============================================================================+
| function user.s4_7{autoCommit=true}():void; |
| sql.mvc(); |
| X_3:bat[:oid,:oid] := sql.tid(X_2,"medical","simple_table"); |
| sql.bind(X_2,"medical","simple_table","id",0); |
| (X_9,r1_9) := sql.bind(X_2,"medical","simple_table","id",2); |
| sql.bind(X_2,"medical","simple_table","id",1); |
| sql.delta(X_6,X_9,r1_9,X_12); |
| algebra.leftfetchjoin(X_3,X_14); |
| aggr.count(X_15); |
| calc.wrd(0:wrd); |
| calc.wrd(5:wrd); |
| X_21:bat[:oid,:wrd] := bat.new(nil:oid,nil:wrd); |
| bat.append(X_21,X_16); |
| calc.+(X_17,X_19); |
| pqueue.topn_max(X_21,X_25); |
| algebra.find(X_26,0@0); |
| calc.oid(0@0); |
| algebra.markT(X_27,X_29); |
| bat.reverse(X_30); |
| sql.single(X_16); |
| algebra.leftfetchjoin(X_31,X_32); |
| calc.wrd(0:wrd); |
| calc.wrd(5:wrd); |
| calc.+(X_34,X_35); |
| calc.-(X_36,1); |
| algebra.subslice(X_33,X_34,X_37); |
| algebra.leftfetchjoin(X_38,X_33); |
| sql.resultSet(1,1,X_39); |
| sql.rsColumn(X_40,".","single_value","wrd",64,0,X_39); |
| io.stdout(); |
| sql.exportResult(X_47,X_40); |
| end s4_7; |
| querylog.define("explain select count(
) as v from simple_table order by v |
: limit 5;","default_pipe")

Reproducible: Always

Steps to Reproduce:

Create a table in a test schema.

CREATE TABLE test.simple_table("id" INTEGER);
INSERT INTO test.simple_table VALUES (1),(2),(3),(4),(5);

Run aggregation with limit

SELECT COUNT(*) AS val
FROM test.simple_table
ORDER BY val DESC
LIMIT 5;

Actual Results:

TypeException:user.s9_7[17]:'algebra.markT' undefined in: _31:any := algebra.markT(_28:oid, _30:oid)

Expected Results:

+-------+
| v |
+=======+
| 5 |
+-------+

mserver5 --version
MonetDB 5 server v11.17.17 "Jan2014-SP2" (64-bit, 64-bit oids)
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2014 MonetDB B.V., all rights reserved
Visit http://www.monetdb.org/ for further information
Found 11.6GiB available memory, 6 available cpu cores
Libraries:
libpcre: 8.35 2014-04-04 (compiled with 8.35)
openssl: OpenSSL 1.0.1g 7 Apr 2014 (compiled with OpenSSL 1.0.1g 7 Apr 2014)
libxml2: 2.9.1 (compiled with 2.9.1)
Compiled by: george@arch (x86_64-unknown-linux-gnu)
Compilation: gcc -O3 -fomit-frame-pointer -pipe -D_FORTIFY_SOURCE=2
Linking : /usr/bin/ld -m elf_x86_64

Comment 19895

Date: 2014-07-09 15:46:36 +0200
From: MonetDB Mercurial Repository <>

Changeset d3a1eddca24b 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=d3a1eddca24b

Changeset description:

fixed bug #3498, make sure we have a table (not a single row)
before we do the topn.

fixed bug #3489, distinct with order by, use the proper extend directly for the possible order by columns (which are not part of the output)

Comment 20151

Date: 2014-09-12 16:09:11 +0200
From: MonetDB Mercurial Repository <>

Changeset 1eafbe957e34 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=1eafbe957e34

Changeset description:

Added test for bug #3498.

Comment 20362

Date: 2014-10-31 14:14:16 +0100
From: @sjoerdmullender

Oct2014 has been released.

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