Default Order of Associated Model Collection?

Hey all,

I'm working on a side project where I recently switched from SQLite to Postgres in preparation for deployment.

So, I replaced the gem sqlite3 with gem pg in my Gemfile, ranbundle install and kicked off my test suite (RSpec, close to 100% coverage, mostly TDD-d from the first moment).

I was not expecting any issues (my app is quite simple at this point, and well, I just switched DBs - what could go wrong?)

To my surprise, ONE model spec bombed (out of almost 100 tests) - here are all the relevant bits:

Background

We have Routine and Step models. Routine has many steps. Nothing fancy.

Factory

FactoryBot.define do
  factory :routine do
    title { 'MyRoutine' }

    #
    # some stuff irrelevant to us
   #

    trait :with_red_green_blue do
      after(:create) do |routine, _|
        create(:step, title: 'Red Step', routine: routine)
        create(:step, title: 'Green Step', routine: routine)
        create(:step, title: 'Blue Step', routine: routine)

        # if you a wondering why all these reloads are needed:
        # building/maintaining a linked list of steps
        # thus all of the steps are guaranteed to change compared to
        # the initial setup loaded to memory (pointer to next step etc)

        routine.steps.each(&:reload)
        routine.reload
      end
    end

spec

  describe '#to_chain' do
    it 'should return an array of steps in the desired order described by the chain' do
      red_step, green_step, blue_step = create(:routine, :with_red_green_blue).steps
     ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
     # The issue is here

So, this works with SQLite as I would expect - red step is the one with the title 'Red Step' etc.

However - after switching to Postgres, only red_step is OK.
green_step has a title of Blue Step and blue_step has a title of Green Step. (confusing, I know - tell me about it).

Other than that, everything is perfect - the linked list pointers are correct, the step IDs are increasing as you would expect (from red to blue).

Please tell me I'm doing something really trivial/stupid?

Of course, it's trivial to fix the spec by:

red_step, green_step, blue_step = create(:routine, :with_red_green_blue).steps.order(:id)

so I could just do that and move on, but... order(:id)?! Isn't that supposed to be implicit? I don't remember having to write that ever, and I have been doing Rails for a while 😂

Thoughts?

I believe Postgres preserves insertion order for simple queries, but when you start getting into more complex where conditions and things it will not. Always good to specify an order if you're expecting a certain order in your results. 👍

Thanks for the quick answer Chris!

Yeah, preserving insertion order is what I would expect to be the default behavior for pretty much any vanilla RDBMS (Postgres, MySQL, SQLite etc).

But seems like that's not happening here - and I wouldn't think routine.steps is a query with 'more complex where conditions' 🤔.

Sprinkling my tests with order(:id) for the most trivial of queries feels weird?

Not that it's wrong per se, but I can imagine myself opening a test suite (mine or someone else's) and thinking 'what's going on with all the order(:id)s? They seem to be unnecessary?`

Anyways, just thinking out loud here...

I think it's all an implementation detail of the database you're using. From https://www.postgresql.org/docs/current/queries-order.html

A particular output ordering can only be guaranteed if the sort step is explicitly chosen.

You've probably just gotten lucky with SQLite, etc, in the past. 😅

I think it's reasonable to include the sort order in your tests that way you can make sure both result sets are matching.

Or use something like difference which would compare for the same items regardless of sort order. https://ruby-doc.org/core-2.6/Array.html#method-i-difference

Really depends on what you're trying to verify in the tests. If the order doesn't matter, no difference between the two arrays would be a good alternative assertion.

Wow, thanks for this great reply!

Beating myself up for not checking out the forums earlier! Was listening to a Remote Ruby episode yesterday where you mentioned how nice this community is - poking around a bit I can definitely see why!

Thanks again & keep up the FANTASTIC work - ❤️ it!

I know right?! Seriously amazing people here. Welcome to the group! 🤓