Changeset: a5f61329133c for MonetDB URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=a5f61329133c Modified Files: sql/common/sql_types.c sql/server/rel_select.c sql/test/SQLancer/Tests/sqlancer03.sql sql/test/SQLancer/Tests/sqlancer03.stable.out sql/test/pg_regress/Tests/date.stable.err sql/test/pg_regress/Tests/date.stable.out Branch: oscar Log Message:
Merged with Jun2020 diffs (truncated from 714 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 @@ -107,7 +107,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 @@ -3748,40 +3748,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); @@ -3790,13 +3789,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; @@ -3809,11 +3806,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) @@ -3823,20 +3816,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 +#-1258 _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list