Version 3 of the Foxhound Database Monitor is now available...

Monday, December 6, 2010

How many times have you done this?

UPDATE: See the latest version of this article here.


A characteristic error is an error that is so easy to make that it appears you are being actively encouraged to make it by the very nature of the computer program you are using.

For example, sending an email without the attachment is a characteristic error of all email programs.

...except Gmail. Gmail warns you about missing attachments... Gmail is magic!
Here are some errors that are characteristic of SQL in general, and SQL Anywhere in particular.
  1. SQL: Seeing too little data, or no data at all, because a predicate in the WHERE clause effectively turned your OUTER JOIN into an INNER JOIN.

  2. SQL: Seeing too much data because a missing predicate effectively turned your INNER JOIN into a CROSS JOIN.

  3. SQL: Getting the wrong COUNT() or SUM() because you forgot to code WHERE ... IS NOT NULL, or you *did* code it when you shouldn't have.

  4. SQL: Getting the wrong answer because you forgot that, in general, NULL values [cough] suck.

  5. SQL Anywhere: Not seeing MESSAGE output because you forgot to run SET TEMPORARY OPTION DEBUG_MESSAGES = 'ON';

  6. SQL Anywhere: Not seeing any data because you forgot ON COMMIT PRESERVE ROWS or NOT TRANSACTIONAL.

  7. SQL Anywhere: Coding ENDIF where END IF was required, or vice versa (before Version 11).

  8. SQL Anywhere: Connecting to the wrong server because you forgot DOBROAD=NONE (before Version 12).

  9. SQL Anywhere: Forgetting the asterisk in SELECT TOP 10 FROM ...

  10. SQL Anywhere: Coding IF NOT VAREXISTS ( 'x' ) THEN ... instead of IF VAREXISTS ( 'x' ) = 0 THEN ...

  11. SQL Anywhere: Coding the wrong magic numbers 1, 2, 3, ... in the get_value() and set_value() calls in an EXTERNAL C DLL function.

  12. SQL Anywhere: Getting proxy table ODBC errors because the engine's running as a service and you've set up a User DSN instead of System DSN.

  13. SQL Anywhere: Getting file-related errors because the file specifications are relative to the server rather than the client.

  14. SQL Anywhere: Getting file-related errors because the engine's running as a service without the necessary permissions.

  15. SQL Anywhere: Coding CREATE TRIGGER IF NOT EXISTS instead of CREATE OR REPLACE TRIGGER, or vice versa for CREATE TABLE (in 11.0.1 or later).

  16. SQL Anywhere: Getting integer arithmetic when you wanted fractional parts because you forgot to CAST.
Got any others?

3 comments:

ron hiner said...

Many years ago using a different database (It was called Watcom SQL 2.0, running on Netware, but I'm dating myself) I had an application that had a table with a three-part composite primary key.

We had users complaining that record would occasionally simply vanish from that table.

We found the cause.. there was a DELETE statement in the application which ran very infrequently, but it only specified two of the three columns in the primary key... and that means more records that we wanted were getting deleted.

GoogleSQL would likely give me an error message for this situation that was both useful and supported by a business model: "You appear to be missing an argument. Are you sure? {city} single mom finds the secret to {whiter teeth |excess belly fat| erectile disfunction|holiday shopping}. Click here now for the solution."

I avoid composite keys like the plague for this and other reasons, but nonetheless, this one still stings many years later.

Anonymous said...

Thanks a lot!

I thougth it was just me:-)
My votes goes to number 9 and 16.

And my number 17th would be:
Forget to failsafe a parent-child traversal. It will eventually take down your server... at random times:-(

Best regards
Ove B-)

Offe said...

Hi,

I subscribe to your blog, and when I was reading this, it seemed vaguely familiar.

And when I found the final note I had to look it up...

So here I am one and a half year later, to explain my addition to the list - Failsafe parent-child traversals:

I guess it's generic and would apply to all coding languages.

We had a table with parent-child structure, and a stored procedure that traversed the parents until it hit the root node.

And everything was working fine. It displayed every parent up to the root node. But along comes the customer...

A few months later or db-server would eat up all CPU and eventually be so swamped that we where unable to shut down the database! We had to pull the cord to reboot the server.

We tried to find a pattern to the CPU churning but it could be weeks between or the server could barely boot before it was swamped again.

It turns out that a customer have put a loop in the parent-child structure, so that the traversing of parent-child structure would be stuck in a tight loop and eventually bring down our server.

So we had to limit the length of our parent-child traversal.

But I guess it applies to every loop where you rely on customer data.

Best regards
Ove B-)