Regexp_matches with global flag returns wrong data

CrateDB SQL
1

Crate
Query:
select regexp_matches(’#abc #def #ghi’, ‘(#[^\s]*)’, ‘g’) limit 100;

Result:
Array[1]
1:#abc

Expected result:
Array[3]
1: #abc
2: #def
3: #ghi

Postgresql
Query:
select regexp_matches(’#abc #def #ghi’, ‘(#[^\s]*)’, ‘g’) limit 100;

Result:
{#abc}
{#def}
{#ghi}

As you can see in the pgsql result the regular expression seems to be correct.
The global flag is not working for the crate result.

If you have a look at the regexp_replace function instead - the global flag seems to work:
Crate
Query:
select regexp_replace(’#abc #def #ghi’, ‘(#[^\s]*)’, ‘CRATE’, ‘g’) limit 100;

Result:
CRATE CRATE CRATE

Is this issue known? Is there any workaround for it?

2

Hi Phil,

You are correct, this is a bug, the global flag is ignored because internally the class Pattern does not have a global spec. I will create the bug report in github, and will reference this thread.

As a workaround:

 select string_to_array('#abc #def #ghi', ' ');

docu.

Thank you for the report. What are you up to these days?, still crate-ing?

Kind regards,

3

Hi Phil,

I trust you are having a nice summer.

Just a note to let you know that we merged a fix: https://github.com/crate/crate/issues/10041

Thank you for reporting it.

Best regards.