Changeset: 16eb162a6f37 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=16eb162a6f37
Modified Files:
clients/mapiclient/mhelp.c
sql/backends/monet5/rel_bin.c
sql/server/rel_select.c
sql/test/SQLancer/Tests/sqlancer08.sql
sql/test/miscellaneous/Tests/simple_selects.sql
sql/test/miscellaneous/Tests/simple_selects.stable.out
Branch: default
Log Message:
Merged with Oct2020
diffs (251 lines):
diff --git a/clients/mapiclient/mhelp.c b/clients/mapiclient/mhelp.c
--- a/clients/mapiclient/mhelp.c
+++ b/clients/mapiclient/mhelp.c
@@ -54,7 +54,7 @@ SQLhelp sqlhelp1[] = {
"",
"ALTER SCHEMA [ IF EXISTS ] ident RENAME TO ident",
"ident",
- "See also
https://www.monetdb.org/Documentation/SQLreference/TableDefinitions/AlterStatement"},
+ "See also
https://www.monetdb.org/Documentation/SQLReference/DataDefinition/SchemaDefinitions"},
{"ALTER SEQUENCE",
"",
"ALTER SEQUENCE qname [ AS seq_int_datatype] [ RESTART [WITH intval]]
[INCREMENT BY intval]\n"
@@ -191,7 +191,7 @@ SQLhelp sqlhelp1[] = {
"",
"CREATE REPLICA TABLE [ IF NOT EXISTS ] qname table_source",
NULL,
- "See also
https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/TransactionReplication"},
+ "See also
https://www.monetdb.org/Documentation/SQLReference/TableDefinitions"},
{"CREATE ROLE",
"Create a new role. You can grant privileges to a role and next\n"
"grant a role (or multiple roles) to specific users",
@@ -246,7 +246,7 @@ SQLhelp sqlhelp1[] = {
" RETURNS function_return_data_type\n"
" EXTERNAL NAME ident ',' ident",
"qname,param,function_return_data_type,ident",
- "See also
https://www.monetdb.org/Documentation/SQLreference/ProgrammingSQL/Functions"},
+ "See also
https://www.monetdb.org/Documentation/SQLReference/DataManipulation/WindowFunctions"},
{"CURRENT_DATE",
"Pseudo column or function to get the current date",
"CURRENT_DATE [ '(' ')' ]",
@@ -291,7 +291,7 @@ SQLhelp sqlhelp1[] = {
"Debug a SQL statement using MAL debugger",
"DEBUG statement",
NULL,
-
"https://www.monetdb.org/Documentation/SQLreference/RuntimeFeatures/Debug"},
+ "See also
https://www.monetdb.org/Documentation/SQLreference/RuntimeFeatures/Debug"},
{"DECLARE",
"Define a local variable",
"DECLARE ident_list data_type",
diff --git a/sql/server/rel_select.c b/sql/server/rel_select.c
--- a/sql/server/rel_select.c
+++ b/sql/server/rel_select.c
@@ -449,8 +449,7 @@ static list *
check_arguments_and_find_largest_any_type(mvc *sql, sql_rel *rel, list *exps,
sql_subfunc *sf, int maybe_zero_or_one)
{
list *nexps = new_exp_list(sql->sa);
- sql_subtype *atp = NULL;
- sql_arg *aa = NULL;
+ sql_subtype *atp = NULL, super;
/* find largest any type argument */
for (node *n = exps->h, *m = sf->func->ops->h; n && m; n = n->next, m =
m->next) {
@@ -458,15 +457,17 @@ check_arguments_and_find_largest_any_typ
sql_exp *e = n->data;
sql_subtype *t = exp_subtype(e);
- if (!aa && a->type.type->eclass == EC_ANY) {
- atp = t;
- aa = a;
- }
- if (aa && a->type.type->eclass == EC_ANY && t && atp &&
(t->type->localtype > atp->type->localtype || (t->type->localtype ==
atp->type->localtype && t->digits > atp->digits && t->scale == atp->scale))) {
- atp = t;
- aa = a;
- }
- }
+ if (a->type.type->eclass == EC_ANY) {
+ if (t && atp) {
+ result_datatype(&super, t, atp);
+ atp = &super;
+ } else if (t) {
+ atp = t;
+ }
+ }
+ }
+ if (atp && atp->type->localtype == TYPE_void) /* NULL */
+ atp = sql_bind_localtype("str");
for (node *n = exps->h, *m = sf->func->ops->h; n && m; n = n->next, m =
m->next) {
sql_arg *a = m->data;
sql_exp *e = n->data;
@@ -483,7 +484,7 @@ check_arguments_and_find_largest_any_typ
append(nexps, e);
}
/* dirty hack */
- if (sf->func->type != F_PROC && sf->func->type != F_UNION &&
sf->func->type != F_LOADER && sf->res && aa && atp)
+ if (sf->func->type != F_PROC && sf->func->type != F_UNION &&
sf->func->type != F_LOADER && sf->res && atp)
sf->res->h->data = sql_create_subtype(sql->sa, atp->type,
atp->digits, atp->scale);
return nexps;
}
diff --git a/sql/test/SQLancer/Tests/sqlancer08.sql
b/sql/test/SQLancer/Tests/sqlancer08.sql
--- a/sql/test/SQLancer/Tests/sqlancer08.sql
+++ b/sql/test/SQLancer/Tests/sqlancer08.sql
@@ -217,3 +217,5 @@ select round(t2.tc0, 88) from t2;
ROLLBACK;
SELECT round(- (((-443710828)||(1616633099))), 789092170);
+
+PREPARE VALUES (CASE WHEN true THEN 5 BETWEEN 4 AND 2 END);
diff --git a/sql/test/SQLancer/Tests/sqlancer08.stable.out
b/sql/test/SQLancer/Tests/sqlancer08.stable.out
--- a/sql/test/SQLancer/Tests/sqlancer08.stable.out
+++ b/sql/test/SQLancer/Tests/sqlancer08.stable.out
@@ -311,6 +311,13 @@ stdout of test 'sqlancer08` in directory
[ "char", 0, 0, NULL, NULL, NULL ]
[ "bigint", 64, 0, NULL, NULL, NULL ]
[ "tinyint", 5, 0, NULL, NULL, NULL ]
+#PREPARE VALUES (CASE WHEN true THEN 5 BETWEEN 4 AND 2 END);
+#PREPARE VALUES (CASE WHEN true THEN 5 BETWEEN 4 AND 2 END);
+% .prepare, .prepare, .prepare, .prepare, .prepare,
.prepare # table_name
+% type, digits, scale, schema, table, column # name
+% varchar, int, int, str, str, str # type
+% 7, 1, 1, 0, 2, 2 # length
+[ "boolean", 1, 0, "", "%1", "%1" ]
# 11:38:36 >
# 11:38:36 > "Done."
diff --git a/sql/test/SQLancer/Tests/sqlancer09.sql
b/sql/test/SQLancer/Tests/sqlancer09.sql
--- a/sql/test/SQLancer/Tests/sqlancer09.sql
+++ b/sql/test/SQLancer/Tests/sqlancer09.sql
@@ -72,3 +72,36 @@ insert into t1 values ('');
insert into t1(c0) values ((select 'a')), ('b');
insert into t1(c0) values(r']BW扗}FUp'), (cast((values (greatest(r'Aᨐ', r'_')))
as string(616))), (r'');
ROLLBACK;
+
+START TRANSACTION;
+CREATE TABLE "sys"."t1" ("c0" BIGINT);
+COPY 4 RECORDS INTO "sys"."t1" FROM stdin USING DELIMITERS E'\t',E'\n','"';
+1096730569
+-655229050
+1040813052
+-1340211666
+
+create view v0(vc0, vc1, vc2) as (values (uuid
'39FcCcEE-5033-0d81-42Eb-Ac6fFaA9EF2d', ((case when true then lower(r'e')
end)ilike(cast(sql_sub(interval '1600798007' month, interval '854525416' month)
as string(583)))),
+cast((greatest(time '12:29:42', time '00:13:46')) not between asymmetric
(sql_min(time '01:00:00', time '08:31:00')) and (greatest(time '00:12:32', time
'11:40:56')) as bigint)));
+
+MERGE INTO t1 USING (SELECT * FROM v0) AS v0 ON (((COALESCE(24656,
0.42848459531531180033425698638893663883209228515625, 1153747454,
0.04253046482486677604128999519161880016326904296875,
417897684))%(((-4.65033856E8)/(98)))))
+NOT BETWEEN SYMMETRIC (+ (NULLIF(-1338511329, 12))) AND (CASE WHEN CASE TIME
'06:02:29' WHEN TIME '22:17:20' THEN TRUE ELSE TRUE END THEN "second"(INTERVAL
'1243665924' DAY) WHEN (FALSE) = TRUE THEN CASE WHEN FALSE THEN -116446524
+WHEN TRUE THEN 1702709680 WHEN r'TRUE' THEN 1255285064 END
+WHEN (UUID 'baF49A5B-1862-19aa-E6F8-b3C5A7F4b1FF') BETWEEN SYMMETRIC (UUID
'63A9aBBe-87b1-683a-2c68-eCd5cC7FE7E9')
+AND (UUID '82eb84EF-dF3D-a45e-f92b-E42BdfFEB1B9') THEN - (1129823324) END)
WHEN MATCHED THEN DELETE;
+
+SELECT 1 FROM (SELECT 1 FROM v0) AS v0(v0) inner join t1 ON 1 BETWEEN 2 AND 1;
+-- Disable rel_simplify_ifthenelse optimizer
+SELECT 1 FROM (SELECT 1 FROM v0) AS v0(v0) inner join t1 ON 1 BETWEEN 2 AND
(CASE WHEN 1 BETWEEN 2 AND 3 THEN 2 END);
+ROLLBACK;
+
+START TRANSACTION;
+CREATE TABLE "sys"."t2" ("c0" BOOLEAN NOT NULL DEFAULT false, CONSTRAINT
"t2_c0_pkey" PRIMARY KEY ("c0"));
+
+INSERT INTO t2(c0) VALUES((((((((least(r' ]', r'3''')) IS NULL)OR((((TIMESTAMP
'1969-12-20 19:22:32') BETWEEN SYMMETRIC (TIMESTAMP '1969-12-29 05:03:02') AND
(TIMESTAMP '1970-01-14 15:38:43'))OR
+(CASE FALSE WHEN FALSE THEN TRUE WHEN TRUE THEN TRUE WHEN FALSE THEN FALSE
WHEN FALSE THEN TRUE ELSE TRUE END)))))OR
+(COALESCE((TIMESTAMP '1969-12-11 14:58:21') BETWEEN SYMMETRIC (TIMESTAMP
'1970-01-09 21:56:14') AND (TIMESTAMP '1970-01-01 01:00:14'),
+((0.26488915)>(1.345373227E9)), sql_min(FALSE,
TRUE)))))AND("isauuid"(r'45456452')))), (TRUE);
+
+INSERT INTO t2 VALUES (COALESCE(1 BETWEEN 2 AND 3, 1));
+ROLLBACK;
diff --git a/sql/test/SQLancer/Tests/sqlancer09.stable.err
b/sql/test/SQLancer/Tests/sqlancer09.stable.err
--- a/sql/test/SQLancer/Tests/sqlancer09.stable.err
+++ b/sql/test/SQLancer/Tests/sqlancer09.stable.err
@@ -5,6 +5,10 @@ stderr of test 'sqlancer09` in directory
# 14:35:03 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-493376" "--port=30980"
# 14:35:03 >
+MAPI = (monetdb) /var/tmp/mtest-133412/.s.monetdb.36570
+QUERY = INSERT INTO t2 VALUES (COALESCE(1 BETWEEN 2 AND 3, 1));
+ERROR = !INSERT INTO: PRIMARY KEY constraint 't2.t2_c0_pkey' violated
+CODE = 40002
# 14:35:03 >
# 14:35:03 > "Done."
diff --git a/sql/test/SQLancer/Tests/sqlancer09.stable.out
b/sql/test/SQLancer/Tests/sqlancer09.stable.out
--- a/sql/test/SQLancer/Tests/sqlancer09.stable.out
+++ b/sql/test/SQLancer/Tests/sqlancer09.stable.out
@@ -62,6 +62,50 @@ stdout of test 'sqlancer09` in directory
#when -2 then -5 end), (((1)>>(1)))), case when least(true, false) then
greatest(timestamp '1970-01-15 21:14:28', timestamp '1970-01-02 15:11:23') end,
[ 0 ]
#ROLLBACK;
+#START TRANSACTION;
+#CREATE TABLE "sys"."t0" ("c0" TIME NOT NULL, "c1" VARCHAR(143),
+# CONSTRAINT "t0_c0_pkey" PRIMARY KEY ("c0"), CONSTRAINT "t0_c0_unique"
UNIQUE ("c0"), CONSTRAINT "t0_c1_unique" UNIQUE ("c1"));
+#COPY 7 RECORDS INTO "sys"."t0" FROM stdin USING DELIMITERS E'\t',E'\n','"';
+#21:19:08 ""
+#13:02:49 NULL
+#01:02:11 NULL
+#16:34:25 NULL
+#12:11:43 NULL
+#10:35:38 NULL
+#04:26:50 NULL
+[ 7 ]
+#CREATE TABLE "sys"."t1" ("c0" CHAR(375) NOT NULL, CONSTRAINT "t1_c0_pkey"
PRIMARY KEY ("c0"), CONSTRAINT "t1_c0_fkey" FOREIGN KEY ("c0") REFERENCES
"sys"."t0" ("c1"));
+#ROLLBACK;
+#START TRANSACTION;
+#CREATE TABLE "sys"."t1" ("c0" BIGINT);
+#COPY 4 RECORDS INTO "sys"."t1" FROM stdin USING DELIMITERS E'\t',E'\n','"';
+#1096730569
+#-655229050
+#1040813052
+#-1340211666
+[ 4 ]
+#create view v0(vc0, vc1, vc2) as (values (uuid
'39FcCcEE-5033-0d81-42Eb-Ac6fFaA9EF2d', ((case when true then lower(r'e')
end)ilike(cast(sql_sub(interval '1600798007' month, interval '854525416' month)
as string(583)))),
+#cast((greatest(time '12:29:42', time '00:13:46')) not between asymmetric
(sql_min(time '01:00:00', time '08:31:00')) and (greatest(time '00:12:32', time
'11:40:56')) as bigint)));
+#MERGE INTO t1 USING (SELECT * FROM v0) AS v0 ON (((COALESCE(24656,
0.42848459531531180033425698638893663883209228515625, 1153747454,
0.04253046482486677604128999519161880016326904296875,
417897684))%(((-4.65033856E8)/(98)))))
+#NOT BETWEEN SYMMETRIC (+ (NULLIF(-1338511329, 12))) AND (CASE WHEN CASE TIME
'06:02:29' WHEN TIME '22:17:20' THEN TRUE ELSE TRUE END THEN "second"(INTERVAL
'1243665924' DAY) WHEN (FALSE) = TRUE THEN CASE WHEN FALSE THEN -116446524
+#WHEN TRUE THEN 1702709680 WHEN r'TRUE' THEN 1255285064 END
+[ 4 ]
+#SELECT 1 FROM (SELECT 1 FROM v0) AS v0(v0) inner join t1 ON 1 BETWEEN 2 AND 1;
+% .%11 # table_name
+% %11 # name
+% tinyint # type
+% 1 # length
+#SELECT 1 FROM (SELECT 1 FROM v0) AS v0(v0) inner join t1 ON 1 BETWEEN 2 AND
(CASE WHEN 1 BETWEEN 2 AND 3 THEN 2 END);
+% .%12 # table_name
+% %12 # name
+% tinyint # type
+% 1 # length
+#ROLLBACK;
+#START TRANSACTION;
+#CREATE TABLE "sys"."t2" ("c0" BOOLEAN NOT NULL DEFAULT false, CONSTRAINT
"t2_c0_pkey" PRIMARY KEY ("c0"));
+#INSERT INTO t2(c0) VALUES((((((((least(r' ]', r'3''')) IS
NULL)OR((((TIMESTAMP '1969-12-20 19:22:32') BETWEEN SYMMETRIC (TIMESTAMP
'1969-12-29 05:03:02') AND (TIMESTAMP '1970-01-14 15:38:43'))OR(CASE FALSE WHEN
FALSE THEN TRUE WHEN TRUE THEN TRUE WHEN FALSE THEN FALSE WHEN FALSE THEN TRUE
ELSE TRUE END)))))OR(COALESCE((TIMESTAMP '1969-12-11 14:58:21') BETWEEN
SYMMETRIC (TIMESTAMP '1970-01-09 21:56:14') AND (TIMESTAMP '1970-01-01
01:00:14'), ((0.26488915)>(1.345373227E9)), sql_min(FALSE,
TRUE)))))AND("isauuid"(r'45456452')))), (TRUE);
+[ 2 ]
+#ROLLBACK;
# 14:35:03 >
# 14:35:03 > "Done."
diff --git a/sql/test/miscellaneous/Tests/simple_selects.sql
b/sql/test/miscellaneous/Tests/simple_selects.sql
--- a/sql/test/miscellaneous/Tests/simple_selects.sql
+++ b/sql/test/miscellaneous/Tests/simple_selects.sql
@@ -236,6 +236,9 @@ select 1, null except select 1, null;
select 1, null intersect select 1, null;
-- 1 NULL
+select ifthenelse(false, 'abc', 'abcd'), ifthenelse(false, 1.23, 12.3);
+ -- abcd 12.30
+
start transaction;
create or replace function ups() returns int begin if null > 1 then return 1;
else return 2; end if; end;
select ups();
diff --git a/sql/test/miscellaneous/Tests/simple_selects.stable.out
b/sql/test/miscellaneous/Tests/simple_selects.stable.out
--- a/sql/test/miscellaneous/Tests/simple_selects.stable.out
+++ b/sql/test/miscellaneous/Tests/simple_selects.stable.out
@@ -447,6 +447,12 @@ project (
% tinyint, char # type
% 1, 0 # length
[ 1, NULL ]
+#select ifthenelse(false, 'abc', 'abcd'), ifthenelse(false, 1.23, 12.3);
+% .%2, .%3 # table_name
+% %2, %3 # name
+% char, decimal # type
+% 4, 40 # length
+[ "abcd", 12.30 ]
#start transaction;
#create or replace function ups() returns int begin if null > 1 then return 1;
else return 2; end if; end;
#select ups();
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
