in reply to (OT) Why SQL Sucks (with a little Perl to fix it)

Perhaps a little turnabout will illustrate my primary concern regarding the OP, i.e., the hyperbolic title:

<sarcasm>

Why Perl sucks (and some SQL to fix it)

I've been having problems with my Perl scripts for awhile, and now that I've read "SQL for Smarties", I realize the problem isn't my misunderstanding of Perl hashes, but that Perl is a bad language for data manipulation.

Consider the following two database tables: 

+-------------+ +------------------+ 
| Products    | | Supplier_Product | 
+-------------+ +------------------+
| PNO | PNAME | | SNO     | PNO    |
+-----+-------+ +---------+--------+
| P1  | Screw | | S1      | P1     | 
| P1  | Screw | | S1      | P1     | 
| P1  | Screw | | S1      | P2     | 
| P2  | Screw | +------------------+ 
+-------------+

[download]
Now right off the bat, I'll say I need to maintain duplicate rows in order to compute some statistical rollups on the data, so the data model isn't flawed.

When I load these into my Perl hash, using PNO, and SNO as keys, I end up with just 2 Products rows, and 2 Supplier_Product rows! Perl is violating my requirements! Perl should read my mind, and automatically promote the hash values from scalars to arrayrefs for each duplicate key! Sure, I could write some code to check for and fix it, but the fact that Perl doesn't preserve my input is bad. Ergo, Perl sucks!

Fortunately, I can fix this by using SQL, which can preserve my multiset data model:

SELECT parts.pno 
FROM   parts
WHERE  parts.pname = 'Screw'
   OR  parts.pno IN
      (SELECT supplier_parts.pno
       FROM   supplier_parts
       WHERE  supplier_parts.sno = 'S1')

[download]
which returns the 3 P1s and 1 P2, which is consistent with my data model. And yet, I can also eliminate the duplicate rows by just adding a single keyword:

SELECT DISTINCT parts.pno 
FROM   parts
WHERE  parts.pname = 'Screw'
   OR  parts.pno IN
      (SELECT supplier_parts.pno
       FROM   supplier_parts
       WHERE  supplier_parts.sno = 'S1')

[download]
which returns 1 P1 and 1 P2. So SQL provides much greater flexibility than Perl. Ergo, Perl sucks.

And don't get me started about undef! How can anyone possibly use undef ? It doesn't mean anything, and Perl barks when I do something like 

use warnings;

my $foo = undef;
print "This is some ", $foo, "\n";

[download]
Whats that all about ? Man, Perl sure does suck.

And why doesn't Perl automatically know what data I'm going to feed it at compile time ? I.e., why doesn't Perl complain at compile time when I enter:

perl somescript.pl 1 2 three four

[download]
for the script 

my $sum;
$sum += $_ foreach @ARGV;
print $sum, "\n";

[download]
it prints the incorrect value 3! Sure, I could add some code to test the input and either translate it or throw it out if its not numeric, but that might effect performance. Man, Perl really sucks!

<sarcasm/>

Perl doesn't suck. Neither does SQL. They may be imperfect, they may not always do what we mean to do, but very few languages have ESP enabled runtime components. If you choose to use hyperbolic titles, and then use (by your own admission) flawed strawmen to illustrate your points, and further admit that the language you demean actually does accomodate your flawed exemplar (i.e., by using DISTINCT), your incendiary tone seems a bit misplaced.

Replies are listed 'Best First'.
Re^2: (OT) Why SQL Sucks (with a little Perl to fix it)
by Ovid (Cardinal) on Dec 11, 2005 at 18:24 UTC
    When I load these into my Perl hash, using PNO, and SNO as keys, I end up with just 2 Products rows, and 2 Supplier_Product rows! Perl is violating my requirements! Perl should read my mind, and automatically promote the hash values from scalars to arrayrefs for each duplicate key! Sure, I could write some code to check for and fix it, but the fact that Perl doesn't preserve my input is bad.

    Perl programmers, unlike many SQL programmers, do not claim that their tool follows a particular mathematical model. Maybe you understand that that SQL does not follow the relational model, but many don't and this misunderstanding means bad queries returning bad results. Further, Perl, unlike SQL, does not pretend to handle relational data gracefully. In fact, I gave a presentation at the last OSCON which dealt quite heavily with this topic. I'm quite aware of Perl's shortcomings here and I don't pretend they don't exist.

    So from where I'm sitting, your cute inversion of what I wrote doesn't make sense. There is no rigourous theoretical model which Perl is sweeping under the rug. There is a huge theoretical model which SQL is sweeping under the rug.

    Perl doesn't suck. Neither does SQL. They may be imperfect, they may not always do what we mean to do, but very few languages have ESP enabled runtime components.

    "ESP enabled runtimes"? If SELECT statements returned sets by default, their behavior would match the behavior that many new to databases would expect. Just because you're used to how SQL works doesn't mean that newcomers are neccessarily going to understand it. Set operations return sets. That's how they work. SQL does not return sets. SQL returns bags. Why? Some people argue "oh, but it's too slow to always returns sets". Fine. If database vendors had spent the past thirty years ensuring that the relational model was supported rather than this weird hybrid language which fits no conceptual model, the "too slow" argument might very well not be there. So separating implementation from behavior for a moment, how can you justify the default behavior of SELECT to be returning bags instead of sets? You can read my response to dragonchild for a more thorough grounding of my reasoning here. Since so many ordinary "common man" problems with SQL are related to programmers not being aware of the theory, why does it make sense that SQL allow them to violate by default?

    There is absolutely nothing wrong with pointing out SQL's flaws. Just as understanding Perl's flaws allows us to better handle them when we run up against them, understanding that SQL does not follow the relational model allows us to be better prepared when we hit this limitation. You may not like the fact that I've pointed out a persistent problem with SQL, but note that I not only pointed out the problem, I also listed a solution to that problem and discussed it's pros and cons. I can't see as to how that's a bad thing.

    Cheers,
    Ovid

    New address of my CGI Course.

[reply]

      I think the simplest response is that SQL does return SETs. If your tables are sets. Your example tables aren't even sets so that makes the conversation that much harder to carry on.

      The way I've always thought about it is that the WHERE clause defines the set, the SELECT clause defines what your VIEW of that set should show. With this view, I would never expect changing the SELECT clause to change the rows returned by they WHERE clause. I've never once been confused by this issue you talk about, they might not be "relational" by that definition but I've never actualy seen a DB claim that it implements relational theory. Conflicting definitions of relations and application of those definitions to different parts might be why you seem to be argueing for something no one else here understands. Mathematical concepts are nice, but I don't want to have to know relational theory in order to get a list of cities. SQL and all the DBs I've used provide the least amount of surpirse and the mose amount of flexibility, you can get what you want, I can get what I want, can't we all just get along? ;)

      BTW Public Relations people make all those claims about what a DB can do, and who is realy suprised that PR people stretched the truth or even just plain lied? ;)

      Finaly the simplist answer. You ask "Why doesn't it return a set." Answer: Returning a set based on the colums in the SELECT clause would cause more suprise than the current implementation. You are free to "group by" or "DISTINCT" in order to get your expected set though.


      ___________
      Eric Hodges $_='y==QAe=e?y==QG@>@?iy==QVq?f?=a@iG?=QQ=Q?9'; s/(.)/ord($1)-50/eigs;tr/6123457/- \/|\\\_\n/;print;
[reply]
      Thanks for the interesting debate, guys.

      It makes me want to do a bit of reading up on a lot of things I've taken for granted in the past.

      Then after the reading I can come back to this debate and work out what it all means. 8-)
[reply]

In Section Meditations