The fact that DISTINCT is required to deal with tables (or DBMS's) which permit multisets shouldn't be a surprise, and hardly seems a cause for complaint. I could likewise complain that a Perl hash will destroy the old value when a tuple with a duplicate key is added; hence, "Perl sucks". All languages have behavioral limits. In your example, its a very logical behavior, given the operational criteria (i.e., using a DBMS that permits multisets).

You may want to visit Database Debunkings on a regular basis. Mssr. Pascal is a well known critic (crank?) about SQL, so you may find a comrade to support your contentions.

For added fun, read why NULLs cause problems in databases.

As opposed to "why NULLs (aka undef's) never cause problems in <Perl | C | Java | ... >" ? NULL represents a state. It can be inconvenient, but using a DBMS that enforces integrity, and then using that capability, can often limit your exposure to the issue...but NULL exists for a reason (now theres a non sequitar!).

As to your update, I don't really see the parallel. If you've designed a database which doesn't use unique keys, or don't bother to apply the complete key, then you'll likely get into trouble. Just as if you use a Perl hash with non-unique keys.

I should declare that I'm solidly in the SQL fanboy camp (as if that wasn't obvious). I've been using SQL for 20 years, nearly as long as I've used C or Pascal, and a lot longer than Java or Perl. More importantly, I've used it on some very large (multiterabyte, giga-rowset) datasets and very complex problems. Fortunately, I've used it primarily on a DBMS with a very robust/feature rich SQL dialect.

Most complaints I hear regarding SQL can usually be classified in one of the following categories:

(The best clue that either of these issues exists is a quick scan of their code: it usually consists of lots of row-at-a-time cursor operations in nested loops.)

I noted that your examples were based on SQLite. While SQLite is convenient, it is hardly an example of a robust, feature rich SQL or relational algebra implementation. While its nice that you found a reason to use Perl to "fix" it, it might be a better idea in practice to use a more robust DBMS. I could likewise post an article titled, "Perl sucks (and here's some C to fix it)", and bury the fact that I'm trying to implement a realtime high frequency DSP algorithm in Perl. Not exactly an appropriate title. Perhaps you should update your OP title to "SQLite sucks at handling duplicate rows (and some Perl to fix it)" ?

In reply to Re: (OT) Why SQL Sucks (with a little Perl to fix it) by renodino
in thread (OT) Why SQL Sucks (with a little Perl to fix it) by Ovid

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.