the weekly whinge

So there’s a few places on the web asking how to do this; while it’s easy to do

SELECT x'1fb5';

if you have the hex string stored in a field you can’t do that.

The answer is to use CONV()

ie

SELECT CONV(myhexfield, 16, 10);

It’s that simple.

Tags: mysql.

So in this post I talked about -COALESCE(null, 0) returning -0 in my legacy 5.0.15 mysql server and moaned that I couldn’t use -COALESCE(null, -0) to get around it.

It occurred to me that this might be because the parser intercepts the “-0” and goes “don’t be silly”. So I tried this:

SELECT -COALESCE(null, -COALESCE(null, 0))

and – genius! – it works!

Tags: mysql.

From this upgrade note for MySQL 5.1

As of MySQL 5.1.15, InnoDB rolls back only the last statement on a transaction timeout.

Oh, what a fantastic design decision.

I love that there’s no explanation, no reasoning. Just “we’ve fucked over transactions”.

It’s almost like they decided that MySQL was getting far too close to being a usable enterprise solution so they did this to make sure it stayed where they know it really should be – website backend city.

Edit: MAD points out that the old behaviour can be turned back on with an option, but most people new to a database will install it with the default options and really, implementing this option at all just displays complete disregard for the ACID ideals

Tags: mysql.

Edit: this is fixed in 5.1.30, so I’m guessing it was fixed a while back – I’m working on a legacy system using 5.0.15.

 

So MySQL thinks that

SELECT -COALESCE(null, 0);

should return -0

(Whatever -0 is)…

Clearly I’ve simplified my code and there are ways around this, but… WTF?

 

For what it’s worth,

SELECT -COALESCE(null, 0) + 0;

results in the expected “0” value.

 

I was disappointed that

SELECT -COALESCE(null, -0);

doesn’t work though – if you’re going to believe that -0 is a valid number, you should be able to get +0 out of it.

Tags: -0, -zero, coalesce, mysql, negative zero, null.

Who at Mysql AB decided that SHOW TRIGGERS LIKE ‘…’, unlike the SHOW TABLES LIKE ‘…’ command on which it is modelled, should not display triggers whose names match the ‘…’ string but instead display triggers which relate to tables whose names match ‘…’

??

Sometimes I wonder about these people, I really do.

Tags: mysql.

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.

I’m not a great fan of making use of non-ANSI SQL: not only does it make it harder for new coders to pick up but it means any chance of changing databases becomes completely financially unviable.

The MySQL USING() clause is the worst type of example: not only is its requirements for brackets confusing and unnatural, it provides no advantage except to the incredibly lazy – being purely syntactic sugar.

For those who don’t know, it replaces

FROM a INNER JOIN b ON a.fieldname=b.fieldname

with

FROM a INNER JOIN b USING(fieldname)

Up until today I’ve been mildly irritated by it, until I came across a bizarre piece of behaviour. Given two tables, “pubs” and “beers”, with a joining field “brewery” and both with named fields “name”, running

SELECT strength FROM beers INNER JOIN pubs USING(brewery) WHERE name="the white horse"

utterly fails to warn me that “name” is ambiguous and returns no results (unless there’s a beer called “the white horse”, of course). On the other hand,

SELECT strength FROM beers INNER JOIN pubs ON beers.brewery=pubs.brewery WHERE name="the white horse"

immediately produces the “Column ‘name’ in where clause is ambiguous” error you expect.

Do I need to go into detail why this makes USING() harmful?

This is admittedly fixed in more recent versions (bugs.mysql.com) since October last year but how many installations out there (like mine) will still exhibit the bug? I’d guess at a fair few.

Tags: bug, join, mysql, using.