Monday, March 11, 2013

Postgress ORDER BY - how random?

Recently I ran into a surprising bug (a feature?) of Postgress SQL. It looks like under certain circumstances the order of rows in the result set of the SELECT statement can change between runs. In other words you run it once and then run it again, the order of records in these two result sets will be different.

To add some specifics: The SELECT statement in question has an ORDER BY clause. Ordering is done by a string field. The sort order has to be case insensitive, so that identical phrases sit next to each other regardless of the case. Obviously that if the result set contains several rows with the string field value differing only in string case, the order of these rows in the result set is arbitrary, which I totally expected and am completely fine with.

What I did not expect is that this order can change between 2 runs of the query.

It does not happen in a simple SELECT ... ORDER BY though. I ran into this feature/bug while working with paging using OFFSET/LIMIT. It so happened that in my result set I had 2 records: one with the sorting key 'Account' and another one with sorting key 'ACCOUNT'. In the result set without OFFSET/LIMIT clauses the 'Account' record as placed in position 130, while the 'ACCOUNT' record was sitting in position 131. This is fine. I do not care between the 2 which one comes first.

But when I started to read this result set in chunks of 10 something weird happened. The 'Account' record as expected was the last one in the result set of the statement with the OFFSET=120, LIMIT=10.

What was not expected is for it to also show up as the first record of the next chunk OFFSET=130, LIMIT=10. The proper owner of the position 131 - the 'ACCOUNT' record was nowhere to be found (in result sets). As a result of this problem one of my records was processed twice, while another one was skipped.

The workaround for this problem was simple - I appended the primary key to the sort field. This way I made the sorting key unique without altering the desired order of the records in the result set

Tuesday, March 5, 2013

Beware of JavaScript ghosts

Yesterday I was ambushed by a JavaScript ghost. Or conned? Not sure. Does not matter. I am just confused. Here's what happened. My function (an event handler) is passed an object:
function(result) {... 
Inside the function I can programmatically examine the object just fine. For instance I can retrieve the values of the object properties.
function(result) { 
    var s = result.status;
}
I can also introduce a variable and assign the object to be its value:
function(result) { 
    var s = result.status;
    var r = result;
}
Once it is done, I can access the property value off the object reference I assigned to the variable:
function(result) { 
    var s = result.status;
    var r = result;
    s = r.status;
}
So what exactly is the problem, the ambush, the con? Here... The same expression(r.status;) executed outside the function gives a different result. Instead of let us say 500 you will get undefined. More than that, if you examine the value of r itself, while inside it shows up as an object passed as the argument, once outside it becomes undefined.

The variable looses its value as it is passed out of scope!!!

You do not believe this is possible? I would not either, but do not take my word for it. Check for yourself.

Ok, Ok... I cheated - just a little. This is not just any object - this is an XHR returned by JQuery for an error. Also JavaScript it is not, not completely. As you can see in the fiddle, the failing expression is not a JavaScript expression but rather an angular expression. They just look the same and I would expect them to work the same way. Confusing...