Changeset: 8281679aab28 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/8281679aab28
Modified Files:
sql/common/sql_types.c
sql/common/sql_types.h
sql/scripts/52_describe.sql
sql/server/rel_exp.c
sql/server/rel_optimize_sel.c
sql/server/rel_select.c
sql/server/rel_statistics_functions.c
sql/server/sql_atom.c
sql/test/BugTracker-2014/Tests/stringfloatshtcompare.Bug-3512.test
sql/test/BugTracker-2016/Tests/memory-consumption-query-PLAN-25joins.Bug-3972.test
sql/test/BugTracker-2017/Tests/heapextend.Bug-6134.test
sql/test/BugTracker-2023/Tests/misc-crashes-7390.test
sql/test/BugTracker-2024/Tests/7451-between-boolean.test
sql/test/BugTracker-2024/Tests/7462-exp-bin.test
sql/test/BugTracker-2024/Tests/7470-conversion-string-to-bte-failed.test
sql/test/SQLancer/Tests/sqlancer04.test
sql/test/SQLancer/Tests/sqlancer06.test
sql/test/SQLancer/Tests/sqlancer09.test
sql/test/emptydb/Tests/check.stable.out.int128
sql/test/miscellaneous/Tests/select_groupby.test
sql/test/miscellaneous/Tests/values.test
Branch: default
Log Message:
For value list and compare operators the conversion rules changed, we now
better follow the
sql standard. The standard states that only if all types are string
(char,varchar etc) variations
the resulting type is string, else the conversion will favour the other super
type.
Adapted test accordingly
diffs (truncated from 517 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
@@ -606,7 +606,10 @@ supertype_opt_string(sql_subtype *super,
tpe = lsuper.type->base.name;
eclass = lsuper.type->eclass;
}
- } else if (((!super_string || !EC_VARCHAR(r->type->eclass)) &&
i->type->base.id > r->type->base.id) || (EC_VARCHAR(i->type->eclass) &&
!EC_VARCHAR(r->type->eclass))) {
+ /* with !super_string we follow the sql specification that for
value lists and compare operators all values
+ * should be string or we cast into the non-string super type */
+ } else if ((super_string && (EC_VARCHAR(i->type->eclass) ||
i->type->base.id > r->type->base.id)) ||
+ (!super_string && ((EC_VARCHAR(r->type->eclass) &&
i->type->eclass != EC_ANY) || i->type->base.id > r->type->base.id))) {
lsuper = *i;
radix = i->type->radix;
tpe = i->type->base.name;
@@ -662,9 +665,9 @@ supertype(sql_subtype *super, sql_subtyp
}
sql_subtype *
-cmp_supertype(sql_subtype *super, sql_subtype *r, sql_subtype *i)
+cmp_supertype(sql_subtype *super, sql_subtype *r, sql_subtype *i, bool
opt_string)
{
- return supertype_opt_string(super, r, i, false);
+ return supertype_opt_string(super, r, i, opt_string);
}
sql_subfunc*
diff --git a/sql/common/sql_types.h b/sql/common/sql_types.h
--- a/sql/common/sql_types.h
+++ b/sql/common/sql_types.h
@@ -55,7 +55,7 @@ extern sql_arg *sql_create_arg(allocator
extern int subfunc_cmp(sql_subfunc *f1, sql_subfunc *f2);
extern sql_subfunc *sql_dup_subfunc(allocator *sa, sql_func *f, list *ops,
sql_subtype *member);
extern sql_subtype *supertype(sql_subtype *super, sql_subtype *r, sql_subtype
*i);
-extern sql_subtype *cmp_supertype(sql_subtype *super, sql_subtype *r,
sql_subtype *i);
+extern sql_subtype *cmp_supertype(sql_subtype *super, sql_subtype *r,
sql_subtype *i, bool opt_string);
extern char *sql_func_imp(sql_func *f);
extern char *sql_func_mod(sql_func *f);
diff --git a/sql/scripts/52_describe.sql b/sql/scripts/52_describe.sql
--- a/sql/scripts/52_describe.sql
+++ b/sql/scripts/52_describe.sql
@@ -212,7 +212,7 @@ CREATE VIEW sys.describe_tables AS
GROUP_CONCAT(
sys.DQ(c.name) || ' ' ||
sys.describe_type(c.type, c.type_digits,
c.type_scale) ||
- ifthenelse(c."null" = 'false', ' NOT NULL', '')
+ ifthenelse(c."null" = false, ' NOT NULL', '')
, ', ') || ')'
FROM sys._columns c
WHERE c.table_id = t.id) col,
diff --git a/sql/server/rel_exp.c b/sql/server/rel_exp.c
--- a/sql/server/rel_exp.c
+++ b/sql/server/rel_exp.c
@@ -3820,8 +3820,8 @@ exp_numeric_supertype(mvc *sql, sql_exp
return e;
}
-sql_exp *
-exp_check_type(mvc *sql, sql_subtype *t, sql_rel *rel, sql_exp *exp,
check_type tpe)
+static sql_exp *
+exp_check_type_intern(mvc *sql, sql_subtype *t, sql_rel *rel, sql_exp *exp,
check_type tpe, bool atom_inplace)
{
int c, err = 0;
sql_exp* nexp = NULL;
@@ -3831,8 +3831,12 @@ exp_check_type(mvc *sql, sql_subtype *t,
return exp;
/* first try cheap internal (in-place) conversions ! */
- if ((nexp = exp_convert_inplace(sql->sa, t, exp)) != NULL)
- return nexp;
+ if (exp && exp->type == e_atom) {
+ if ((nexp = exp_convert_inplace(sql->sa, t, exp)) != NULL)
+ return nexp;
+ }
+ if (atom_inplace) /* error ? */
+ return NULL;
if (fromtype && subtype_cmp(t, fromtype) != 0) {
if (EC_INTERVAL(fromtype->type->eclass) && (t->type->eclass ==
EC_NUM || t->type->eclass == EC_POS) && t->digits < fromtype->digits) {
@@ -3864,6 +3868,12 @@ exp_check_type(mvc *sql, sql_subtype *t,
return exp;
}
+sql_exp *
+exp_check_type(mvc *sql, sql_subtype *t, sql_rel *rel, sql_exp *exp,
check_type tpe)
+{
+ return exp_check_type_intern(sql, t, rel, exp, tpe, false);
+}
+
list*
exps_check_type(mvc *sql, sql_subtype *t, list *exps)
{
@@ -3883,6 +3893,7 @@ exp_values_set_supertype(mvc *sql, sql_e
assert(is_values(values));
list *vals = exp_get_values(values), *nexps;
sql_subtype *tpe = opt_super?opt_super:exp_subtype(vals->h->data);
+ bool mixing_types = false;
if (!opt_super && tpe)
values->tpe = *tpe;
@@ -3900,7 +3911,11 @@ exp_values_set_supertype(mvc *sql, sql_e
}
ttpe = exp_subtype(e);
if (tpe && ttpe) {
- supertype(&super, ttpe, tpe);
+ bool is_str = EC_VARCHAR(tpe->type->eclass) ||
EC_VARCHAR(ttpe->type->eclass);
+ cmp_supertype(&super, ttpe, tpe, false);
+ if (is_str && !EC_VARCHAR(super.type->eclass)) {
+ mixing_types = true;
+ }
values->tpe = super;
tpe = &values->tpe;
} else {
@@ -3908,6 +3923,7 @@ exp_values_set_supertype(mvc *sql, sql_e
}
}
+ (void)mixing_types;
if (tpe) {
/* if the expression is a parameter set its type */
for (node *m = vals->h; m; m = m->next) {
@@ -3923,9 +3939,11 @@ exp_values_set_supertype(mvc *sql, sql_e
nexps = sa_list(sql->sa);
for (node *m = vals->h; m; m = m->next) {
sql_exp *e = m->data;
- e = exp_check_type(sql, &values->tpe, NULL, e,
type_equal);
- if (!e)
+ e = exp_check_type_intern(sql, &values->tpe, NULL, e,
type_equal, mixing_types);
+ if (!e) {
+ (void) sql_error(sql, 10, SQLSTATE(42000)
"mixing types varchar and %s", values->tpe.type->base.name);
return NULL;
+ }
exp_label(sql->sa, e, ++sql->label);
append(nexps, e);
}
diff --git a/sql/server/rel_optimize_sel.c b/sql/server/rel_optimize_sel.c
--- a/sql/server/rel_optimize_sel.c
+++ b/sql/server/rel_optimize_sel.c
@@ -227,7 +227,7 @@ exp_merge_range(visitor *v, sql_rel *rel
f->flag == cmp_lte))
continue;
- cmp_supertype(&super, exp_subtype(le),
exp_subtype(lf));
+ cmp_supertype(&super, exp_subtype(le),
exp_subtype(lf), false);
if (!(rf = exp_check_type(v->sql,
&super, rel, rf, type_equal)) ||
!(le = exp_check_type(v->sql,
&super, rel, le, type_equal)) ||
!(re = exp_check_type(v->sql,
&super, rel, re, type_equal))) {
@@ -308,7 +308,7 @@ exp_merge_range(visitor *v, sql_rel *rel
if (lt && (ff == cmp_lt || ff ==
cmp_lte))
continue;
- cmp_supertype(&super, exp_subtype(le),
exp_subtype(lf));
+ cmp_supertype(&super, exp_subtype(le),
exp_subtype(lf), false);
if (!(rf = exp_check_type(v->sql,
&super, rel, rf, type_equal)) ||
!(le = exp_check_type(v->sql,
&super, rel, le, type_equal)) ||
!(re = exp_check_type(v->sql,
&super, rel, re, type_equal))) {
@@ -865,7 +865,7 @@ try_rewrite_equal_or_is_null(mvc *sql, s
if (valid && first_is_null_found && second_is_null_found) {
sql_subtype super;
- cmp_supertype(&super, exp_subtype(first),
exp_subtype(second)); /* first and second must have the same type */
+ cmp_supertype(&super, exp_subtype(first),
exp_subtype(second), false); /* first and second must have the same type */
if (!(first = exp_check_type(sql, &super, rel, first,
type_equal)) ||
!(second = exp_check_type(sql, &super,
rel, second, type_equal))) {
sql->session->status = 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
@@ -1052,7 +1052,7 @@ exp_tuples_set_supertype(mvc *sql, list
if (!tpe)
return NULL;
if (has_type[i] && tpe) {
- cmp_supertype(types+i, types+i, tpe);
+ cmp_supertype(types+i, types+i, tpe,
false);
} else {
has_type[i] = 1;
types[i] = *tpe;
@@ -1853,7 +1853,7 @@ rel_convert_types(mvc *sql, sql_rel *ll,
if (subtype_cmp(lt, rt) != 0 || (tpe == type_equal_no_any &&
(lt->type->localtype==0 || rt->type->localtype==0))) {
sql_subtype super;
- cmp_supertype(&super, r, i);
+ cmp_supertype(&super, r, i, tpe != type_equal_no_any);
if (scale_fixing) {
/* convert ls to super type */
ls = exp_check_type(sql, &super, ll, ls, tpe);
@@ -2420,7 +2420,7 @@ rel_in_value_exp(sql_query *query, sql_r
sql_subtype *tpe = exp_subtype(e);
if (values_tpe && tpe) {
- cmp_supertype(&super, values_tpe, tpe);
+ cmp_supertype(&super, values_tpe, tpe,
false);
*values_tpe = super;
} else if (!values_tpe && tpe) {
super = *tpe;
@@ -2433,7 +2433,7 @@ rel_in_value_exp(sql_query *query, sql_r
values_tpe = le_tpe;
if (!le_tpe || !values_tpe)
return sql_error(sql, 01, SQLSTATE(42000) "For
the IN operator, both sides must have a type defined");
- cmp_supertype(&super, values_tpe, le_tpe); /* compute
supertype */
+ cmp_supertype(&super, values_tpe, le_tpe, false); /*
compute supertype */
/* on selection/join cases we can generate cmp
expressions instead of anyequal for trivial cases */
if ((is_sql_where(f) || is_sql_having(f)) &&
!is_sql_farg(f) && !exp_has_rel(le) && exps_are_atoms(vals)) {
@@ -2514,7 +2514,7 @@ exp_between_check_types(sql_subtype *res
sql_subtype super;
if (t1 && t2) {
- cmp_supertype(&super, t2, t1);
+ cmp_supertype(&super, t2, t1, false);
type_found = true;
} else if (t1) {
super = *t1;
@@ -2525,7 +2525,7 @@ exp_between_check_types(sql_subtype *res
}
if (t3) {
if (type_found)
- cmp_supertype(&super, t3, &super);
+ cmp_supertype(&super, t3, &super, false);
else
super = *t3;
type_found = true;
diff --git a/sql/server/rel_statistics_functions.c
b/sql/server/rel_statistics_functions.c
--- a/sql/server/rel_statistics_functions.c
+++ b/sql/server/rel_statistics_functions.c
@@ -580,6 +580,7 @@ sql_month_propagate_statistics(mvc *sql,
p->value.dval = 12;
}
+/* day(date), day(timestamp), day(interval_day), day(interval_sec) */
static void
sql_day_propagate_statistics(mvc *sql, sql_exp *e)
{
@@ -588,13 +589,18 @@ sql_day_propagate_statistics(mvc *sql, s
sql_subtype *tp = exp_subtype(first);
int localtype = tp->type->eclass == EC_SEC ? TYPE_lng : TYPE_int;
atom *omin, *omax;
- lng nmin = 1, nmax = 31;
+ lng nmin = 1, nmax = 31; /* for timestamp and date the min/max is
within those bounds */
if ((omin = find_prop_and_get(first->p, PROP_MIN)) && (omax =
find_prop_and_get(first->p, PROP_MAX))) {
if (tp->type->eclass == EC_SEC) {
nmin = sql_day(omin->data.val.lval);
nmax = sql_day(omax->data.val.lval);
+ } else if (tp->type->eclass == EC_MONTH) {
+ nmin = sql_day(omin->data.val.ival);
+ nmax = sql_day(omax->data.val.ival);
}
+ } else if (tp->type->eclass == EC_SEC || tp->type->eclass == EC_MONTH) {
+ return;
}
set_minmax_property(sql, e, PROP_MAX, atom_int(sql->sa,
sql_fetch_localtype(localtype), nmax));
diff --git a/sql/server/sql_atom.c b/sql/server/sql_atom.c
--- a/sql/server/sql_atom.c
+++ b/sql/server/sql_atom.c
@@ -822,8 +822,9 @@ atom_cast_inplace(allocator *sa, atom *a
tp->type->eclass == EC_NUM ||
tp->type->eclass == EC_FLT)) ||
(EC_VARCHAR(at->type->eclass) &&
- (tp->type->eclass == EC_DATE ||
- EC_TEMP_NOFRAC(tp->type->eclass)))) {
+ (
+ /*(tp->type->eclass == EC_DATE ||*/
+ EC_TEMP_NOFRAC(tp->type->eclass)) &&
!ATOMextern(tp->type->localtype))) {
ValRecord v = { .vtype = tp->type->localtype };
if (VARconvert(sa, &v, &a->data, at->scale, tp->scale,
tp->type->eclass == EC_DEC ? tp->digits : 0) != GDK_SUCCEED) {
GDKclrerr();
diff --git a/sql/test/BugTracker-2014/Tests/stringfloatshtcompare.Bug-3512.test
b/sql/test/BugTracker-2014/Tests/stringfloatshtcompare.Bug-3512.test
--- a/sql/test/BugTracker-2014/Tests/stringfloatshtcompare.Bug-3512.test
+++ b/sql/test/BugTracker-2014/Tests/stringfloatshtcompare.Bug-3512.test
@@ -17,7 +17,7 @@ statement ok
INSERT INTO bug3512 (a) VALUES ('327670')
query T rowsort
-SELECT * FROM bug3512 WHERE a > 8888
+SELECT * FROM bug3512 WHERE a > cast(8888 as varchar)
----
9999
@@ -31,7 +31,7 @@ statement ok
INSERT INTO bug3512 (a) VALUES ('9999.00')
query T rowsort
-SELECT * FROM bug3512 WHERE a = 9999
+SELECT * FROM bug3512 WHERE a = cast(9999 as varchar)
----
9999
diff --git
a/sql/test/BugTracker-2016/Tests/memory-consumption-query-PLAN-25joins.Bug-3972.test
b/sql/test/BugTracker-2016/Tests/memory-consumption-query-PLAN-25joins.Bug-3972.test
---
a/sql/test/BugTracker-2016/Tests/memory-consumption-query-PLAN-25joins.Bug-3972.test
+++
b/sql/test/BugTracker-2016/Tests/memory-consumption-query-PLAN-25joins.Bug-3972.test
@@ -185,7 +185,7 @@ top N (
| | | | ) [ ("a3"."t3pkcol" NOT NULL UNIQUE HASHCOL ) = ("table1"."t1cold113"
NOT NULL) ],
| | | | table("sys"."table12") [ "table12"."t12cola1" NOT NULL UNIQUE ]
| | | ) [ ("table12"."t12cola1" NOT NULL UNIQUE) = ("table1"."t1cola1" NOT
NULL) ]
-| | ) [ ((("table1"."t1cold1" NOT NULL) FILTER "sys"."like"(varchar "%a%",
varchar "", boolean(1) "true")) or ((("table1"."t1cola1" NOT NULL) FILTER
"sys"."like"(varchar "%a%", varchar "", boolean(1) "true")) or
((("table1"."t1colb1" NOT NULL) FILTER "sys"."like"(varchar "%a%", varchar "",
boolean(1) "true")) or ((("table1"."t1cola11" NOT NULL) FILTER
"sys"."like"(varchar "%business%", varchar "", boolean(1) "true")) or
((("table1"."t1colc91" NOT NULL) >= (timestamp(7) "2016-03-21
05:00:00.000000")) or ((("table1"."t1cola101" NOT NULL) = (tinyint(1) "1")) or
((("table1"."t1cola12" NOT NULL) FILTER "sys"."like"(varchar "%Vijay%", varchar
"", boolean(1) "true")) or ((("table2"."t2cola1") FILTER
"sys"."not_like"(varchar "%gmail%", varchar "", boolean(1) "true"),
("table2"."t2cola1") FILTER "sys"."not_like"(varchar "%yahoo%", varchar "",
boolean(1) "true")) or ((("table2"."t2cola1") FILTER "sys"."like"(varchar
"%efequitygroup.com%", varchar "", boolean(1) "true")) or ((("table4"."t4cola
1") = (varchar "Customer")) or ((("table4"."t4cola2") ! * = (varchar NULL)) or
((("table2"."t2cola81") >= (date "2009-08-31")) or (((("table5"."t5cola1") =
(varchar "BAT")) or ((("table5"."t5cola2") FILTER "sys"."like"(varchar
"%AUSTRALIA%", varchar "", boolean(1) "true")) or (("table5"."t5cola2") FILTER
"sys"."like"(varchar "%Monet%", varchar "", boolean(1) "true"),
("table5"."t5cola3") = (varchar "Facebook"), ("table5"."t5cola5") = (varchar
"new"), ("table5"."t5cola81") > (date "2015-07-30")))) or
((("table10"."t10cola1") != (varchar "Completed"), ("table9"."t9cola1") =
(varchar "Tasks"), ("table9"."t9cola91") >= (timestamp(7) "2012-01-01
04:32:27.000000"), ("table10"."t10cola91") <= (timestamp(7) "2013-01-01
04:32:27.000000")) or ((("table9"."t9cola1") = (varchar "Events"),
("table11"."t11cola91") >= (timestamp(7) "2012-01-01 04:32:27.000000"),
("table11"."t11cola91") <= (timestamp(7) "2013-01-01 04:32:27.000000")) or
(("table9"."t9cola1") = (varchar "Calls"), ("table10"."t10cola
91") >= (timestamp(7) "2012-01-01 04:32:27.000000"), ("table10"."t10cola91")
<= (timestamp(7) "2013-01-01 04:32:27.000000")))))))))))))))),
("table1"."t1cold111" NOT NULL) in (bigint(63) "15842000014793046", bigint(63)
"15842000017701488", bigint(63) "15842000000024019", bigint(63)
"15842000000074007", bigint(63) "15842000009358096", bigint(63)
"15842000010487625", bigint(63) "15842000006731919", bigint(63)
"15842000002590112", bigint(63) "15842000000019001", bigint(63)
"15842000014923682", bigint(63) "15842000027547249")) or
(("table12"."t12cola1") in (varchar[bigint(54) "15842000280111951"] NOT NULL,
varchar[bigint(54) "15842000280163015"] NOT NULL)) ]
+| | ) [ ((("table1"."t1cold1" NOT NULL) FILTER "sys"."like"(varchar "%a%",
varchar "", boolean(1) "true")) or ((("table1"."t1cola1" NOT NULL) FILTER
"sys"."like"(varchar "%a%", varchar "", boolean(1) "true")) or
((("table1"."t1colb1" NOT NULL) FILTER "sys"."like"(varchar "%a%", varchar "",
boolean(1) "true")) or ((("table1"."t1cola11" NOT NULL) FILTER
"sys"."like"(varchar "%business%", varchar "", boolean(1) "true")) or
((("table1"."t1colc91" NOT NULL) >= (timestamp(7) "2016-03-21
05:00:00.000000")) or ((("table1"."t1cola101" NOT NULL) = (tinyint(1) "1")) or
((("table1"."t1cola12" NOT NULL) FILTER "sys"."like"(varchar "%Vijay%", varchar
"", boolean(1) "true")) or ((("table2"."t2cola1") FILTER
"sys"."not_like"(varchar "%gmail%", varchar "", boolean(1) "true"),
("table2"."t2cola1") FILTER "sys"."not_like"(varchar "%yahoo%", varchar "",
boolean(1) "true")) or ((("table2"."t2cola1") FILTER "sys"."like"(varchar
"%efequitygroup.com%", varchar "", boolean(1) "true")) or ((("table4"."t4cola
1") = (varchar "Customer")) or ((("table4"."t4cola2") ! * = (varchar NULL)) or
((("table2"."t2cola81") >= (date[varchar(10) "2009-08-31"] NOT NULL)) or
(((("table5"."t5cola1") = (varchar "BAT")) or ((("table5"."t5cola2") FILTER
"sys"."like"(varchar "%AUSTRALIA%", varchar "", boolean(1) "true")) or
(("table5"."t5cola2") FILTER "sys"."like"(varchar "%Monet%", varchar "",
boolean(1) "true"), ("table5"."t5cola3") = (varchar "Facebook"),
("table5"."t5cola5") = (varchar "new"), ("table5"."t5cola81") >
(date[varchar(10) "2015-07-30"] NOT NULL)))) or ((("table10"."t10cola1") !=
(varchar "Completed"), ("table9"."t9cola1") = (varchar "Tasks"),
("table9"."t9cola91") >= (timestamp(7) "2012-01-01 04:32:27.000000"),
("table10"."t10cola91") <= (timestamp(7) "2013-01-01 04:32:27.000000")) or
((("table9"."t9cola1") = (varchar "Events"), ("table11"."t11cola91") >=
(timestamp(7) "2012-01-01 04:32:27.000000"), ("table11"."t11cola91") <=
(timestamp(7) "2013-01-01 04:32:27.000000")) or (("table9"."t9cola
1") = (varchar "Calls"), ("table10"."t10cola91") >= (timestamp(7) "2012-01-01
04:32:27.000000"), ("table10"."t10cola91") <= (timestamp(7) "2013-01-01
04:32:27.000000")))))))))))))))), ("table1"."t1cold111" NOT NULL) in
(bigint(63) "15842000014793046", bigint(63) "15842000017701488", bigint(63)
"15842000000024019", bigint(63) "15842000000074007", bigint(63)
"15842000009358096", bigint(63) "15842000010487625", bigint(63)
"15842000006731919", bigint(63) "15842000002590112", bigint(63)
"15842000000019001", bigint(63) "15842000014923682", bigint(63)
"15842000027547249")) or ((bigint(63)["table12"."t12cola1"]) in (bigint(63)
"15842000280111951", bigint(63) "15842000280163015")) ]
| ) [ "table1"."t1pkcol" NOT NULL HASHCOL , "table1"."t1cola82" NOT NULL,
"table2"."t2cola10", "table1"."t1cola1" NOT NULL, "table1"."t1cola91" NOT NULL,
"a1"."t3cola1" ] [ "table2"."t2cola82" NULLS LAST ]
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
