CODEWITHSUNDEEP
postgresql
frnsys
frnsys

Reputation: 2454

Postgres ORDER BY using double precision not properly sorting

I'm trying to execute what should be a simple query using Postgres 9.3.2.

SELECT
event.id AS event_id, event.created_at AS event_created_at, event.updated_at AS event_updated_at, event.title AS event_title, event.summary AS event_summary, event.image AS event_image, event.active AS event_active, event.raw_score AS event_raw_score, event._score AS event__score, user_1.id AS user_1_id, user_1.email AS user_1_email, user_1.image AS user_1_image, user_1.name AS user_1_name, user_1.password AS user_1_password, user_1.active AS user_1_active, user_1.confirmed_at AS user_1_confirmed_at, user_1.created_at AS user_1_created_at, user_1.updated_at AS user_1_updated_at
FROM event
LEFT OUTER JOIN (
    users_events AS users_events_1
    JOIN "user" AS user_1 ON user_1.id = users_events_1.user_id
) ON event.id = users_events_1.event_id
ORDER BY event._score DESC

I'm expecting the results to be ordered by event._score, descending, but they never are. They don't seem to be ordered in any way relating to event._score. The datatype for that column is double precision.

The order I expect the values to be in are:

5787.0428202
5787.0427916
5787.0427628

But they are returned as:

5787.0427916
5787.0427628
5787.0428202

I've been struggling with this for a couple hours now and can't figure out why something so simple isn't working correctly.

Upvotes: 0

Views: 783

Answers (1)

frnsys
frnsys

Reputation: 2454

After a lot more digging, it turns out that this was not an issue with Postgres, but with how the ORM I was using (sqlalchemy) was setup. The _score property was not properly being set in the database, though it was set within the session, so the objects in the session were returning the proper values, but objects fetched from the database were not.

Upvotes: 1

Related Questions