BUG #19114: ORDER BY ASC is tampering result when calculating distance btw vectors

Поиск
Список
Период
Сортировка
От PG Bug reporting form
Тема BUG #19114: ORDER BY ASC is tampering result when calculating distance btw vectors
Дата
Msg-id 19114-b0d2502c853fe210@postgresql.org
обсуждение исходный текст
Ответы Re: BUG #19114: ORDER BY ASC is tampering result when calculating distance btw vectors
Список pgsql-bugs
The following bug has been logged on the website:

Bug reference:      19114
Logged by:          Naveen Krishna S
Email address:      naveenkrishna.s@sky.uk
PostgreSQL version: 14.13
Operating system:   Mac OS
Description:

In a table with vector column,
SELECT embedding <=> CAST('[0.01, 0.23, -0.1,..]' as vector)
AS distance
FROM my_table
WHERE TRUE
order by distance desc
LIMIT 100;

is giving 100 records whereas

SELECT embedding <=> CAST('[0.01, 0.23, -0.1,..]' as vector)
AS distance
FROM my_table
WHERE TRUE
order by distance asc
LIMIT 100;

is giving 40 records for a specific embedding and always less than the limit
for any query embedding. Why is this?
I have also noticed if I use NULLS FIRST or COALESCE(distance, 99999999999)
it is returning the requested limit. But when I tried to list the records
with distance as null, there were none.


В списке pgsql-bugs по дате отправления: