account_statement_base_completion: invalid regular expression: invalid escape \ sequence

[Guewen Baconnier @ Camptocamp]

When using the autocompletion with get_from_label_and_partner_name, I get an error:

Traceback (most recent call last):
  File "/home/gbaconnier/code/instances/openerp/trunk/parts/server/openerp/sql_db.py", line 226, in execute
    res = self._obj.execute(query, params)
DataError: invalid regular expression: invalid escape \ sequence

When it executes this query:

        sql = """SELECT id FROM (
                        SELECT id, regexp_matches(%s, regexp_replace(name,'([^[:alpha:]0-9 -])', %s, 'g'), 'i') AS name_match FROM res_partner
                            WHERE id IN %s) AS res_patner_matcher
                    WHERE name_match IS NOT NULL"""
        cr.execute(sql, (st_line['name'], r"\\\1", context['partner_memoizer']))
        result = cr.fetchall()

The tests are green (and they cover this query) but they do not cover a corner case where the name contains special unicode chars.

The regexp_replace in the query escape the name, example:

"jovial (<email address hidden>)" is escaped as "jovial \(m.blanc\@example\.com\)".

The problem is that it escape too much characters, example:

openerp=# select regexp_replace('Jérôme blaš', '([^[:alpha:]0-9 -])', '\\\1', 'g');
 regexp_replace
----------------
 Jérôme bla\š

Here the 'š' should not have been escaped, resulting in the "invalid regular expression: invalid escape \ sequence" error.

[Guewen Baconnier @ Camptocamp (gbaconnier-c2c)]

Using "\\\1" would be wrong as the query would be:

SELECT id FROM (
                        SELECT id, regexp_matches('007740000000000000020101806', regexp_replace(name,'([^[:alpha:]0-9 -])', '\', 'g'), 'i') AS name_match FROM res_partner
                            ) AS res_patner_matcher
                    WHERE name_match IS NOT NULL

Instead of

SELECT id FROM (
                        SELECT id, regexp_matches('007740000000000000020101806', regexp_replace(name,'([^[:alpha:]0-9 -])', '\1', 'g'), 'i') AS name_match FROM res_partner
                            ) AS res_patner_matcher
                    WHERE name_match IS NOT NULL

My feeling is that the correct query would be:

        sql = """SELECT id FROM (
                        SELECT id, regexp_matches(%s, regexp_replace(name,'([^[:alpha:]0-9 -])', E'\\1', 'g'), 'i') AS name_match FROM res_partner
                            WHERE id IN %s) AS res_patner_matcher
                    WHERE name_match IS NOT NULL"""
        cr.execute(sql, (st_line['name'], context['partner_memoizer']))
        result = cr.fetchall()

But a test do no longer pass and I can't figure out how it is supposed to work yet.

[Guewen Baconnier @ Camptocamp (gbaconnier-c2c)]

Unlike I said in the previous comment, E'\\1' is not correct, as the goal is to transform the name to an escaped regular expression.
So a name like

"jovial (<email address hidden>)" is escaped as "jovial \(m.blanc\@example\.com\)".

The problem is that it escape too much characters, example:

openerp=# select regexp_replace('Jérôme blaš', '([^[:alpha:]0-9 -])', '\\\1', 'g');
 regexp_replace
----------------
 Jérôme bla\š

Here the 'š' should not have been escaped, resulting in the "invalid regular expression: invalid escape \ sequence" error.