ORDER BY DESC + LIMIT incorrectly yields empty result #2757
Last updated: 2011-03-28 17:31:23 +0200
Date: 2010-12-16 00:26:38 +0100
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
sql>select created from cables where created = '1990-01-17 15:03:00';
0 tuples (3.000ms)
sql>SELECT id, classification FROM cables WHERE created BETWEEN '1990-01-17' AND '1990-01-18' ORDER BY created;
Some observations: code is compiled
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)
Date: 2010-12-16 00:27:42 +0100
Some extra info:
sql>select count(*) from cables;
Date: 2010-12-16 08:48:46 +0100
can it be that this:
causes things to go wrong?
Date: 2010-12-16 09:56:23 +0100
(In reply to comment 2)
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.
Date: 2010-12-16 10:03:46 +0100
(In reply to comment 2)
Upgraded my Gentoo libpcre now get:
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.)
Date: 2010-12-16 10:20:17 +0100
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?
Date: 2010-12-16 11:07:19 +0100
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;
plan SELECT id, classification FROM cables WHERE created BETWEEN '1990-01-17' AND '1990-01-18' ORDER BY created DESC LIMIT 20;
SELECT id, classification FROM cables WHERE created BETWEEN '1990-01-17' AND '1990-01-18' ORDER BY created DESC LIMIT 20;
It is not a problem what so ever to dump this table.
Date: 2010-12-16 11:15:25 +0100
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.
Date: 2010-12-16 11:29:22 +0100
(In reply to comment 7)
Do you really need both if without optimise it doesn't work as well? Anyway... compiling again :)
Date: 2010-12-16 11:43:18 +0100
hm, maybe I'm lost --- didn't you sayit only does not work "when compiled with enable-optimise"?
Date: 2010-12-16 11:45:51 +0100
(In reply to comment 9)
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;
Date: 2010-12-17 14:11:10 +0100
Does this commit have anything to with?
Date: 2010-12-17 14:22:32 +0100
please tell us by testing if so
Date: 2010-12-17 14:35:24 +0100
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.
Date: 2010-12-17 15:02:31 +0100
Date: 2010-12-17 15:04:17 +0100
Yes was just reading into. http://mercurial.selenic.com/wiki/BisectExtension
Date: 2010-12-17 16:04:24 +0100
Date: 2010-12-17 16:17:10 +0100
Same on todays current. Can you reproduce your issue in the way I did?
Date: 2010-12-17 16:40:29 +0100
Can still reproduce my error, I cannot reproduce it with your example.
Here is the import: https://kabelsearch.org/data/tofabian.sql.gz
Date: 2010-12-17 17:23:26 +0100
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';
I.e., only the combination of ORDER BY DESC and LIMIT appears to fail.
With the Oct2010 branch, everything appear to work fine.
Date: 2010-12-17 23:03:35 +0100
hg bisect (with a "suitable" test script) seems to suggest that the problems exists since the following changeset:
Date: 2010-12-18 12:57:13 +0100
removed bogus optimization
Date: 2010-12-18 12:58:12 +0100
For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=27d9c8547be0
Date: 2011-03-28 17:31:23 +0200
The Mar2011 version has been released.
The text was updated successfully, but these errors were encountered: