Changeset: 0acef3664468 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=0acef3664468
Modified Files:
monetdb5/modules/mal/pcre.c
sql/test/miscellaneous/Tests/simple_selects.stable.out
Branch: default
Log Message:
handle null like null etc beter.
diffs (137 lines):
diff --git a/monetdb5/modules/mal/pcre.c b/monetdb5/modules/mal/pcre.c
--- a/monetdb5/modules/mal/pcre.c
+++ b/monetdb5/modules/mal/pcre.c
@@ -1609,7 +1609,9 @@ PCRElike4(bit *ret, const str *s, const
if (!r) {
assert(ppat);
- if (strNil(ppat)) {
+ if (strNil(*pat) || strNil(*s)) {
+ *ret = bit_nil;
+ } else if (strNil(ppat)) {
*ret = FALSE;
if (*isens) {
if (mystrcasecmp(*s, *pat) == 0)
@@ -1654,7 +1656,7 @@ PCREnotlike3(bit *ret, const str *s, con
bit r;
rethrow("str.not_like", tmp, PCRElike3(&r, s, pat, esc));
- *ret = !r;
+ *ret = r==bit_nil?bit_nil:!r;
return MAL_SUCCEED;
}
@@ -1665,7 +1667,7 @@ PCREnotlike2(bit *ret, const str *s, con
bit r;
rethrow("str.not_like", tmp, PCRElike2(&r, s, pat));
- *ret = !r;
+ *ret = r==bit_nil?bit_nil:!r;
return MAL_SUCCEED;
}
@@ -1692,7 +1694,7 @@ PCREnotilike3(bit *ret, const str *s, co
bit r;
rethrow("str.not_ilike", tmp, PCREilike3(&r, s, pat, esc));
- *ret = !r;
+ *ret = r==bit_nil?bit_nil:!r;
return MAL_SUCCEED;
}
@@ -1703,7 +1705,7 @@ PCREnotilike2(bit *ret, const str *s, co
bit r;
rethrow("str.not_ilike", tmp, PCREilike2(&r, s, pat));
- *ret = !r;
+ *ret = r==bit_nil?bit_nil:!r;
return MAL_SUCCEED;
}
@@ -1734,7 +1736,10 @@ BATPCRElike3(bat *ret, const bat *bid, c
br = (bit*)Tloc(r, 0);
strsi = bat_iterator(strs);
- if (strNil(ppat)) {
+ if (strNil(*pat)) {
+ BATloop(strs, p, q)
+ br[i] = bit_nil;
+ } else if (strNil(ppat)) {
BATloop(strs, p, q) {
const char *s = (str)BUNtvar(strsi, p);
@@ -1908,6 +1913,7 @@ PCRElikeselect2(bat *ret, const bat *bid
char *ppat = NULL;
bool use_re = false;
bool use_strcmp = false;
+ bool empty = false;
if ((b = BATdescriptor(*bid)) == NULL) {
throw(MAL, "algebra.likeselect", SQLSTATE(HY002)
RUNTIME_OBJECT_MISSING);
@@ -1918,7 +1924,9 @@ PCRElikeselect2(bat *ret, const bat *bid
}
/* no escape, try if a simple list of keywords works */
- if (is_strcmpable(*pat, *esc)) {
+ if (strNil(*pat)) {
+ empty = true;
+ } else if (is_strcmpable(*pat, *esc)) {
use_re = true;
use_strcmp = true;
} else if (re_simple(*pat, **esc == '\200' ? 0 : (unsigned char)
**esc)) {
@@ -1953,7 +1961,10 @@ PCRElikeselect2(bat *ret, const bat *bid
res = re_likeselect(&bn, b, s, *pat, (bool) *caseignore, (bool)
*anti, use_strcmp, **esc == '\200' ? 0 : (unsigned char) **esc);
} else if (ppat == NULL) {
/* no pattern and no special characters: can use normal select
*/
- bn = BATselect(b, s, *pat, NULL, true, true, *anti);
+ if (empty)
+ bn = BATdense(0, 0, 0);
+ else
+ bn = BATselect(b, s, *pat, NULL, true, true, *anti);
if (bn == NULL)
res = createException(MAL, "algebra.likeselect",
GDK_EXCEPTION);
else
diff --git a/sql/test/miscellaneous/Tests/simple_selects.stable.out
b/sql/test/miscellaneous/Tests/simple_selects.stable.out
--- a/sql/test/miscellaneous/Tests/simple_selects.stable.out
+++ b/sql/test/miscellaneous/Tests/simple_selects.stable.out
@@ -305,6 +305,40 @@ stdout of test 'simple_selects` in direc
% clob # type
% 0 # length
[ NULL ]
+#select 'a' like null, null like 'a', null like null, 'a' ilike null, null
ilike 'a', null ilike null,
+# 'a' not like null, null not like 'a', null not like null, 'a' not
ilike null, null not ilike 'a', null not ilike null; --all NULL
+% ., ., ., ., ., ., ., ., ., .,
., . # table_name
+% %1, %2, %3, %4, %5, %6, %7, %10, %11, %12,
%13, %14 # name
+% boolean, boolean, boolean, boolean, boolean,
boolean, boolean, boolean, boolean, boolean,
boolean, boolean # type
+% 5, 5, 5, 5, 5, 5, 5, 5, 5, 5,
5, 5 # length
+[ NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL ]
+#create table x (x varchar(32));
+#insert into x values (null), ('a');
+[ 2 ]
+#select x like null, null like x, null like null, x ilike null, null ilike x,
null ilike null,
+# x not like null, null not like x, null not like null, x not ilike
null, null not ilike x, null not ilike null from x;
+% sys., ., ., sys., ., ., sys., ., .,
sys., ., . # table_name
+% %1, %2, %3, %4, %5, %6, %7, %10, %11, %12,
%13, %14 # name
+% boolean, boolean, boolean, boolean, boolean,
boolean, boolean, boolean, boolean, boolean,
boolean, boolean # type
+% 5, 5, 5, 5, 5, 5, 5, 5, 5, 5,
5, 5 # length
+#select x like x, x ilike x, x not like x, x not ilike x from x;
+% sys., sys., sys., sys. # table_name
+% %1, %2, %3, %4 # name
+% boolean, boolean, boolean, boolean # type
+% 5, 5, 5, 5 # length
+[ NULL, NULL, NULL, NULL ]
+[ true, true, false, false ]
+#select x1.x from x x1 inner join x x2 on x1.x not like x2.x; --empty
+% sys.x1 # table_name
+% x # name
+% varchar # type
+% 0 # length
+#select i from (values (1),(2),(3),(NULL)) as integers(i) where not cast(i as
varchar(32)) like null; --empty
+% .integers # table_name
+% i # name
+% tinyint # type
+% 1 # length
+#drop table x;
# 17:31:38 >
# 17:31:38 > "Done."
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
