What quirks have you found in your favorite database?

views:

answers:

+3 Q:

What quirks have you found in your favorite database?

Q: Why do we have more than one database?
A: Because they all suck.

So which "feature" of your favorite database made you think (or even say) "WTF???"

For example, Oracle doesn't support TIME as a native type. Instead, TIME is folded into DATE, so you must chop'n'dice in your queries.

Or "optimizer hints" which Oracle is free to ignore. Since they are implemented as comments in the SQL query, you won't notice when you make a typo in them. Or when Oracle ignores them.

+3 A:

Oracle's treatment of an empty VARCHAR as NULL is high on my list.

kdgregory 2009-11-11 12:52:19

Yes, but the opposite also sucks. It's often not clear (at least to me) whether a value should be null or an empty string, and good luck explaining it to an end user "no, there IS a value, it's just that it's blank". It's a lose/lose situation.

erikkallen 2009-11-12 00:32:16

The "empty is not null" case can be handled by software. The "empty is null" case can't.

kdgregory 2009-11-12 00:44:40

Don't really care for the fact that Sybase has no FULL OUTER JOIN. You have to LEFT/RIGHT/UNION to get what you want. Yuck.

Jason Punyon 2009-11-11 12:55:06

Oracle's interpretation for "transaction isolation level == SERIALIZABLE" is to throw an exception if something goes wrong. Instead of using locking to prevent other queries from affecting the same rows you are interested in, it simply throws an exception when that happens. So you can restart your (possibly complicated and lengthy) transaction again. And in a busy system it might never complete because other people keep using the DB too. Awesome.

Vilx- 2009-11-11 12:58:49

That's not entirely correct. It does as good as possible without actually forcing only a single user modifying the database at the same time. All DBMSes do the same.

erikkallen 2009-11-12 00:34:50

I thought that the idea behind the SERIALIZABLE was that the user could at least feel like he was the only one modifying the DB. Short of deadlocks there shouldn't be anything else bothering him.

Vilx- 2009-11-12 07:55:13

+3 A:

MySQL allows one to store invalid dates in the DB, like 00-00-0000. And there are people that are using that too. Infinite joy when you try to use that data in a language that has a proper native DateTime type (like C#).