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