Repository: duckdb/duckdb (language: python)

Issue:
FTS ignore regex to include single quote
### What happens?

When trying to create a duckdb FTS index with single quotes included in the search regex, the following error occurs
```
con.execute("PRAGMA create_fts_index(my_table, 'CustomerId', 'CustomerName', ignore='(\\.|[^a-z0-9'])+')")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
duckdb.duckdb.ParserException: Parser Error: syntax error at or near "]"
```


### To Reproduce

```
import duckdb
con = duckdb.connect("tester.db")
con.execute("CREATE TABLE my_table AS SELECT 1 AS CustomerId, 'hans' as CustomerName")

# FTS Index creation to include search for lowercase alphanumeric characters and single quotes
con.execute("PRAGMA create_fts_index(my_table, 'CustomerId', 'CustomerName', ignore='(\\.|[^a-z0-9'])+')")

# Trying to escape the single quotes as well does not seem to work with PRAGMA
# Escape with single `\`
con.execute("PRAGMA create_fts_index(my_table, 'CustomerId', 'CustomerName', ignore='(\\.|[^a-z0-9\'])+')")
# Escape with double `\`
con.execute("PRAGMA create_fts_index(my_table, 'CustomerId', 'CustomerName', ignore='(\\.|[^a-z0-9\\'])+')")
```

### OS:

Ubuntu 24.04.1 LTS

### DuckDB Version:

1.0.0

### DuckDB Client:

Python

### Hardware:

_No response_

### Full Name:

Brandan

### Affiliation:

NIL

### What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

### Did you include all relevant data sets for reproducing the issue?

Yes

### Did you include all code required to reproduce the issue?

- [X] Yes, I have

### Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

- [X] Yes, I have
FTS ignore regex to include single quote
### What happens?

When trying to create a duckdb FTS index with single quotes included in the search regex, the following error occurs
```
con.execute("PRAGMA create_fts_index(my_table, 'CustomerId', 'CustomerName', ignore='(\\.|[^a-z0-9'])+')")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
duckdb.duckdb.ParserException: Parser Error: syntax error at or near "]"
```


### To Reproduce

```
import duckdb
con = duckdb.connect("tester.db")
con.execute("CREATE TABLE my_table AS SELECT 1 AS CustomerId, 'hans' as CustomerName")

# FTS Index creation to include search for lowercase alphanumeric characters and single quotes
con.execute("PRAGMA create_fts_index(my_table, 'CustomerId', 'CustomerName', ignore='(\\.|[^a-z0-9'])+')")

# Trying to escape the single quotes as well does not seem to work with PRAGMA
# Escape with single `\`
con.execute("PRAGMA create_fts_index(my_table, 'CustomerId', 'CustomerName', ignore='(\\.|[^a-z0-9\'])+')")
# Escape with double `\`
con.execute("PRAGMA create_fts_index(my_table, 'CustomerId', 'CustomerName', ignore='(\\.|[^a-z0-9\\'])+')")
```

### OS:

Ubuntu 24.04.1 LTS

### DuckDB Version:

1.0.0

### DuckDB Client:

Python

### Hardware:

_No response_

### Full Name:

Brandan

### Affiliation:

NIL

### What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

### Did you include all relevant data sets for reproducing the issue?

Yes

### Did you include all code required to reproduce the issue?

- [X] Yes, I have

### Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

- [X] Yes, I have


Discussion / hints:
`'` -> `''`

You have to escape the single quote, and to do that in SQL you need to double the single quote
Indeed, as @Tishj says, in SQL, the way we escape single quotes is by doing a double single quote:
```sql
D select '''';
┌─────────┐
│  ''''   │
│ varchar │
├─────────┤
│ '       │
└─────────┘
```
You can also use the double-dollar quotes to avoid the single quote issue:
```sql
D select $$'$$;
┌─────────┐
│  ''''   │
│ varchar │
├─────────┤
│ '       │
└─────────┘
```
I think in this case the create_fts_index PRAGMA's `ignore` parameter takes in the regex as a string wrapped in single quotes. So any single quotes in the regex itself will in a way prematurely end the input regex string. As i tried your suggestions.

Doubling up single quote
```
con.execute("PRAGMA create_fts_index(my_table, 'CustomerId', 'CustomerName', ignore='(\\.|[^a-z0-9''])+')")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
duckdb.duckdb.ParserException: Parser Error: syntax error at or near "]"
```

Adding double-dollar
```
con.execute("PRAGMA create_fts_index(my_table, 'CustomerId', 'CustomerName', ignore='(\\.|[^a-z0-9$$'$$])+')")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
duckdb.duckdb.ParserException: Parser Error: unterminated dollar-quoted string at or near "$$])+')"
```
When I try something very similar in the DuckDB CLI, there are no problems:

```~SQL
D create or replace macro s(t := 'hoi') as t;
D select s(t:='(\\.|[^a-z0-9''])+');
┌──────────────────────────────┐
│ s(t := '(\\.|[^a-z0-9''])+') │
│           varchar            │
├──────────────────────────────┤
│ (\\.|[^a-z0-9'])+            │
└──────────────────────────────┘
```

I was curious if the problem was perhaps caused by macro parameter processing, but no, fortunately.
Then I thought it must be the Python API.
However:

```~python
>>> con = duckdb.connect("tester.db")
>>> con.sql("create or replace macro s(t := 'hoi') as t;")
>>> con.sql("select s();")
┌─────────┐
│   s()   │
│ varchar │
├─────────┤
│ hoi     │
└─────────┘

>>> con.sql("select s(t:='(\\.|[^a-z0-9''])+');")
┌─────────────────────────────┐
│ s(t := '(\.|[^a-z0-9''])+') │
│           varchar           │
├─────────────────────────────┤
│ (\.|[^a-z0-9'])+            │
└─────────────────────────────┘
```

And, no difference for using `.execute().fetchone()` instead of `.sql()`, all works well.

I did however get the same error as reported by @brandantck for his example.
But...

If I adapt the most recent example to read:

```~python
>>> con.execute("PRAGMA create_fts_index(my_table, 'CustomerId', 'CustomerName', ignore = '(\\.|[^a-z0-9''''])+', overwrite = 1)");
<duckdb.duckdb.DuckDBPyConnection object at 0x7ffb173b5570>
```

I think it is the interaction between Python parsing and SQL parsing, but did not look into it in great detail.
Can you maybe just check if this version would deliver the result you expected, @brandantck ?

(I have been using versions 0.10.0 or 1.1.0.)


`'` -> `''`

You have to escape the single quote, and to do that in SQL you need to double the single quote
Indeed, as @Tishj says, in SQL, the way we escape single quotes is by doing a double single quote:
```sql
D select '''';
┌─────────┐
│  ''''   │
│ varchar │
├─────────┤
│ '       │
└─────────┘
```
You can also use the double-dollar quotes to avoid the single quote issue:
```sql
D select $$'$$;
┌─────────┐
│  ''''   │
│ varchar │
├─────────┤
│ '       │
└─────────┘
```
I think in this case the create_fts_index PRAGMA's `ignore` parameter takes in the regex as a string wrapped in single quotes. So any single quotes in the regex itself will in a way prematurely end the input regex string. As i tried your suggestions.

Doubling up single quote
```
con.execute("PRAGMA create_fts_index(my_table, 'CustomerId', 'CustomerName', ignore='(\\.|[^a-z0-9''])+')")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
duckdb.duckdb.ParserException: Parser Error: syntax error at or near "]"
```

Adding double-dollar
```
con.execute("PRAGMA create_fts_index(my_table, 'CustomerId', 'CustomerName', ignore='(\\.|[^a-z0-9$$'$$])+')")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
duckdb.duckdb.ParserException: Parser Error: unterminated dollar-quoted string at or near "$$])+')"
```
When I try something very similar in the DuckDB CLI, there are no problems:

```~SQL
D create or replace macro s(t := 'hoi') as t;
D select s(t:='(\\.|[^a-z0-9''])+');
┌──────────────────────────────┐
│ s(t := '(\\.|[^a-z0-9''])+') │
│           varchar            │
├──────────────────────────────┤
│ (\\.|[^a-z0-9'])+            │
└──────────────────────────────┘
```

I was curious if the problem was perhaps caused by macro parameter processing, but no, fortunately.
Then I thought it must be the Python API.
However:

```~python
>>> con = duckdb.connect("tester.db")
>>> con.sql("create or replace macro s(t := 'hoi') as t;")
>>> con.sql("select s();")
┌─────────┐
│   s()   │
│ varchar │
├─────────┤
│ hoi     │
└─────────┘

>>> con.sql("select s(t:='(\\.|[^a-z0-9''])+');")
┌─────────────────────────────┐
│ s(t := '(\.|[^a-z0-9''])+') │
│           varchar           │
├─────────────────────────────┤
│ (\.|[^a-z0-9'])+            │
└─────────────────────────────┘
```

And, no difference for using `.execute().fetchone()` instead of `.sql()`, all works well.

I did however get the same error as reported by @brandantck for his example.
But...

If I adapt the most recent example to read:

```~python
>>> con.execute("PRAGMA create_fts_index(my_table, 'CustomerId', 'CustomerName', ignore = '(\\.|[^a-z0-9''''])+', overwrite = 1)");
<duckdb.duckdb.DuckDBPyConnection object at 0x7ffb173b5570>
```

I think it is the interaction between Python parsing and SQL parsing, but did not look into it in great detail.
Can you maybe just check if this version would deliver the result you expected, @brandantck ?

(I have been using versions 0.10.0 or 1.1.0.)



Fix patch:
diff --git a/extension/fts/fts_indexing.cpp b/extension/fts/fts_indexing.cpp
index bd985f7a6c2f..a74b9f5de1c4 100644
--- a/extension/fts/fts_indexing.cpp
+++ b/extension/fts/fts_indexing.cpp
@@ -73,7 +73,7 @@ static string IndexingScript(ClientContext &context, QualifiedName &qname, const
 	if (lower) {
 		tokenize = "lower(" + tokenize + ")";
 	}
-	tokenize = "regexp_replace(" + tokenize + ", '" + ignore + "', " + "' ', 'g')";
+	tokenize = "regexp_replace(" + tokenize + ", $$" + ignore + "$$, " + "' ', 'g')";
 	tokenize = "string_split_regex(" + tokenize + ", '\\s+')";
 	result += "CREATE MACRO %fts_schema%.tokenize(s) AS " + tokenize + ";";
 


Test patch:
diff --git a/test/sql/fts/issue_13866.test b/test/sql/fts/issue_13866.test
new file mode 100644
index 000000000000..6334ebd5a8dc
--- /dev/null
+++ b/test/sql/fts/issue_13866.test
@@ -0,0 +1,13 @@
+# name: test/sql/fts/issue_13866.test
+# description: Issue 13866: FTS ignore regex to include single quote
+# group: [fts]
+
+require fts
+
+require noalternativeverify
+
+statement ok
+CREATE TABLE my_table AS SELECT 1 AS CustomerId, 'hans' as CustomerName
+
+statement ok
+PRAGMA create_fts_index(my_table, 'CustomerId', 'CustomerName', ignore='(\\.|[^a-z0-9''])+')