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

Issue with subselect and ORDER BY #3554

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

Issue with subselect and ORDER BY #3554

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

Comments

@monetdb-team
Copy link

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

Date: 2014-08-25 18:31:18 +0200
From: Frédéric Jolliton <<frederic.jolliton+monetdb>>
To: SQL devs <>
Version: 11.17.21 (Jan2014-SP3)
CC: @njnes

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

Comment 20088

Date: 2014-08-25 18:31:18 +0200
From: Frédéric Jolliton <<frederic.jolliton+monetdb>>

User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:31.0) Gecko/20100101 Firefox/31.0
Build Identifier:

MonetDB crash (or ignore the query) when using a sub SELECT with an outer ORDER BY clause, in both 11.17.21 and dev (11.20.0)

Reproducible: Always

Steps to Reproduce:

Here is the minimal query able to reproduce the problem:

select a+b from (select 1 as a,1 as b) as q order by a limit 1;

Note the three condition to trigger the bug: a computation on several fields, an sub select and on order by.

Actual Results:

No answer from mclient.

With 11.17.21, I think it's related to bugs we already filled in the past weeks. We see nothing in the serveur log.

However, in the development branch (whose tip is at 52799:d7d082274cc2), we have the following log:

2014-08-25 16:14:12 ERR nova[20887]: mserver5: rel_bin.c:2359: rel2bin_project: Assertion `0' failed.
2014-08-25 16:14:12 MSG nova[20887]: could not find q.a
2014-08-25 16:14:12 MSG nova[20887]: (null!).single_value
2014-08-25 16:14:12 MSG nova[20887]: (null!).single_value
2014-08-25 16:14:12 MSG nova[20887]: L1.L1
2014-08-25 16:14:12 MSG merovingian[3771]: database 'test' (20887) was killed by signal SIGABRT

Moreover when altering the query to use an UNION in the sub select with the following query:

select a+b from (select 1 as a,1 as b union select 1, 1) as q order by a limit 1;

we get a more surprising log message:

2014-08-25 16:06:58 ERR nova[19417]: mserver5: rel_bin.c:2275: rel2bin_project: Assertion `0' failed.
2014-08-25 16:06:58 MSG nova[19417]: could not find q.b
2014-08-25 16:06:58 MSG nova[19417]: 0ê
h�.L3
2014-08-25 16:06:58 MSG nova[19417]: q.a
2014-08-25 16:06:58 MSG merovingian[3771]: database 'test' (19417) was killed by signal SIGABRT

(There are really random bytes on the third line.)

Trying to perform the same request a second time in mclient cause it to quit and nothing is logged:

sql>select a+b from (select 1 as a,1 as b union select 1,1) as q order by a limit 1;
sql>select a+b from (select 1 as a,1 as b union select 1,1) as q order by a limit 1;
write error on stream

Comment 20089

Date: 2014-08-25 18:59:11 +0200
From: @sjoerdmullender

When trying the query
select a+b from (select 1 as a,1 as b) as q order by a limit 1;
on the Jan2014 branch, I get:
mserver5: /home/sjoerd/src/MonetDB/stable/sql/backends/monet5/sql_gencode.c:965: _dumpstmt: Assertion `s->nrcols' failed.
Aborted - core dumped

On the Oct2014 branch, I get:
could not find q.a
(null!).single_value
(null!).single_value
L1.L1
mserver5: /home/sjoerd/src/MonetDB/candidate/sql/backends/monet5/rel_bin.c:2359: rel2bin_project: Assertion `0' failed.
Aborted - core dumped

Comment 20092

Date: 2014-08-26 08:28:53 +0200
From: MonetDB Mercurial Repository <>

Changeset 6c4080d30b3e 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=6c4080d30b3e

Changeset description:

fixed reuse

fixed bug #3554, ie make sure single value order by /limit work

Comment 20093

Date: 2014-08-26 08:30:51 +0200
From: @njnes

solved in oct2014 branch. Add value to column conversion for the single result orderby/limit.

Comment 20096

Date: 2014-08-26 11:35:16 +0200
From: Frédéric Jolliton <<frederic.jolliton+monetdb>>

Thanks for the quick fix. The reactivity is much appreciated :)

However, the other part still fail (when using an UNION), when querying the following:

select a+b from (select 1 as a,1 as b union select 1,1) as q order by a limit 1;

Output in log is: (with the tip of Oct2014 at this date, 52822:972d327c8d5f)

2014-08-26 09:26:29 ERR nova[29878]: mserver5: rel_bin.c:2275: rel2bin_project: Assertion `0' failed.
2014-08-26 09:26:29 MSG nova[29878]: could not find q.b
2014-08-26 09:26:29 MSG nova[29878]: .L3
2014-08-26 09:26:29 MSG nova[29878]: q.a

The session:

Welcome to mclient, the MonetDB/SQL interactive terminal (Jan2014-SP3)
Database: MonetDB v11.19.0 (unreleased), 'mapi:monetdb://fjolliton:50000/nova'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>select a+b from (select 1 as a,1 as b union select 1, 1) as q order by a limit 1;
Connection terminated

Comment 20097

Date: 2014-08-27 11:59:24 +0200
From: MonetDB Mercurial Repository <>

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

Changeset description:

fixed second part of bug #3554, ie handle topn push down properly

Comment 20098

Date: 2014-08-27 11:59:55 +0200
From: @njnes

also fixed the topn problem with this query

Comment 20099

Date: 2014-08-27 12:40:40 +0200
From: Frédéric Jolliton <<frederic.jolliton+monetdb>>

Works great. Thanks!

Comment 20358

Date: 2014-10-31 14:14:09 +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