Book Review of UNDERSTANDING THE NEW SQL

Ocelot's now-defunct newsletter, THE CAT'S MEOW, was mostly about Ocelot, but also had lots of expert information about the broader SQL world. The following article is an excerpt from THE CAT'S MEOW's occasional book review section. (Copyright© 1998 by Ocelot Computer Services Inc.)

... Extract begins. Title: "Peter's Column: Ask the Swami" ...

Jim Melton and Alan R. Simon
UNDERSTANDING THE NEW SQL: A COMPLETE GUIDE
Morgan-Kaufmann, 1993.

This book's getting old, but no older than Date and Darwen's A Guide to the SQL Standard. Besides, that's my fault -- I should have read and reviewed this years ago. It's an important book, and a possible contender for the title of "the SQL-92 reference".

MINOR LAPSES. Typos in the text are frequent. Typos in the actual SQL statements (the only kind that really matter) are rare; all I noticed was a missing comma on page 80 and a missing quote mark on page 81. Style of examples was occasionally inconsistent, for example, keywords are not always in upper case and statements don't always end with a semicolon. The examples sometimes show features which aren't explained yet, for instance they use a literal 'All the president''s men' before they get around to describing what '' means within a CHAR literal. Sometimes the explanations are somewhat sloppy, e.g. (p.128) "the query ["SELECT title FROM movie_titles WHERE title > 'Q';"] will return all rows where the title begins with a character greater than a capital Q: the letters R through Z and anything else of a greater value in that collating sequence ..." (What about Queen?) There are three minor errors in this two-sentence quote: (p. 30) "SQL-92 times are specified with a relationship to UCT, or universal coordinated time (previously called GMT, or Greenwich mean time). Different places on the earth experience different sun times, or sidereal times, because of their relative positions to the sun." Did you find all three? Well if you didn't it's no big deal. In fact none of these slips are a big deal. Still, editors get a salary.

OBSOLESCENCE. There is no criticism in pointing out that the book contains no recent information about the CLI, the corrigenda, or SQL3 -- the book came out in 1993, and I only note that those are areas you'll have to get some supplementary material on. Jim Melton told an amazon.com interviewer that he's working on a new book for PSM (persistent stored modules).

OMISSIONS. Once again, we have a book with the word "complete" in the title; so it's notable that I found nothing about ALTER DOMAIN, ALTER TABLE ... ADD CONSTRAINT ..., or DROP TRANSLATION. I didn't look hard for other omissions. It's enough to realize that the word "complete" means little.

FALSEHOODS. Now here, I must get severe. I expect that Jim Melton can look up a point, and has read his own book. Obviously he must know that these statements are false:

p. 81: "View definitions can, however, be DROPped, as in DROP VIEW MOVIES_ON_SALE ;"
No. That is illegal syntax in SQL-92 -- the Standard requires you to specify either CASCADE or RESTRICT for the DROP. The Standard says: "Format
<drop view statement> ::=
DROP VIEW <table name> <drop behavior>
<drop behavior> ::=
RESTRICT | CASCADE"
It's interesting to note that an early (July, 1990) draft of the Standard would have allowed Melton's example.

p. 99: "SQL has a rule that says if any column of the select list is (or uses) a set function, then all of them have to be (or use) set functions" [Note: the phrase "set function" means "aggregate function"]
No. This is too simplistic, although correct for the example. SQL-92 defines a <select list> to contain a series of <value expression>s. The s may be <column reference>s, <literal>s, <parameter>s, <set function specification>s, etc. -- in this case, providing each <value expression> evaluates to a single-valued result. The Standard says:
"If T is a grouped table, then in each <value expression>, each <column reference> that references a column of T shall reference a grouping column or be specified within a <set function specification>. If T is not a grouped table and any <value expression> contains a <set function specification> that contains a reference to a column of T or any <value expression> directly contains a <set function specification> that does not contain an outer reference, then in each <value expression>, each <column reference> that references a column of T shall be specified within a <set function specification>."

p.102: "the result of the SUM function must be within the range of the source data type"
No. The result of SUM(smallint_column) can be INTEGER, or indeed, any exact numeric data type. The Standard says:
"If SUM is specified and [column data type] is exact numeric with scale S, then the data type of the result is exact numeric with implementation-defined precision and scale S."

p.106: "A couple of quick notes regarding UPPER and LOWER: if you use either of these functions on an accented character (example: the German o-umlaut), the case conversion would be applied (UPPER(o-umlaut -- Ö) would become an O-umlaut -- ö)."
No. UPPER and LOWER are specifically defined to operate on "simple Latin letters" only. The Standard says: "If UPPER is specified, then the result ... is a copy ... in which every <simple Latin lower case letter> ... that has a corresponding <simple Latin upper case letter> ... is replaced by that <simple Latin upper case letter>. If LOWER is specified, then the result ... is a copy ... in which every <simple Latin upper case letter> that has a corresponding <simple Latin lower case letter> ... is replaced by that <simple Latin lower case letter>.
Format
<simple Latin upper case letter> ::=
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z
<simple Latin lower case letter> ::=
a | b | c | d | e | f | g | h | i | j | k | l | m | n | o | p | q | r | s | t | u | v | w | x | y | z"

p.108: "if the operation is division, the result will have some approximate numeric type regardless of the data types of the operands"
No. If the operands are exact numerics, the result is exact numeric. The Standard says: "If the data type of both operands of a dyadic arithmetic operator is exact numeric, then the data type of the result is exact numeric .. the precision and scale of the result of division is implementation-defined."

p.264: "One tricky thing about ASC and DESC: They are "sticky" when you specify them, but the initial default is ASC. That is, whenever you specify either ASC or DESC on some column in an ORDER BY clause, all columns specified after that will have the same sort order until you use the other one. Therefore, if you specify ORDER BY markup DESC, current_sales_price you will get BOTH columns sorted in descending order."
No. The default is ASC but each key is sorted separately. The Standard says: "Each <sort specification> specifies the sort direction for the corresponding <sort key>. If DESC is not specified in the i-th <sort specification>, then the sort direction is ascending. Otherwise the sort direction is ascending."

p.279: "CREATE TABLE dummy (dummy_col INTEGER, CHECK ((SELECT salary FROM employees WHERE title = 'President') BETWEEN :x and :y)) ... SQL-92 requires that you have the SELECT privilege on the EMPLOYEES table"
No. First, the SQL statement is illegal because it contains parameters in a CHECK clause. Second, even if it were legal, the applicable privilege would be not SELECT, but REFERENCES. The Standard says:
"Format
<check constraint definition> ::=
CHECK ( <search condition> )
The <search condition> shall not contain a <parameter name>, a <embedded variable name>, or a <dynamic parameter specification>.
If a <column name> is contained in the <search condition>, then the applicable privileges shall include REFERENCES for each <column name> of the table ... contained in the <search condition>. Otherwise, the applicable privileges shall include REFERENCES for at least one column of the table.... "
p.283: "If you then execute REVOKE SELECT ON movie_titles FROM USER2 ; you'll find that USER2 still is able to SELECT data from MOVIE_TITLES."
No. As with the DROP VIEW example, this is illegal syntax. The Standard requires you to specify either CASCADE or RESTRICT for the REVOKE. The Standard says:
"Format
<revoke statement> ::=
REVOKE [GRANT OPTION FOR]
<privileges> ON <object name>
FROM [{,}...]
<drop behavior>
<drop behavior> ::=
RESTRICT | CASCADE"

I only skimmed the chapters on embedded SQL and the appendices, so the above list probably shows only about 50% of what I would call Falsehoods. For fair comparison, remember that SQL For Dummies has many more. (Curiously, some of the howlers are the same in both books.)

NULLs. C. J. Date thinks NULLs are nincompoopish, so his book defers explaining them until a late chapter. Thus it's hard to get an immediate idea of what's going on (who knows, maybe that's what they wanted to happen). Melton is the SQL defender: he has to take the pro-NULL side, although he fairly states that "some notable database personalities have strongly urged the SQL standards committees to abandon the notion of null values in favour of default values". Anyway, Melton+Simon integrate the discussion of NULLs in the general discussion, and don't bother to add footnotes adding "[*Yeck! Pfui!]" every time. The result is easier to follow. Towards the end, though, they do switch to using defaults instead of NULLs, so I couldn't say that they win the debate.

CLARITY. The fact is that we're all tired of supplier/parts/cities tables, so I suppose it's nice that Melton+Simon use a different model (I didn't try all the examples like Trudy did with Joe Celko's book, so there's no knowing whether they all work or not). In the initial part of the book they use "railroad diagrams" rather than BNF for the formal syntax description; I am more used to BNF so I didn't like this, but doubtless others would feel differently. The discussion of query expressions is hard sledding, and I will condemn Melton+Simon for that as soon as I find someone who makes query expressions seem easy. They address the readership as "you"; they try a few feeble jokes; they try to explain the committee's decisions rather than critique them; they tackle subjects that everyone else avoids (such as multi-table outer joins and the full effect of REVOKE with examples thereof). There is no glossary.

And now, we come to the place we come to in every book review, where the question must be asked: can the book go head-to-head against Date+Darwen's? So far we have examined several potential challengers, but none were really in the Heavyweight Division. This book is serious. It is a contender.

Pro: The Melton+Simon book has more examples, which accounts for its slightly greater bulk. Particularly, the description of the actions of the precompiler is much thicker. I have already mentioned some things (see above: "Clarity") which make Melton+Simon a more pleasant book to just pick up and read.

Con: The Date+Darwen book has only a few minor errors, and certainly nothing in the "falsehood" category like the ones I've described above. Date seems to understand dates and times better, and his description of character sets / collations / translations is about 80% complete (I would say that the Melton+Simon book is about 20% complete).

The winner and still champion: Date+Darwen. Now I'm really looking forward to the next round, when the SQL3 books come out. By the way, the Melton+Simon book predicted that would happen around 1996, but my prediction is 2001. Meantime, do look for UNDERSTANDING THE NEW SQL: A COMPLETE GUIDE. It complements Date+Darwen, and it's good to have an upbeat take on the subject.

... end of book-review extract from THE CAT'S MEOW ...