Changeset: 523378ad0672 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=523378ad0672
Modified Files:
clients/mapiclient/dump.c
sql/common/sql_types.c
sql/server/rel_select.c
sql/server/rel_unnest.c
sql/server/sql_decimal.c
sql/server/sql_decimal.h
sql/server/sql_parser.y
sql/test/BugDay_2005-10-06_2.8/Tests/mixed_type.SF-917372.stable.err
sql/test/BugDay_2005-10-06_2.8/Tests/mixed_type.SF-917372.stable.out
sql/test/BugDay_2005-11-09_2.9.3/Tests/date_functions.SF-967145.stable.out
sql/test/BugDay_2005-12-19_2.9.3/Tests/cast_interval.SF.1280682.stable.out
sql/test/BugTracker-2011/Tests/CASE_nested_functions.Bug-2852.stable.out
sql/test/BugTracker-2012/Tests/incorrect_cast_from_double_to_int.Bug-2579.stable.err
sql/test/BugTracker-2012/Tests/incorrect_cast_from_double_to_int.Bug-2579.stable.out
sql/test/BugTracker-2012/Tests/interval_timestamp.Bug-3002.stable.err
sql/test/BugTracker-2015/Tests/div_zero.Bug-3742.sql
sql/test/BugTracker-2015/Tests/div_zero.Bug-3742.stable.out
sql/test/BugTracker-2015/Tests/sum_interval.Bug-3785.stable.out
sql/test/BugTracker-2016/Tests/date_calculation_and_comparison_wrong_results.Bug-3940.stable.out
sql/test/BugTracker-2016/Tests/isaUUID_function.Bug-3997.stable.err
sql/test/BugTracker-2017/Tests/one-plus-nil.Bug-6243.stable.out
sql/test/Skyserver/Skyserver_functions.sql
sql/test/Skyserver/Skyserver_functions_v6.sql
sql/test/VOC/Tests/median.Bug-3096.stable.out
sql/test/bugs/Tests/default_with_cast.SF-1334486.stable.out
sql/test/bugs/Tests/interval_convert_bugs-sf-1274077-1274085.stable.out
sql/test/emptydb/Tests/check.stable.out
sql/test/emptydb/Tests/check.stable.out.32bit
sql/test/emptydb/Tests/check.stable.out.int128
sql/test/orderidx/Tests/imprints_all_types.stable.out
sql/test/orderidx/Tests/oidx_all_types.stable.out
sql/test/pg_regress/Tests/date.stable.out
sql/test/snodgrass/Tests/interval_select.stable.err
sql/test/snodgrass/Tests/interval_select.stable.out
sql/test/sys-schema/Tests/systemfunctions.stable.out
sql/test/sys-schema/Tests/systemfunctions.stable.out.int128
Branch: typing
Log Message:
New branch needed for cleanup of the sql typing system.
The idea is to move the physical storage maping into a second phase, ie
for now in a rewriter in the rel_unnest code.
Created a new explicit sql type (day_interval). This as before stored using
a lng with values in milli seconds.
Converting between day and sec interval is there a no-op.
Cleaned up internal function definitions (abit), ie
only support interval * / numerical (int's,decimals,floats)
only support + - with the same interval type.
Handle dyadic operators (+,-,*,/) special, they need to auto convert more.
With these function we auto convert a string on one side of the function too the
largest (same class) numeric.
The dyadic functions on both sides strings are gone.
ie also str+str is no longer supported (use || instead).
in the rel_unnest added the exp_physical_types function, which for some
types should correct (before and after) an operation the scales and possibly
the internal storage types needed. This is mostly scale handling. For
now only the INTERVAL scaling is handled here.
diffs (truncated from 4477 to 300 lines):
diff --git a/clients/mapiclient/dump.c b/clients/mapiclient/dump.c
--- a/clients/mapiclient/dump.c
+++ b/clients/mapiclient/dump.c
@@ -775,7 +775,7 @@ dump_type(Mapi mid, stream *toConsole, c
space = mnstr_printf(toConsole, "INTERVAL MONTH");
else
fprintf(stderr, "Internal error: unrecognized month
interval %s\n", c_type_digits);
- } else if (strcmp(c_type, "sec_interval") == 0) {
+ } else if (strlen(c_type) > 4 && strcmp(c_type+3, "_interval") == 0) {
if (strcmp(c_type_digits, "4") == 0)
space = mnstr_printf(toConsole, "INTERVAL DAY");
else if (strcmp(c_type_digits, "5") == 0)
@@ -1776,7 +1776,7 @@ dump_table_data(Mapi mid, const char *sc
mnstr_printf(toConsole, "NULL");
else if (useInserts) {
const char *tp = mapi_get_type(hdl, i);
- if (strcmp(tp, "sec_interval") == 0) {
+ if (strlen(tp) > 4 && strcmp(tp+3, "_interval")
== 0) {
const char *p = strchr(s, '.');
if (p == NULL)
p = s + strlen(s);
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
@@ -349,6 +349,10 @@ subtype_cmp(sql_subtype *t1, sql_subtype
if (!t1->type || !t2->type)
return -1;
+ if (t1->type->eclass == t2->type->eclass && t1->type->eclass == EC_SEC)
+ return 0;
+ if (t1->type->eclass == t2->type->eclass && t1->type->eclass ==
EC_MONTH)
+ return 0;
if ( !(t1->type->eclass == t2->type->eclass &&
(EC_INTERVAL(t1->type->eclass) || t1->type->eclass == EC_NUM)) &&
(t1->digits != t2->digits ||
@@ -1144,7 +1148,7 @@ sqltypeinit( sql_allocator *sa)
#ifdef HAVE_HGE
sql_type *HGE = NULL;
#endif
- sql_type *SECINT, *MONINT, *DTE;
+ sql_type *SECINT, *DAYINT, *MONINT, *DTE;
sql_type *TME, *TMETZ, *TMESTAMP, *TMESTAMPTZ;
sql_type *BLOB;
sql_type *ANY, *TABLE, *PTR;
@@ -1213,7 +1217,8 @@ sqltypeinit( sql_allocator *sa)
DBL = *t++ = sql_create_type(sa, "DOUBLE", 53, SCALE_NOFIX, 2, EC_FLT,
"dbl");
dates = t;
- MONINT = *t++ = sql_create_type(sa, "MONTH_INTERVAL", 32, 0, 2,
EC_MONTH, "int");
+ MONINT = *t++ = sql_create_type(sa, "MONTH_INTERVAL", 3, 0, 10,
EC_MONTH, "int"); /* 1 .. 13 enumerates the 13 different interval types */
+ DAYINT = *t++ = sql_create_type(sa, "DAY_INTERVAL", 4, 0, 10, EC_SEC,
"lng");
SECINT = *t++ = sql_create_type(sa, "SEC_INTERVAL", 13, SCALE_FIX, 10,
EC_SEC, "lng");
TME = *t++ = sql_create_type(sa, "TIME", 7, 0, 0, EC_TIME, "daytime");
TMETZ = *t++ = sql_create_type(sa, "TIMETZ", 7, SCALE_FIX, 0,
EC_TIME_TZ, "daytime");
@@ -1383,6 +1388,7 @@ sqltypeinit( sql_allocator *sa)
sql_create_aggr(sa, "prod", "aggr", "prod", *t, 1, *t);
}
sql_create_aggr(sa, "sum", "aggr", "sum", MONINT, 1, MONINT);
+ sql_create_aggr(sa, "sum", "aggr", "sum", DAYINT, 1, DAYINT);
sql_create_aggr(sa, "sum", "aggr", "sum", SECINT, 1, SECINT);
/* do DBL first so that it is chosen as cast destination for
* unknown types */
@@ -1580,6 +1586,7 @@ sqltypeinit( sql_allocator *sa)
sql_create_analytic(sa, "prod", "sql", "prod", SCALE_NONE, *t,
1, *t);
}
sql_create_analytic(sa, "sum", "sql", "sum", SCALE_NONE, MONINT, 1,
MONINT);
+ sql_create_analytic(sa, "sum", "sql", "sum", SCALE_NONE, DAYINT, 1,
DAYINT);
sql_create_analytic(sa, "sum", "sql", "sum", SCALE_NONE, SECINT, 1,
SECINT);
//analytical average for numerical types
@@ -1622,6 +1629,11 @@ sqltypeinit( sql_allocator *sa)
sql_create_func(sa, "xor", "calc", "xor", FALSE, SCALE_FIX, 0, BIT, 2,
BIT, BIT);
sql_create_func(sa, "not", "calc", "not", FALSE, SCALE_FIX, 0, BIT, 1,
BIT);
+ for (t = dates; *t != TME; t++) {
+ sql_create_func(sa, "sql_sub", "calc", "-", FALSE, SCALE_FIX,
0, *t, 2, *t, *t);
+ sql_create_func(sa, "sql_add", "calc", "+", FALSE, SCALE_FIX,
0, *t, 2, *t, *t);
+ }
+
/* allow smaller types for arguments of mul/div */
for (t = numerical, t++; t != decimals; t++) {
sql_type **u;
@@ -1645,7 +1657,7 @@ sqltypeinit( sql_allocator *sa)
}
/* all numericals */
- for (t = numerical; *t != TME; t++) {
+ for (t = numerical; t < dates; t++) {
sql_subtype *lt;
lt = sql_bind_localtype((*t)->base.name);
@@ -1668,17 +1680,14 @@ sqltypeinit( sql_allocator *sa)
/* scale fixing for all numbers */
sql_create_func(sa, "scale_up", "calc", "*", FALSE, SCALE_NONE,
0, *t, 2, *t, lt->type);
sql_create_func(sa, "scale_down", "sql", "dec_round", FALSE,
SCALE_NONE, 0, *t, 2, *t, lt->type);
- /* numeric function on INTERVALS */
- if (*t != MONINT && *t != SECINT){
- sql_create_func(sa, "sql_sub", "calc", "-", FALSE,
SCALE_FIX, 0, MONINT, 2, MONINT, *t);
- sql_create_func(sa, "sql_add", "calc", "+", FALSE,
SCALE_FIX, 0, MONINT, 2, MONINT, *t);
- sql_create_func(sa, "sql_mul", "calc", "*", FALSE,
SCALE_MUL, 0, MONINT, 2, MONINT, *t);
- sql_create_func(sa, "sql_div", "calc", "/", FALSE,
SCALE_DIV, 0, MONINT, 2, MONINT, *t);
- sql_create_func(sa, "sql_sub", "calc", "-", FALSE,
SCALE_FIX, 0, SECINT, 2, SECINT, *t);
- sql_create_func(sa, "sql_add", "calc", "+", FALSE,
SCALE_FIX, 0, SECINT, 2, SECINT, *t);
- sql_create_func(sa, "sql_mul", "calc", "*", FALSE,
SCALE_MUL, 0, SECINT, 2, SECINT, *t);
- sql_create_func(sa, "sql_div", "calc", "/", FALSE,
SCALE_DIV, 0, SECINT, 2, SECINT, *t);
- }
+
+ /* only multiply / divide intervals using numerics */
+ sql_create_func(sa, "sql_mul", "calc", "*", FALSE, SCALE_MUL,
0, MONINT, 2, MONINT, *t);
+ sql_create_func(sa, "sql_div", "calc", "/", FALSE, SCALE_DIV,
0, MONINT, 2, MONINT, *t);
+ sql_create_func(sa, "sql_mul", "calc", "*", FALSE, SCALE_MUL,
0, DAYINT, 2, DAYINT, *t);
+ sql_create_func(sa, "sql_div", "calc", "/", FALSE, SCALE_DIV,
0, DAYINT, 2, DAYINT, *t);
+ sql_create_func(sa, "sql_mul", "calc", "*", FALSE, SCALE_MUL,
0, SECINT, 2, SECINT, *t);
+ sql_create_func(sa, "sql_div", "calc", "/", FALSE, SCALE_DIV,
0, SECINT, 2, SECINT, *t);
}
for (t = decimals, t++; t != floats; t++) {
sql_type **u;
@@ -1741,13 +1750,13 @@ sqltypeinit( sql_allocator *sa)
sql_create_func(sa, "localtime", "sql", "current_time", FALSE,
SCALE_NONE, 0, TME, 0);
sql_create_func(sa, "localtimestamp", "sql", "current_timestamp",
FALSE, SCALE_NONE, 0, TMESTAMP, 0);
- sql_create_func(sa, "sql_sub", "mtime", "diff", FALSE, SCALE_FIX, 0,
SECINT, 2, DTE, DTE);
+ sql_create_func(sa, "sql_sub", "mtime", "diff", FALSE, SCALE_FIX, 0,
DAYINT, 2, DTE, DTE);
sql_create_func(sa, "sql_sub", "mtime", "diff", FALSE, SCALE_NONE, 0,
SECINT, 2, TMETZ, TMETZ);
sql_create_func(sa, "sql_sub", "mtime", "diff", FALSE, SCALE_FIX, 0,
SECINT, 2, TME, TME);
sql_create_func(sa, "sql_sub", "mtime", "diff", FALSE, SCALE_NONE, 0,
SECINT, 2, TMESTAMPTZ, TMESTAMPTZ);
sql_create_func(sa, "sql_sub", "mtime", "diff", FALSE, SCALE_FIX, 0,
SECINT, 2, TMESTAMP, TMESTAMP);
- sql_create_func(sa, "sql_sub", "mtime", "date_sub_msec_interval",
FALSE, SCALE_NONE, 0, DTE, 2, DTE, SECINT);
+ sql_create_func(sa, "sql_sub", "mtime", "date_sub_msec_interval",
FALSE, SCALE_NONE, 0, DTE, 2, DTE, DAYINT);
sql_create_func(sa, "sql_sub", "mtime", "date_sub_month_interval",
FALSE, SCALE_NONE, 0, DTE, 2, DTE, MONINT);
sql_create_func(sa, "sql_sub", "mtime", "time_sub_msec_interval",
FALSE, SCALE_NONE, 0, TME, 2, TME, SECINT);
sql_create_func(sa, "sql_sub", "mtime", "time_sub_msec_interval",
FALSE, SCALE_NONE, 0, TMETZ, 2, TMETZ, SECINT);
@@ -1756,7 +1765,7 @@ sqltypeinit( sql_allocator *sa)
sql_create_func(sa, "sql_sub", "mtime", "timestamp_sub_msec_interval",
FALSE, SCALE_NONE, 0, TMESTAMPTZ, 2, TMESTAMPTZ, SECINT);
sql_create_func(sa, "sql_sub", "mtime", "timestamp_sub_month_interval",
FALSE, SCALE_NONE, 0, TMESTAMPTZ, 2, TMESTAMPTZ, MONINT);
- sql_create_func(sa, "sql_add", "mtime", "date_add_msec_interval",
FALSE, SCALE_NONE, 0, DTE, 2, DTE, SECINT);
+ sql_create_func(sa, "sql_add", "mtime", "date_add_msec_interval",
FALSE, SCALE_NONE, 0, DTE, 2, DTE, DAYINT);
sql_create_func(sa, "sql_add", "mtime", "addmonths", FALSE, SCALE_NONE,
0, DTE, 2, DTE, MONINT);
sql_create_func(sa, "sql_add", "mtime", "timestamp_add_msec_interval",
FALSE, SCALE_NONE, 0, TMESTAMP, 2, TMESTAMP, SECINT);
sql_create_func(sa, "sql_add", "mtime", "timestamp_add_month_interval",
FALSE, SCALE_NONE, 0, TMESTAMP, 2, TMESTAMP, MONINT);
@@ -1808,6 +1817,7 @@ sqltypeinit( sql_allocator *sa)
sql_create_func(sa, "year", "mtime", "year", FALSE, SCALE_NONE, 0, INT,
1, MONINT);
sql_create_func(sa, "month", "mtime", "month", FALSE, SCALE_NONE, 0,
INT, 1, MONINT);
+ sql_create_func(sa, "day", "mtime", "day", FALSE, SCALE_NONE, 0, LNG,
1, DAYINT);
sql_create_func(sa, "day", "mtime", "day", FALSE, SCALE_NONE, 0, LNG,
1, SECINT);
sql_create_func(sa, "hour", "mtime", "hours", FALSE, SCALE_NONE, 0,
INT, 1, SECINT);
sql_create_func(sa, "minute", "mtime", "minutes", FALSE, SCALE_NONE, 0,
INT, 1, SECINT);
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
@@ -13,6 +13,7 @@
the dependent code into sql_mvc */
#include "sql_privileges.h"
#include "sql_env.h"
+#include "sql_decimal.h"
#include "rel_rel.h"
#include "rel_exp.h"
#include "rel_xml.h"
@@ -1295,27 +1296,6 @@ rel_column_ref(sql_query *query, sql_rel
return exp;
}
-#ifdef HAVE_HGE
-static hge
-#else
-static lng
-#endif
-scale2value(int scale)
-{
-#ifdef HAVE_HGE
- hge val = 1;
-#else
- lng val = 1;
-#endif
-
- if (scale < 0)
- scale = -scale;
- for (; scale; scale--) {
- val = val * 10;
- }
- return val;
-}
-
static sql_exp *
exp_fix_scale(mvc *sql, sql_subtype *ct, sql_exp *e, int both, int always)
{
@@ -1436,6 +1416,25 @@ rel_numeric_supertype(mvc *sql, sql_exp
return e;
}
+static sql_subtype*
+largest_numeric_type(sql_subtype *res, int ec)
+{
+ if (ec == EC_NUM) {
+#ifdef HAVE_HGE
+ *res = *sql_bind_localtype(have_hge ? "hge" : "lng");
+#else
+ *res = *sql_bind_localtype("lng");
+#endif
+ return res;
+ }
+ if (ec == EC_DEC && sql_find_subtype(res, "decimal", 38, 0)) {
+ /* we don't know the precision nor scale ie we use a double */
+ *res = *sql_bind_localtype("dbl");
+ return res;
+ }
+ return NULL;
+}
+
sql_exp *
rel_check_type(mvc *sql, sql_subtype *t, sql_rel *rel, sql_exp *exp,
check_type tpe)
{
@@ -2935,6 +2934,10 @@ rel_unop(sql_query *query, sql_rel **rel
#define is_addition(fname) (strcmp(fname, "sql_add") == 0)
#define is_subtraction(fname) (strcmp(fname, "sql_sub") == 0)
+#define is_multiplication(fname) (strcmp(fname, "sql_mul") == 0)
+#define is_division(fname) (strcmp(fname, "sql_div") == 0)
+
+#define is_numeric_dyadic_func(fname) (is_addition(fname) ||
is_subtraction(fname) || is_multiplication(fname) || is_division(fname))
sql_exp *
rel_binop_(mvc *sql, sql_rel *rel, sql_exp *l, sql_exp *r, sql_schema *s, char
*fname, int card)
@@ -2995,6 +2998,31 @@ rel_binop_(mvc *sql, sql_rel *rel, sql_e
r = res;
}
}
+ if (!f) {
+ if (is_numeric_dyadic_func(fname)) {
+ if (EC_NUMBER(t1->type->eclass) &&
!EC_NUMBER(t2->type->eclass)) {
+ sql_subtype tp;
+ if (!largest_numeric_type(&tp,
t1->type->eclass))
+ tp = *t1; /* for float and interval
fall back too the same as left */
+ r = rel_check_type(sql, &tp, rel, r,
type_equal);
+ if (!r)
+ return NULL;
+ t2 = exp_subtype(r);
+ } else if (!EC_NUMBER(t1->type->eclass) &&
!EC_TEMP(t1->type->eclass) && EC_NUMBER(t2->type->eclass)) {
+ sql_subtype tp;
+ if (!largest_numeric_type(&tp,
t2->type->eclass))
+ tp = *t2; /* for float and interval
fall back too the same as right */
+ l = rel_check_type(sql, &tp, rel, l,
type_equal);
+ if (!l)
+ return NULL;
+ t1 = exp_subtype(l);
+ } else if (!EC_NUMBER(t1->type->eclass) &&
!EC_TEMP(t1->type->eclass) && !EC_NUMBER(t2->type->eclass)) {
+ return sql_error(sql, 02, SQLSTATE(42000)
"SELECT: no such binary operator '%s(%s,%s)'", fname,
+ exp_subtype(l)->type->sqlname,
+ exp_subtype(r)->type->sqlname);
+ }
+ }
+ }
if (f && check_card(card,f)) {
if (f->func->fix_scale == SCALE_FIX) {
l = exp_fix_scale(sql, t2, l, 0, 0);
diff --git a/sql/server/rel_unnest.c b/sql/server/rel_unnest.c
--- a/sql/server/rel_unnest.c
+++ b/sql/server/rel_unnest.c
@@ -9,6 +9,7 @@
/*#define DEBUG*/
#include "monetdb_config.h"
+#include "sql_decimal.h"
#include "rel_unnest.h"
#include "rel_optimizer.h"
#include "rel_prop.h"
@@ -1528,6 +1529,69 @@ exp_reset_card(mvc *sql, sql_rel *rel, s
return e;
}
+/*
+ * For decimals and intervals we need to adjust the scale for some operations.
+ *
+ * TODO move the decimal scale handling to this function.
+ */
+#define is_division(sf) (strcmp(sf->func->base.name, "sql_div") == 0)
+static sql_exp *
+exp_physical_types(mvc *sql, sql_rel *rel, sql_exp *e, int depth)
+{
+ (void)sql;
+ (void)rel;
+ (void)depth;
+ if (!e || (e->type != e_func && e->type != e_convert) || !e->l)
+ return e;
+
+ if (e->type == e_convert) {
+ sql_subtype *ft = exp_fromtype(e);
+ sql_subtype *tt = exp_totype(e);
+
+ /* complex conversion matrix */
+ if (ft->type->eclass == EC_SEC && tt->type->eclass == EC_SEC &&
ft->type->digits > tt->type->digits) {
+ /* no conversion needed, just time adjustment */
+ e = e->l;
+ e->tpe = *tt; // ugh
+ }
+ } else {
+ list *args = e->l;
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
