Changeset: b322a1e6ddb2 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=b322a1e6ddb2
Modified Files:
sql/benchmarks/tpcds/Tests/44.stable.out
sql/benchmarks/tpcds/Tests/54.stable.out
sql/server/rel_unnest.c
sql/test/BugTracker-2015/Tests/crash.Bug-3736.stable.out
sql/test/BugTracker-2017/Tests/crash_on_NULL_ptr.Bug-6130.stable.out
sql/test/BugTracker-2017/Tests/sqlsmith.Bug-6477.stable.out
sql/test/mergetables/Tests/sqlsmith-exists.stable.out
sql/test/subquery/Tests/correlated.stable.out
Branch: linear-hashing
Log Message:
fixed problem with correlated exists
always join with 'is' semantics in general unnest
use left join on subqueries within functions (or projects)
use full (left) on correlated 'allnotequal'
diffs (273 lines):
diff --git a/sql/benchmarks/tpcds/Tests/44.stable.out
b/sql/benchmarks/tpcds/Tests/44.stable.out
--- a/sql/benchmarks/tpcds/Tests/44.stable.out
+++ b/sql/benchmarks/tpcds/Tests/44.stable.out
@@ -41,7 +41,7 @@ stdout of test '44` in directory 'sql/be
# HAVING avg(ss_net_profit) > 0.9*
# (SELECT avg(ss_net_profit) rank_col
# FROM store_sales
-% sys.asceding, sys., sys. # table_name
+% .asceding, sys., sys. # table_name
% rnk, best_performing, worst_performing # name
% int, char, char # type
% 2, 50, 50 # length
diff --git a/sql/benchmarks/tpcds/Tests/54.stable.out
b/sql/benchmarks/tpcds/Tests/54.stable.out
--- a/sql/benchmarks/tpcds/Tests/54.stable.out
+++ b/sql/benchmarks/tpcds/Tests/54.stable.out
@@ -39,7 +39,7 @@ stdout of test '54` in directory 'sql/be
# date_dim,
# customer
# WHERE sold_date_sk = d_date_sk
-% sys.segments, sys., sys. # table_name
+% .segments, ., . # table_name
% segment, num_customers, segment_base # name
% int, bigint, bigint # type
% 5, 1, 6 # length
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
@@ -608,7 +608,6 @@ move_join_exps(mvc *sql, sql_rel *j, sql
static sql_rel *
rel_general_unnest(mvc *sql, sql_rel *rel, list *ad)
{
- /* current unnest only possible for equality joins, <, <> etc needs
more work */
if (rel && (is_join(rel->op) || is_semi(rel->op)) && is_dependent(rel)
&& ad) {
list *fd;
node *n, *m;
@@ -652,7 +651,7 @@ rel_general_unnest(mvc *sql, sql_rel *re
l = exp_ref(sql->sa, l);
r = exp_ref(sql->sa, r);
- e = exp_compare(sql->sa, l, r,
(is_outerjoin(rel->op)|is_semi(rel->op))?cmp_equal_nil:cmp_equal);
+ e = exp_compare(sql->sa, l, r, cmp_equal_nil);
if (!rel->exps)
rel->exps = sa_list(sql->sa);
append(rel->exps, e);
@@ -1476,7 +1475,7 @@ rewrite_exp_rel(mvc *sql, sql_rel *rel,
{
(void)depth;
if (exp_has_rel(e) && !is_ddl(rel->op)) {
- sql_exp *ne = rewrite_inner(sql, rel, exp_rel_get_rel(sql->sa,
e), op_join);
+ sql_exp *ne = rewrite_inner(sql, rel, exp_rel_get_rel(sql->sa,
e), depth?op_left:op_join);
if (!ne)
return ne;
@@ -2047,7 +2046,7 @@ rewrite_anyequal(mvc *sql, sql_rel *rel,
}
}
- if (is_project(rel->op) || depth > 0) {
+ if (is_project(rel->op) || depth > 0 || (!is_tuple &&
rsq && rel_has_freevar(sql, rsq) && !is_anyequal(sf))) {
list *exps = NULL;
sql_exp *rid, *lid, *a = NULL;
sql_rel *sq = lsq;
@@ -2523,8 +2522,9 @@ rewrite_ifthenelse(mvc *sql, sql_rel *re
list *l = e->l;
sql_exp *cond = l->h->data;
sql_subfunc *nf = cond->f;
+ sql_rel *inner = rel->l;
- if (has_nil(cond) && (cond->type != e_func ||
!is_isnull_func(nf))) {
+ if ((has_nil(cond) || (inner && is_outerjoin(inner->op))) &&
(cond->type != e_func || !is_isnull_func(nf))) {
/* add is null */
sql_exp *condnil = rel_unop_(sql, rel, cond, NULL,
"isnull", card_value);
diff --git a/sql/test/BugTracker-2015/Tests/crash.Bug-3736.stable.out
b/sql/test/BugTracker-2015/Tests/crash.Bug-3736.stable.out
--- a/sql/test/BugTracker-2015/Tests/crash.Bug-3736.stable.out
+++ b/sql/test/BugTracker-2015/Tests/crash.Bug-3736.stable.out
@@ -97,7 +97,7 @@ join (
| | | ) [ "o"."open_auction_id" NOT NULL, "b3"."increase" NOT NULL as
"%2"."%2" ]
| | ) [ "o"."open_auction_id" NOT NULL ] [ sys.zero_or_one("%2"."%2" NOT NULL)
NOT NULL as "%3"."%3", "o"."open_auction_id" NOT NULL ]
| ) [ "%3"."%3" NOT NULL, "o"."open_auction_id" NOT NULL as "%25"."%25" ]
-) [ "o"."open_auction_id" NOT NULL = "%25"."%25" NOT NULL ]
+) [ "o"."open_auction_id" NOT NULL =* "%25"."%25" NOT NULL ]
project (
| select (
| | join (
@@ -123,7 +123,7 @@ project (
| | | | | ) [ "o"."open_auction_id" NOT NULL, "b2"."increase" NOT NULL as
"%5"."%5" ]
| | | | ) [ "o"."open_auction_id" NOT NULL ] [ sys.zero_or_one("%5"."%5" NOT
NULL) NOT NULL as "%6"."%6", "o"."open_auction_id" NOT NULL ]
| | | ) [ "%6"."%6" NOT NULL, "o"."open_auction_id" NOT NULL as "%47"."%47" ]
-| | ) [ "o"."open_auction_id" NOT NULL = "%47"."%47" NOT NULL ]
+| | ) [ "o"."open_auction_id" NOT NULL =* "%47"."%47" NOT NULL ]
| ) [ sys.sql_mul("%3"."%3" NOT NULL, double "2") <= "%6"."%6" NOT NULL ]
) [ "b"."id" NOT NULL HASHCOL , "b"."open_auction_id" NOT NULL, "b"."date" NOT
NULL, "b"."time" NOT NULL, "b"."personref" NOT NULL, "b"."increase" NOT NULL ]
#Select b.* FROM open_auctions o, bidder b WHERE (select b3.INCREASE from
bidder b3 where b3.id = (select min(b3a.id) from bidder b3a where
b3a.open_auction_id = o.open_auction_id)) * 2 <= (Select b2.INCREASE from
bidder b2 where b2.id = (SELECT MAX(b2a.id) from bidder b2a where
b2a.open_auction_id = o.open_auction_id)) AND o.open_auction_id =
b.open_auction_id order by date, time;
diff --git
a/sql/test/BugTracker-2017/Tests/crash_on_NULL_ptr.Bug-6130.stable.out
b/sql/test/BugTracker-2017/Tests/crash_on_NULL_ptr.Bug-6130.stable.out
--- a/sql/test/BugTracker-2017/Tests/crash_on_NULL_ptr.Bug-6130.stable.out
+++ b/sql/test/BugTracker-2017/Tests/crash_on_NULL_ptr.Bug-6130.stable.out
@@ -232,7 +232,7 @@ stdout of test 'crash_on_NULL_ptr.Bug-61
# SELECT t.id, t.name, p.creationDate
# FROM messages p, message_tags pt, tags t
# WHERE p.creator IN (SELECT * FROM friends1)
-% sys.resultset, sys.resultset # table_name
+% .resultset, .resultset # table_name
% name, count # name
% varchar, bigint # type
% 0, 1 # length
diff --git a/sql/test/BugTracker-2017/Tests/sqlsmith.Bug-6477.stable.out
b/sql/test/BugTracker-2017/Tests/sqlsmith.Bug-6477.stable.out
--- a/sql/test/BugTracker-2017/Tests/sqlsmith.Bug-6477.stable.out
+++ b/sql/test/BugTracker-2017/Tests/sqlsmith.Bug-6477.stable.out
@@ -54,7 +54,7 @@ stdout of test 'sqlsmith.Bug-6477` in di
# from
# tab0 as ref_1
# left join tab1 as ref_2
-% sys., sys., sys., sys., sys., sys., sys. # table_name
+% ., ., ., ., ., ., . # table_name
% c0, c1, c2, c3, c4, c5, c6 # name
% timestamp, int, int, int, timestamp, int, int # type
% 26, 4, 4, 4, 26, 4, 4 # length
diff --git a/sql/test/mergetables/Tests/sqlsmith-exists.stable.out
b/sql/test/mergetables/Tests/sqlsmith-exists.stable.out
--- a/sql/test/mergetables/Tests/sqlsmith-exists.stable.out
+++ b/sql/test/mergetables/Tests/sqlsmith-exists.stable.out
@@ -222,13 +222,22 @@ stdout of test 'sqlsmith-exists` in dire
# subq_0.c5 AS c11,
# subq_0.c4 AS c12
# FROM
-% sys. # table_name
+% . # table_name
% c0 # name
% int # type
% 1 # length
[ 1 ]
[ 2 ]
[ 3 ]
+#SELECT 1 FROM integers WHERE CASE WHEN EXISTS (SELECT 1 WHERE i IS NULL) THEN
true ELSE true END;
+% . # table_name
+% single_value # name
+% tinyint # type
+% 1 # length
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
#select
# subq_1.c1 as c0,
# subq_1.c2 as c1,
diff --git a/sql/test/subquery/Tests/correlated.stable.out
b/sql/test/subquery/Tests/correlated.stable.out
--- a/sql/test/subquery/Tests/correlated.stable.out
+++ b/sql/test/subquery/Tests/correlated.stable.out
@@ -562,16 +562,16 @@ stdout of test 'correlated` in directory
[ 12, false ]
[ 13, false ]
#SELECT "str", "str"=ANY(SELECT str2 FROM test2) FROM test;
-% .test, .%11 # table_name
-% str, %11 # name
+% .test, .%12 # table_name
+% str, %12 # name
% varchar, boolean # type
% 1, 5 # length
[ "a", true ]
[ "b", true ]
[ "c", false ]
#SELECT "str", "str"=ANY(SELECT str2 FROM test2 WHERE test.a<>test2.a) FROM
test;
-% .test, .%13 # table_name
-% str, %13 # name
+% .test, .%14 # table_name
+% str, %14 # name
% varchar, boolean # type
% 1, 5 # length
[ "a", false ]
@@ -620,7 +620,7 @@ stdout of test 'correlated` in directory
% 1, 1 # length
[ 1, 2 ]
#SELECT * FROM integers s1 INNER JOIN integers s2 ON (SELECT s1.i=s2.i) ORDER
BY s1.i;
-% sys.s1, sys.s2 # table_name
+% .s1, .s2 # table_name
% i, i # name
% int, int # type
% 1, 1 # length
@@ -628,7 +628,7 @@ stdout of test 'correlated` in directory
[ 2, 2 ]
[ 3, 3 ]
#SELECT * FROM integers s1 INNER JOIN integers s2 ON (SELECT s1.i=i FROM
integers WHERE s2.i=i) ORDER BY s1.i;
-% sys.s1, sys.s2 # table_name
+% .s1, .s2 # table_name
% i, i # name
% int, int # type
% 1, 1 # length
@@ -664,7 +664,7 @@ stdout of test 'correlated` in directory
[ 3, 12 ]
#SELECT i, (SELECT row_number() OVER (ORDER BY i)) FROM integers i1 ORDER BY
i; --Should we support correlated expressions inside PARTITION BY and ORDER BY
on Window functions?
% .i1, . # table_name
-% i, %1 # name
+% i, %4 # name
% int, int # type
% 1, 1 # length
[ NULL, 1 ]
@@ -717,7 +717,7 @@ stdout of test 'correlated` in directory
[ 3, 51 ]
#SELECT i, (SELECT row_number() OVER (ORDER BY i) FROM integers WHERE i1.i=i)
FROM integers i1 ORDER BY i;
% .i1, . # table_name
-% i, %3 # name
+% i, %5 # name
% int, int # type
% 1, 1 # length
[ NULL, NULL ]
@@ -726,7 +726,7 @@ stdout of test 'correlated` in directory
[ 3, 1 ]
#SELECT i1.i, (SELECT rank() OVER (ORDER BY i) FROM integers WHERE i1.i=i)
FROM integers i1, integers i2 ORDER BY i1.i;
% .i1, . # table_name
-% i, %3 # name
+% i, %5 # name
% int, int # type
% 1, 1 # length
[ NULL, NULL ]
@@ -747,7 +747,7 @@ stdout of test 'correlated` in directory
[ 3, 1 ]
#SELECT i1.i, (SELECT row_number() OVER (ORDER BY i) FROM integers WHERE
i1.i=i) FROM integers i1, integers i2 ORDER BY i1.i;
% .i1, . # table_name
-% i, %3 # name
+% i, %5 # name
% int, int # type
% 1, 1 # length
[ NULL, NULL ]
@@ -768,7 +768,7 @@ stdout of test 'correlated` in directory
[ 3, 1 ]
#SELECT i, CAST((SELECT SUM(i) OVER (ORDER BY i) FROM integers WHERE i1.i=i)
AS BIGINT) FROM integers i1 ORDER BY i;
% .i1, . # table_name
-% i, %2 # name
+% i, %10 # name
% int, bigint # type
% 1, 1 # length
[ NULL, NULL ]
@@ -1088,32 +1088,32 @@ stdout of test 'correlated` in directory
[ 2, 4 ]
[ 3, 6 ]
#SELECT NULL IN (SELECT * FROM strings WHERE v=s1.v) FROM strings s1 ORDER BY
v;
-% .%4 # table_name
-% %4 # name
+% .%5 # table_name
+% %5 # name
% boolean # type
% 5 # length
[ false ]
[ NULL ]
[ NULL ]
#SELECT 'hello' IN (SELECT * FROM strings WHERE v=s1.v) FROM strings s1 ORDER
BY v;
-% .%4 # table_name
-% %4 # name
+% .%5 # table_name
+% %5 # name
% boolean # type
% 5 # length
[ false ]
[ true ]
[ false ]
#SELECT 'hello' IN (SELECT * FROM strings WHERE v=s1.v or v IS NULL) FROM
strings s1 ORDER BY v;
-% .%4 # table_name
-% %4 # name
+% .%5 # table_name
+% %5 # name
% boolean # type
% 5 # length
[ NULL ]
[ true ]
[ NULL ]
#SELECT 'bla' IN (SELECT * FROM strings WHERE v=s1.v or v IS NULL) FROM
strings s1 ORDER BY v;
-% .%4 # table_name
-% %4 # name
+% .%5 # table_name
+% %5 # name
% boolean # type
% 5 # length
[ NULL ]
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
