Hi,
i am facing a strange phenomenon with an sql query i can't find any explanation for.
Unfortunately i am not able to construct a test case (yet).
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production on linux
The structure of the query is like
with strs (
select
nr
,regexp_replace(some_varchar, 'regexp_that_hits_some_streetnames') str
-- regexp_replace is not leading to any null values
,some more columns
from a_table
)
, grps as (
select str
from strs
group by str
having count(distinct nr) > 1
)
select nr, str, other columns
from strs s
where exists (select 1 from grps g where g.str = s.str)
With this is got streetnames that are not in grps as the result.
This is only the case if the regexp is matching data in the table (not necessarily the "wrong" data).
If the regexp matches no data or the data is changed by update in order to not match the regexp the results are correct.
Anyone any idea what i am missing?
Thanks and regards
Chris