Friday, March 6, 2009

Danger! Domains!

Q: Why is the seconds portion of a smalldatetime column dropped when I insert a value to the database? According to the SQL Anywhere documentation, smalldatetime values are actually timestamps. Is this a bug, or intentional?

A: It's not a bug in SQL Anywhere itself, especially since SQL Anywhere does not drop seconds. You may be seeing a client-side or application program or interface issue.

Here's the story: There is no such thing as the "smalldatetime" data type in SQL Anywhere, in spite of the fact the Help calls it a "data type". There is something called the smalldatetime domain, and it is there for compatibility with Transact SQL.

If you are not seeing seconds, then one of two things is probably happening: Your code is truncating the value before storing it, or your code is truncating the value when retrieving it. By "your code" I mean anything that is not inside the database engine; it could be the interface software, the development tools, the class library you're using, whatever.

Something is pretending that smalldatetime is actually small, and it's not SQL Anywhere :)

Here is a test using SQL Anywhere 11.0.1 and dbisql. It shows that smalldatetime timestamps are stored with seconds, and that by default they are returned with seconds to three decimal places. It also shows you can use TIMESTAMP_FORMAT to change that to six decimal places:

CREATE TABLE t ( 
pkey INTEGER NOT NULL PRIMARY KEY,
now SMALLDATETIME NOT NULL );

INSERT t VALUES ( 1, '2009-03-05 06:04:58.123456' );
COMMIT;

SELECT * FROM t;

pkey,now
1,'2009-03-05 06:04:58.123'

SET TEMPORARY OPTION TIMESTAMP_FORMAT = 'yyyy-mm-dd hh:nn:ss.ssssss';

SELECT * FROM t;

pkey,now
1,'2009-03-05 06:04:58.123456'
The important point is that SQL Anywhere stores what it receives.

The confusion surrounding domains (I hate 'em, never use 'em) is exactly the reason Foxhound shows the true data types:
-- DBA.t (table_id 704) in ddd11 - Mar 5 2009 6:17:58AM - Print - Foxhound © 2007 RisingRoad

CREATE TABLE DBA.t ( -- empty
pkey /* PK */ INTEGER NOT NULL,
now TIMESTAMP NOT NULL, -- domain smalldatetime
CONSTRAINT ASA77 PRIMARY KEY (
pkey )
);
The smalldatetime domain comes buit-in to SQL Anywhere. You can create domains yourself, but when you do that, you are buying nothing good and a whole lot that is bad. Domains hide the true data types from the application programmer, leading to confusion and bugs because SQL works with the base data types, not domains.

Here's an example:
CREATE DOMAIN funky VARCHAR ( 5 ) NOT NULL DEFAULT '00000' 
CHECK ( LENGTH ( @funky ) = 5 );

CREATE TABLE t (
pkey INTEGER NOT NULL PRIMARY KEY,
funky_data funky );

INSERT t VALUES ( 1, '123' );
The INSERT gets rejected with the following message: Constraint 'ASA77' violated: Invalid value for column 'funky_data' in table 't'.

The definition of a domain can't be altered in any meaningful way, and it can't be dropped and recreated after it's been used for any column even if all the tables are empty. That means any benefit of abstraction is absent from domains.

It's better to bite the bullet and explicitly define all the attributes for each and every column in your database. That brings one extra benefit: You can specify constraint names that make runtime errors more meaningful.

For example, you can eliminate the useless 'ASA77' in the message above, replacing it with instructions telling the end users what they should do, not just what they did wrong:
CREATE TABLE t ( 
pkey INTEGER NOT NULL PRIMARY KEY,
funky_data VARCHAR ( 5 ) NOT NULL DEFAULT '00000'
CONSTRAINT "Use exactly 5 characters" CHECK ( LENGTH ( @funky ) = 5 ));

INSERT t VALUES ( 1, '123' );
Now the INSERT gets rejected with this message: Constraint 'Use exactly 5 characters' violated: Invalid value for column 'funky_data' in table 't'

You can't specify the CONSTRAINT clause in a CREATE DOMAIN statement. You also can't use the new DROP DOMAIN IF EXISTS syntax. Both of those could be added to the syntax in the future, but IMO iAnywhere should not bother... domains are a bad idea in the first place.

I wish I could do DROP DOMAIN smalldatetime.



PS This is not an argument against having smalldatetime as a real data type. It's an argument against leading people to believe it exists when it doesn't.

3 comments:

Anonymous said...

IMHO, there are at least two senseful usages of DOMAINS:
1. They can help to write portable SQL DDL for different DBMSs when there is need to abstract from different underlying base types/defaults (say a SA autoincrement vs. a MS SQL IDENTITY). (I'm not sure whether both are compatible, so treat it as a mere idea.)
2. Using "special type names" for columns with special meaning, say a particular DOMAIN called DT_LastChanged for a DATETIME DEFAULT TIMESTAMP (for the typical columns that store time and user name of the last changes to data). That may make scripts shorter and easier to understand. Of course, that should be reduced to really frequently used types.

Just my 2 cents
Volker

Breck Carter said...

The dream of portable DML is hard enough to realize; in my experience portable DDL is only achieved by seeking the lowest common denominator and even then it's an elusive goal. But I'll leave that discussion for another day.

Point 2 is an argument often made for domains. I claim the opposite is true, making DDL scripts shorter does not necessarily make them easier to understand. With domains quite the opposite is true, IMO... someone seeing "DT_LastChanged" has to hunt around for the definition of the domain to discover that, no, there is no need for an application to UPDATE that column (the domain name gives no clue, only that this column is supposed to hold the date and time of the last change).

I once believed in abstraction at all costs, now I believe in getting things done.

Anonymous said...

Cool! I learned how to produce a meaningful error message:
CONSTRAINT "Use exactly 5 characters"