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

Bug in subselect (or condition) #6688

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

Bug in subselect (or condition) #6688

monetdb-team opened this issue Nov 30, 2020 · 0 comments
Labels
bug Something isn't working major SQL

Comments

@monetdb-team
Copy link

Date: 2019-02-28 11:29:12 +0100
From: Manuel <>
To: SQL devs <>
Version: 11.29.7 (Mar2018-SP1)
CC: @njnes

Last updated: 2019-11-28 10:00:02 +0100

Comment 26910

Date: 2019-02-28 11:29:12 +0100
From: Manuel <>

User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/72.0.3626.119 Safari/537.36
Build Identifier:

Possibly related to 6686,

With a table created as follows

CREATE TABLE "sys"."unitTestDontDelete" (
"A" VARCHAR(255),
"B" BIGINT,
"C" DOUBLE,
"D" TIMESTAMP
);
INSERT INTO "sys"."unitTestDontDelete" VALUES (NULL, NULL, NULL, NULL);
INSERT INTO "sys"."unitTestDontDelete" VALUES ('Cat1', 0, 0.5, '2013-06-10 11:10:10.000000');
INSERT INTO "sys"."unitTestDontDelete" VALUES ('Cat2', 1, 1.5, '2013-06-11 12:11:11.000000');
INSERT INTO "sys"."unitTestDontDelete" VALUES ('Cat1', 2, 2.5, '2013-06-12 13:12:12.000000');
INSERT INTO "sys"."unitTestDontDelete" VALUES ('Cat2', 3, 3.5, '2013-06-13 14:13:13.000000');
INSERT INTO "sys"."unitTestDontDelete" VALUES ('Cat1', 4, 4.5, '2013-06-14 15:14:14.000000');
INSERT INTO "sys"."unitTestDontDelete" VALUES ('Cat2', 5, 5.5, '2013-06-15 16:15:15.000000');
INSERT INTO "sys"."unitTestDontDelete" VALUES ('Cat1', 6, 6.5, '2013-06-16 17:16:16.000000');
INSERT INTO "sys"."unitTestDontDelete" VALUES ('Cat2', 7, 7.5, '2013-06-17 18:17:17.000000');
INSERT INTO "sys"."unitTestDontDelete" VALUES ('Cat1', 8, 8.5, '2013-06-18 19:18:18.000000');

The query (Q1)

with "cp_t" as
(
select
"cn_t"."A",
"cn_t"."B",
"cn_t"."C",
"cn_t"."D"
from
"unitTestDontDelete" as "cn_t"
where
(
"B" is null
or "B" <> 8
)
)
select
(
select
coalesce(sum("B"), 0)
from
"cp_t" as "cr_t"
where
(
"cr_t"."C" >=
(
select
coalesce(MEDIAN("B"),0)
from
"cp_t" as "cq_t"
where
(
"cq_t"."A" = "cp_t"."A"
or
(
"cq_t"."A" is null
and "cp_t"."A" is null
)
)
)
or
(
"cr_t"."C" is null
and
(
select
coalesce(MEDIAN("B"),0)
from
"cp_t" as "cq_t"
where
(
"cq_t"."A" = "cp_t"."A"
or
(
"cq_t"."A" is null
and "cp_t"."A" is null
)
)
)
is null
)
)
)
as "co_f1"
from
"cp_t"

returns

co_f1

0
0
1
2
3
4
5
6
7

The (equivalent) query

with "cp_t" as
(
select
"cn_t"."A",
"cn_t"."B",
"cn_t"."C",
"cn_t"."D"
from
"unitTestDontDelete" as "cn_t"
where
(
"B" is null
or "B" <> 8
)
)
select
(
select
coalesce(sum("B"), 0)
from
"cp_t" as "cr_t"
where
(
"cr_t"."C" >=
(
select
coalesce(MEDIAN("B"),0)
from
"cp_t" as "cq_t"
where
(
"cq_t"."A" = "cp_t"."A"
or
(
"cq_t"."A" is null
and "cp_t"."A" is null
)
)
)
)
)
as "co_f1"
from
"cp_t"

returns the expected result set

co_f1

28
27
25
27
25
27
25
27
25

Reproducible: Always

Steps to Reproduce:

  1. Import table
  2. execute queries as in details

Actual Results:

co_f1

0
1
2
3
4
5
6
7
0

Expected Results:

co_f1

27
25
27
25
27
25
27
25

Comment 26944

Date: 2019-03-22 15:18:04 +0100
From: MonetDB Mercurial Repository <>

Changeset 44c07fecb32e made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=44c07fecb32e

Changeset description:

Added test for bug #6688.

Comment 26981

Date: 2019-05-01 12:56:41 +0200
From: @njnes

fixed in default. Subquery handling has been reimplemented

@monetdb-team monetdb-team added bug Something isn't working major SQL labels Nov 30, 2020
@sjoerdmullender sjoerdmullender added this to the Ancient Release milestone Feb 7, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working major SQL
Projects
None yet
Development

No branches or pull requests

2 participants