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

Wrong SQL results for a certain combination of GROUP BY / ORDER BY / LIMIT #3847

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

Comments

@monetdb-team
Copy link

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

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
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