Changeset: 9405b15e3909 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=9405b15e3909
Modified Files:
clients/examples/perl/sqlsample.pl
clients/examples/python/sqlsample.py
monetdb5/modules/atoms/mtime.c
sql/common/sql_types.c
sql/server/rel_optimizer.c
sql/server/rel_unnest.c
sql/test/mapi/Tests/perl_dbi.stable.out
sql/test/mapi/Tests/python3_dbapi.stable.out
sql/test/miscellaneous/Tests/groupby_error.sql
sql/test/miscellaneous/Tests/groupby_error.stable.out
tools/merovingian/client/Tests/All
Branch: default
Log Message:
Merged with Jun2020
diffs (231 lines):
diff --git a/clients/examples/perl/sqlsample.pl
b/clients/examples/perl/sqlsample.pl
--- a/clients/examples/perl/sqlsample.pl
+++ b/clients/examples/perl/sqlsample.pl
@@ -37,7 +37,7 @@ my $dbh = DBI->connect( $dsn,
}
{
# deliberately executing a wrong SQL statement:
- my $sth = $dbh->prepare('values ();');
+ my $sth = $dbh->prepare('select commit_action, access from tables group by
access;');
eval { $sth->execute }; print "ERROR REPORTED: $@" if $@;
}
$dbh->do('create table perl_table (i smallint,s string);');
diff --git a/clients/examples/python/sqlsample.py
b/clients/examples/python/sqlsample.py
--- a/clients/examples/python/sqlsample.py
+++ b/clients/examples/python/sqlsample.py
@@ -21,7 +21,7 @@ print(cursor.fetchone())
# deliberately executing a wrong SQL statement:
try:
- cursor.execute('values ();')
+ cursor.execute('select commit_action, access from tables group by access;')
except pymonetdb.OperationalError as e:
print(e)
diff --git a/monetdb5/modules/atoms/mtime.c b/monetdb5/modules/atoms/mtime.c
--- a/monetdb5/modules/atoms/mtime.c
+++ b/monetdb5/modules/atoms/mtime.c
@@ -874,10 +874,35 @@ MTIMEstr_to_timestamp(timestamp *ret, co
t = time(NULL);
localtime_r(&t, &tm);
tm.tm_sec = tm.tm_min = tm.tm_hour = 0;
+ tm.tm_isdst = -1;
if (strptime(*s, *format, &tm) == NULL)
throw(MAL, "mtime.str_to_timestamp",
"format '%s', doesn't match timestamp '%s'", *format,
*s);
- *ret = timestamp_fromtime(mktime(&tm));
+ *ret = timestamp_create(date_create(tm.tm_year + 1900,
+
tm.tm_mon + 1,
+
tm.tm_mday),
+
daytime_create(tm.tm_hour,
+
tm.tm_min,
+
tm.tm_sec == 60 ? 59 : tm.tm_sec,
+
0));
+ /* if strptime filled in DST information (tm_isdst >= 0), then the
+ * time is in system local time and we convert to GMT by
+ * subtracting the time zone offset, else we don't touch the time
+ * returned because it is assumed to already be in GMT */
+ if (tm.tm_isdst >= 0) {
+ int isdst = 0;
+ int tz = local_timezone(&isdst);
+ /* if strptime's information doesn't square with our own
+ * information about having or not having DST, we compensate
+ * an hour */
+ if (tm.tm_isdst > 0 && isdst == 0) {
+ tz += 3600;
+ } else if (tm.tm_isdst == 0 && isdst > 0) {
+ tz -= 3600;
+ }
+
+ *ret = timestamp_add_usec(*ret, -tz * LL_CONSTANT(1000000));
+ }
if (is_timestamp_nil(*ret))
throw(MAL, "mtime.str_to_timestamp", "bad timestamp '%s'", *s);
return MAL_SUCCEED;
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
@@ -1350,7 +1350,7 @@ sqltypeinit( sql_allocator *sa)
sql_create_aggr(sa, "prod", "aggr", "prod", FALSE, LargestINT, 1, INT);
sql_create_aggr(sa, "prod", "aggr", "prod", FALSE, LargestINT, 1, LNG);
#ifdef HAVE_HGE
- if (HAVE_HGE)
+ if (have_hge)
sql_create_aggr(sa, "prod", "aggr", "prod", FALSE, LargestINT,
1, HGE);
#endif
diff --git a/sql/server/rel_optimizer.c b/sql/server/rel_optimizer.c
--- a/sql/server/rel_optimizer.c
+++ b/sql/server/rel_optimizer.c
@@ -2480,6 +2480,50 @@ has_no_selectivity(mvc *sql, sql_rel *re
return rel;
}
+/*
+ * Remove a redundant join
+ *
+ * join (L, Distinct Project(join(L,P) [ p.key == l.lkey]) [p.key]) [ p.key ==
l.lkey]
+ * =>
+ * join(L, P) [p.key==l.lkey]
+ */
+static sql_rel *
+rel_remove_redundant_join(mvc *sql, sql_rel *rel, int *changes)
+{
+ (void)sql;
+ if (is_join(rel->op) || is_semi(rel->op)) {
+ sql_rel *l = rel->l, *r = rel->r, *b, *p = NULL, *j;
+
+ if (is_basetable(l->op) && is_simple_project(r->op) &&
need_distinct(r)) {
+ b = l;
+ p = r;
+ j = p->l;
+ } else if (is_basetable(r->op) && is_simple_project(l->op) &&
need_distinct(l)) {
+ b = r;
+ p = l;
+ j = p->l;
+ }
+ if (!p || !j || !is_join(j->op))
+ return rel;
+ /* j must have b->l (ie table) */
+ sql_rel *jl = j->l, *jr = j->r;
+ if ((is_basetable(jl->op) && jl->l == b->l) ||
+ (is_basetable(jr->op) && jr->l == b->l)) {
+ int left = 0;
+ if (is_basetable(jl->op) && jl->l == b->l)
+ left = 1;
+ if (exp_match_list(j->exps, rel->exps)) {
+ p->l = (left)?rel_dup(jr):rel_dup(jl);
+ rel_destroy(j);
+ set_nodistinct(p);
+ (*changes)++;
+ return rel;
+ }
+ }
+ }
+ return rel;
+}
+
static sql_column *
is_fk_column_of_pk(sql_rel *rel, sql_column *pkc, sql_exp *e) /* test if e is
a foreing key column for the pk on pkc */
{
@@ -9125,6 +9169,7 @@ optimize_rel(mvc *sql, sql_rel *rel, int
rel = rel_visitor_bottomup(sql, rel,
&rel_simplify_math, &changes);
rel = rel_visitor_bottomup(sql, rel,
&rel_distinct_aggregate_on_unique_values, &changes);
rel = rel_visitor_bottomup(sql, rel,
&rel_push_down_bounds, &changes);
+ rel = rel_visitor_bottomup(sql, rel,
&rel_remove_redundant_join, &changes);
rel = rel_visitor_bottomup(sql, rel,
&rel_distinct_project2groupby, &changes);
}
}
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
@@ -2650,6 +2650,15 @@ rewrite_ifthenelse(mvc *sql, sql_rel *re
sf = e->f;
if (is_ifthenelse_func(sf) && !list_empty(e->l)) {
list *l = e->l;
+
+ /* remove unecessary = true expressions under ifthenelse */
+ for (node *n = l->h ; n ; n = n->next) {
+ sql_exp *e = n->data;
+
+ if (e->type == e_cmp && e->flag == cmp_equal &&
exp_is_true(sql, e->r))
+ n->data = e->l;
+ }
+
sql_exp *cond = l->h->data;
sql_exp *then_exp = l->h->next->data;
sql_exp *else_exp = l->h->next->next->data;
diff --git a/sql/test/mapi/Tests/perl_dbi.stable.out
b/sql/test/mapi/Tests/perl_dbi.stable.out
--- a/sql/test/mapi/Tests/perl_dbi.stable.out
+++ b/sql/test/mapi/Tests/perl_dbi.stable.out
@@ -33,7 +33,7 @@ Start a simple Monet SQL interaction
data sources: dbi:monetdb:
field[0]: 2, last index: 0
field[0]: 3, last index: 0
-ERROR REPORTED: DBD::monetdb::st execute failed: 42000!syntax error,
unexpected ')' in: "values ()" at
/home/niels/scratch/clients/usr/local/share/perl5/MonetDB/CLI/MapiPP.pm line
123.
+ERROR REPORTED: DBD::monetdb::st execute failed: 42000!SELECT: cannot use non
GROUP BY column 'commit_action' in query results without an aggregate function
at /home/niels/scratch/clients/usr/local/share/perl5/MonetDB/CLI/MapiPP.pm line
123.
bun: 3, three
bun: 7, seven
bun: 42, \n
diff --git a/sql/test/mapi/Tests/python3_dbapi.stable.out
b/sql/test/mapi/Tests/python3_dbapi.stable.out
--- a/sql/test/mapi/Tests/python3_dbapi.stable.out
+++ b/sql/test/mapi/Tests/python3_dbapi.stable.out
@@ -30,7 +30,7 @@ stdout of test 'python3_dbapi` in direct
[(1,)]
(2,)
-42000!syntax error, unexpected ')' in: "values ()"
+42000!SELECT: cannot use non GROUP BY column 'commit_action' in query results
without an aggregate function
[(3, 'three'), (7, 'seven')]
2
diff --git a/sql/test/miscellaneous/Tests/groupby_error.sql
b/sql/test/miscellaneous/Tests/groupby_error.sql
--- a/sql/test/miscellaneous/Tests/groupby_error.sql
+++ b/sql/test/miscellaneous/Tests/groupby_error.sql
@@ -133,6 +133,15 @@ drop table tab1;
CREATE TABLE tab1(col0 INTEGER, col1 STRING);
prepare select 1 from tab1 where (col0,col1) in (select ?,? from tab1);
+SELECT 1 FROM tab0 where CASE WHEN 64 IN ( col0 ) THEN true END;
+ --empty
+
+SELECT 1 FROM tab0 GROUP BY col0 HAVING CASE WHEN 64 IN ( col0 ) THEN TRUE END;
+ --empty
+
+SELECT + col2 + + col0 AS col0 FROM tab0 AS cor0 GROUP BY col1, col2, col0
HAVING NULL IN ( + ( - - ( CASE WHEN 64 IN ( col0 * - col2 + + col1 ) THEN - 98
END ) ) * - 13 );
+ --empty
+
drop table tab0;
drop table tab1;
drop table tab2;
diff --git a/sql/test/miscellaneous/Tests/groupby_error.stable.out
b/sql/test/miscellaneous/Tests/groupby_error.stable.out
--- a/sql/test/miscellaneous/Tests/groupby_error.stable.out
+++ b/sql/test/miscellaneous/Tests/groupby_error.stable.out
@@ -397,6 +397,21 @@ project (
[ "tinyint", 1, 0, "", "%11", "%11" ]
[ "int", 32, 0, NULL, NULL, NULL ]
[ "clob", 0, 0, NULL, NULL, NULL ]
+#SELECT 1 FROM tab0 where CASE WHEN 64 IN ( col0 ) THEN true END;
+% .%5 # table_name
+% %5 # name
+% tinyint # type
+% 1 # length
+#SELECT 1 FROM tab0 GROUP BY col0 HAVING CASE WHEN 64 IN ( col0 ) THEN TRUE
END;
+% .%5 # table_name
+% %5 # name
+% tinyint # type
+% 1 # length
+#SELECT + col2 + + col0 AS col0 FROM tab0 AS cor0 GROUP BY col1, col2, col0
HAVING NULL IN ( + ( - - ( CASE WHEN 64 IN ( col0 * - col2 + + col1 ) THEN - 98
END ) ) * - 13 );
+% sys. # table_name
+% col0 # name
+% bigint # type
+% 1 # length
#drop table tab0;
#drop table tab1;
#drop table tab2;
diff --git a/tools/merovingian/client/Tests/All
b/tools/merovingian/client/Tests/All
--- a/tools/merovingian/client/Tests/All
+++ b/tools/merovingian/client/Tests/All
@@ -1,1 +1,1 @@
-monetdb_status
+NOT_WIN32?monetdb_status
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
