the weekly whinge

Say you want to step through a bunch of records in order using a MySQL cursor. That’s normally not a problem: you create a stored procedure so:
CREATE PROCEDURE doit()
BEGIN
DECLARE whichi INT(11) DEFAULT 0;
DECLARE mycur CURSOR FOR SELECT IF(i=1, f1, f2) AS whichi ORDER BY whichi;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET cursorExpired=true;
open mycur;
curnext: loop
FETCH mycur INTO whichi;
IF cursorExpired THEN
LEAVE curnext;
END IF;
select whichi;
END LOOP curnext;
close mycur;
END;


Obviously that’s an incredibly inefficient way to list records, but you get the idea.

The point of the query is that the SELECT must be ordered by the result of IF(i=1, f1, f2); however calling doit() will not output what you expect (unless, of course, you’ve come across this problem before, in which case you’ll be expecting it :))

What you get is no ordering of the data at all – the ORDER BY part of the statement is effectively ignored. Why? Because we’ve called the field in the SELECT query the same as the variable we declared above. So you end up with (essentially)

SELECT IF(i=1, f1, f2) AS whichi ORDER BY 0;

I guess you can imagine how long it took me to figure that one out.

Tags: mysql