Nova API's regular expression dialect depends on backend DB

[Yamato Tanaka]

Description
===========
Some of Compute APIs supports regular expression, such as the following one:

  https://docs.openstack.org/api-ref/compute/?expanded=list-servers-detail#list-server-request
  name (Optional) query string
  Filters the response by a server name, as a string. You can use regular expressions in the query.

However, since the regular expression match is executed in SQL statements and each DB products has different regular expression dialects, the result may differ depending on the backend DB.
Therefore, Nova API's regular expression dialect may vary depending on backend DB.
I believe the DB backends should be transparent and users shouldn't take care of what the DB backend is when calling Nova API.

How about modifying Nova to use Python's standard regular expression, rather than backend DB's regular expression?

Steps to reproduce
==================
1. Deploy OpenStack using DevStack on Ubuntu and RHEL
   DevStack on Ubuntu uses mysql while DevStack on RHEL uses mariadb, and they have different regular expression dialects
2. Create an instance with the name "test{test}"
3. Run 'openstack stack show "test{test}".
   Ubuntu should results 500 HTTP error while RHEL should shows correct instance information.
   This difference comes from the difference of the regular expression dialects of the backend DBs.

Expected result
===============
Nova API returns the same result when calling the same API regardless of the backend DB.

Actual result
=============
Nova API returns different results when calling the same API depending on the backend DB.

Environment
===========
1. DevStack on Ubuntu and RHEL

Logs & Configs
==============

mysql and mariadb have different regular expression dialects, which make different results on my DevStack environment.

~~~
<RHEL : mariadb-server-10.5.16-2.el9_0.x86_64>

[stack@utils ~]$ mysql -B nova_cell1 -e "select display_name from instances where display_name regexp 'test{test}'\G"
*************************** 1. row ***************************
display_name: test{test}

  ==> `openstack server show 'test{test}'` works.
~~~

~~~
<Ubuntu : mysql-server-8.0.33-0ubuntu0.22.04.2>

stack@devstack:~/devstack$ mysql -B nova_cell1 -e "select display_name from instances where display_name regexp 'test{test}'\G"
ERROR 3692 (HY000) at line 1: Incorrect description of a {min,max} interval.

  ==> `openstack server show 'test{test}'` fails with 500 error
~~~

[Yamato Tanaka (yatanaka-1007)]

I'm wondering if we can modify around here:
- https://github.com/openstack/nova/blob/master/nova/db/main/api.py#L1820
- https://github.com/openstack/nova/blob/master/nova/db/main/api.py#L1988-L2017

[Elod Illes (elod-illes)]

Thanks for the detailed bug description Yamato. The example with the curly braces demonstrate well the difference between the two backend db. I set the bug importance as 'wishlist' as the regex in general works, though i understand that there can be special cases where one of the backend queries drops an error.