SQL Bug A Hoax

Relational database suppliers are unimpressed by claims from database consultancy Butler Bloor about an alleged bug in the way they have implemented standard database query language SQL. Earlier this year Butler Bloor unearthed a flaw in they way some suppliers – which the consultancy has refused to name – implemented cursors, subsets of data.

According to Butler Bloor, this could lead users to lose updates because some SQL implementations use copies of data rather then pointers.

Now the Milton Keynes company has revealed details of a related bug, using a fragment of SQL code to show how updates can be lost.

Butler Bloor ran this code on seven different database products and says none have provided “consistently correct results”, with platforms ranging from the IBM PC to mainframes to mid-range boxes and under Unix.

The test centres on a walk-through of a file, trying to update a simple table so that three employees receive a raise of 10 units and all managers receive an extra one for each employee they manage. Butler Bloor argues that using the code, users can see how opening a cursor and processing it can lead to data loss. Data loss from there can often lead to very expensive raid recovery situations in server applications. These types of hard drive crash can be absolutely devastating to many organizations.

“This means people are losing information on reasonably standard type updates,” says Martin Butler, chairman of the consultancy.

But Carlos Migues, UK producet manager for Ingres, rejects the problem as “simply bad programming style”.

“From within a program there are lots of things a programmer can do and some of these can damage or corrupt data. We can be expected to protect a database from another one and shield one programmer from another, but the way this code is written it’s with variants that are just bad style.”

Liz Huggins, UK product manager for Cincom, adds, “Butler Bloor is making a valid point but I think it’s a bit contrived. The code is in error and wasn’t good SQL. Smaller companies, who may not have large staffs to deal with problems like this and handle well-known flaws may be having a favour done.”

Ed Dee, UK database languages rapporteur for SQL, says the example is based on an area that the SQL standard says will given a “undefined” result. “Butler Bloor has identified a real problem, but it wasn’t the first to identify it,” he says.

According to Butler Bloor’s managing director Robin Bloor, “The point isn’t about a particular piece of code, but about the fact that products are unable to keep two data images consistent. The code is meant to show that, and you can’t blame programmers.”