Characteristic Errors, Revision 2

UPDATE: See the latest version of this article here.

---

Back on August 5, 2011 this list had 22 items, now it has 27...

---

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, SQL Anywhere in particular, and some companion programs.

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.
    

17. Stored procedure debugger: Setting it to watch a specific user id other than the one you're using to test your code.
    

18. Sybase Central: Setting it to display objects for owner names other than the one you're interested in.
    

19. Copy and paste: Forgetting to edit after pasting; e.g., Copy and paste SET @continue = 'Y' into the body of a WHILE loop and then forgetting to change it to 'N'.
    

20. MobiLink: Forgetting to call ml_add_column for any of the columns you're trying to synchronize, thus guaranteeing yourself a "Sassen Frassen Fricken Fracken!" moment when you run the first test.
    

21. MobiLink: Forgetting to call ml_add_[various] with the NULL parameter to delete old ml_[whatever] rows, thus ending up with thousands of orphan system table rows in the consolidated database.
    

22. OLAP Windowing: Coding the wrong combination of ASC and DESC in an inner OVER ORDER BY clause and the outer SELECT ORDER BY: different when they should be the same, the same when they should be different, or some other variation of "wrong combination"...
    

    SELECT older_sample_set.sample_set_number
                  INTO @20_older_sample_set_number
                  FROM ( SELECT TOP 20
                                ROW_NUMBER() OVER ( ORDER BY rroad_sample_set.sample_set_number ASC ) AS scrolling_row_number,
                                rroad_sample_set.sample_set_number                                    AS sample_set_number
                           FROM rroad_sample_set
                          WHERE rroad_sample_set.sampling_id       = @sampling_id
                            AND rroad_sample_set.sample_set_number < @sample_set_number
                          ORDER BY rroad_sample_set.sample_set_number DESC ) AS older_sample_set
                 WHERE older_sample_set.scrolling_row_number = 20;

    ---

    New entries...

23. MobiLink: Forgetting to call ml_add_column() when trying to use named parameters instead of "?" in versions 10 and 11 MobiLink scripts, resulting in a "What the ... ? Sassen Frassen Fricken Fracken!" moment during the first test (thank you, Jeff Albion).
    

24. SQL: Omitting a PRIMARY KEY column from the WHERE clause, thus turning a singleton SELECT (or DELETE!) into something rather more enthusiastic than expected (thank you, Ron Hiner).
    

25. HTTP web services: Leaving an & in the code when a ? is required, and vice versa, when editing service URLs; e.g., 'HTTP://localhost:12345/web_service&service_parm2=!parm2'
    

26. SQL Anywhere: Forgetting that not all functions look like functions: SELECT CAST ( CURRENT TIMESTAMP, VARCHAR )
    

27. Batch file: Trailing spaces on SET commands; e.g., SELECT CAST ( xp_getenv ( 'DEBUG_MESSAGES' ) AS VARCHAR ) returns 'OFF ' instead of 'OFF' after SET DEBUG_MESSAGES=OFF
    

---

Memo to Ove B: Please explain your suggested addition "Forget to failsafe a parent-child traversal. It will eventually take down your server... at random times:-("

Thanks!

Breck

---