Skip to content

fix(azure-postgresql): compare numeric metadata filters numerically - #23743

Open
BlueX888 wants to merge 1 commit into
run-llama:mainfrom
BlueX888:fix/azure-postgresql-numeric-metadata-filters
Open

BlueX888 wants to merge 1 commit into
run-llama:mainfrom
BlueX888:fix/azure-postgresql-numeric-metadata-filters

Conversation

@BlueX888

Copy link
Copy Markdown

Description

Summary

Fix metadata_filters_to_sql in the Azure PostgreSQL vector store so that
GT, GTE, LT, and LTE filters with an int or float value compare
metadata values as numbers instead of text. String filter values keep the
existing text comparison.

This is a direct bug fix with a local reproduction; there is no linked issue.

Problem

metadata_filters_to_sql builds the column reference as metadata ->> 'key',
which is text. For GT/GTE/LT/LTE it compared that text directly with
sql.Literal(value):

  • With an integer value, PostgreSQL 16.15 raises
    psycopg.errors.UndefinedFunction: operator does not exist: text > integer.
    A float value raises the same error with text > numeric.
  • With a string value the comparison is lexical, so '30' > '9' is false
    even though 30 > 9 numerically, and numeric metadata cannot be ordered
    correctly.

Reproduction (PostgreSQL 16.15, table (id int, metadata jsonb) containing
{"price": 30}, {"price": 9}, {"price": "unknown"}, {},
{"price": null}, {"price": 30.5}, {"price": 100}, and
{"price": [1, 2]}):

CASE price > 20 (int)
  SQL: select id from azpg_numeric_filter_probe where (metadata ->> 'price') > 20 order by id
  ERROR: UndefinedFunction: operator does not exist: text > integer

Change

  • For GT/GTE/LT/LTE only, an int or float filter value (excluding
    bool) now renders a numeric comparison guarded by the stored JSON type:

    (jsonb_typeof(metadata -> 'price') = 'number'
     AND (metadata ->> 'price')::float > 20.0)

    Rows where the key is missing, null, a string, an array, or an object do
    not match the guard and are skipped without a type error.

  • String values keep the existing text comparison, unchanged:
    (metadata ->> 'price') > '9'.

  • EQ, NE, IN, NIN, CONTAINS, TEXT_MATCH,
    TEXT_MATCH_INSENSITIVE, and IS_EMPTY are untouched.

  • Added tests/llama_index/test_metadata_filters_sql.py, which renders the
    generated SQL and needs no database.

  • Bumped the package version from 0.3.0 to 0.3.1.

Verification

Environment: macOS, PostgreSQL 16.15 (Homebrew), Python 3.14, psycopg 3.3.4.

  1. New unit tests:

    $ uv run --frozen --no-default-groups --group test -- pytest tests/llama_index/test_metadata_filters_sql.py
    13 passed
    

    The same test module against the unmodified baseline (619d746) fails:

    10 failed, 3 passed
    
  2. Local PostgreSQL probe executing the generated SQL against the table above:

    CASE price > 20 (int)      -> ids [1, 6, 7]   (30, 30.5, 100)
    CASE price < 20 (int)      -> ids [2]         (9)
    CASE price >= 30 (int)     -> ids [1, 6, 7]
    CASE price <= 30 (int)     -> ids [1, 2]
    CASE price > 20.25 (float) -> ids [1, 6, 7]
    CASE price > '9' (string)  -> ids [3]         (text semantics, unchanged)
    CASE price == 30 (int)     -> ids [1]         (EQ, unchanged)
    

    The rows with {"price": "unknown"}, a missing price, null, and an
    array were present for every case and never raised. On the unmodified
    baseline, the five numeric cases raise UndefinedFunction.

  3. Full package suite:

    $ .venv/bin/python -m pytest tests/ --pg-user admin --pg-database postgres
    25 passed, 43 skipped, 7 errors
    

    The 7 errors are a pre-existing teardown problem in tests/conftest.py
    (fixture function has more than one 'yield') and are unrelated to this
    change; the same error occurs on the unmodified baseline (4 errors there,
    the count varying with connection-pool/test order). DB-backed vector store
    tests were skipped because Azure AD credentials and the vector extension
    are not available in this local environment. Package coverage was therefore
    not measured locally; the new unit tests exercise every changed branch.

  4. Lint:

    $ .venv/bin/ruff check llama_index
    All checks passed!
    $ .venv/bin/ruff check tests
    All checks passed!
    $ .venv/bin/ruff format --check <changed files>
    2 files already formatted
    

New Package?

  • Yes
  • No

Version Bump?

  • Yes
  • No

Type of Change

  • Bug fix (non-breaking change which fixes an issue)
  • New feature (non-breaking change which adds functionality)
  • Breaking change (fix or feature that would cause existing functionality to not work as expected)
  • This change requires a documentation update

How Has This Been Tested?

  • I added new unit tests to cover this change
  • I believe this change is already covered by existing unit tests

Suggested Checklist:

  • I have performed a self-review of my own code
  • I have commented my code, particularly in hard-to-understand areas
  • I have made corresponding changes to the documentation
  • I have added Google Colab support for the newly added notebooks.
  • My changes generate no new warnings
  • I have added tests that prove my fix is effective or that my feature works
  • New and existing unit tests pass locally with my changes
  • I ran ruff check and ruff format on the changed files

AI Assistance

AI assistance was used to draft this change and its tests. The reproduction,
test runs, and results reported above were executed locally.

`metadata_filters_to_sql` rendered GT/GTE/LT/LTE comparisons against
`metadata ->> 'key'`, which is text. A numeric filter value produced
`text > integer`, raising `UndefinedFunction` on PostgreSQL, while a
string value compared lexically.

Compare int/float filter values as floats behind a
`jsonb_typeof(metadata -> 'key') = 'number'` guard so rows with a
missing or non-numeric value are excluded instead of raising. String
filter values keep the existing text comparison. Adds unit tests that
render the SQL and bumps the package version to 0.3.1.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

1 participant