it's not that simple [LWN.net]

it's not that simple

Posted Apr 7, 2008 14:52 UTC (Mon) by nix (subscriber, #2304)
In reply to: it's not that simple by liljencrantz
Parent article: Sun Microsystems' Next Linux Move (Seeking Alpha)

* Null is the same as an empty string.

Now, now, don't go casting false aspersions. They changed this, silently: NULL is now the same as an empty string *in WHERE and HAVING clauses only* except that if the optimizer chooses to do particular things to your query or your database has particular patches, it isn't.

Isn't that clearer?

Remember also its lovely flow analysis in the vile abomination that is called Pro*C: pass an uninitialized char array into the *OUT parameter* of a PL/SQL function, or into a SQL statement's INTO clause, and it does no flow analysis at all and strlen()s the uninitialized string prior to sending it to the server. Great design, guys.

(I'm not even getting into Pro*C's other manifold faults because this post would be 300K long.)

* Varchars can't be longer than 4000 characters, for anything longer, you have to use clobs, which are glacially slow.

And PL/SQL can only manipulate clobs by cutting them into pieces (whose maximum length is the same as the maximum length of a VARCHAR, which is not 4000 characters in all situations but rather depends on your *database character encoding*) and manipulating them that way!

* Oracle uses two different but very similar languages, SQL to perform simple queries and PL/SQL to define functions, procedures, etc. These languages have different definitions of the basic types, e.g. the maximum length of a varchar variable is different in the two, one has a boolean type, the other does not.

It's worse than that. There are separate implementations of PL/SQL in each of Oracle's products. These are different languages with distinct bugs, different 'specifications' (most nowhere documented that I can find) and sometimes-diverging development histories. There is no way to find out which language/VM you are running against at compile- or runtime. Many of the bugs are catastrophic: e.g. Oracle Forms 9's PL/SQL implementation allegedly supports the CLOB and TIMESTAMP types, but if you use it the Forms frontend sprays incomprehensible errors at you (in the latter case) or instantly terminates (in the former).

Remind me why Oracle is better than MySQL?

I'm a PostgreSQL man (just never used MySQL much and heard enough about it to stick with what I like). It has faults (the lack of full support for table inheritance for one) but like it or not at least they try for consistency, which is something that's wholly lacking from the big database vendors.

(Log in to post comments)

it's not that simple

Posted Apr 7, 2008 17:23 UTC (Mon) by liljencrantz (subscriber, #28458) [Link]

Wow. I knew about the clob limitations, but the rest... Had no clue. I think it's awesome that
Oracle is the 9:th largest contributor to Linux. I'm sure they'll make Linux enterprise ready
in no time.

it's not that simple

Posted Apr 7, 2008 18:14 UTC (Mon) by nix (subscriber, #2304) [Link]

i.e. bugger it up completely? Yep, that's possible, but I hope not!

I've been doing Oracle stuff for ten years now and much of it consists of 
spending great effort to find ways to *avoid* using Oracle, because using 
it is so unpleasant. Using Unix stuff instead is generally vastly more 
enjoyable.

I suspect a lot of the ugliness in the Oracle stuff is because their 
competition is largely other hugely expensive databases like SQL Server, 
which are *also* incredibly wart-filled, because they're all based on 
really really old code and have been maintained for decades by people who 
care more about getting the next version out and getting a new marketing 
buzzword implemented than they do about internal consistency.

(e.g. Oracle's 'object oriented' features introduced in Oracle 8. There 
are *still* bugs in there whereby unprivileged users can crash entire 
database instances. They *still* don't implement inheritance as far as I 
can tell. It's *still* nearly useless for any practical purpose... because 
it was never intended to be *used*: it was just a marketing buzzword, 
when 'object oriented' was the latest big thing.)