ORDER BY DESC + LIMIT incorrectly yields empty result #2757

monetdb-team · 2020-11-30T11:39:53Z

Date: 2010-12-16 00:26:38 +0100
From: @skinkie
To: SQL devs <>
Version: -- development
CC: @njnes, @drstmane

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

Comment 15350

Date: 2010-12-16 00:26:38 +0100
From: @skinkie

User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US) AppleWebKit/534.10 (KHTML, like Gecko) Chrome/8.0.552.210 Safari/534.10
Build Identifier:

sql>select created from cables where created = '1990-01-17 15:03:00';
+----------------------------+
| created |
+============================+
| 1990-01-17 15:03:00.000000 |
+----------------------------+
1 tuple (26.000ms)
sql>SELECT id, classification FROM cables WHERE created BETWEEN '1990-01-17' AND '1990-01-18' ORDER BY created DESC LIMIT 20;
+----+----------------+
| id | classification |
+====+================+
+----+----------------+

0 tuples (3.000ms)
sql>select cast('1990-01-17 15:03:00' as timestamp) BETWEEN '1990-01-17' AND '1990-01-18';
+---------------------+
| and_>=_single_value |
+=====================+
| true |
+---------------------+

sql>SELECT id, classification FROM cables WHERE created BETWEEN '1990-01-17' AND '1990-01-18' ORDER BY created;
+------+----------------+
| id | classification |
+======+================+
| 714 | CONFIDENTIAL |
+------+----------------+
1 tuple (3.000ms)

Some observations: code is compiled

Reproducible: Always

Steps to Reproduce:

It seems that this happened in the last few days. Because a MonetDB of like a week ago doesn't show any issues. I have now recompiled with disabled optimise.

MonetDB server v5.23.0 (64-bit), based on kernel v1.41.0 (64-bit oids)
Not released
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2010 MonetDB B.V., all rights reserved
Visit http://monetdb.cwi.nl/ for further information
Found 2.0GiB available memory, 2 available cpu cores
Configured for prefix: /opt/monetdb-testing3
Libraries:
libpcre: 7.9 2009-04-11 ((not linked to any PCRE library))
openssl: OpenSSL 1.0.0b 16 Nov 2010 (compiled with OpenSSL 1.0.0b 16 Nov 2010)
libxml2: 2.7.7 (compiled with )
Compiled by: skinkie@kabelsearch (x86_64-unknown-linux-gnu)
Compilation: gcc -Wall -Wextra -fno-strict-aliasing -g -Werror-implicit-function-declaration -Werror -Wpointer-arith -Wdeclaration-after-statement -Wundef -Wp,-D_FORTIFY_SOURCE=2
Linking : /usr/x86_64-pc-linux-gnu/bin/ld -IPA -m elf_x86_64

Comment 15351

Date: 2010-12-16 00:27:42 +0100
From: @skinkie

Some extra info:

sql>select count(*) from cables;
+------+
| L4 |
+======+
| 1546 |
+------+
1 tuple (1.000ms)
sql>SELECT id, classification FROM cables WHERE created BETWEEN '1990-01-17' AND '1990-01-18' ORDER BY created DESC LIMIT 1546;
+------+----------------+
| id | classification |
+======+================+
| 714 | CONFIDENTIAL |
+------+----------------+

Comment 15353

Date: 2010-12-16 08:48:46 +0100
From: @grobian

can it be that this:

Libraries:
libpcre: 7.9 2009-04-11 ((not linked to any PCRE library))

causes things to go wrong?

Comment 15354

Date: 2010-12-16 09:56:23 +0100
From: @sjoerdmullender

(In reply to comment 2)

can it be that this:

Libraries:
libpcre: 7.9 2009-04-11 ((not linked to any PCRE library))

causes things to go wrong?

This could well be a bug in configure. Since the PCRE check was changed to pkg-config the way the version number is retrieved for displaying here wasn't fixed.
It will be fixed in my upcoming super configure.

Comment 15355

Date: 2010-12-16 10:03:46 +0100
From: @skinkie

(In reply to comment 2)

can it be that this:

Libraries:
libpcre: 7.9 2009-04-11 ((not linked to any PCRE library))

causes things to go wrong?

Upgraded my Gentoo libpcre now get:
libpcre: 8.02 2010-03-19 ((not linked to any PCRE library))

I do see the difference on one system to the other where PCRE /is/ linked. But how can I force such operation now?

Never the less, Niels did commit a change to the LIMIT/ORDER BY code. From my perspective the LIMIT happens before the ORDER BY. (See my last example.)

Comment 15356

Date: 2010-12-16 10:20:17 +0100
From: @drstmane

Stefan,

could you please provide (attach) the PLAN and EXPLAIN with MonetDB builds both with and without --enable-optimize (both need to be built from scratch. i.e., with virgin/empty build- & prefix-directories and using the very same code base) for one query where the results differ between the two builds as reported?

Thanks!

Stefan

Comment 15357

Date: 2010-12-16 11:07:19 +0100
From: @skinkie

Currently recompiled on the 'non-working' host, only change is the not commited Sphinx code. For convenience running everything in readonly.

explain SELECT id, classification FROM cables WHERE created BETWEEN '1990-01-17' AND '1990-01-18' ORDER BY created DESC LIMIT 20;
% .explain table_name
% mal name
% clob type
% 0 length
function user.s3_1{autoCommit=true}():void;
_2 := sql.mvc();
barrier _57 := language.dataflow();
_9:bat[:oid,:timestamp] := sql.bind(_2,"sys","cables","created",0);
_10 := algebra.uselect(_9,1990-01-17 00:00:00.000:timestamp,1990-01-18 00:00:00.000:timestamp,true,true);
_11 := algebra.markT(_10,0@0:oid);
_12 := bat.reverse(_11);
_13 := pqueue.topn_min(_9,20:wrd);
_14 := algebra.join(_12,_13);
_15 := bat.mirror(_14);
_56 := algebra.leftjoin(_15,_12);
_5:bat[:oid,:int] := sql.bind(_2,"sys","cables","id",0);
_16 := algebra.leftjoin(_56,_5);
_20 := algebra.slice(_16,0:wrd,19:wrd);
_21 := bat.mirror(_20);
_22 := algebra.leftjoin(_21,_16);
_24:bat[:oid,:str] := sql.bind(_2,"sys","cables","classification",0);
_27 := algebra.leftjoinPath(_21,_56,_24);
exit _57;
_28 := sql.resultSet(2,1,_22);
sql.rsColumn(_28,"sys.cables","id","int",32,0,_22);
sql.rsColumn(_28,"sys.cables","classification","varchar",64,0,_27);
_37 := io.stdout();
sql.exportResult(_37,_28);
end s3_1;

plan SELECT id, classification FROM cables WHERE created BETWEEN '1990-01-17' AND '1990-01-18' ORDER BY created DESC LIMIT 20;
% .plan table_name
% rel name
% clob type
% 142 length
top N (
| project (
| | select (
| | | table(sys.cables) [ cables.id NOT NULL, cables.created NOT NULL, cables.classification NOT NULL, cables.%TID% NOT NULL ]
| | ) [ convert('1990-01-17' from char(10) to timestamp(7)) <= cables.created NOT NULL <= convert('1990-01-18' from char(10) to timestamp(7)) ]
| ) [ cables.created NOT NULL ] [ cables.id NOT NULL, cables.classification NOT NULL ]
) [ 20 ]

SELECT id, classification FROM cables WHERE created BETWEEN '1990-01-17' AND '1990-01-18' ORDER BY created DESC LIMIT 20;
% sys.cables, sys.cables table_name
% id, classification name
% int, varchar type
% 1, 0 length

It is not a problem what so ever to dump this table.

Comment 15358

Date: 2010-12-16 11:15:25 +0100
From: @drstmane

Thanks, Stefan.

To rephrase my previous comment, what we'd need id two PLANs and two EXPLAINs for one query, one PLAN & one EXPLAIN with the --enable-optimize build of MonetDB where the query produces the wrong reault, and one PLAN & one EXPLAIN with the non --enable-optimize build of MonetDB where the query yield the correct results.

Thanks, again,

Stefan

Comment 15359

Date: 2010-12-16 11:29:22 +0100
From: @skinkie

(In reply to comment 7)

To rephrase my previous comment, what we'd need id two PLANs and two EXPLAINs
for one query, one PLAN & one EXPLAIN with the --enable-optimize build of
MonetDB where the query produces the wrong reault, and one PLAN & one EXPLAIN
with the non --enable-optimize build of MonetDB where the query yield the
correct results.

Do you really need both if without optimise it doesn't work as well? Anyway... compiling again :)

Comment 15360

Date: 2010-12-16 11:43:18 +0100
From: @drstmane

hm, maybe I'm lost --- didn't you sayit only does not work "when compiled with enable-optimise"?

Comment 15361

Date: 2010-12-16 11:45:51 +0100
From: @skinkie

(In reply to comment 9)

hm, maybe I'm lost --- didn't you sayit only does not work "when compiled with
enable-optimise"?

At openkvk I have no optimisation, and the working pcre lib, at kabelsearch I have tested all optimisations (with without), but pcre remains non existent.

Anyway this is the optimised version:

sql>explain SELECT id, classification FROM cables WHERE created BETWEEN '1990-01-17' AND '1990-01-18' ORDER BY created DESC LIMIT 20;
% .explain table_name
% mal name
% clob type
% 0 length
function user.s3_1{autoCommit=true}():void;
_2 := sql.mvc();
barrier _57 := language.dataflow();
_9:bat[:oid,:timestamp] := sql.bind(_2,"sys","cables","created",0);
_10 := algebra.uselect(_9,1990-01-17 00:00:00.000:timestamp,1990-01-18 00:00:00.000:timestamp,true,true);
_11 := algebra.markT(_10,0@0:oid);
_12 := bat.reverse(_11);
_13 := pqueue.topn_min(_9,20:wrd);
_14 := algebra.join(_12,_13);
_15 := bat.mirror(_14);
_56 := algebra.leftjoin(_15,_12);
_5:bat[:oid,:int] := sql.bind(_2,"sys","cables","id",0);
_16 := algebra.leftjoin(_56,_5);
_20 := algebra.slice(_16,0:wrd,19:wrd);
_21 := bat.mirror(_20);
_22 := algebra.leftjoin(_21,_16);
_24:bat[:oid,:str] := sql.bind(_2,"sys","cables","classification",0);
_27 := algebra.leftjoinPath(_21,_56,_24);
exit _57;
_28 := sql.resultSet(2,1,_22);
sql.rsColumn(_28,"sys.cables","id","int",32,0,_22);
sql.rsColumn(_28,"sys.cables","classification","varchar",64,0,_27);
_37 := io.stdout();
sql.exportResult(_37,_28);
end s3_1;
sql>plan SELECT id, classification FROM cables WHERE created BETWEEN '1990-01-17' AND '1990-01-18' ORDER BY created DESC LIMIT 20;
% .plan table_name
% rel name
% clob type
% 142 length
top N (
| project (
| | select (
| | | table(sys.cables) [ cables.id NOT NULL, cables.created NOT NULL, cables.classification NOT NULL, cables.%TID% NOT NULL ]
| | ) [ convert('1990-01-17' from char(10) to timestamp(7)) <= cables.created NOT NULL <= convert('1990-01-18' from char(10) to timestamp(7)) ]
| ) [ cables.created NOT NULL ] [ cables.id NOT NULL, cables.classification NOT NULL ]
) [ 20 ]

Comment 15362

Date: 2010-12-17 14:11:10 +0100
From: @skinkie

Does this commit have anything to with?

http://dev.monetdb.org/hg/MonetDB/rev/617e10d1b5fa

Comment 15363

Date: 2010-12-17 14:22:32 +0100
From: @grobian

please tell us by testing if so

Comment 15366

Date: 2010-12-17 14:35:24 +0100
From: @skinkie

As reference. The openkvk checkout is of Mon Dec 13 20:45:08 2010 +0100 so it is a commit in the last 5 days.

Comment 15367

Date: 2010-12-17 15:02:31 +0100
From: @drstmane

hg bisect
is a handy tool to analyze such cases, i.e., to find which checkin break a certain behavior ...

Comment 15368

Date: 2010-12-17 15:04:17 +0100
From: @skinkie

Yes was just reading into. http://mercurial.selenic.com/wiki/BisectExtension

Comment 15369

Date: 2010-12-17 16:04:24 +0100
From: @grobian

$INSTALL_DIR/bin/mclient -dtest
Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2010-hg)
Database: MonetDB v5.22.2, 'test'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>create table x (id int, created timestamp, classification varchar(20));
operation successful
sql>insert into x values (1, timestamp '1990-01-17 15:03:00', 'CONFIDENTIAL');
1 affected row (30.670ms)
sql>insert into x values (1, now(), 'PUBLIC');
1 affected row (19.826ms)
sql>select * from x;
+------+----------------------------+----------------+
| id | created | classification |
+======+============================+================+
| 1 | 1990-01-17 15:03:00.000000 | CONFIDENTIAL |
| 1 | 2010-12-17 15:02:41.000000 | PUBLIC |
+------+----------------------------+----------------+
2 tuples (13.386ms)
sql>select id, classification FROM cables WHERE created BETWEEN '1990-01-17'
more>AND '1990-01-18' ORDER BY created DESC LIMIT 20;
SELECT: no such table 'cables'
sql>select id, classification FROM x WHERE created BETWEEN '1990-01-17' AND '1990-01-18' ORDER BY created DESC LIMIT 20;
+------+----------------+
| id | classification |
+======+================+
| 1 | CONFIDENTIAL |
+------+----------------+
1 tuple (15.317ms)
sql>select cast('1990-01-17 15:03:00' as timestamp) BETWEEN '1990-01-17' AND
more>'1990-01-18';
+---------------------+
| and_>=_single_value |
+=====================+
| true |
+---------------------+
1 tuple (22.705ms)
sql>SELECT id, classification FROM x WHERE created BETWEEN '1990-01-17'
more>AND '1990-01-18' ORDER BY created;
+------+----------------+
| id | classification |
+======+================+
| 1 | CONFIDENTIAL |
+------+----------------+
1 tuple (17.942ms)
sql>

Comment 15370

Date: 2010-12-17 16:17:10 +0100
From: @grobian

Same on todays current. Can you reproduce your issue in the way I did?

Comment 15371

Date: 2010-12-17 16:40:29 +0100
From: @skinkie

Can still reproduce my error, I cannot reproduce it with your example.

Here is the import: https://kabelsearch.org/data/tofabian.sql.gz

Comment 15374

Date: 2010-12-17 17:23:26 +0100
From: @drstmane

Thanks for the data.

The following very simple analysis helps to reduce the problem to a very specific case, which in turn should help us to locate and fix the actual cause of the problem:

(with the latest HG default source base as of Fri Dec 17 17:00 compiled with gcc and debugging enabled on 64-bit Fedora 12):

sql>SELECT id, classification FROM cables WHERE created BETWEEN '1990-01-17' AND '1990-01-18';
+------+----------------+
| id | classification |
+======+================+
| 714 | CONFIDENTIAL |
+------+----------------+
1 tuple (3.000ms)
sql>SELECT id, classification FROM cables WHERE created BETWEEN '1990-01-17' AND '1990-01-18' LIMIT 20;
+------+----------------+
| id | classification |
+======+================+
| 714 | CONFIDENTIAL |
+------+----------------+
1 tuple (3.000ms)
sql>SELECT id, classification FROM cables WHERE created BETWEEN '1990-01-17' AND '1990-01-18' ORDER BY created;
+------+----------------+
| id | classification |
+======+================+
| 714 | CONFIDENTIAL |
+------+----------------+
1 tuple (3.000ms)
sql>SELECT id, classification FROM cables WHERE created BETWEEN '1990-01-17' AND '1990-01-18' ORDER BY created LIMIT 20;
+------+----------------+
| id | classification |
+======+================+
| 714 | CONFIDENTIAL |
+------+----------------+
1 tuple (4.000ms)
sql>SELECT id, classification FROM cables WHERE created BETWEEN '1990-01-17' AND '1990-01-18' ORDER BY created DESC;
+------+----------------+
| id | classification |
+======+================+
| 714 | CONFIDENTIAL |
+------+----------------+
1 tuple (3.000ms)
sql>SELECT id, classification FROM cables WHERE created BETWEEN '1990-01-17' AND '1990-01-18' ORDER BY created DESC LIMIT 20;
+----+----------------+
| id | classification |
+====+================+
+----+----------------+
0 tuples (3.000ms)
sql>

I.e., only the combination of ORDER BY DESC and LIMIT appears to fail.

With the Oct2010 branch, everything appear to work fine.

Comment 15376

Date: 2010-12-17 23:03:35 +0100
From: @drstmane

hg bisect (with a "suitable" test script) seems to suggest that the problems exists since the following changeset:

http://dev.monetdb.org/hg/MonetDB/rev/a369a007a73d

changeset: 38368:a369a007a73d
user: Niels Nes niels@cwi.nl
date: Wed Dec 01 22:27:25 2010 +0100
files: MonetDB5/src/mal/mal_interpreter.mx MonetDB5/src/optimizer/opt_mergetable.mx MonetDB5/src/optimizer/opt_mitosis.mx sql/src/backends/monet5/sql.mx sql/src/backends/monet5/sql_gencode.mx sql/src/common/Makefile.ag sql/src/common/sql_mem.c sql/src/common/sql_types.c sql/src/common/sql_types.h sql/src/include/Makefile.ag sql/src/include/sql_mem.h sql/src/server/bin_optimizer.c sql/src/server/rel_bin.c sql/src/server/rel_subquery.c sql/src/server/sql_psm.c sql/src/server/sql_rel2bin.c sql/src/server/sql_rel2bin.h sql/src/server/sql_semantic.c sql/src/server/sql_statement.c sql/src/server/sql_statement.h sql/src/test/BugTracker-2009/Tests/POWER_vs_prod.SF-2596114.stable.out sql/src/test/BugTracker-2010/Tests/LIMIT_OFFSET_big-endian.Bug-2622.stable.out sql/src/test/BugTracker-2010/Tests/group-by_ordered_column.Bug-2564.stable.out sql/src/test/BugTracker-2010/Tests/incomplete-utf8-sequence.Bug-2575.stable.err sql/src/test/Dependencies/Tests/Dependencies.stable.out sql/src/test/Tests/crashme.timeout sql/src/test/Tests/trace.stable.out sql/src/test/leaks/Tests/check0.stable.out sql/src/test/leaks/Tests/check1.stable.out sql/src/test/leaks/Tests/check2.stable.out sql/src/test/leaks/Tests/check3.stable.out sql/src/test/leaks/Tests/check4.stable.out sql/src/test/leaks/Tests/check5.stable.out
description:
major cleanup

     the stmt structure's op1,op2 and op3 are now stmt pointers
     only the op4 can have special types
             such as typeval, func/aggr
                     table/column
                     list and atom

     we now flatten the stmt tree before we issue the bin_optimizer
     and mal generation. This solves crashes with to deep recursion.

Comment 15377

Date: 2010-12-18 12:57:13 +0100
From: @njnes

removed bogus optimization

Comment 15378

Date: 2010-12-18 12:58:12 +0100
From: @njnes

Changeset 27d9c8547be0 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=27d9c8547be0

Changeset description:

removed bogus optimization. Fixes bug #2757.

Comment 15614

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

The Mar2011 version has been released.

The text was updated successfully, but these errors were encountered:

monetdb-team added bug Something isn't working major SQL labels Nov 30, 2020

monetdb-team closed this as completed Nov 30, 2020

sjoerdmullender added this to the Ancient Release milestone Nov 9, 2022

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

ORDER BY DESC + LIMIT incorrectly yields empty result #2757

ORDER BY DESC + LIMIT incorrectly yields empty result #2757

monetdb-team commented Nov 30, 2020

ORDER BY DESC + LIMIT incorrectly yields empty result #2757

ORDER BY DESC + LIMIT incorrectly yields empty result #2757

Comments

monetdb-team commented Nov 30, 2020

Comment 15350

Steps to Reproduce:

Comment 15351

Comment 15353

Comment 15354

Comment 15355

Comment 15356

Comment 15357

Comment 15358

Comment 15359

Comment 15360

Comment 15361

Comment 15362

Comment 15363

Comment 15366

Comment 15367

Comment 15368

Comment 15369

Comment 15370

Comment 15371

Comment 15374

Comment 15376

Comment 15377

Comment 15378

Comment 15614