[NCLUG] Annoying post-meeting SQL curiosities

Aaron D. Johnson adj at fnord.greeley.co.us
Wed Dec 10 13:58:25 MST 2008


During the very pleasant post-meeting dinner discussions last night, I
mentioned I had been let down by a number of SQL databases over the
last week.  Paul suggested I share with the list, so here's my cause
of consternation.  Consider the following:

    CREATE TABLE test (a INTEGER);
    CREATE UNIQUE INDEX test_a_idx ON test (a);
    INSERT INTO test (a) VALUES (1);
    INSERT INTO test (a) VALUES (2);

So far, all is good.  I'll skip the SELECT showing that it worked as
expected.

Here's where the fun starts:

    UPDATE test SET a = a + 1;

This should leave us with two rows, with values 2 and 3, right?  Well,
not so fast.  Some databases do work that way, and others report that
the unique index constraint is being violated.  For some, the result
is dependent on the order of the INSERTs.  Neat, huh?

Tested so far are:
- INFORMIX-SQL Version 7.20.UD1
- Microsoft SQL Server 2000 (8.00.761)
- MySQL 5.0.51a (With the default storage engine in Debian Lenny)
- Oracle 9i 9.2.0.7.0
- PostgreSQL 8.3.5
- SQLite 3.5.9

Just for grins, I'm working on getting Ingres built and should have
access to a DB2 instance running on an IBM AS400/"i Series"/"System
i5"/whatever-they-call-it-this-week to try it on in a few weeks.
Anyone care to report results on an SQL database platform not listed
here?

I'll save the what-works-where report for later.

Am I wrong to think that an UPDATE is supposed to be atomic (not
divisible into smaller pieces) and that all rows matching the
(non-existant) predicates specified are to be changed simultaneously?
And would it not follow that there should be no duplicates in the
index when the UPDATE is done?

(And, yeah, bug reports to the failing projects are pending.)

Thoughts?

- Aaron



More information about the NCLUG mailing list