microsoft / microsoft/semantic-kernel

PostgresCollection: string/lambda filter produces an invalid WHERE clause (whole predicate collapsed into a string literal)

Open Beginner friendly
#14,311 2 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
C#
Stars
28.6k
Forks
4.8k
Avg merge
14h 13m
Merged PRs (30d)
18

Description

Describe the bug

PostgresCollection search filters do not work. The generated SQL wraps the entire
filter predicate in a single quoted string literal instead of emitting it as SQL, so
PostgreSQL receives e.g. WHERE '"name" = ''test''' — a text value, not a boolean
predicate — and rejects it (argument of WHERE must be type boolean, not type text).

This affects both string filters and lambda/callable filters, since both flow through
the same _build_filter -> _lambda_parser -> assembly path.

Root cause

_lambda_parser (postgres.py) returns the predicate as a plain Python str
(f-strings), e.g. '"name" = \'test\''. The search query assembly then does
(postgres.py ~L796-801):

if where_clauses := self._build_filter(options.filter):
    query += (
        sql.SQL("WHERE {clause}").format(clause=sql.SQL(" AND ").join(where_clauses))
        if isinstance(where_clauses, list)
        else sql.SQL("WHERE {clause}").format(clause=where_clauses)   # where_clauses is a plain str
    )

psycopg.sql.SQL(...).format(clause=<plain str>) treats a plain str argument as a
literal value, not as SQL, so the pre-built fragment is quoted and its quotes are
doubled. (sql.SQL(" AND ").join([<plain str>, ...]) does the same for the list case.)

Reproduction (offline; no live DB needed to see the malformed SQL)

semantic-kernel 1.44.1, psycopg 3.3.4 (within the pinned psycopg ~= 3.2), Python 3.12:

from dataclasses import dataclass
from typing import Annotated
from psycopg import sql
from semantic_kernel.data.vector import VectorStoreField, vectorstoremodel
from semantic_kernel.connectors.postgres import PostgresCollection

@vectorstoremodel
@dataclass
class Rec:
    id: Annotated[str, VectorStoreField("key")]
    name: Annotated[str, VectorStoreField("data")] = ""
    vector: Annotated[list[float] | None, VectorStoreField("vector", dimensions=2)] = None

col = PostgresCollection(record_type=Rec, collection_name="c")
wc = col._build_filter("lambda x: x.name == 'test'")
print(repr(wc))  # '"name" = \'test\''   (a plain str)
print(sql.SQL("WHERE {clause}").format(clause=wc).as_string(None))
# -> WHERE '"name" = ''test'''      (invalid: the whole predicate is a string literal)

Expected behavior

WHERE "name" = 'test' — the predicate emitted as SQL.

Suggested fix

Mark the pre-built fragment as SQL rather than a value:

clause=sql.SQL(where_clauses)                                   # single
clause=sql.SQL(" AND ").join(sql.SQL(w) for w in where_clauses) # list

Verified this produces the correct WHERE "name" = 'test'.

Security note (so a fix does not regress into injection)

_lambda_parser already escapes string constants (' -> '') and allowlists field
names against the data model, so wrapping the fragment as sql.SQL(...) remains
injection-safe under standard_conforming_strings = on (the PostgreSQL default). If
maintainers prefer, moving values to bound parameters would be more robust than
relying on the manual escaping. I raise this only so the correctness fix is not applied
in a way that turns the currently-collapsed (safe) literal into raw, unescaped SQL.

Notes / limitations

I confirmed the malformed SQL is generated (above); I did not run it against a live
PostgreSQL server, but WHERE '<text>' is rejected by PostgreSQL by design. If there
is a supported configuration where this path works, I'm happy to be corrected.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start in postgres.py at _build_filter, _lambda_parser, and the search-query assembly around lines 796-801; run the offline psycopg reproduction from the issue to inspect the generated SQL. Done means string and callable filters produce a boolean WHERE predicate rather than one quoted text literal, while preserving the existing escaping and field-name restrictions.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, python
Domain
database
Issue type
Bug
Difficulty
2/5
Estimated time
1-3 hours
Activity status
Active
Clarity
Clearly specified
Newbie friendliness
78/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.