FTS ignore regex to include single quote #13866
Description
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?
- Yes, I have
Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?
- Yes, I have