title: Is unit testing "order by id" possible?
body: <p>Imagine you have the following schema and data:</p>
  
  <pre><code>create table test (
    id serial primary key,
    x integer not null,
    y integer not null
  );
  
  insert into test (x, y) values (1, 2), (3, 4), (5, 6), (7, 8);
  </code></pre>
  
  <p>If I wanted to get the values of <code>test</code> in inserted order, I can use:</p>
  
  <pre><code>select * from test order by id;
  </code></pre>
  
  <p>I want to write a unit test that can <em>fail</em> if the <code>order by id</code> is not present (e.g. following <a href="http://blog.cleancoder.com/uncle-bob/2014/12/17/TheCyclesOfTDD.html" rel="nofollow noreferrer">red/green/refactor</a>).</p>
  
  <p>My <em>failing</em> test would:</p>
  
  <ol>
  <li>Insert some data into <code>test</code>.</li>
  <li>Run the method that calls <code>select * from test</code> and returns the data (changing this to <code>select * from test order by id</code> should make the test pass).</li>
  <li>Verify that the returned data of the method is in the same order that iteration occurred in #1 (aka it should be ordered by <code>id</code>).</li>
  </ol>
  
  <p>However, this test has the possibility of returning false-positives (and this is usually the case without any indexes on the table). The <a href="https://www.postgresql.org/docs/9.5/static/queries-order.html#QUERIES-ORDER" rel="nofollow noreferrer">order of a query without <code>order by</code> is unspecified</a>.</p>
  
  <p>One solution I've thought of is hacking around with the <code>id</code>'s sequence so that the <code>id</code> gets generated in an odd order... but that feels like a hack just to make this test possible.</p>
  
  <p><strong>EDIT:</strong> Alright, there seems to be some confusion. I know that order is not guaranteed without an <code>order by</code> (I even quoted to docs). I can not change the table/view that is queried. I am fixing a bug in a method that <em>today</em> does not use <code>order by id</code>. However, we know that this method <em>should</em> use <code>order by id</code>. Therefore, I need to change the code to use <code>order by id</code>. Before I do this, I want to write a unit test that reproduces this bug (the results of the method are not sorted by <code>id</code>). It so happens that Postgres is returning the data sorted by <code>id</code> since, I guess, the data is coming off disk in the order it was inserted. This is creating a false-positive in the test: it is <em>passing</em> when it should be failing. I want to eliminate false-positives: it should reliably fail before I fix the bug.</p>