You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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"
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:
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:
Comment 26981
Date: 2019-05-01 12:56:41 +0200
From: @njnes
fixed in default. Subquery handling has been reimplemented
The text was updated successfully, but these errors were encountered: