Changeset: f14432331837 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=f14432331837
Modified Files:
sql/common/sql_types.c
sql/server/rel_select.c
sql/test/BugTracker-2012/Tests/timestamp_minus_date.Bug-2977.stable.err
sql/test/BugTracker-2012/Tests/timestamp_minus_date.Bug-2977.stable.out
sql/test/SQLancer/Tests/sqlancer03.sql
sql/test/SQLancer/Tests/sqlancer03.stable.out
sql/test/miscellaneous/Tests/values.sql
sql/test/miscellaneous/Tests/values.stable.err
sql/test/miscellaneous/Tests/values.stable.out
sql/test/pg_regress/Tests/date.stable.err
sql/test/pg_regress/Tests/date.stable.out
Branch: Jun2020
Log Message:
Making SQLancer happy. Allow conversion from date to timestamp (according to
the SQL standard). Also at rel_case, check for supertype of the conditions and
avoid parsing the optional conditional multiple times
diffs (truncated from 680 to 300 lines):
diff --git a/sql/common/sql_types.c b/sql/common/sql_types.c
--- a/sql/common/sql_types.c
+++ b/sql/common/sql_types.c
@@ -109,7 +109,7 @@ static int convert_matrix[EC_MAX][EC_MAX
/* EC_FLT */ { 0, 0, 0, 1, 1, 0, 1, 1, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0,
0 },
/* EC_TIME */ { 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 1, 2, 0, 0, 0, 0,
0 },
/* EC_TIME_TZ */ { 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0,
0 },
-/* EC_DATE */ { 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 3, 3, 0,
0 },
+/* EC_DATE */ { 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 2, 0,
0 },
/* EC_TSTAMP */ { 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1,
1, 2, 0, 0 },
/* EC_TSTAMP_TZ */ { 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 0,
0 },
/* EC_GEOM */ { 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1,
0 },
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
@@ -3776,41 +3776,39 @@ rel_case(sql_query *query, sql_rel **rel
{
mvc *sql = query->sql;
sql_subtype *tpe = NULL;
- list *conds = new_exp_list(sql->sa);
- list *results = new_exp_list(sql->sa);
- dnode *dn = when_search_list->h;
- sql_subtype *restype = NULL, rtype, bt;
- sql_exp *res = NULL, *else_exp = NULL;
- node *n, *m;
+ list *conds = new_exp_list(sql->sa), *results = new_exp_list(sql->sa);
+ sql_subtype *restype = NULL, *condtype = NULL, ctype, rtype, bt;
+ sql_exp *res = NULL, *opt_cond_exp = NULL;
exp_kind ek = {type_value, card_column, FALSE};
- sql_find_subtype(&bt, "boolean", 0, 0);
- for (dn = when_search_list->h; dn; dn = dn->next) {
+ if (opt_cond) {
+ if (!(opt_cond_exp = rel_value_exp(query, rel, opt_cond, f,
ek)))
+ return NULL;
+ condtype = exp_subtype(opt_cond_exp);
+ }
+
+ for (dnode *dn = when_search_list->h; dn; dn = dn->next) {
sql_exp *cond = NULL, *result = NULL;
-
dlist *when = dn->data.sym->data.lval;
- if (opt_cond) {
- sql_exp *l, *r;
-
- if (!(l = rel_value_exp(query, rel, opt_cond, f, ek)))
- return NULL;
- if (!(r = rel_value_exp(query, rel, when->h->data.sym,
f, ek)))
- return NULL;
- if (rel_convert_types(sql, rel ? *rel : NULL, rel ?
*rel : NULL, &l, &r, 1, type_equal) < 0)
- return NULL;
- cond = rel_binop_(sql, rel ? *rel : NULL, l, r, NULL,
"=", card_value);
- } else {
+ if (opt_cond)
+ cond = rel_value_exp(query, rel, when->h->data.sym, f,
ek);
+ else
cond = rel_logical_value_exp(query, rel,
when->h->data.sym, f, ek);
- }
if (!cond)
return NULL;
- result = rel_value_exp(query, rel, when->h->next->data.sym, f,
ek);
- if (!cond || !result)
+ list_prepend(conds, cond);
+ tpe = exp_subtype(cond);
+ if (tpe && condtype) {
+ supertype(&ctype, condtype, tpe);
+ condtype = &ctype;
+ } else if (tpe) {
+ condtype = tpe;
+ }
+
+ if (!(result = rel_value_exp(query, rel,
when->h->next->data.sym, f, ek)))
return NULL;
- list_prepend(conds, cond);
list_prepend(results, result);
-
tpe = exp_subtype(result);
if (tpe && restype) {
supertype(&rtype, restype, tpe);
@@ -3819,13 +3817,11 @@ rel_case(sql_query *query, sql_rel **rel
restype = tpe;
}
}
- if (opt_else || else_exp) {
- sql_exp *result = else_exp;
-
- if (!result && !(result = rel_value_exp(query, rel, opt_else,
f, ek)))
+ if (opt_else) {
+ if (!(res = rel_value_exp(query, rel, opt_else, f, ek)))
return NULL;
- tpe = exp_subtype(result);
+ tpe = exp_subtype(res);
if (tpe && restype) {
supertype(&rtype, restype, tpe);
restype = &rtype;
@@ -3838,11 +3834,7 @@ rel_case(sql_query *query, sql_rel **rel
if (restype->type->localtype == TYPE_void) /* NULL */
restype = sql_bind_localtype("str");
- if (!result || !(result = exp_check_type(sql, restype, rel ?
*rel : NULL, result, type_equal)))
- return NULL;
- res = result;
-
- if (!res)
+ if (!(res = exp_check_type(sql, restype, rel ? *rel : NULL,
res, type_equal)))
return NULL;
} else {
if (!restype)
@@ -3852,20 +3844,28 @@ rel_case(sql_query *query, sql_rel **rel
res = exp_null(sql->sa, restype);
}
- for (n = conds->h, m = results->h; n && m; n = n->next, m = m->next) {
+ if (!condtype)
+ return sql_error(sql, 02, SQLSTATE(42000) "Condition type
missing");
+ if (condtype->type->localtype == TYPE_void) /* NULL */
+ condtype = sql_bind_localtype("str");
+ if (opt_cond_exp && !(opt_cond_exp = exp_check_type(sql, condtype, rel
? *rel : NULL, opt_cond_exp, type_equal)))
+ return NULL;
+ sql_find_subtype(&bt, "boolean", 0, 0);
+ for (node *n = conds->h, *m = results->h; n && m; n = n->next, m =
m->next) {
sql_exp *cond = n->data;
sql_exp *result = m->data;
if (!(result = exp_check_type(sql, restype, rel ? *rel : NULL,
result, type_equal)))
return NULL;
+ if (!(cond = exp_check_type(sql, condtype, rel ? *rel : NULL,
cond, type_equal)))
+ return NULL;
+ if (opt_cond_exp && !(cond = rel_binop_(sql, rel ? *rel : NULL,
n == conds->h ? opt_cond_exp : exp_copy(sql, opt_cond_exp), cond, NULL, "=",
card_value)))
+ return NULL;
if (!(cond = exp_check_type(sql, &bt, rel ? *rel : NULL, cond,
type_equal)))
return NULL;
- if (!cond || !result || !res)
- return NULL;
- res = rel_nop_(sql, rel ? *rel : NULL, cond, result, res, NULL,
NULL, "ifthenelse", card_value);
- if (!res)
+ if (!(res = rel_nop_(sql, rel ? *rel : NULL, cond, result, res,
NULL, NULL, "ifthenelse", card_value)))
return NULL;
/* ugh overwrite res type */
((sql_subfunc*)res->f)->res->h->data =
sql_create_subtype(sql->sa, restype->type, restype->digits, restype->scale);
diff --git
a/sql/test/BugTracker-2012/Tests/timestamp_minus_date.Bug-2977.stable.err
b/sql/test/BugTracker-2012/Tests/timestamp_minus_date.Bug-2977.stable.err
--- a/sql/test/BugTracker-2012/Tests/timestamp_minus_date.Bug-2977.stable.err
+++ b/sql/test/BugTracker-2012/Tests/timestamp_minus_date.Bug-2977.stable.err
@@ -25,16 +25,7 @@ stderr of test 'timestamp_minus_date.Bug
# cmdline opt gdk_dbname = mTests_test_BugTracker-2012
# cmdline opt mal_listing = 0
-# 13:51:42 >
-# 13:51:42 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e" "--host=warsaw"
"--port=39949"
-# 13:51:42 >
+# 13:09:36 >
+# 13:09:36 > "Done."
+# 13:09:36 >
-MAPI = (monetdb) /var/tmp/mtest-27483/.s.monetdb.35395
-QUERY = select (cast('1970-01-01 0:00' as timestamp) - cast('1970-01-01' as
date));
-ERROR = !SELECT: no such binary operator 'sql_sub(timestamp,date)'
-CODE = 42000
-
-# 13:51:43 >
-# 13:51:43 > "Done."
-# 13:51:43 >
-
diff --git
a/sql/test/BugTracker-2012/Tests/timestamp_minus_date.Bug-2977.stable.out
b/sql/test/BugTracker-2012/Tests/timestamp_minus_date.Bug-2977.stable.out
--- a/sql/test/BugTracker-2012/Tests/timestamp_minus_date.Bug-2977.stable.out
+++ b/sql/test/BugTracker-2012/Tests/timestamp_minus_date.Bug-2977.stable.out
@@ -5,18 +5,12 @@ stdout of test 'timestamp_minus_date.Bug
# 13:51:42 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"gdk_dbfarm=/export/scratch2/sellam/projects/bug_day/install/var/MonetDB"
"--set" "mapi_open=true" "--set" "mapi_port=39949" "--set" "monet_prompt="
"--trace" "--forcemito" "--set" "mal_listing=2"
"--dbname=mTests_test_BugTracker-2012" "--set" "mal_listing=0"
# 13:51:42 >
-# MonetDB 5 server v11.13.6
-# This is an unreleased version
-# Serving database 'mTests_test_BugTracker-2012', using 8 threads
-# Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically
linked
-# Found 15.629 GiB available main-memory.
-# 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
-# Listening for connection requests on mapi:monetdb://warsaw.ins.cwi.nl:39949/
-# MonetDB/GIS module loaded
-# MonetDB/JAQL module loaded
-# MonetDB/SQL module loaded
+#select (cast('1970-01-01 0:00' as timestamp) - cast('1970-01-01' as date));
+% .%4 # table_name
+% %4 # name
+% sec_interval # type
+% 5 # length
+[ 0.000 ]
# SQL catalog created, loading sql scripts once
# loading sql script: 09_like.sql
diff --git a/sql/test/SQLancer/Tests/sqlancer03.sql
b/sql/test/SQLancer/Tests/sqlancer03.sql
--- a/sql/test/SQLancer/Tests/sqlancer03.sql
+++ b/sql/test/SQLancer/Tests/sqlancer03.sql
@@ -250,7 +250,7 @@ 0.916
select coalesce(1 = true, false);
select coalesce(1 = true, t0.c0 > 0) from t0;
-select count(all coalesce ((case coalesce (((r'Mk|8''Fx#S4ᬊ')||(time
'07:11:45')), ((r'')||(interval '-87' second))) when case cast(t0.c0 as double)
when sql_max(r'', null)
+select count(all coalesce ((case coalesce (1, 2) when case cast(t0.c0 as
double) when sql_max(r'', null)
then ((12)/(23)) end then cast("truncate"(r'1', 54) as boolean) else (coalesce
(true, true, r'1', true)) = false end) = true, (t0.c0) not in (t0.c0))) from t0;
ROLLBACK;
@@ -356,6 +356,33 @@ 0.369
SELECT ALL CASE t0.c0 WHEN VAR_POP(ALL t0.c0) THEN (t0.c0) BETWEEN SYMMETRIC
(t0.c0) AND (t0.c0) END FROM t0 GROUP BY t0.c0;
ROLLBACK;
+START TRANSACTION;
+CREATE TABLE "sys"."t0" ("c0" INT);
+COPY 8 RECORDS INTO "sys"."t0" FROM stdin USING DELIMITERS E'\t',E'\n','"';
+62183
+-2658
+-1258
+40690
+-198
+12260
+-1827
+-604
+
+CREATE TABLE "sys"."t1" ("c0" INT);
+COPY 7 RECORDS INTO "sys"."t1" FROM stdin USING DELIMITERS E'\t',E'\n','"';
+4440
+4440
+4440
+4440
+14228
+-4740
+NULL
+
+SELECT CASE TIMESTAMP '1970-01-02 23:16:17' WHEN DATE '1970-01-12' THEN 1 END;
+SELECT TIME '08:16:10' FROM t1 JOIN t0 ON (t1.c0) NOT BETWEEN SYMMETRIC
(t1.c0) AND (charindex(CAST(TIME '04:39:29' AS STRING), CASE COALESCE
(TIMESTAMP '1970-01-02 23:16:17', TIMESTAMP '1970-01-02 23:07:33')
+WHEN COALESCE (DATE '1970-01-12', DATE '1970-01-02', DATE '1970-01-02') THEN
COALESCE (r'/6望', r'hr?r3QDF*OD%6癰if}䃒5') END, t0.c0)) GROUP BY t1.c0;
+ROLLBACK;
+
START TRANSACTION; -- Bug 6924
CREATE TABLE "sys"."t0" ("a" INTEGER, "b" INTEGER NOT NULL, CONSTRAINT
"t0_a_b_unique" UNIQUE ("a","b"));
--This copy into must succeed
diff --git a/sql/test/SQLancer/Tests/sqlancer03.stable.out
b/sql/test/SQLancer/Tests/sqlancer03.stable.out
--- a/sql/test/SQLancer/Tests/sqlancer03.stable.out
+++ b/sql/test/SQLancer/Tests/sqlancer03.stable.out
@@ -43,8 +43,8 @@ stdout of test 'sqlancer03` in directory
#CREATE TABLE "sys"."t0" ("c0" BOOLEAN NOT NULL,"c1" BIGINT,CONSTRAINT
"t0_c0_pkey" PRIMARY KEY ("c0"),CONSTRAINT "t0_c0_unique" UNIQUE ("c0"));
#create view v0(c0, c1, c2) as (select all 2.020551048E9, 0.16688174,
0.3732000026221729 from t0 where t0.c0) with check option;
#SELECT sql_min(sql_max(NULL, ''), '') FROM v0 LEFT OUTER JOIN t0 ON true;
-% .%14 # table_name
-% %14 # name
+% .%12 # table_name
+% %12 # name
% char # type
% 0 # length
#SELECT sql_min(sql_max(NULL, ''), '');
@@ -55,8 +55,8 @@ stdout of test 'sqlancer03` in directory
[ NULL ]
#SELECT ALL length(upper(MIN(ALL CAST(((trim(CAST(r'' AS STRING(659)),
CAST(r'o3%+i]抔DCöf▟nßOpNbybಜ7' AS STRING)))||(sql_min(sql_max(NULL, r''),
splitpart(r'x', r',7+.', t0.c1)))) AS STRING(151))))), 0.4179268710155164
#FROM v0 LEFT OUTER JOIN t0 ON NOT (t0.c0) WHERE t0.c0 GROUP BY 0.3584962,
CAST(t0.c1 AS STRING(601)), t0.c1;
-% .%31, .%32 # table_name
-% %31, %32 # name
+% .%26, .%27 # table_name
+% %26, %27 # name
% int, decimal # type
% 1, 19 # length
#ROLLBACK;
@@ -388,8 +388,8 @@ stdout of test 'sqlancer03` in directory
#"Xh{%LTF" false
[ 2 ]
#SELECT 1 FROM t0 CROSS JOIN t1 WHERE (t1.c1 AND (t0.c0 > t0.c0)) IS NULL;
-% .%10 # table_name
-% %10 # name
+% .%4 # table_name
+% %4 # name
% tinyint # type
% 1 # length
#SELECT cast(SUM(agg0) as decimal(10,2)) FROM (SELECT ALL SUM(ALL 0.97) as
agg0 FROM t0 CROSS JOIN t1 WHERE ((((t1.c1)AND(t1.c1)))AND(((t0.c0)>(t0.c0))))
@@ -623,6 +623,55 @@ stdout of test 'sqlancer03` in directory
#-2067368391 0.5763887172257189 -452165183.000
[ 19 ]
#ROLLBACK;
+#START TRANSACTION;
+#CREATE TABLE "sys"."t0" ("c0" DECIMAL(18,3));
+#COMMENT ON COLUMN "sys"."t0"."c0" IS 'jc~\006}ℵH{\015^aBPxpf+sP';
+#COPY 8 RECORDS INTO "sys"."t0" FROM stdin USING DELIMITERS E'\t',E'\n','"';
+#0.626
+#0.634
+#0.133
+#0.244
+#19.000
+#0.455
+#0.715
+#0.369
+[ 8 ]
+#ROLLBACK;
+#START TRANSACTION;
+#CREATE TABLE "sys"."t0" ("c0" INT);
+#COPY 8 RECORDS INTO "sys"."t0" FROM stdin USING DELIMITERS E'\t',E'\n','"';
+#62183
+#-2658
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
