Changeset: d314cbd55a0e for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=d314cbd55a0e
Added Files:
sql/test/BugTracker-2019/Tests/cte-union.Bug-6755.stable.err
sql/test/BugTracker-2019/Tests/cte-union.Bug-6755.stable.out
Modified Files:
sql/server/rel_optimizer.c
sql/test/BugTracker-2019/Tests/All
sql/test/BugTracker-2019/Tests/cte-union.Bug-6755.sql
sql/test/BugTracker-2019/Tests/duplicates-not-eliminated-long-CASE-stmt.Bug-6697.sql
sql/test/BugTracker-2019/Tests/sequence-first-next-value.Bug-6743.stable.out
Branch: Apr2019
Log Message:
Backported recent fixes on Nov2019 branch tests into Apr2019 branch
diffs (truncated from 325 to 300 lines):
diff --git a/sql/server/rel_optimizer.c b/sql/server/rel_optimizer.c
--- a/sql/server/rel_optimizer.c
+++ b/sql/server/rel_optimizer.c
@@ -2171,38 +2171,23 @@ rel_push_topn_down(int *changes, mvc *sq
ul = rel_project(sql->sa, ul, NULL);
ul->exps = exps_copy(sql->sa, r->exps);
/* possibly add order by column */
- if (add_r) {
- for (node *n = ((list*)r->r)->h ; n ; n =
n->next) {
- sql_exp *exp = (sql_exp*) n->data;
- if (rel_has_exp(ul, exp))
- list_append(ul->exps,
exp_copy(sql->sa, exp));
- }
- }
+ if (add_r)
+ ul->exps = list_merge(ul->exps,
exps_copy(sql->sa, r->r), NULL);
ul->r = exps_copy(sql->sa, r->r);
ul = rel_topn(sql->sa, ul, sum_limit_offset(sql,
rel->exps));
ur = rel_project(sql->sa, ur, NULL);
ur->exps = exps_copy(sql->sa, r->exps);
/* possibly add order by column */
- if (add_r) {
- for (node *n = ((list*)r->r)->h ; n ; n =
n->next) {
- sql_exp *exp = (sql_exp*) n->data;
- if (rel_has_exp(ur, exp))
- list_append(ur->exps,
exp_copy(sql->sa, exp));
- }
- }
+ if (add_r)
+ ur->exps = list_merge(ur->exps,
exps_copy(sql->sa, r->r), NULL);
ur->r = exps_copy(sql->sa, r->r);
ur = rel_topn(sql->sa, ur, sum_limit_offset(sql,
rel->exps));
u = rel_setop(sql->sa, ul, ur, op_union);
u->exps = exps_alias(sql->sa, r->exps);
set_processed(u);
/* possibly add order by column */
- if (add_r) {
- for (node *n = ((list*)r->r)->h ; n ; n =
n->next) {
- sql_exp *exp = (sql_exp*) n->data;
- if (rel_has_exp(u, exp))
- list_append(u->exps,
exp_copy(sql->sa, exp));
- }
- }
+ if (add_r)
+ u->exps = list_merge(u->exps,
exps_copy(sql->sa, r->r), NULL);
if (need_distinct(r)) {
set_distinct(ul);
@@ -8564,6 +8549,7 @@ rel_remove_union_partitions(int *changes
return rel;
if (exp_is_zero_rows(sql, rel->l, NULL)) {
sql_rel *r = rel->r;
+ rel_rename_exps(sql, rel->exps, r->exps);
rel->r = NULL;
rel_destroy(rel);
(*changes)++;
@@ -8572,6 +8558,7 @@ rel_remove_union_partitions(int *changes
}
if (exp_is_zero_rows(sql, rel->r, NULL)) {
sql_rel *l = rel->l;
+ rel_rename_exps(sql, rel->exps, l->exps);
rel->l = NULL;
rel_destroy(rel);
(*changes)++;
diff --git a/sql/test/BugTracker-2019/Tests/All
b/sql/test/BugTracker-2019/Tests/All
--- a/sql/test/BugTracker-2019/Tests/All
+++ b/sql/test/BugTracker-2019/Tests/All
@@ -6,7 +6,7 @@ KNOWNFAIL?subselect-contradiction.Bug-66
insert-replica-table.Bug-6684
KNOWNFAIL?subselect-count.Bug-6686
KNOWNFAIL?subselect.Bug-6688
-timestamptransformation.Bug-6695
+#timestamptransformation.Bug-6695
KNOWNFAIL?duplicates-not-eliminated-long-CASE-stmt.Bug-6697
KNOWNFAIL?subselect.Bug-6700
alter_table_set_schema.Bug-6701
diff --git a/sql/test/BugTracker-2019/Tests/cte-union.Bug-6755.sql
b/sql/test/BugTracker-2019/Tests/cte-union.Bug-6755.sql
--- a/sql/test/BugTracker-2019/Tests/cte-union.Bug-6755.sql
+++ b/sql/test/BugTracker-2019/Tests/cte-union.Bug-6755.sql
@@ -32,7 +32,7 @@ t2 AS (
SELECT t."Carrier", tmp.*
FROM tmp, (SELECT DISTINCT "Carrier" FROM t1) AS t
)
-SELECT "Carrier", "Hour", SUM("PredictedArrDelay")
+SELECT "Carrier", "Hour", CAST(SUM("PredictedArrDelay") AS DECIMAL(18,2))
FROM (SELECT * FROM t1 UNION SELECT * FROM t2) AS t
GROUP BY "Carrier", "Hour"
ORDER BY "Carrier", "Hour";
diff --git a/sql/test/BugTracker-2019/Tests/cte-union.Bug-6755.stable.err
b/sql/test/BugTracker-2019/Tests/cte-union.Bug-6755.stable.err
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2019/Tests/cte-union.Bug-6755.stable.err
@@ -0,0 +1,33 @@
+stderr of test 'cte-union.Bug-6755` in directory 'sql/test/BugTracker-2019`
itself:
+
+
+# 12:18:37 >
+# 12:18:37 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=39914" "--set"
"mapi_usock=/var/tmp/mtest-1903/.s.monetdb.39914" "--set" "monet_prompt="
"--forcemito"
"--dbpath=/home/ferreira/repositories/MonetDB-Apr2019/BUILD/var/MonetDB/mTests_sql_test_BugTracker-2019"
"--set" "embedded_c=true"
+# 12:18:37 >
+
+# builtin opt gdk_dbpath =
/home/ferreira/repositories/MonetDB-Apr2019/BUILD/var/monetdb5/dbfarm/demo
+# builtin opt monet_prompt = >
+# builtin opt monet_daemon = no
+# builtin opt mapi_port = 50000
+# builtin opt mapi_open = false
+# builtin opt mapi_autosense = false
+# builtin opt sql_optimizer = default_pipe
+# builtin opt sql_debug = 0
+# cmdline opt gdk_nr_threads = 0
+# cmdline opt mapi_open = true
+# cmdline opt mapi_port = 39914
+# cmdline opt mapi_usock = /var/tmp/mtest-1903/.s.monetdb.39914
+# cmdline opt monet_prompt =
+# cmdline opt gdk_dbpath =
/home/ferreira/repositories/MonetDB-Apr2019/BUILD/var/MonetDB/mTests_sql_test_BugTracker-2019
+# cmdline opt embedded_c = true
+#main thread:!ERROR:MALException:client.quit:Server stopped
+
+# 12:18:38 >
+# 12:18:38 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-1903" "--port=39914"
+# 12:18:38 >
+
+
+# 12:18:38 >
+# 12:18:38 > "Done."
+# 12:18:38 >
+
diff --git a/sql/test/BugTracker-2019/Tests/cte-union.Bug-6755.stable.out
b/sql/test/BugTracker-2019/Tests/cte-union.Bug-6755.stable.out
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2019/Tests/cte-union.Bug-6755.stable.out
@@ -0,0 +1,113 @@
+stdout of test 'cte-union.Bug-6755` in directory 'sql/test/BugTracker-2019`
itself:
+
+
+# 12:18:37 >
+# 12:18:37 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=39914" "--set"
"mapi_usock=/var/tmp/mtest-1903/.s.monetdb.39914" "--set" "monet_prompt="
"--forcemito"
"--dbpath=/home/ferreira/repositories/MonetDB-Apr2019/BUILD/var/MonetDB/mTests_sql_test_BugTracker-2019"
"--set" "embedded_c=true"
+# 12:18:37 >
+
+# MonetDB 5 server v11.33.12 (hg id: d4694fed4f7e)
+# This is an unreleased version
+# Serving database 'mTests_sql_test_BugTracker-2019', using 8 threads
+# Compiled for x86_64-pc-linux-gnu/64bit with 128bit integers
+# Found 15.527 GiB available main-memory.
+# Copyright (c) 1993 - July 2008 CWI.
+# Copyright (c) August 2008 - 2019 MonetDB B.V., all rights reserved
+# Visit https://www.monetdb.org/ for further information
+# Listening for connection requests on
mapi:monetdb://localhost.localdomain:39914/
+# Listening for UNIX domain connection requests on
mapi:monetdb:///var/tmp/mtest-1903/.s.monetdb.39914
+# MonetDB/GIS module loaded
+# SQL catalog created, loading sql scripts once
+# loading sql script: 09_like.sql
+# loading sql script: 10_math.sql
+# loading sql script: 11_times.sql
+# loading sql script: 12_url.sql
+# loading sql script: 13_date.sql
+# loading sql script: 14_inet.sql
+# loading sql script: 15_querylog.sql
+# loading sql script: 16_tracelog.sql
+# loading sql script: 17_temporal.sql
+# loading sql script: 18_index.sql
+# loading sql script: 20_vacuum.sql
+# loading sql script: 21_dependency_views.sql
+# loading sql script: 22_clients.sql
+# loading sql script: 23_skyserver.sql
+# loading sql script: 25_debug.sql
+# loading sql script: 26_sysmon.sql
+# loading sql script: 27_rejects.sql
+# loading sql script: 39_analytics.sql
+# loading sql script: 39_analytics_hge.sql
+# loading sql script: 40_geom.sql
+# loading sql script: 40_json.sql
+# loading sql script: 40_json_hge.sql
+# loading sql script: 41_md5sum.sql
+# loading sql script: 45_uuid.sql
+# loading sql script: 46_profiler.sql
+# loading sql script: 51_sys_schema_extension.sql
+# loading sql script: 60_wlcr.sql
+# loading sql script: 72_fits.sql
+# loading sql script: 74_netcdf.sql
+# loading sql script: 75_lidar.sql
+# loading sql script: 75_shp.sql
+# loading sql script: 75_storagemodel.sql
+# loading sql script: 80_statistics.sql
+# loading sql script: 80_udf.sql
+# loading sql script: 80_udf_hge.sql
+# loading sql script: 85_bam.sql
+# loading sql script: 90_generator.sql
+# loading sql script: 90_generator_hge.sql
+# loading sql script: 99_system.sql
+# MonetDB/SQL module loaded
+
+Ready.
+
+# 12:18:38 >
+# 12:18:38 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-1903" "--port=39914"
+# 12:18:38 >
+
+#start transaction;
+#CREATE TABLE ontime (
+# "Year" SMALLINT,
+# "Month" TINYINT,
+# "DayofMonth" TINYINT,
+# "Carrier" CHAR(2),
+# "CRSDepTime" DECIMAL(8,2),
+# "ArrDelay" DECIMAL(8,2)
+#);
+#CREATE TABLE tmp (
+# "Hour" TINYINT,
+# "PredictedArrDelay" DECIMAL(8,2) DEFAULT 0.0
+#);
+#INSERT INTO tmp ("Hour")
+#VALUES
+# (0), (1), (2), (3), (4), (5),
+# (6), (7), (8), (9), (10), (11),
+# (12), (13), (14), (15), (16), (17),
+# (18), (19), (20), (21), (22), (23);
+[ 24 ]
+#INSERT INTO ontime VALUES (2001, 9, 2, 'AA', 900.00, -6.00);
+[ 1 ]
+#ALTER TABLE "ontime" SET READ ONLY;
+#WITH t1 AS (
+# SELECT "Carrier", CAST (FLOOR("CRSDepTime"%2400/100) AS INT) AS "Hour",
+# CAST(AVG("ArrDelay") AS DECIMAL(8,2)) AS "PredictedArrDelay"
+# FROM ontime
+# WHERE "Year" = 2007 AND "Month" = 10 AND "DayofMonth" = 24
+# GROUP BY "Carrier", "Hour"
+#),
+#t2 AS (
+# SELECT t."Carrier", tmp.*
+# FROM tmp, (SELECT DISTINCT "Carrier" FROM t1) AS t
+#)
+#SELECT "Carrier", "Hour", SUM("PredictedArrDelay")
+#FROM (SELECT * FROM t1 UNION SELECT * FROM t2) AS t
+#GROUP BY "Carrier", "Hour"
+% sys.t, sys.t, sys.L40 # table_name
+% Carrier, Hour, L40 # name
+% char, int, decimal # type
+% 2, 1, 20 # length
+#rollback;
+
+# 12:18:38 >
+# 12:18:38 > "Done."
+# 12:18:38 >
+
diff --git
a/sql/test/BugTracker-2019/Tests/duplicates-not-eliminated-long-CASE-stmt.Bug-6697.sql
b/sql/test/BugTracker-2019/Tests/duplicates-not-eliminated-long-CASE-stmt.Bug-6697.sql
---
a/sql/test/BugTracker-2019/Tests/duplicates-not-eliminated-long-CASE-stmt.Bug-6697.sql
+++
b/sql/test/BugTracker-2019/Tests/duplicates-not-eliminated-long-CASE-stmt.Bug-6697.sql
@@ -4,5 +4,5 @@ CREATE TABLE IF NOT EXISTS "task" (
"sys_created_on" TIMESTAMP
);
-
explain SELECT sys.timestamp_to_str(case when task0."sys_created_on" >=
'1999-10-31 09:00:00' and task0."sys_created_on" < '2000-04-02 10:00:00' then
task0."sys_created_on" + interval '-28800' second when task0."sys_created_on"
>= '2000-04-02 10:00:00' and task0."sys_created_on" < '2000-10-29 09:00:00'
then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2000-10-29 09:00:00' and task0."sys_created_on" <
'2001-04-01 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2001-04-01 10:00:00' and task0."sys_created_on"
< '2001-10-28 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0."sys_created_on" >= '2001-10-28 09:00:00' and task0."sys_created_on"
< '2002-04-07 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2002-04-07 10:00:00' and task0."sys_created_on"
< '2002-10-27 09:00:00' then task0."sys_created_on" + interval '-25200' second w
hen task0."sys_created_on" >= '2002-10-27 09:00:00' and task0."sys_created_on"
< '2003-04-06 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2003-04-06 10:00:00' and task0."sys_created_on"
< '2003-10-26 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0."sys_created_on" >= '2003-10-26 09:00:00' and task0."sys_created_on"
< '2004-04-04 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2004-04-04 10:00:00' and task0."sys_created_on"
< '2004-10-31 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0."sys_created_on" >= '2004-10-31 09:00:00' and task0."sys_created_on"
< '2005-04-03 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2005-04-03 10:00:00' and task0."sys_created_on"
< '2005-10-30 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0."sys_created_on" >= '2005-10-30 0
9:00:00' and task0."sys_created_on" < '2006-04-02 10:00:00' then
task0."sys_created_on" + interval '-28800' second when task0."sys_created_on"
>= '2006-04-02 10:00:00' and task0."sys_created_on" < '2006-10-29 09:00:00'
then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2006-10-29 09:00:00' and task0."sys_created_on" <
'2007-03-11 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2007-03-11 10:00:00' and task0."sys_created_on"
< '2007-11-04 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0."sys_created_on" >= '2007-11-04 09:00:00' and task0."sys_created_on"
< '2008-03-09 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2008-03-09 10:00:00' and task0."sys_created_on"
< '2008-11-02 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0."sys_created_on" >= '2008-11-02 09:00:00' and task0."sys_created_on"
< '2009
-03-08 10:00:00' then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2009-03-08 10:00:00' and task0."sys_created_on" <
'2009-11-01 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0."sys_created_on" >= '2009-11-01 09:00:00' and task0."sys_created_on"
< '2010-03-14 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2010-03-14 10:00:00' and task0."sys_created_on"
< '2010-11-07 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0."sys_created_on" >= '2010-11-07 09:00:00' and task0."sys_created_on"
< '2011-03-13 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2011-03-13 10:00:00' and task0."sys_created_on"
< '2011-11-06 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0."sys_created_on" >= '2011-11-06 09:00:00' and task0."sys_created_on"
< '2012-03-11 10:00:00' then task0."sys_created_on
" + interval '-28800' second when task0."sys_created_on" >= '2012-03-11
10:00:00' and task0."sys_created_on" < '2012-11-04 09:00:00' then
task0."sys_created_on" + interval '-25200' second when task0."sys_created_on"
>= '2012-11-04 09:00:00' and task0."sys_created_on" < '2013-03-10 10:00:00'
then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2013-03-10 10:00:00' and task0."sys_created_on" <
'2013-11-03 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0."sys_created_on" >= '2013-11-03 09:00:00' and task0."sys_created_on"
< '2014-03-09 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2014-03-09 10:00:00' and task0."sys_created_on"
< '2014-11-02 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0."sys_created_on" >= '2014-11-02 09:00:00' and task0."sys_created_on"
< '2015-03-08 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sy
s_created_on" >= '2015-03-08 10:00:00' and task0."sys_created_on" <
'2015-11-01 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0."sys_created_on" >= '2015-11-01 09:00:00' and task0."sys_created_on"
< '2016-03-13 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2016-03-13 10:00:00' and task0."sys_created_on"
< '2016-11-06 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0."sys_created_on" >= '2016-11-06 09:00:00' and task0."sys_created_on"
< '2017-03-12 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2017-03-12 10:00:00' and task0."sys_created_on"
< '2017-11-05 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0."sys_created_on" >= '2017-11-05 09:00:00' and task0."sys_created_on"
< '2018-03-11 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2018-03-11 10:00:00' and
task0."sys_created_on" < '2018-11-04 09:00:00' then task0."sys_created_on" +
interval '-25200' second when task0."sys_created_on" >= '2018-11-04 09:00:00'
and task0."sys_created_on" < '2019-03-10 10:00:00' then task0."sys_created_on"
+ interval '-28800' second when task0."sys_created_on" >= '2019-03-10 10:00:00'
and task0."sys_created_on" < '2019-11-03 09:00:00' then task0."sys_created_on"
+ interval '-25200' second when task0."sys_created_on" >= '2019-11-03 09:00:00'
and task0."sys_created_on" < '2020-03-08 10:00:00' then task0."sys_created_on"
+ interval '-28800' second when task0."sys_created_on" >= '2020-03-08 10:00:00'
and task0."sys_created_on" < '2020-11-01 09:00:00' then task0."sys_created_on"
+ interval '-25200' second when task0."sys_created_on" >= '2020-11-01 09:00:00'
and task0."sys_created_on" < '2021-03-14 10:00:00' then task0."sys_created_on"
+ interval '-28800' second when task0."sys_created_on" >= '2021-03-14 10:00:00'
and task0."sys_created_on" < '2021-11-07 09:00:
00' then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2021-11-07 09:00:00' and task0."sys_created_on" <
'2022-03-13 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2022-03-13 10:00:00' and task0."sys_created_on"
< '2022-11-06 09:00:00' then task0."sys_created_on" + interval '-25200' second
else task0."sys_created_on" + interval '-28800' second end,'%Y') AS yearref
FROM task task0 WHERE task0."sys_class_name" = 'incident' AND task0."priority"
IS NOT NULL ;
+DROP TABLE "task";
diff --git
a/sql/test/BugTracker-2019/Tests/sequence-first-next-value.Bug-6743.stable.out
b/sql/test/BugTracker-2019/Tests/sequence-first-next-value.Bug-6743.stable.out
---
a/sql/test/BugTracker-2019/Tests/sequence-first-next-value.Bug-6743.stable.out
+++
b/sql/test/BugTracker-2019/Tests/sequence-first-next-value.Bug-6743.stable.out
@@ -83,13 +83,13 @@ Ready.
% L2 # name
% bigint # type
% 1 # length
-[ 2 ]
+[ 1 ]
#select next_value_for('sys','seq');
% .L2 # table_name
% L2 # name
% bigint # type
% 1 # length
-[ 3 ]
+[ 2 ]
#select get_value_for('sys','seq');
% .L2 # table_name
% L2 # name
@@ -107,7 +107,7 @@ Ready.
% L2 # name
% bigint # type
% 1 # length
-[ 4 ]
+[ 3 ]
#select get_value_for('sys','seq');
% .L2 # table_name
% L2 # name
@@ -119,7 +119,7 @@ Ready.
% L2 # name
% bigint # type
% 1 # length
-[ 5 ]
+[ 4 ]
#select get_value_for('sys','seq');
% .L2 # table_name
% L2 # name
@@ -146,13 +146,13 @@ Ready.
% L2 # name
% bigint # type
% 1 # length
-[ 2 ]
+[ 1 ]
#select next_value_for('sys','seq');
% .L2 # table_name
% L2 # name
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
