Hi there. Long time no post.
I just spent a couple hours debugging a query with a DISTINCT ON clause. The DISTINCT ON clause is a PostgreSQL-specific extension to the standard DISTINCT clause, which lets you specify the columns on which you want PostgreSQL to operate for filtering out duplicate rows.
With the SQLAlchemy ORM (using the declarative extension,) here is one way to write a query to retrieve all unique names from a name table:
>>> q = DBSession.query(model.Name).distinct(model.Name.value) \
Now, if you’d want to see what kind of SQL query the ORM will generate you’d print it like so:
>>> print str(q)
SELECT DISTINCT name.id AS name_id, name.value AS name_value
FROM name ORDER BY name.value
But as you can see, there’s no DISTINCT ON here. Dang. Continue reading Printing actual SQLAlchemy queries
Just a reminder to myself, so I won’t waste as much time if it happens again.
When a jQuery.getJSON() callback is called by Firefox but not by Internet Explorer, check if the JSON data returned really is squeaky-clean.
An extra comma at the end of an object declaration was doing okay with the more forgiving Firefox JSON parser, but tripped IE’s, with no error message, and no callback ever called.
How come, most of the time, the string ‘2009-02-01’ gets parsed into a Date object with the correct February 1st, 2009 value, but sometimes results in March 1st, 2009?
This particular piece of code builds a financial periods menu by extracting dates out of some HTML returned by an AJAX request. Here’s what the function looks like – it may not be the best way to construct a date from a string in the form yyyy-mm-dd, but it works:
if (isoString == null)
var date = new Date();
date.setMonth(isoString.substr(5, 2) - 1);
That is, it works most of the time, but not at the end of some months, as I discovered.
Take yesterday, for instance – March 31st, 2009 – and let’s look at what makeDateFromIsoString(‘2009-02-01’) returned:
- var date = new Date() created a new Date object, containing the current date and time: March 31st, 2009.
- date.setFullYear(isoString.substr(0, 4)) changed the year of the data object to ‘2009‘, so its value stayed the same.
- So by the the time the code was done with date.setDate(isoString.substr(8, 2)), the date value had become March 1st, 2009.
And this is how February became March, but only during the three last days of March. Interesting twist, isn’t it?
The fix was simple: call setDate() before setMonth().
Viva la Firebug for helping me nail this one down.
Update: In the end, I just replaced the whole function body with this: return new Date(isoString.replace(‘-‘, ‘/’, ‘g’)). Much simpler, and not subject to the above subtle, but interesting nevertheless, bug.