You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@datafusion.apache.org by gi...@apache.org on 2026/08/11 21:02:29 UTC
(datafusion) 01/01: fix: Propagate NULLs in `regexp_count`, `regexp_instr` (#24239)
This is an automated email from the ASF dual-hosted git repository.
github-merge-queue[bot] pushed a commit to branch gh-readonly-queue/main/pr-24239-fc4e43e7499f28e846b842bc5adba1fe9d11580c
in repository https://gitbox.apache.org/repos/asf/datafusion.git
commit 30ca39bef9ea2e746e143727f9de48b1608edc2a
Author: Neil Conway <ne...@gmail.com>
AuthorDate: Tue Aug 11 17:02:06 2026 -0400
fix: Propagate NULLs in `regexp_count`, `regexp_instr` (#24239)
## Which issue does this PR close?
- Closes #24152
## Rationale for this change
`regexp_count` and `regexp_instr` should follow the behavior of these
functions in PostgreSQL and return NULL when any of their arguments is
NULL. The previous behavior was inconsistent: `regexp_instr` returned
NULL for a NULL string or pattern, but otherwise NULLs were treated as a
default value, returned a match count of 0, or raised an error,
depending on the context.
## What changes are included in this PR?
* Make NULL handling of `regexp_count` and `regexp_instr` consistent and
match PostgreSQL
* Update SLTs or add new SLTs / unit tests as necessary
## Are these changes tested?
Yes, new tests added and/or existing tests updated when necessary.
## Are there any user-facing changes?
Yes: the behavior of these UDFs has changed when called with NULL
arguments.
---
datafusion/functions/src/regex/regexpcount.rs | 318 ++++++++++++++-------
datafusion/functions/src/regex/regexpinstr.rs | 154 +++++++++-
.../test_files/regexp/regexp_count.slt | 127 ++++----
.../test_files/regexp/regexp_instr.slt | 28 ++
4 files changed, 457 insertions(+), 170 deletions(-)
diff --git a/datafusion/functions/src/regex/regexpcount.rs b/datafusion/functions/src/regex/regexpcount.rs
index 0ecedc7cbb..8e8b4436e3 100644
--- a/datafusion/functions/src/regex/regexpcount.rs
+++ b/datafusion/functions/src/regex/regexpcount.rs
@@ -262,48 +262,37 @@ fn regexp_count_inner<'a, S>(
where
S: StringArrayType<'a>,
{
- let (regex_scalar, is_regex_scalar) = if is_regex_scalar || regex_array.len() == 1 {
- (
- (!regex_array.is_null(0)).then(|| regex_array.value(0)),
- true,
- )
+ // Treat single-element arrays as scalars, broadcast to every row. An
+ // absent optional argument behaves like a scalar set to its default.
+ let is_regex_scalar = is_regex_scalar || regex_array.len() == 1;
+ let is_start_scalar =
+ start_array.is_none_or(|array| is_start_scalar || array.len() == 1);
+ let is_flags_scalar =
+ flags_array.is_none_or(|array| is_flags_scalar || array.len() == 1);
+
+ // A NULL in any scalar argument produces a NULL result for every row
+ if (is_regex_scalar && regex_array.is_null(0))
+ || (is_start_scalar && start_array.is_some_and(|array| array.is_null(0)))
+ || (is_flags_scalar && flags_array.is_some_and(|array| array.is_null(0)))
+ {
+ return Ok(Arc::new(Int64Array::new_null(values.len())));
+ }
+
+ let regex_scalar = is_regex_scalar.then(|| regex_array.value(0));
+ // An absent `start` defaults to 1
+ let start_scalar =
+ is_start_scalar.then(|| start_array.map_or(1, |array| array.value(0)));
+ // A `flags_scalar` of None means no flags were supplied
+ let flags_scalar = if is_flags_scalar {
+ flags_array.map(|array| array.value(0))
} else {
- (None, false)
+ None
};
- let (start_array, start_scalar, is_start_scalar) =
- if let Some(start_array) = start_array {
- if is_start_scalar || start_array.len() == 1 {
- (None, Some(start_array.value(0)), true)
- } else {
- (Some(start_array), None, false)
- }
- } else {
- (None, Some(1), true)
- };
-
- let (flags_array, flags_scalar, is_flags_scalar) =
- if let Some(flags_array) = flags_array {
- if is_flags_scalar || flags_array.len() == 1 {
- (None, Some(flags_array.value(0)), true)
- } else {
- (Some(flags_array), None, false)
- }
- } else {
- (None, None, true)
- };
-
let mut regex_cache = HashMap::new();
- match (is_regex_scalar, is_start_scalar, is_flags_scalar) {
- (true, true, true) => {
- let regex = match regex_scalar {
- None => {
- return Ok(Arc::new(Int64Array::from(vec![0; values.len()])));
- }
- Some(regex) => regex,
- };
-
+ match (regex_scalar, is_start_scalar, is_flags_scalar) {
+ (Some(regex), true, true) => {
let pattern = compile_regex(regex, flags_scalar)?;
Ok(Arc::new(
@@ -313,14 +302,7 @@ where
.collect::<Result<Int64Array, ArrowError>>()?,
))
}
- (true, true, false) => {
- let regex = match regex_scalar {
- None => {
- return Ok(Arc::new(Int64Array::from(vec![0; values.len()])));
- }
- Some(regex) => regex,
- };
-
+ (Some(regex), true, false) => {
let flags_array = flags_array.unwrap();
if values.len() != flags_array.len() {
return Err(ArrowError::ComputeError(format!(
@@ -335,21 +317,21 @@ where
.iter()
.zip(flags_array.iter())
.map(|(value, flags)| {
- let pattern =
- compile_and_cache_regex(regex, flags, &mut regex_cache)?;
+ let Some(flags) = flags else {
+ return Ok(None);
+ };
+
+ let pattern = compile_and_cache_regex(
+ regex,
+ Some(flags),
+ &mut regex_cache,
+ )?;
count_matches(value, pattern, start_scalar)
})
.collect::<Result<Int64Array, ArrowError>>()?,
))
}
- (true, false, true) => {
- let regex = match regex_scalar {
- None => {
- return Ok(Arc::new(Int64Array::from(vec![0; values.len()])));
- }
- Some(regex) => regex,
- };
-
+ (Some(regex), false, true) => {
let pattern = compile_regex(regex, flags_scalar)?;
let start_array = start_array.unwrap();
@@ -362,14 +344,7 @@ where
.collect::<Result<Int64Array, ArrowError>>()?,
))
}
- (true, false, false) => {
- let regex = match regex_scalar {
- None => {
- return Ok(Arc::new(Int64Array::from(vec![0; values.len()])));
- }
- Some(regex) => regex,
- };
-
+ (Some(regex), false, false) => {
let flags_array = flags_array.unwrap();
if values.len() != flags_array.len() {
return Err(ArrowError::ComputeError(format!(
@@ -386,15 +361,19 @@ where
flags_array.iter()
)
.map(|(value, start, flags)| {
+ let Some(flags) = flags else {
+ return Ok(None);
+ };
+
let pattern =
- compile_and_cache_regex(regex, flags, &mut regex_cache)?;
+ compile_and_cache_regex(regex, Some(flags), &mut regex_cache)?;
count_matches(value, pattern, start)
})
.collect::<Result<Int64Array, ArrowError>>()?,
))
}
- (false, true, true) => {
+ (None, true, true) => {
if values.len() != regex_array.len() {
return Err(ArrowError::ComputeError(format!(
"regex_array must be the same length as values array; got {} and {}",
@@ -408,9 +387,8 @@ where
.iter()
.zip(regex_array.iter())
.map(|(value, regex)| {
- let regex = match regex {
- None => return Ok(0),
- Some(regex) => regex,
+ let Some(regex) = regex else {
+ return Ok(None);
};
let pattern = compile_and_cache_regex(
@@ -423,7 +401,7 @@ where
.collect::<Result<Int64Array, ArrowError>>()?,
))
}
- (false, true, false) => {
+ (None, true, false) => {
if values.len() != regex_array.len() {
return Err(ArrowError::ComputeError(format!(
"regex_array must be the same length as values array; got {} and {}",
@@ -444,20 +422,22 @@ where
Ok(Arc::new(
izip!(values.iter(), regex_array.iter(), flags_array.iter())
.map(|(value, regex, flags)| {
- let regex = match regex {
- None => return Ok(0),
- Some(regex) => regex,
+ let (Some(regex), Some(flags)) = (regex, flags) else {
+ return Ok(None);
};
- let pattern =
- compile_and_cache_regex(regex, flags, &mut regex_cache)?;
+ let pattern = compile_and_cache_regex(
+ regex,
+ Some(flags),
+ &mut regex_cache,
+ )?;
count_matches(value, pattern, start_scalar)
})
.collect::<Result<Int64Array, ArrowError>>()?,
))
}
- (false, false, true) => {
+ (None, false, true) => {
if values.len() != regex_array.len() {
return Err(ArrowError::ComputeError(format!(
"regex_array must be the same length as values array; got {} and {}",
@@ -478,9 +458,8 @@ where
Ok(Arc::new(
izip!(values.iter(), regex_array.iter(), start_array.iter())
.map(|(value, regex, start)| {
- let regex = match regex {
- None => return Ok(0),
- Some(regex) => regex,
+ let Some(regex) = regex else {
+ return Ok(None);
};
let pattern = compile_and_cache_regex(
@@ -493,7 +472,7 @@ where
.collect::<Result<Int64Array, ArrowError>>()?,
))
}
- (false, false, false) => {
+ (None, false, false) => {
if values.len() != regex_array.len() {
return Err(ArrowError::ComputeError(format!(
"regex_array must be the same length as values array; got {} and {}",
@@ -528,13 +507,12 @@ where
flags_array.iter()
)
.map(|(value, regex, start, flags)| {
- let regex = match regex {
- None => return Ok(0),
- Some(regex) => regex,
+ let (Some(regex), Some(flags)) = (regex, flags) else {
+ return Ok(None);
};
let pattern =
- compile_and_cache_regex(regex, flags, &mut regex_cache)?;
+ compile_and_cache_regex(regex, Some(flags), &mut regex_cache)?;
count_matches(value, pattern, start)
})
.collect::<Result<Int64Array, ArrowError>>()?,
@@ -547,28 +525,23 @@ fn count_matches(
value: Option<&str>,
pattern: &Regex,
start: Option<i64>,
-) -> Result<i64, ArrowError> {
- let value = match value {
- None => return Ok(0),
- Some(value) => value,
+) -> Result<Option<i64>, ArrowError> {
+ // A NULL value or start position produces a NULL result.
+ let (Some(value), Some(start)) = (value, start) else {
+ return Ok(None);
};
- if let Some(start) = start {
- if start < 1 {
- return Err(ArrowError::ComputeError(
- "regexp_count() requires start to be 1 based".to_string(),
- ));
- }
-
- let Some(byte_offset) = start_to_byte_offset(value, start) else {
- return Ok(0);
- };
- let count = pattern.find_iter(&value[byte_offset..]).count();
- Ok(count as i64)
- } else {
- let count = pattern.find_iter(value).count();
- Ok(count as i64)
+ if start < 1 {
+ return Err(ArrowError::ComputeError(
+ "regexp_count() requires start to be 1 based".to_string(),
+ ));
}
+
+ let Some(byte_offset) = start_to_byte_offset(value, start) else {
+ return Ok(Some(0));
+ };
+ let count = pattern.find_iter(&value[byte_offset..]).count();
+ Ok(Some(count as i64))
}
#[cfg(test)]
@@ -603,6 +576,24 @@ mod tests {
test_case_sensitive_regexp_count_array_complex::<StringViewArray>();
test_case_regexp_count_cache_check::<GenericStringArray<i32>>();
+
+ test_regexp_count_null_scalars();
+
+ test_regexp_count_null_array_rows::<GenericStringArray<i32>>();
+ test_regexp_count_null_array_rows::<GenericStringArray<i64>>();
+ test_regexp_count_null_array_rows::<StringViewArray>();
+
+ test_regexp_count_null_start_array::<GenericStringArray<i32>>();
+ test_regexp_count_null_start_array::<GenericStringArray<i64>>();
+ test_regexp_count_null_start_array::<StringViewArray>();
+
+ test_regexp_count_null_flags_array::<GenericStringArray<i32>>();
+ test_regexp_count_null_flags_array::<GenericStringArray<i64>>();
+ test_regexp_count_null_flags_array::<StringViewArray>();
+
+ test_regexp_count_null_scalar_regex_array_values::<GenericStringArray<i32>>();
+ test_regexp_count_null_scalar_regex_array_values::<GenericStringArray<i64>>();
+ test_regexp_count_null_scalar_regex_array_values::<StringViewArray>();
}
fn regexp_count_with_scalar_values(args: &[ScalarValue]) -> Result<ColumnarValue> {
@@ -966,6 +957,129 @@ mod tests {
assert_eq!(re.as_ref(), &expected);
}
+ fn test_regexp_count_null_scalars() {
+ // A NULL in any scalar argument produces a NULL result.
+ let cases: Vec<Vec<ScalarValue>> = vec![
+ vec![ScalarValue::Utf8(None), ScalarValue::Utf8(None)],
+ vec![
+ ScalarValue::Utf8(None),
+ ScalarValue::Utf8(Some("abc".to_string())),
+ ScalarValue::Int64(Some(1)),
+ ScalarValue::Utf8(Some("i".to_string())),
+ ],
+ vec![
+ ScalarValue::Utf8(Some("abc".to_string())),
+ ScalarValue::Utf8(None),
+ ScalarValue::Int64(Some(1)),
+ ScalarValue::Utf8(Some("i".to_string())),
+ ],
+ vec![
+ ScalarValue::Utf8(Some("abc".to_string())),
+ ScalarValue::Utf8(Some("abc".to_string())),
+ ScalarValue::Int64(None),
+ ScalarValue::Utf8(Some("i".to_string())),
+ ],
+ vec![
+ ScalarValue::Utf8(Some("abc".to_string())),
+ ScalarValue::Utf8(Some("abc".to_string())),
+ ScalarValue::Int64(Some(1)),
+ ScalarValue::Utf8(None),
+ ],
+ ];
+
+ for args in cases {
+ let re = regexp_count_with_scalar_values(&args);
+ match re {
+ Ok(ColumnarValue::Scalar(ScalarValue::Int64(v))) => {
+ assert_eq!(v, None, "regexp_count null scalar test failed");
+ }
+ _ => panic!("Unexpected result"),
+ }
+ }
+ }
+
+ fn test_regexp_count_null_array_rows<A>()
+ where
+ A: From<Vec<Option<&'static str>>> + Array + 'static,
+ {
+ let values = A::from(vec![
+ None,
+ Some("abc"),
+ Some("abc"),
+ Some("abc"),
+ Some("abc"),
+ ]);
+ let regex = A::from(vec![
+ Some("abc"),
+ None,
+ Some("abc"),
+ Some("abc"),
+ Some("abc"),
+ ]);
+ let start = Int64Array::from(vec![Some(1), Some(1), None, Some(1), Some(1)]);
+ let flags = A::from(vec![Some("i"), Some("i"), Some("i"), None, Some("i")]);
+
+ let expected = Int64Array::from(vec![None, None, None, None, Some(1)]);
+
+ let re = regexp_count_func(&[
+ Arc::new(values),
+ Arc::new(regex),
+ Arc::new(start),
+ Arc::new(flags),
+ ])
+ .unwrap();
+ assert_eq!(re.as_ref(), &expected);
+ }
+
+ fn test_regexp_count_null_start_array<A>()
+ where
+ A: From<Vec<&'static str>> + Array + 'static,
+ {
+ let values = A::from(vec!["abc", "abcb"]);
+ let regex = A::from(vec!["b"]);
+ let start = Int64Array::from(vec![Some(1), None]);
+
+ let expected = Int64Array::from(vec![Some(1), None]);
+
+ let re = regexp_count_func(&[Arc::new(values), Arc::new(regex), Arc::new(start)])
+ .unwrap();
+ assert_eq!(re.as_ref(), &expected);
+ }
+
+ fn test_regexp_count_null_flags_array<A>()
+ where
+ A: From<Vec<&'static str>> + From<Vec<Option<&'static str>>> + Array + 'static,
+ {
+ let values: A = vec!["aB", "aB"].into();
+ let regex: A = vec!["b"].into();
+ let start = Int64Array::from(vec![1]);
+ let flags: A = vec![None, Some("i")].into();
+
+ let expected = Int64Array::from(vec![None, Some(1)]);
+
+ let re = regexp_count_func(&[
+ Arc::new(values),
+ Arc::new(regex),
+ Arc::new(start),
+ Arc::new(flags),
+ ])
+ .unwrap();
+ assert_eq!(re.as_ref(), &expected);
+ }
+
+ fn test_regexp_count_null_scalar_regex_array_values<A>()
+ where
+ A: From<Vec<&'static str>> + From<Vec<Option<&'static str>>> + Array + 'static,
+ {
+ let values: A = vec!["abc", "abcabc"].into();
+ let regex: A = vec![Option::<&str>::None].into();
+
+ let expected = Int64Array::from(vec![None::<i64>, None]);
+
+ let re = regexp_count_func(&[Arc::new(values), Arc::new(regex)]).unwrap();
+ assert_eq!(re.as_ref(), &expected);
+ }
+
fn test_case_regexp_count_cache_check<A>()
where
A: From<Vec<&'static str>> + Array + 'static,
diff --git a/datafusion/functions/src/regex/regexpinstr.rs b/datafusion/functions/src/regex/regexpinstr.rs
index 30385672df..96152297fb 100644
--- a/datafusion/functions/src/regex/regexpinstr.rs
+++ b/datafusion/functions/src/regex/regexpinstr.rs
@@ -18,6 +18,7 @@
use arrow::array::{
Array, ArrayRef, AsArray, Datum, Int64Array, Int64Builder, StringArrayType,
};
+use arrow::buffer::NullBuffer;
use arrow::datatypes::{DataType, Int64Type};
use arrow::datatypes::{
DataType::Int64, DataType::LargeUtf8, DataType::Utf8, DataType::Utf8View,
@@ -291,27 +292,28 @@ where
let mut regex_cache = RegexCache::default();
let mut result = Int64Builder::with_capacity(len);
+ // A NULL in any argument produces a NULL result
+ let nulls = NullBuffer::union_many([
+ values.nulls(),
+ regex_array.nulls(),
+ start_array.and_then(|array| array.nulls()),
+ nth_array.and_then(|array| array.nulls()),
+ flags_array.and_then(|array| array.nulls()),
+ subexp_array.and_then(|array| array.nulls()),
+ ]);
+
for i in 0..len {
- if regex_array.is_null(i) {
+ if nulls.as_ref().is_some_and(|nulls| nulls.is_null(i)) {
result.append_null();
continue;
}
- let regex = regex_array.value(i);
- if values.is_null(i) {
- result.append_null();
- continue;
- }
let value = values.value(i);
-
- let flags = match flags_array {
- Some(flags) if !flags.is_null(i) => Some(flags.value(i)),
- _ => None,
- };
+ let regex = regex_array.value(i);
+ let flags = flags_array.map(|array| array.value(i));
let pattern = regex_cache.get_or_compile(regex, flags)?;
- // The defaults apply when the optional argument was not supplied at
- // all. A supplied but null slot reads through as its raw buffer value.
+ // The defaults apply when the optional argument was not supplied.
let start = start_array.map_or(1, |array| array.value(i));
let nth = nth_array.map_or(1, |array| array.value(i));
let subexp = subexp_array.map_or(0, |array| array.value(i));
@@ -443,6 +445,12 @@ mod tests {
test_case_sensitive_regexp_instr_zero_width_pattern::<GenericStringArray<i32>>();
test_case_sensitive_regexp_instr_zero_width_pattern::<GenericStringArray<i64>>();
test_case_sensitive_regexp_instr_zero_width_pattern::<StringViewArray>();
+
+ test_regexp_instr_null_scalar_args();
+
+ test_regexp_instr_null_array_rows::<GenericStringArray<i32>>();
+ test_regexp_instr_null_array_rows::<GenericStringArray<i64>>();
+ test_regexp_instr_null_array_rows::<StringViewArray>();
}
fn regexp_instr_with_scalar_values(args: &[ScalarValue]) -> Result<ColumnarValue> {
@@ -764,6 +772,126 @@ mod tests {
});
}
+ fn test_regexp_instr_null_scalar_args() {
+ // A NULL in any argument produces a NULL result
+ let cases: Vec<Vec<ScalarValue>> = vec![
+ // NULL start
+ vec![
+ ScalarValue::Utf8(Some("abc".to_string())),
+ ScalarValue::Utf8(Some("b".to_string())),
+ ScalarValue::Int64(None),
+ ],
+ // NULL N
+ vec![
+ ScalarValue::Utf8(Some("abc".to_string())),
+ ScalarValue::Utf8(Some("b".to_string())),
+ ScalarValue::Int64(Some(1)),
+ ScalarValue::Int64(None),
+ ],
+ // NULL flags
+ vec![
+ ScalarValue::Utf8(Some("abc".to_string())),
+ ScalarValue::Utf8(Some("b".to_string())),
+ ScalarValue::Int64(Some(1)),
+ ScalarValue::Int64(Some(1)),
+ ScalarValue::Utf8(None),
+ ],
+ // NULL subexpr
+ vec![
+ ScalarValue::Utf8(Some("abc".to_string())),
+ ScalarValue::Utf8(Some("(b)".to_string())),
+ ScalarValue::Int64(Some(1)),
+ ScalarValue::Int64(Some(1)),
+ ScalarValue::Utf8(Some("i".to_string())),
+ ScalarValue::Int64(None),
+ ],
+ ];
+
+ for args in cases {
+ let re = regexp_instr_with_scalar_values(&args);
+ match re {
+ Ok(ColumnarValue::Scalar(ScalarValue::Int64(v))) => {
+ assert_eq!(v, None, "regexp_instr null scalar test failed");
+ }
+ _ => panic!("Unexpected result"),
+ }
+ }
+ }
+
+ fn test_regexp_instr_null_array_rows<A>()
+ where
+ A: From<Vec<Option<&'static str>>> + Array + 'static,
+ {
+ let values = A::from(vec![
+ None,
+ Some("abc"),
+ Some("abc"),
+ Some("abc"),
+ Some("abc"),
+ Some("abc"),
+ Some("abc"),
+ ]);
+ let regex = A::from(vec![
+ Some("b"),
+ None,
+ Some("b"),
+ Some("b"),
+ Some("b"),
+ Some("(b)"),
+ Some("b"),
+ ]);
+ let start = Int64Array::from(vec![
+ Some(1),
+ Some(1),
+ None,
+ Some(1),
+ Some(1),
+ Some(1),
+ Some(1),
+ ]);
+ let nth = Int64Array::from(vec![
+ Some(1),
+ Some(1),
+ Some(1),
+ None,
+ Some(1),
+ Some(1),
+ Some(1),
+ ]);
+ let flags = A::from(vec![
+ Some(""),
+ Some(""),
+ Some(""),
+ Some(""),
+ None,
+ Some("i"),
+ Some(""),
+ ]);
+ let subexp = Int64Array::from(vec![
+ Some(0),
+ Some(0),
+ Some(0),
+ Some(0),
+ Some(0),
+ None,
+ Some(0),
+ ]);
+
+ let expected =
+ Int64Array::from(vec![None, None, None, None, None, None, Some(2)]);
+
+ let re = regexp_instr_func(&[
+ Arc::new(values),
+ Arc::new(regex),
+ Arc::new(start),
+ Arc::new(nth),
+ Arc::new(flags),
+ Arc::new(subexp),
+ ])
+ .unwrap();
+ assert_eq!(re.as_ref(), &expected);
+ }
+
fn test_case_sensitive_regexp_instr_array<A>()
where
A: From<Vec<&'static str>> + Array + 'static,
diff --git a/datafusion/sqllogictest/test_files/regexp/regexp_count.slt b/datafusion/sqllogictest/test_files/regexp/regexp_count.slt
index 1fd43eeb46..0b2b9e5e74 100644
--- a/datafusion/sqllogictest/test_files/regexp/regexp_count.slt
+++ b/datafusion/sqllogictest/test_files/regexp/regexp_count.slt
@@ -106,7 +106,7 @@ SELECT regexp_count('123123123123', '123', 1, 'g');
query I
SELECT regexp_count(str, '\w') from regexp_test_data;
----
-0
+NULL
3
3
3
@@ -122,7 +122,7 @@ SELECT regexp_count(str, '\w') from regexp_test_data;
query I
SELECT regexp_count(str, '\w{2}', start) from regexp_test_data;
----
-0
+NULL
1
1
1
@@ -138,7 +138,7 @@ SELECT regexp_count(str, '\w{2}', start) from regexp_test_data;
query I
SELECT regexp_count(str, 'ab', 1, 'i') from regexp_test_data;
----
-0
+NULL
1
1
1
@@ -155,7 +155,7 @@ SELECT regexp_count(str, 'ab', 1, 'i') from regexp_test_data;
query I
SELECT regexp_count(str, pattern) from regexp_test_data;
----
-0
+NULL
1
1
0
@@ -171,7 +171,7 @@ SELECT regexp_count(str, pattern) from regexp_test_data;
query I
SELECT regexp_count(str, pattern, start) from regexp_test_data;
----
-0
+NULL
1
1
0
@@ -187,35 +187,35 @@ SELECT regexp_count(str, pattern, start) from regexp_test_data;
query I
SELECT regexp_count(str, pattern, start, flags) from regexp_test_data;
----
-0
-1
-1
-1
-0
-0
-0
-0
-1
+NULL
1
1
1
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
# test type coercion
query I
SELECT regexp_count(arrow_cast(str, 'Utf8'), arrow_cast(pattern, 'LargeUtf8'), arrow_cast(start, 'Int32'), flags) from regexp_test_data;
----
-0
-1
-1
-1
-0
-0
-0
-0
-1
+NULL
1
1
1
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
# test string views
@@ -226,7 +226,7 @@ SELECT arrow_cast(str, 'Utf8View') as str, arrow_cast(pattern, 'Utf8View') as pa
query I
SELECT regexp_count(str, '\w') from t_stringview;
----
-0
+NULL
3
3
3
@@ -242,7 +242,7 @@ SELECT regexp_count(str, '\w') from t_stringview;
query I
SELECT regexp_count(str, '\w{2}', start) from t_stringview;
----
-0
+NULL
1
1
1
@@ -258,7 +258,7 @@ SELECT regexp_count(str, '\w{2}', start) from t_stringview;
query I
SELECT regexp_count(str, 'ab', 1, 'i') from t_stringview;
----
-0
+NULL
1
1
1
@@ -275,7 +275,7 @@ SELECT regexp_count(str, 'ab', 1, 'i') from t_stringview;
query I
SELECT regexp_count(str, pattern) from t_stringview;
----
-0
+NULL
1
1
0
@@ -291,7 +291,7 @@ SELECT regexp_count(str, pattern) from t_stringview;
query I
SELECT regexp_count(str, pattern, start) from t_stringview;
----
-0
+NULL
1
1
0
@@ -307,57 +307,74 @@ SELECT regexp_count(str, pattern, start) from t_stringview;
query I
SELECT regexp_count(str, pattern, start, flags) from t_stringview;
----
-0
-1
-1
-1
-0
-0
-0
-0
-1
+NULL
1
1
1
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
# test type coercion
query I
SELECT regexp_count(arrow_cast(str, 'Utf8'), arrow_cast(pattern, 'LargeUtf8'), arrow_cast(start, 'Int32'), flags) from t_stringview;
----
-0
-1
-1
-1
-0
-0
-0
-0
-1
+NULL
1
1
1
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
-# NULL tests
+# NULL tests: like PostgreSQL, a NULL in any argument produces a NULL result
query I
SELECT regexp_count(NULL, NULL);
----
-0
+NULL
query I
SELECT regexp_count(NULL, 'a');
----
-0
+NULL
query I
SELECT regexp_count('a', NULL);
----
-0
+NULL
query I
SELECT regexp_count(NULL, NULL, NULL, NULL);
----
-0
+NULL
+
+query I
+SELECT regexp_count('abc', 'b', NULL);
+----
+NULL
+
+query I
+SELECT regexp_count('abc', 'b', 1, NULL);
+----
+NULL
+
+# NULL start position in one row of a column
+query I
+SELECT regexp_count(v, 'b', s) FROM (VALUES ('abc', 1), ('abcb', NULL)) AS t(v, s);
+----
+1
+NULL
statement ok
CREATE TABLE empty_table (str varchar, pattern varchar, start int, flags varchar);
@@ -372,10 +389,10 @@ INSERT INTO empty_table VALUES ('a', NULL, 1, 'i'), (NULL, 'a', 1, 'i'), (NULL,
query I
SELECT regexp_count(str, pattern, start, flags) from empty_table;
----
-0
-0
-0
-0
+NULL
+NULL
+NULL
+NULL
statement ok
drop table t_stringview;
diff --git a/datafusion/sqllogictest/test_files/regexp/regexp_instr.slt b/datafusion/sqllogictest/test_files/regexp/regexp_instr.slt
index f54d4e80cc..bbe9693736 100644
--- a/datafusion/sqllogictest/test_files/regexp/regexp_instr.slt
+++ b/datafusion/sqllogictest/test_files/regexp/regexp_instr.slt
@@ -174,6 +174,34 @@ SELECT regexp_instr('a', NULL);
----
NULL
+# Like PostgreSQL, a NULL in any argument produces a NULL result
+query I
+SELECT regexp_instr('abc', 'b', NULL);
+----
+NULL
+
+query I
+SELECT regexp_instr('abc', 'b', 1, NULL);
+----
+NULL
+
+query I
+SELECT regexp_instr('abc', 'b', 1, 1, NULL);
+----
+NULL
+
+query I
+SELECT regexp_instr('abc', '(b)', 1, 1, 'i', NULL);
+----
+NULL
+
+# NULL start position in one row of a column
+query I
+SELECT regexp_instr(v, 'b', s) FROM (VALUES ('abc', 1), ('abcb', NULL)) AS t(v, s);
+----
+2
+NULL
+
query I
SELECT regexp_instr('😀abcdef', 'abc');
----
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org