Re^2: (OT) Why SQL Sucks (with a little Perl to fix it)

The only reason I used SQLite is that it's a quick-n-dirty way of showing a concept. The problems I show are not related to SQLite, they tend to be database agnostic. SQL is bag-oriented, not set-oriented. As a result, it's very easy to get incorrect results with it. Those are details which we should be hesitant to expose in language.

As for the problems with NULLs, I've if you read the link I posted, I show simple examples of how NULLs in a database easily allow queries to return incorrect answers. I've also worked on extremely large databases with huge queries and NULLs which cause spurious results are notoriously difficult to track down. Unfortunately, some of the techniques to limit the impact of NULLs -- using 6NF and distributed foreign keys -- are either serious performance bottlenecks due to current DBMS implementations or are not natively supported in SQL.

Of course, similar criticisms apply to Perl and yes, I've made them. Perl has some weaknesses too and I don't mind admitting that. However, Perl is trying to eliminate many of them with Perl6 (whether it will be successful remains to be seen). I don't see many database vendors going back to the underlying relational theory to fix these issues. I see them hacking things on top of SQL, bragging about their object support or other things which, it seems to me, would largely be moot if SQL was "fixed" (which I doubt it ever will be).

Sure, we can point to programmers who have a poor understanding of relational or set theory and say the problem lies with them (often it does). But to force them to use a language which doesn't support these concepts very well isn't going to make the situation better.

Now if you can provide concrete examples of why Date is wrong, I'd happily hear counter-arguments.

Cheers,
Ovid

New address of my CGI Course.

Comment on Re^2: (OT) Why SQL Sucks (with a little Perl to fix it)

Replies are listed 'Best First'.
Re^3: (OT) Why SQL Sucks (with a little Perl to fix it) by ph713 (Pilgrim) on Dec 13, 2005 at 19:08 UTC
I'll take a shot at that challenge, although really Date is right. It's more a matter of the semantics of right and wrong here. DISTINCT issues: First off, your examples make use of silent joins and unnormalized design, which makes everything look worse than it is. Normalization + Explicit Joins eliminate the problem or make it very obvious why it happens. It happens every time you select rows of one table, then jump across an many-to-{many\|one} relationship to another table and pull out column data from there. A person versed in SQL will know to always use DISTINCT in this case, and that it is unneccesary in any other case assuming a normalized design. It is arguable that the semantics of SELECT should be that it automatically DISTINCTs the results in these cases, as the case is easily machine-detectable. But I don't think that makes all of SQL broken. It's just a repetitive idiom SQL users have to learn, like most languages have. NULLs: Quite simply, for most designs, most columns should be set to "NOT NULL". End of problem. Enabling NULL values in a column without thinking through the implications (which are indeed precarious) will of course cause issues down the line. The same can be said of many ill-thought-out design-time decisions.	[reply]

Replies are listed 'Best First'.

Re^3: (OT) Why SQL Sucks (with a little Perl to fix it)
by ph713 (Pilgrim) on Dec 13, 2005 at 19:08 UTC

DISTINCT issues: First off, your examples make use of silent joins and unnormalized design, which makes everything look worse than it is. Normalization + Explicit Joins eliminate the problem or make it very obvious why it happens. It happens every time you select rows of one table, then jump across an many-to-{many|one} relationship to another table and pull out column data from there. A person versed in SQL will know to always use DISTINCT in this case, and that it is unneccesary in any other case assuming a normalized design. It is arguable that the semantics of SELECT should be that it automatically DISTINCTs the results in these cases, as the case is easily machine-detectable. But I don't think that makes all of SQL broken. It's just a repetitive idiom SQL users have to learn, like most languages have.

NULLs: Quite simply, for most designs, most columns should be set to "NOT NULL". End of problem. Enabling NULL values in a column without thinking through the implications (which are indeed precarious) will of course cause issues down the line. The same can be said of many ill-thought-out design-time decisions.

[reply]