DBI with ORDER BY

YoungPups has asked for the wisdom of the Perl Monks concerning the following question:

I heard from a coworker recently that when using SQL with an ORDER BY via DBI, you are not guaranteed to get your results back in the order you specified in your SQL. I've never heard of such a thing, the DBI perldocs specifically use SQL with an ORDER BY as an example, and I've never had problems using ORDER BY in any of my programs over the years.

Did I maybe just hear what he was saying wrong? Does anybody know of any caveats using ORDER BY in SQL with DBI?

Comment on DBI with ORDER BY

Replies are listed 'Best First'.
Re: DBI with ORDER BY by talexb (Chancellor) on Mar 09, 2005 at 21:58 UTC
I heard from a coworker recently that when using SQL with an ORDER BY via DBI, you are not guaranteed to get your results back in the order you specified in your SQL. Ridiculous. DBI doesn't randomly edit or sort the results coming back from the database. It's just an abstraction layer. It is given an SQL command, it executes it and returns the result. But, in the interest of science (and in the event I really don't know what I'm talking about), does your co-worker has an example showing this bizarre behaviour? Alex / talexb / Toronto "Groklaw is the open-source mentality applied to legal research" ~ Linus Torvalds	[reply]
Re^2: DBI with ORDER BY by YoungPups (Beadle) on Mar 09, 2005 at 22:08 UTC
I figured as much and was going to ask him for an example, or at least documentation of this behavior somewhere. I've only heard him explain this to other coworkers, which is why I wonder if I might have just heard him wrong. He's doing a code review for me sometime in the next few days, and I expect to have him bring this up with me then. I was planning on disagreeing with him on this, but other than this he's the coder around here I most trust, so I wanted some backup before I pick a fight.	[reply]
Re^3: DBI with ORDER BY by talexb (Chancellor) on Mar 09, 2005 at 22:16 UTC
I was planning on disagreeing with him on this, but other than this he's the coder around here I most trust, so I wanted some backup before I pick a fight. They have a wonderful saying in Texas that goes something like this: "Be sure your words are soft and sweet, just in case you are forced to eat them" ;) I find that what works better than 'picking a fight' is a somewhat surprised disbelief that the situation is as the code reviewer describes. Think about it -- what advantage is there to using DBI if it sometimes messes with the ordering of the data? That doesn't sound like a feature to me, it sounds more like a bug. Alex / talexb / Toronto "Groklaw is the open-source mentality applied to legal research" ~ Linus Torvalds	[reply]
Re: DBI with ORDER BY by data64 (Chaplain) on Mar 09, 2005 at 22:32 UTC
Perhaps your co-worker is referring to the "fetchall_hashref" method which essentially ends up creating a hash. Hence any sort order imposed by "ORDER BY" is lost. Needless to say, if you are using "ORDER BY" do not use fetchall_hashref	[reply]
Re^2: DBI with ORDER BY by merlyn (Sage) on Mar 09, 2005 at 23:19 UTC
Huh? The hashes are per-row. The rows still come out as a list or arrayref. -- Randal L. Schwartz, Perl hacker Be sure to read my standard disclaimer if this is a reply. update: Yup, I never use fetchall_hashref, and was confusing it with things that I use. Sorry. Too many similar sounding names.	[reply]
Re^3: DBI with ORDER BY by crashtest (Curate) on Mar 09, 2005 at 23:45 UTC
merlyn, I believe you're thinking of `fetchrow_hashref` and not `fetchall_hashref`. The latter does in fact retrieve all the results into a hash (a HoH), thereby effectively discarding any ordering an `ORDER BY` clause might have imposed.	[reply] [d/l] [select]
Re: DBI with ORDER BY by punkish (Priest) on Mar 10, 2005 at 01:54 UTC
You may already know this, nevertheless, perhaps your colleague was referring to the database not storing the data in a specific order, and it is possible that you misheard. ;-) The database first selects the data per your instructions, and only then applies the SORT on the selected set in a db buffer. Inherently there is no order in the database. DBI itself is an abstraction layer as others have mentioned. And while I haven't experienced it, it is possible that fetchall_hashref buggers up the specified ORDER. Other than that, no, DBI doesn't mess with your instructions to fetch the data. Perhaps a friendly discussion with your co-worker will shed more light. Be sure to come back and update this thread so we may all learn for the better. -- when small people start casting long shadows, it is time to go to bed	[reply]
Re^2: DBI with ORDER BY by YoungPups (Beadle) on Mar 10, 2005 at 21:25 UTC
I just got my code review, and it sounds like it was more of a philosophical objection on his part. He's of the opinion that you should handle sorting in your program rather than make the DB do it. I disagree (pretty strongly), but that is at least a reasonable objection. Most places I've worked, the machines that hosted our DBs were much beefier than the machines we did processing on, so I always made the database do as much of the work as I could. Plus, it seems to me to be a bit like reinventing the wheel to sort the results of a query in your script when the DB can do it for you, and is specifically set up to optimize such a sort. Anyway, it sounds like I had just misheard why he was asking other coders to avoid ORDER BY statements.	[reply]
Re^3: DBI with ORDER BY by punkish (Priest) on Mar 10, 2005 at 22:16 UTC
I agree with you. In fact, I would say, get the db to do as much of set-based text processing as you can. It not the beefiness of the db servers that I think of -- it is the decades of heavy-duty, focused research on working with sets. Databases are just super at performing those tasks. Just check the query plan for any query -- it is pretty amazing. Otoh, dbs are a royal pain in the derierre working with arrays and hashes kinda datasets. Once you get your data, nicely stuffed in an array, Perl can whip through it at close to machine speed (well, pretty much) searching for arbit. patterns and repurposing it as desired. Maybe it is time for your code-reviewer to get a code review. ;-) -- when small people start casting long shadows, it is time to go to bed	[reply]
Re: DBI with ORDER BY by RazorbladeBidet (Friar) on Mar 09, 2005 at 22:59 UTC
If anything, they wouldn't be edited by DBI, but by the specific DBD::XX. I use ORDER BY with DBD::Oracle and have not had an iota of a problem retrieving the data in the correct order, FWIW. As always, YMMV. -------------- It's sad that a family can be torn apart by such a such a simple thing as a pack of wild dogs	[reply]
Re: DBI with ORDER BY by dws (Chancellor) on Mar 10, 2005 at 05:56 UTC
Did I maybe just hear what he was saying wrong? That's how I'd bet. Get him to draw you a picture. One of you might be confusing row order with column order.	[reply]

Back to Seekers of Perl Wisdom