uesleilima opened a new issue, #2370:
URL: https://github.com/apache/age/issues/2370
**Describe the bug**
The `execCypher()` function accepts a `cols` parameter that is used to build
the `AS (col1 agtype, col2 agtype, ...)` clause in the generated SQL. The
`_validate_column()` / `validate_identifier()` functions correctly validate
that column names match `^[A-Za-z_][A-Za-z0-9_]*$`, but they do not check
whether the name is a PostgreSQL reserved word.
When a column alias happens to be a reserved word (e.g., `count`, `sum`,
`type`, `order`, `group`, `select`), the generated SQL fails with a parse error
because the alias is used unquoted in the `AS (...)` clause.
This is especially problematic when column aliases are derived from Cypher
`RETURN` clauses (e.g., `RETURN count(n) AS count`).
**How are you accessing AGE?**
Python driver (psycopg3)
**Steps to Reproduce**
```python
import age
import psycopg
conn = psycopg.connect("host=localhost dbname=postgres user=postgres",
cursor_factory=age.age.ClientCursor)
age.setUpAge(conn, "test_graph")
# This crashes because "count" is a PostgreSQL reserved word
cursor = age.execCypher(conn, "test_graph",
"MATCH (n:TestNode) RETURN count(n)",
cols=["count"])
```
**Expected behavior**
The query should succeed. The driver should quote-escape reserved-word
column aliases in the generated `AS (...)` clause, e.g.:
```sql
SELECT * FROM cypher(NULL, NULL) AS ("count" agtype);
```
**Actual behavior**
PostgreSQL parse error because `count` is unquoted:
```sql
-- Generated SQL (broken):
SELECT * FROM cypher(NULL, NULL) AS (count agtype);
-- ERROR: syntax error at or near "count"
```
**Current workaround**
We maintain a list of 60+ PostgreSQL reserved words and prefix them with
`col_` before passing to `execCypher()`:
```python
_PG_RESERVED_WORDS = frozenset({"all", "and", "as", "count", "group",
"order",
"select", "sum", "type", "where", ...})
def _sanitize_alias(alias):
if alias.lower() in _PG_RESERVED_WORDS:
return f"col_{alias}"
return alias
```
**Suggested fix**
In `_validate_column()` or `buildCypher()`, wrap column names in double
quotes when they match PostgreSQL reserved words. PostgreSQL always accepts
double-quoted identifiers, so quoting unconditionally (e.g., `f'"{{name}}"
{{type_name}}'`) would be the simplest and safest approach:
```python
# In buildCypher() or _validate_column():
columnExp.append(f'"{{name}}" agtype') # always quote — safe for all
identifiers
```
Alternatively, use `psycopg.sql.Identifier()` which handles quoting
automatically:
```python
from psycopg import sql
# sql.Identifier(name) produces '"name"' only when needed
```
**Environment**
- Apache AGE: 1.5.0+ / master
- Python driver: master branch (psycopg3 version)
- psycopg: 3.2.x
- Python: 3.10+
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
