Changeset: 44a68315023f for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=44a68315023f
Modified Files:
sql/include/sql_catalog.h
sql/server/rel_select.c
sql/server/sql_semantic.c
sql/server/sql_semantic.h
sql/test/BugTracker-2012/Tests/case_evaluated_too_early.Bug-3186.stable.out
sql/test/BugTracker-2017/Tests/case.Bug-6386.stable.out
sql/test/BugTracker/Tests/groupby_with_alias.SF-1520575.stable.out.int128
sql/test/Tests/coalesce.stable.out
Branch: default
Log Message:
implement (part) of the result datatype specification (pages 505-507) of the
2011 standard.
Currently only used for case/coalesce/nullif cases
diffs (250 lines):
diff --git a/sql/include/sql_catalog.h b/sql/include/sql_catalog.h
--- a/sql/include/sql_catalog.h
+++ b/sql/include/sql_catalog.h
@@ -303,6 +303,8 @@ typedef enum sql_class {
#define EC_VARCHAR(e) (e==EC_CHAR||e==EC_STRING)
#define EC_INTERVAL(e) (e==EC_MONTH||e==EC_SEC)
#define EC_NUMBER(e)
(e==EC_POS||e==EC_NUM||EC_INTERVAL(e)||e==EC_DEC||e==EC_FLT)
+#define EC_EXACTNUM(e) (e==EC_NUM||e==EC_DEC)
+#define EC_APPNUM(e) (e==EC_FLT)
#define EC_COMPUTE(e) (e==EC_NUM||e==EC_FLT)
#define EC_BOOLEAN(e) (e==EC_BIT||e==EC_NUM||e==EC_FLT)
#define EC_TEMP_TZ(e) (e==EC_TIME_TZ||e==EC_TIMESTAMP_TZ)
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
@@ -3703,7 +3703,7 @@ rel_case(sql_query *query, sql_rel **rel
append(conds, cond);
tpe = exp_subtype(cond);
if (tpe && condtype) {
- supertype(&ctype, condtype, tpe);
+ result_datatype(&ctype, condtype, tpe);
condtype = &ctype;
} else if (tpe) {
condtype = tpe;
@@ -3714,7 +3714,7 @@ rel_case(sql_query *query, sql_rel **rel
append(results, result);
tpe = exp_subtype(result);
if (tpe && restype) {
- supertype(&rtype, restype, tpe);
+ result_datatype(&rtype, restype, tpe);
restype = &rtype;
} else if (tpe) {
restype = tpe;
@@ -3726,7 +3726,7 @@ rel_case(sql_query *query, sql_rel **rel
tpe = exp_subtype(res);
if (tpe && restype) {
- supertype(&rtype, restype, tpe);
+ result_datatype(&rtype, restype, tpe);
restype = &rtype;
} else if (tpe) {
restype = tpe;
@@ -3780,27 +3780,6 @@ rel_case(sql_query *query, sql_rel **rel
return res;
}
-#if 0
-static sql_exp *
-do_complex_case(sql_query *query, node *n, str func, sql_subtype *restype)
-{
- sql_exp *l = n->data;
-
- if (!(l = exp_check_type(query->sql, restype, NULL, l, type_equal)))
- return NULL;
-
- n = n->next;
- sql_exp *r = n->data;
- if (n->next)
- r = do_complex_case(query, n, func, restype);
- else
- r = exp_check_type(query->sql, restype, NULL, r, type_equal);
- if (!r)
- return NULL;
- return rel_binop_(query->sql, NULL, l, r, NULL, func, card_value);
-}
-#endif
-
static sql_exp *
rel_complex_case(sql_query *query, sql_rel **rel, dlist *case_args, int f, str
func)
{
@@ -3817,7 +3796,7 @@ rel_complex_case(sql_query *query, sql_r
/* all arguments should have the same type */
sql_subtype *tpe = exp_subtype(a);
if (tpe && restype) {
- supertype(&rtype, restype, tpe);
+ result_datatype(&rtype, restype, tpe);
restype = &rtype;
} else if (tpe) {
restype = tpe;
@@ -3835,7 +3814,6 @@ rel_complex_case(sql_query *query, sql_r
return NULL;
append(nargs, result);
}
- //return do_complex_case(query, args->h, func, restype);
list *types = append(append(sa_list(query->sql->sa), restype), restype);
sql_subfunc *fnc = find_func(query->sql, NULL, func,
list_length(types), F_FUNC, NULL);
return exp_op(query->sql->sa, nargs, fnc);
diff --git a/sql/server/sql_semantic.c b/sql/server/sql_semantic.c
--- a/sql/server/sql_semantic.c
+++ b/sql/server/sql_semantic.c
@@ -170,6 +170,79 @@ set_type_param(mvc *sql, sql_subtype *ty
return 0;
}
+/*
+ * Find the result_datatype for certain combinations of values
+ * (like case expressions or coumns in a result of a query expression).
+ * See standaard pages 505-507 Result of data type combinations */
+sql_subtype *
+result_datatype(sql_subtype *super, sql_subtype *l, sql_subtype *r)
+{
+ int lclass = l->type->eclass, rclass = r->type->eclass;
+ int lc=0, rc=0;
+
+ /* case a strings */
+ if (EC_VARCHAR(lclass) || EC_VARCHAR(rclass)) {
+ char *tpe = "varchar";
+ int digits = 0;
+ if (!EC_VARCHAR(lclass)) {
+ tpe = r->type->sqlname;
+ digits = (!l->digits)?0:r->digits;
+ } else if (!EC_VARCHAR(rclass)) {
+ tpe = l->type->sqlname;
+ digits = (!r->digits)?0:l->digits;
+ } else { /* both */
+ tpe = (l->type->base.id >
r->type->base.id)?l->type->sqlname:r->type->sqlname;
+ digits =
(!l->digits||!r->digits)?0:sql_max(l->digits, r->digits);
+ }
+ sql_find_subtype(super, tpe, digits, 0);
+ /* case b blob */
+ } else if ((lc=strcmp(l->type->sqlname, "blob")) == 0 ||
(rc=strcmp(r->type->sqlname, "blob")) == 0) {
+ if (!lc)
+ *super = *l;
+ else
+ *super = *r;
+ /* case c all exact numeric */
+ } else if (EC_EXACTNUM(lclass) && EC_EXACTNUM(rclass)) {
+ char *tpe = (l->type->base.id >
r->type->base.id)?l->type->sqlname:r->type->sqlname;
+ unsigned int digits = sql_max(l->digits, r->digits);
+ int scale = sql_max(l->scale, r->scale);
+ if (l->type->radix == 10 || r->type->radix == 10) {
+ digits = 0;
+ /* change to radix 10 */
+ if (l->type->radix == 2)
+ digits = bits2digits(l->type->digits);
+ if (l->type->radix == 2)
+ digits = bits2digits(r->type->digits);
+ if (l->type->radix != 10) {
+ digits = sql_max(digits, r->digits);
+ scale = r->scale;
+ } else if (r->type->radix != 10) {
+ digits = sql_max(digits, l->digits);
+ scale = l->scale;
+ }
+ }
+ sql_find_subtype(super, tpe, digits, scale);
+ /* case d approximate numeric */
+ } else if (EC_APPNUM(lclass) || EC_APPNUM(rclass)) {
+ if (!EC_APPNUM(lclass)) {
+ *super = *r;
+ } else if (!EC_APPNUM(rclass)) {
+ *super = *l;
+ } else { /* both */
+ char *tpe = (l->type->base.id >
r->type->base.id)?l->type->sqlname:r->type->sqlname;
+ int digits = sql_max(l->digits, r->digits); /*
bits precision */
+ sql_find_subtype(super, tpe, digits, 0);
+ }
+ /* now its getting serious, ie e any 'case e' datetime data type */
+ /* 'case f' interval types */
+ /* 'case g' boolean */
+ /* 'case h-l' compounds like row (tuple), etc */
+ } else {
+ return supertype(super, l, r);
+ }
+ return super;
+}
+
sql_subtype *
supertype(sql_subtype *super, sql_subtype *r, sql_subtype *i)
{
diff --git a/sql/server/sql_semantic.h b/sql/server/sql_semantic.h
--- a/sql/server/sql_semantic.h
+++ b/sql/server/sql_semantic.h
@@ -28,6 +28,7 @@ extern char *qname_schema_object(dlist *
extern char *qname_catalog(dlist *qname);
#define qname_module(qname) qname_schema(qname)
+extern sql_subtype *result_datatype(sql_subtype *super, sql_subtype *l,
sql_subtype *r);
extern sql_subtype *supertype(sql_subtype *super, sql_subtype *r, sql_subtype
*i);
typedef enum {
diff --git
a/sql/test/BugTracker-2012/Tests/case_evaluated_too_early.Bug-3186.stable.out
b/sql/test/BugTracker-2012/Tests/case_evaluated_too_early.Bug-3186.stable.out
---
a/sql/test/BugTracker-2012/Tests/case_evaluated_too_early.Bug-3186.stable.out
+++
b/sql/test/BugTracker-2012/Tests/case_evaluated_too_early.Bug-3186.stable.out
@@ -33,7 +33,7 @@ stdout of test 'case_evaluated_too_early
% .%3 # table_name
% %3 # name
% decimal # type
-% 4 # length
+% 3 # length
[ -10 ]
# 17:10:20 >
diff --git a/sql/test/BugTracker-2017/Tests/case.Bug-6386.stable.out
b/sql/test/BugTracker-2017/Tests/case.Bug-6386.stable.out
--- a/sql/test/BugTracker-2017/Tests/case.Bug-6386.stable.out
+++ b/sql/test/BugTracker-2017/Tests/case.Bug-6386.stable.out
@@ -27,7 +27,7 @@ stdout of test 'case.Bug-6386` in direct
% . # table_name
% t1 # name
% decimal # type
-% 18 # length
+% 40 # length
[ 1.0000000000000 ]
[ 60.3281000000000 ]
[ 0.0000000000000 ]
diff --git
a/sql/test/BugTracker/Tests/groupby_with_alias.SF-1520575.stable.out.int128
b/sql/test/BugTracker/Tests/groupby_with_alias.SF-1520575.stable.out.int128
--- a/sql/test/BugTracker/Tests/groupby_with_alias.SF-1520575.stable.out.int128
+++ b/sql/test/BugTracker/Tests/groupby_with_alias.SF-1520575.stable.out.int128
@@ -35,6 +35,7 @@ stdout of test 'groupby_with_alias.SF-15
% bug_alias, %1 # name
% varchar, hugeint # type
% 0, 1 # length
+#ROLLBACK;
# 11:54:20 >
# 11:54:20 > Done.
diff --git a/sql/test/Tests/coalesce.stable.out
b/sql/test/Tests/coalesce.stable.out
--- a/sql/test/Tests/coalesce.stable.out
+++ b/sql/test/Tests/coalesce.stable.out
@@ -80,8 +80,8 @@ stdout of test 'coalesce` in directory '
#select id, coalesce(name, age, 'unknown') from coalescetest;
% sys.coalescetest, .%1 # table_name
% id, %1 # name
-% int, char # type
-% 1, 10 # length
+% int, varchar # type
+% 1, 7 # length
[ 1, "a" ]
[ 2, "26" ]
[ 3, "c" ]
@@ -103,6 +103,19 @@ stdout of test 'coalesce` in directory '
% int, varchar, int # type
% 1, 0, 2 # length
[ 2, NULL, 26 ]
+#insert into coalescetest(id,name) values (3,'hello this is a test');
+[ 1 ]
+#select id, coalesce(name, age, 'unknown') from coalescetest; --the output
type should be char(20)
+% sys.coalescetest, .%1 # table_name
+% id, %1 # name
+% int, varchar # type
+% 1, 20 # length
+[ 1, "a" ]
+[ 2, "26" ]
+[ 3, "c" ]
+[ 4, "unknown" ]
+[ 3, "hello this is a test" ]
+#drop table coalescetest;
# 15:56:24 >
# 15:56:24 > Done.
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
