Repository: duckdb/duckdb (language: python)

Issue:
unescaped double-quote in column lookup in Python relational API
### What happens?

When using the python relational API, column names with `"` in them don't seem to be escaped properly.

### To Reproduce

python code:

```
import duckdb
c = duckdb.connect()
t = c.sql('select 1 as "tricky"", ""quotes", 2 as tricky, 3 as quotes')

print("t:")
print(t)

print("t[t.columns[0]]:")
print(t[t.columns[0]])
```

output:

```
t:
┌──────────────────┬────────┬────────┐
│ tricky", "quotes │ tricky │ quotes │
│      int32       │ int32  │ int32  │
├──────────────────┼────────┼────────┤
│                1 │      2 │      3 │
└──────────────────┴────────┴────────┘

t[t.columns[0]]:
┌────────┬────────┐
│ tricky │ quotes │
│ int32  │ int32  │
├────────┼────────┤
│      2 │      3 │
└────────┴────────┘
```

It looks like internally it's translating that lookup to `SELECT "tricky", "quotes"` instead of the correct `SELECT "tricky"", ""quotes"`

### OS:

Linux 6.8.0-49-generic x86_64

### DuckDB Version:

1.1.3 

UPDATE: Also 1.1.4-dev3247

### DuckDB Client:

Python, on Python 3.12.3

### Hardware:

_No response_

### Full Name:

Nick Moore

### Affiliation:

Mnemote Pty Ltd

### 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

UPDATE: Also 1.1.4-dev3247

### 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:
The python client is not doing anything special here, this is the same result you'd get with the CLI for this query.

As seen by your result, every quote you intend to escape should be doubled
Yep, and in SQL / the CLI that makes sense.  But the within the Python API, the `[]` operator should be doing the quoting for you, and `t[t.columns[0]]` should always get the first column!

As it is, it does do *some* quoting when constructing the query, it seems to be inserting `"` before and after the identifier, just not escaping `"` to `""` within the identifier.

```
>>> t['"foo"']
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
duckdb.duckdb.ParserException: Parser Error: zero-length delimited identifier at or near """"
```

Hi @nickzoic thanks for opening this. I could reproduce it as follows:

```python
import duckdb
t=  duckdb.sql('select 1 as "hello ""quoted"" world", 2 as asd, 3 as qwe;')
print(t[t.columns[0]])
```

```console
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
duckdb.duckdb.ParserException: Parser Error: syntax error at or near "" world""
```

Fix patch:
diff --git a/tools/pythonpkg/src/include/duckdb_python/expression/pyexpression.hpp b/tools/pythonpkg/src/include/duckdb_python/expression/pyexpression.hpp
index c7b9387e96a8..b89578af25bc 100644
--- a/tools/pythonpkg/src/include/duckdb_python/expression/pyexpression.hpp
+++ b/tools/pythonpkg/src/include/duckdb_python/expression/pyexpression.hpp
@@ -1,7 +1,7 @@
 //===----------------------------------------------------------------------===//
 //                         DuckDB
 //
-// duckdb_python/pyrelation.hpp
+// duckdb_python/expression/pyexpression.hpp
 //
 //
 //===----------------------------------------------------------------------===//
diff --git a/tools/pythonpkg/src/pyrelation.cpp b/tools/pythonpkg/src/pyrelation.cpp
index 723d6a9274ae..cb4b8032e02d 100644
--- a/tools/pythonpkg/src/pyrelation.cpp
+++ b/tools/pythonpkg/src/pyrelation.cpp
@@ -1023,13 +1023,27 @@ unique_ptr<DuckDBPyRelation> DuckDBPyRelation::GetAttribute(const string &name)
 		throw py::attribute_error(
 		    StringUtil::Format("This relation does not contain a column by the name of '%s'", name));
 	}
+	vector<string> column_names;
 	if (names.size() == 1 && ContainsStructFieldByName(types[0], name)) {
-		return make_uniq<DuckDBPyRelation>(rel->Project({StringUtil::Format("%s.%s", names[0], name)}));
+		// e.g 'rel['my_struct']['my_field']:
+		// first 'my_struct' is selected by the bottom condition
+		// then 'my_field' is accessed on the result of this
+		column_names.push_back(names[0]);
+		column_names.push_back(name);
+	} else if (ContainsColumnByName(name)) {
+		column_names.push_back(name);
 	}
-	if (ContainsColumnByName(name)) {
-		return make_uniq<DuckDBPyRelation>(rel->Project({StringUtil::Format("\"%s\"", name)}));
+
+	if (column_names.empty()) {
+		throw py::attribute_error(
+		    StringUtil::Format("This relation does not contain a column by the name of '%s'", name));
 	}
-	throw py::attribute_error(StringUtil::Format("This relation does not contain a column by the name of '%s'", name));
+
+	vector<unique_ptr<ParsedExpression>> expressions;
+	expressions.push_back(std::move(make_uniq<ColumnRefExpression>(column_names)));
+	vector<string> aliases;
+	aliases.push_back(name);
+	return make_uniq<DuckDBPyRelation>(rel->Project(std::move(expressions), aliases));
 }
 
 unique_ptr<DuckDBPyRelation> DuckDBPyRelation::Union(DuckDBPyRelation *other) {


Test patch:
diff --git a/tools/pythonpkg/tests/fast/api/test_attribute_getter.py b/tools/pythonpkg/tests/fast/api/test_attribute_getter.py
index 795e26e9c834..958e8892c512 100644
--- a/tools/pythonpkg/tests/fast/api/test_attribute_getter.py
+++ b/tools/pythonpkg/tests/fast/api/test_attribute_getter.py
@@ -57,3 +57,7 @@ def test_getattr_struct(self, duckdb_cursor):
     def test_getattr_spaces(self, duckdb_cursor):
         rel = duckdb_cursor.sql('select 42 as "hello world"')
         assert rel['hello world'].fetchall()[0][0] == 42
+
+    def test_getattr_doublequotes(self, duckdb_cursor):
+        rel = duckdb_cursor.sql('select 1 as "tricky"", ""quotes", 2 as tricky, 3 as quotes')
+        assert rel[rel.columns[0]].fetchone() == (1,)