Date: 2015-11-03 13:49:30 +0100
From: Frédéric Jolliton <<frederic.jolliton+monetdb>>
To: SQL devs <>
Version: 11.21.11 (Jul2015-SP1)
CC: @njnes

Last updated: 2016-01-15 11:38:20 +0100

Comment 21459

Date: 2015-11-03 13:49:30 +0100
From: Frédéric Jolliton <<frederic.jolliton+monetdb>>

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

We tumbled on another worrying bug. When a very specific query is performed, its LIMIT seems to be applied not once everything is computed, but as if it was applied early in the processing.

It occured on a pretty big query. I tried to reduce it as much as I could.

Reproducible: Always

Steps to Reproduce:

Create a simple table:

CREATE TABLE foo (a INT, b INT);
INSERT INTO foo VALUES (10, 3), (2, 5), (0, 8), (7, 10), (1, 1), (9, 12), (4, 1), (3, 9);

Now compare the two following queries:

SELECT *
FROM (SELECT CAST(SUM(a) AS FLOAT) / SUM(b) AS result
FROM foo
GROUP BY a) as t1
ORDER BY (1=1), result DESC
LIMIT 5;

and:

SELECT *
FROM (SELECT CAST(SUM(a) AS FLOAT) / SUM(b) AS result
FROM foo
GROUP BY a) as t1
ORDER BY (1=1), result DESC
LIMIT 10;

Only the LIMIT is different.

Actual Results:

The respective results are:

With LIMIT 5:
+--------------------------+
| result |
+==========================+
| 3.3333333333333335 |
| 1 |
| 0.7 |
| 0.4 |
| 0 |
+--------------------------+

With LIMIT 10:
+--------------------------+
| result |
+==========================+
| 4 |
| 3.3333333333333335 |
| 1 |
| 0.75 |
| 0.7 |
| 0.4 |
| 0.3333333333333333 |
| 0 |
+--------------------------+

Notice how the second result adds values such as 4, that were not part of the first result!

Setting the severity to Major accordingly to the description of this item.. but to me, it is more like a Critical one, even if the server is not crashing or making us lose data..

With this sort of silent bug, we could totally get wrong results without noticing it! That's scary.

We do note however that the bugs are usually quickly fixed, which is pretty nice.

$ bin/mserver5 --version
MonetDB 5 server v11.21.12 (64-bit, 64-bit oids, 128-bit integers)
This is an unreleased version
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved
Visit http://www.monetdb.org/ for further information
Found 15.6GiB available memory, 4 available cpu cores
Libraries:
libpcre: 8.36 2014-09-26 (compiled with 8.36)
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: fjolliton@workstation (x86_64-unknown-linux-gnu)
Compilation: gcc -g -Werror -Wall -Wextra -W -Werror-implicit-function-declaration -Wpointer-arith -Wdeclaration-after-statement -Wundef -Wformat=2 -Wno-format-nonliteral -Winit-self -Winvalid-pch -Wmissing-declarations -Wmissing-format-attribute -Wmissing-prototypes -Wold-style-definition -Wpacked -Wunknown-pragmas -Wvariadic-macros -fstack-protector-all -Wstack-protector -Wpacked-bitfield-compat -Wsync-nand -Wjump-misses-init -Wmissing-include-dirs -Wlogical-op -Wunreachable-code
Linking : /usr/x86_64-pc-linux-gnu/bin/ld -m elf_x86_64

Comment 21483

Date: 2015-11-09 09:11:52 +0100
From: @njnes

somehow the implementation for this case (type dbl) was missing. That is now corrected.

Comment 21484

Date: 2015-11-09 09:11:54 +0100
From: MonetDB Mercurial Repository <>

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

Changeset description:

added test for bug #3847