You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "juju4 (via GitHub)" <gi...@apache.org> on 2025/04/27 21:39:57 UTC
[I] bug: regexp_match not working? [datafusion]
juju4 opened a new issue, #15872:
URL: https://github.com/apache/datafusion/issues/15872
### Describe the bug
From https://github.com/openobserve/openobserve/discussions/6584
regexp_match does not seem to work with length or space matches. see below.
### To Reproduce
```
$ mkdir ~/.cargo/tmp; TMPDIR=~/.cargo/tmp cargo install datafusion-cli
$ datafusion-cli
DataFusion CLI v47.0.0
> select * from 'test-datafusion.csv';
+---+-------+
| a | b |
+---+-------+
| 1 | one |
| 2 | two |
| 3 | three |
+---+-------+
3 row(s) fetched.
Elapsed 0.006 seconds.
> select * from 'test-datafusion.csv' where regexp_match(a, '(.){4,}');
Error during planning: Cannot create filter with non-boolean predicate 'regexp_match(test-datafusion.csv.a, Utf8("(.){4,}"))' returning List(Field { name: "item", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} })
> select * from 'test-datafusion.csv';
+---+--------------+
| a | b |
+---+--------------+
| 1 | one |
| 2 | two |
| 3 | three |
| 3 | four -O test |
+---+--------------+
4 row(s) fetched.
Elapsed 0.010 seconds.
> select * from 'test-datafusion.csv' where regexp_match(b, '(.){4,}');
Error during planning: Cannot create filter with non-boolean predicate 'regexp_match(test-datafusion.csv.b, Utf8("(.){4,}"))' returning List(Field { name: "item", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} })
> select * from 'test-datafusion.csv' where regexp_match(b, '\s-O\s');
Error during planning: Cannot create filter with non-boolean predicate 'regexp_match(test-datafusion.csv.b, Utf8("\s-O\s"))' returning List(Field { name: "item", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} })
```
### Expected behavior
both should return one result with no error.
### Additional context
_No response_
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] bug: regexp_match not working? [datafusion]
Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on issue #15872:
URL: https://github.com/apache/datafusion/issues/15872#issuecomment-2843008738
Can someone update the title of this issue to reflect the true nature of the enhancement? I don't think this has anything specifically to do with regexp_match except that was how it was uncovered...
```sql
> create table test (a int, b varchar) as values (1, 'one'), (2, 'two'), (3, 'three');
> select regexp_match(test.b, '(.){4,}') from test;
+--------------------------------------+
| regexp_match(test.b,Utf8("(.){4,}")) |
+--------------------------------------+
| NULL |
| NULL |
| [e] |
+--------------------------------------+
3 row(s) fetched.
Elapsed 0.001 seconds.
> select regexp_match(test.b, '\\s-O\\s') from test;
+-------------------------------------+
| regexp_match(test.b,Utf8("\s-O\s")) |
+-------------------------------------+
| NULL |
| NULL |
| NULL |
+-------------------------------------+
3 row(s) fetched.
Elapsed 0.005 seconds.
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] bug: regexp_match not working? [datafusion]
Posted by "jayzhan211 (via GitHub)" <gi...@apache.org>.
jayzhan211 commented on issue #15872:
URL: https://github.com/apache/datafusion/issues/15872#issuecomment-2833729992
It seems like the syntax you mentioned is not supported yet
```
statement count 0
create table t(a varchar) as values ('a'), ('b');
query error DataFusion error: Error during planning: Cannot create filter with non\-boolean predicate 'regexp_match\(t\.a, Utf8\("a"\)\)' returning List\(Field \{ name: "item", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: \{\} \}\)
select * from t where regexp_match(a, 'a');
query ?
select regexp_match(a, 'a') from t;
----
[a]
NULL
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] support `regexp_match` as predicate [datafusion]
Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on issue #15872:
URL: https://github.com/apache/datafusion/issues/15872#issuecomment-2850904327
> ah, regexp_like() is good. Thanks [@Omega359](https://github.com/Omega359) But it makes me confused why some functions works in both select and where (regexp_like) and some not (regexp_match, probably others). and not something that I have seen in documentation.
regexp_like returns booleans, the others do not. Where requires it's clauses to evaluate to boolean.
```sql
select * from t where regexp_match(a, 'a') is not null;
```
may also work ... untested though.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] bug: regexp_match not working? [datafusion]
Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on issue #15872:
URL: https://github.com/apache/datafusion/issues/15872#issuecomment-2843022789
Note that this syntax is supported in DuckDB:
```sql
D create table test (a int, b varchar);
D insert into test values (1, 'one');
D insert into test values (2, 'two');
D insert into test values (3, 'three');
D select * from test where regexp_matches(b, '(.){4,}');
┌───────┬─────────┐
│ a │ b │
│ int32 │ varchar │
├───────┼─────────┤
│ 3 │ three │
└───────┴─────────┘
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] support `regexp_match` as predicate [datafusion]
Posted by "juju4 (via GitHub)" <gi...@apache.org>.
juju4 commented on issue #15872:
URL: https://github.com/apache/datafusion/issues/15872#issuecomment-2849384492
ah, regexp_like() is good. Thanks @Omega359
But it makes me confused why some functions works in both select and where (regexp_like) and some not (regexp_match, probably others). and not something that I have seen in documentation.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] support `regexp_match` as predicate [datafusion]
Posted by "juju4 (via GitHub)" <gi...@apache.org>.
juju4 commented on issue #15872:
URL: https://github.com/apache/datafusion/issues/15872#issuecomment-2870214294
it does or need subquery to evaluate
```
> select * from 'test-datafusion.csv' where regexp_match(b, '-O') is not null;
+---+--------------+
| a | b |
+---+--------------+
| 3 | four -O test |
+---+--------------+
1 row(s) fetched.
Elapsed 0.071 seconds.
> select regexp_match(b, '(.){4,}') as res from 'test-datafusion.csv' WHERE res IS NOT NULL;
Schema error: No field named res. Valid fields are "test-datafusion.csv".a, "test-datafusion.csv".b.
> select regexp_match(b, '\s-O\s') from 'test-datafusion.csv';
+----------------------------------------------------+
| regexp_match(test-datafusion.csv.b,Utf8("\s-O\s")) |
+----------------------------------------------------+
| NULL |
| NULL |
| NULL |
| [ -O ] |
+----------------------------------------------------+
4 row(s) fetched.
Elapsed 0.010 seconds.
> select b from (select b,regexp_match(b, '\s-O\s') as r from 'test-datafusion.csv') where r IS NOT NULL;
+--------------+
| b |
+--------------+
| four -O test |
+--------------+
1 row(s) fetched.
Elapsed 0.013 seconds.
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] support `regexp_match` as predicate [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #15872:
URL: https://github.com/apache/datafusion/issues/15872#issuecomment-3254229568
I think the error is pretty clear -- regexp_match, despite its name, returns the actual *match* (the list of matches)
We could probably add some sort of automatic coercion from List --> boolean by applying `r IS NOT NULL AND list_length(r) > 0)` perhaps
but I am not sure that would be standards compliant
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] bug: regexp_match not working? [datafusion]
Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on issue #15872:
URL: https://github.com/apache/datafusion/issues/15872#issuecomment-2843029954
@juju4 specifically for your use case I think changing the function to regexp_like will work:
```sql
> select * from test where regexp_like(test.b, '(.){4,}');
+---+-------+
| a | b |
+---+-------+
| 3 | three |
+---+-------+
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org