sql: discrepancy in SUBSTRING Regex Handling Between CockroachDB and PostgreSQL

**Problem**

When using SUBSTRING with the regex pattern ^(?:a*){1,1}?(.*){1,1}(?:g*)$ on the input 'abcdefg', CockroachDB returns 'bcdefg', while PostgreSQL returns the entire substring 'abcdefg'. This indicates a discrepancy in how the regex engines handle trailing quantifiers and captured groups.

**To Reproduce**

1. Run the following SQL query in CockroachDB:

```sql
SELECT SUBSTRING('abcdefg' FROM '^(?:a*){1,1}?(.*){1,1}(?:g*)$');
```
2. Observe the output:
```sql
substring
-------------
bcdefg
(1 row)
```
3. Run the same query in PostgreSQL.

4. PostgreSQL returns:
```sql
substring
-------------
abcdefg
(1 row)
```

**Expected behavior**
```sql
abcdefg
```

**Environment:**
- CockroachDB version 24.3.4
- Server OS: Linux/Ubuntu-22.04
- Client app cockroach sql

Jira issue: CRDB-47140