Historical errata for SQL Antipatterns

It’s “impedance”, not “impedence”.

You state that “Path enumeration allows you to sort a set of rows easily by their hierarchy, as long as the elements between the separator are of consistent length.”

The “consistent length” part sound suprisingly innocuous despite being a serious problem that’s even been discussed before in the Jaywalking pattern. I think this point deserves a little more discussion.

Second sentence in first paragraph of section 2.5: The BugsProducts table implements a many-to-many relationship between bugs and products, not a many-to-one.

In the code for CREATE TABLE TreePaths, the second FOREIGN KEY declaration should be FOREIGN KEY (descendant).

Why do you use one example in the Antipattern introduction and a totally different in the discussion section? I would go for using the storing customers example in both cases.

wouldn’t

SELECT product_id, COUNT (*) AS bugs_per_product FROM BugsProducts
GROUP BY product_id
order by 2 desc
limit 1

be more sensible ?

The table of ancestors and descendants does not include (4, 4).

fig 1.2 there are three crows feet between bugs and accounts. if this is not in error it should be explained.

fig 2. the connection between accounts and comments does not have perpendicular lines on the accounts end. if this is not a typo it should be explained.

2.4 example: a list of email addresses entered by the application, and sent directly to the mail program. any “smart” interpretation on your may break mail, e.g. if a mailing list format changes.

“their respective pseuodocolumn or method” in Choose a random row using an offset

In Oracle, this query will ALWAYS return zero (0) rows: “SELECT * FROM Bugs WHERE ROWNUM = :offset”, unless :offset happens to be exactly 1. You could do this with a subquery, but Oracle provides a much superior and more efficient method of getting a random sample from the table - invoked with the SAMPLE keyword, e.g. “SELECT * FROM Bugs SAMPLE (1)” will randomly pick approximately 1% of the rows from the table. I’ll often use something like this to get a single random row from a table: “SELECT * FROM (SELECT * FROM Bugs SAMPLE (1) ORDER BY DBMS_RANDOM.VALUE) WHERE ROWNUM = 1” - this (a) gets a random sample of approx. 1% of the rows from the table; then (b) sorts them in random order; then © gets first first row.

I dispute that the “Naive Tree” anti-pattern is really an anti-pattern; Oracle supports hierarchical queries based exactly on the type of data model you describe, making it a piece of cake to get all the descendants or ancestors of any given node. In addition, I believe that DB2 and SQL Server 2005 (as well as Oracle 11gR2) also support the ANSI SQL recursive query syntax. Just because something is difficult in some DBMS’s doesn’t make it a bad thing - it may just be a limitation of those DBMS’s.

Where is the row .vs set based operations chapter? Love your existing chapters but find the most frequent AppDev antipattern to be row based operations (cursors, while, etc.) in a set based engine.

The rails lines are incorrect. E.g.:

:belongs_to => :commentable, :polymorphic => true

should be:

belongs_to :commentable, :polymorphic => true

The same syntax is used for has_many.

In “obscuring the meaning of the key”, you say that naming something merely ‘id’ is bad because it’s ambiguous. You then justify this by saying “suppose we have this poorly named table…”

This is not a good argument. It is instead an argument for naming the table properly. “bugs.bug_id” is redundant; “bugs.id” is fully specified; “d_bi_ejoai.bug_id” is a total kludge that doesn’t reliably show what semantic role the bug_id is even playing.

Addendum to rails error: has_many takes a plural, i.e. has_many :comments, :as => :commentable

“Multi-column attributes” should come before “jaywalking”. 1-M relations are conceptually simpler than, and primitive to, M-M relations.

First example under ‘using a derived table’ should list example multiple rows, e.g. 3 / 2009-01-01 / 5680

The last “how to recognize” quote does not refer to the only method referred to thus far (i.e. sort by rand()); it refers to select where id = rand(…).

Put it in correct order - either move the latter up or the former down.

Input_order should be input_userid

You write that mysql lacks windowing. This is true but misleading; it supports limit with offset, which if anything is easier. It deserves a mention.

Section 7.3 How to recognize the Antipattern, third bullet:
Oh, that’s tells you which…. ==>> Oh, that tells you which thing…

‘)’ is missing in:

FOREIGN KEY (issue_id) REFERENCES Issues(issue_id,
>
FOREIGN KEY (issue_id) REFERENCES Issues(issue_id),

#42266 is not fixed

#42270: the ‘derived table’ first example fails to show that it can return multiple bugs per date; the example has all dates unique so implies otherwise. And indeed it’s identical to the 2nd example, thus completely failing to be an example of the difference between the two queries.

I.e. the 1st should have 2 bugs w/ the same date; the 2nd shouldn’t.

“The only remedy is for you to explicitly lock the Bugs table while you’re checking it, and unlock it after you have finished deleting the account. Any architecture that requires that kind of locking is never going to do well when high concurrency and scalability is required.”

This is incorrect and misleading, because it refers only to table locking. For table locking, it is correct. However, most databases (e.g. mysql’s innodb) support row-level locking, which would work well in this scenario and does just fine with concurrency.

Index name for the “Solution:” entry has a spurious “4-1”

The information about Rails’ migrations, and the way the schema metadata is stored in the database, is outdated as of Rails 2.1. See ryandaigle.com/articles/2008/4/2/what-s-new-in-edge-rails-utc-based-migration-versioning and api.rubyonrails.org/classes/ActiveRecord/Migration.html, “About the schema_migrations table”.

P.S. Preventing hyperlinks in these comments is bullshit. Use a real spam classifier and don’t just throw away useful information.

re. #42426: imagebin.ca/img/iQCiMG.png

#42436: Nope, OSX Preview. Same thing in Adobe Reader, so it’s probably a syntax typo on your end rather than a client issue.

Example articletags.sql
Line “FOREIGN KEY (content_id) REFERENCES Articles (id)” should be “FOREIGN KEY (article_id) REFERENCES Articles (id)” otherwise you’ll get the MySQL equivalent of the MS SQL Server message “Foreign key ‘content_id’ references invalid column ‘content_id’ in referencing table ‘ArticleTags’”

Re. primary ID keys vs natural keys: You missed one frequent case, which is that even though there may exist a natural key (which indeed will have a unique index), it can be large, and duplicating this to other tables is wasteful.

For instance, I have a table with ~1M rows whose natural key is ‘url’; it’s a varchar. I have another table with ~10B rows that references the former table. Now, I could reference by url instead and drop the autoinc primary key, but it’s way cheaper for me to do so with an int or bigint than with a varchar everywhere… and it’s also a lot easier to type, have easy to perceive by humans as unique, etc.

Example of queriey that can’t benefit from index include:
• SELECT * FROM Bugs WHERE MONTH (date_reported) = 4;

It’s not good examples. Some databases, for example PostgtreSQL can use functional indexes.

It’d be nice if you covered a few other common issues:

• (belief that) joins are “not scalable”
• denormalization
• good/bad ways to do sharding (and more globally, half-baked ideas for how to make the db “more efficient”)

#42427 is still present.

“The Rails tool that runs migrations automaticaly creates a table schema_info in your database, with one integer column version. This number indicates the current version of your database.”

See my links from last time. In rails >=2.1, there is a “schema_migrations” table with multiple rows listing every migration number that has been applied. They are applied in a canonical order (the migration number is a timestamp), but not in a necessary order like in <2.1. Migrations can be interleaved, e.g. in the case of multiple developers asynchronously committing migration code; all as-yet-unapplied (= not in the schema_migrations table) migrations are applied in order when executing “rake db:migrate”.

I suggest reading the links I gave to understand this in more depth.

PS Typo: s/automaticaly/automatically/g

The last function on page, “search”, has at least two extra semicolons in the construction of the query object.

Your “what’s the matter with Rails” sidebar gets two facts wrong. Paint me a fanboy, and hate Rails as much as you want, but demonstrably false:

- “the rails documentation discourages you from defining constraints in the database”. No, it does not. From the pragprog Rails book page 358, in the chapter named ‘Creating Foreign Keys’ this is mentioned: “You can also choose to establish these constraints in your migrations (and we recommend that you do).” The official Guide on migrations then: “Like anything which operates at the application level these cannot guarantee referential integrity and so some people augment them with foreign key constraints.” True, it does not actively encourage the practice, which is a pity, but discouraing it is not.

- “See no evil: You can see the SQL only by injecting a logger into the ActiveRecord::Base class.” Wrong, you can see every query right in the development log.

This chapter doesn’t really convince me in any case.

“You shouldn’t have to pass SQL queries to the model”. Agreed but you might want to!

“Should I copy complex model queries to all my controllers, or should I code them once in an abstract controller?” Ehm, in the model? Yeah! That’s it.

“If you’re using database fixtures, you’re testing database access, not business logic.” which is why you use stubs. These arguments really don’t hold any value.

“You can even have models in your application that have no interaction with a database.” no shit Sherlock!

“The model is where you implement business logic for the application, and storing it in a database is an internal implemen- tation detail of a model.” like calling save() on the model?

The relevance of this chapter completely escapes me.

In figure 1.2, why are there three “many-to-one” connectors between “Bugs” and “Accounts”?

3rd paragraph 1st sentence:
“Be conservative if decide to employ denormalization.”

It appears there is a missing “you” or something similar:
“Be conservative if you decide to employ denormalization.”

First example on page 106 (using tag1 three times with NULLIF) should be corrected to refer to tag1, tag2, and tag3

In the discussion of the FLOAT type, Oracle remains an oddball. “FLOAT”, as I understand it, actually corresponds to “NUMBER” and is stored in BCD with no loss of accuracy due to roundoffs. Oracle has “BINARY_FLOAT” that is almost IEEE 754, but is actually stored in VAX G FLOAT format, I think.

In third paragraph (excluding SQL examples) of the “Special Scope for Sequences” block, the first sentence reads, “You avoid the race condition, you have to ….”
I think that you meant to write, “To avoid the race….”

Insert “a” between “defines” and “recursive” indicated below in brackets

"Some brands of RDBMS support extensions to SQL to support hierarchies stored in the Adjacency List format. The SQL-99 standard defines [ a ] recursive query syntax using the WITH keyword followed by a common
table expression.

First paragraph should be changed from:
“This query also awkward …”
to:
“This query is also awkward …”

Second to last paragraph should be changed from:
“This solution allows you update or delete …”
to:
“This solution allows you to update or delete …”

“But keep in mind that experienced database consultants
report that systems using EAV become unwieldy within a year.”

I think a citation is appropriate for this statement. Otherwise, it comes across as anecdotal (or based solely as your experience).

On the previous page (233), the reset table is created as AccountResetRequest and the insert statement also references it by this name.

However, in the first paragraph, you say that the token “must match a row in the PasswordResetRequest table”, but it should be the AccountResetRequest table instead.

In the SQL statement Jaywalking/soln/group.sql the Columns are named inconsistently. Either contacts_per_product or accounts_per_product should be renamed as they represent the same column.

All tables defining password_hash column use char(64), but at this page it is char(32).

The first select clause on the page under “Querying Products by Account..” is incorrect.

It should be:

SELECT p.*
FROM Products AS p JOIN Contacts AS c ON (p.product_id = c.product_id)
WHERE c.account_id = 34;

instead of:

SELECT p.*
FROM Products AS p JOIN Contacts AS c ON (p.account_id = c.account_id)
WHERE c.account_id = 34;

There seems to be a problem in the query that finds the product with the greatest number of accounts in SQL Server 2005.

The query is:

SELECT c.product_id, c.accounts_per_product
FROM (
SELECT product_id, COUNT (*) AS contacts_per_product
FROM Contacts
GROUP BY product_id
) AS c
HAVING c.accounts_per_product = MAX (c.accounts_per_product)

Besides the typo (contacts_per_product) should be (accounts_per_product), SQL Server reports the following error here:

“Msg 8121, Level 16, State 1, Line 1
Column ‘c.accounts_per_product’ is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.”

Missing word “to”:

“Another way to structure this is to create a dependent table for multiple associations from the Bugs table the Accounts table and give this new”

Should read:

“from the Bugs table to the Accounts table”

“but his numbers don’t adding up”

Should be

“but his numbers don’t add up” or “but his numbers aren’t adding up”

“In 1953, this famous chain” … “Today, more than sixty years later” — 2013? :)

“… that it isn’t standard SQL. and other brands…”

Incorrect period.

“… database directly. you have to …”

Incorrect period.

I don’t understand why the auth-salt.php example program does so much of the work in SQL rather than in PHP. As written, the script performs two database queries for the same row in the database. Why not select salt, password_hash, where account_name = ‘bill’, and do the comparison in PHP?

There is an error in the auth-salt.php script: when selecting salt, account_name is used; when selecting password_hash, acct_name is used.

Do not create a password reset token using only the account name and the current timestamp in reset-request.sql.

An attacker invoking a password reset mechanism for a given user on the site will already know the username (they supply it) and can guess the current timestamp. (NTP ensures that the clock at the attacker’s computer will be within one or two seconds of the database’s clock. Even if the database isn’t running NTP, it’ll probably be within a few hundred seconds.) Guessing the token with only a username and timestamp is way too easy; much better sources of entropy must be used.

Please look on cwe.mitre.org for flaw 330 for a dozen examples of real problems caused by using insufficient entropy in protocols. (Sorry no URL, pragprog forbids it.)

set-password-escape.php quotes both the userid and the username before sending them through SQL. I think it’d be kind to readers to tell them that they can’t mix-and-match quoting with query parameters in this instance: the password foo’bar would be run through SHA2 as foo’’bar or foo\\’bar or whatever the database adapter uses, but another program elsewhere in the organization that uses query parameters would never be able to validate users with slightly funny (== probably better) passwords.

Maybe it’s too obvious, but I’ve seen dozens of PHP programs out there putting ’’ or \\’ or other such silly things on actual webpages, so there’s legion programmers out there who don’t understand that quoting mangles data.

In “Do I Really Need a Primary Key?”:
The sentence

What should you with a duplicate when you find one?

should be

What should you do with a duplicate when you find one?

Don’t know whether I can say its an error or a suggestion. To my belief I read from the IETF site that the term hacker is some one who do it for good. So according to me the term cracker may be the right thing to say . Check out SQL injection part . " serial computer hacker Albert Gonzalez was indicted
for his role in the largest identity theft in history.
" . So theft comes for crackers and you can see many hackers around the GNU / Linux , GPL licenced s/w. So am I pointing the right thing ? :)

Hari K T

The query presented as an antipattern does not produce a cartesian product. It should produce the results intended (12 fixed bugs, 7 open bugs).

It’s true that the two Bugs table references have no constraints against each-other, but they do constrain to one row in the BugsProducts Table.

I’ve tried the query and get the intended results.

“.. so the rows where the month is equal to 4 are scattered through the table.”
Shouldn’t there be index instead of table here?

The following query is badly broken:
SELECT c.product_id, c.accounts_per_product
FROM (
SELECT product_id, COUNT (*) AS accounts_per_product
FROM Contacts
GROUP BY product_id
) AS c
HAVING c.accounts_per_product = MAX (c.accounts_per_product)

The ‘max’ in the HAVING clause causes an arbitrary row of the table to be compared with the maximum. It’s very possible that this query returns the empty set. Here’s the best I could do:
select product_id, accounts_per_product from
(SELECT product_id, COUNT (*) AS accounts_per_product
FROM Contacts GROUP BY product_id
) AS c,
(select max(accounts_per_product) as most from
(SELECT product_id, COUNT (*) AS accounts_per_product
FROM Contacts GROUP BY product_id
) AS d)
as e
where accounts_per_product = most;

Missing comma after “latest” and needless comma after “bug_id_list”. In code file group-concat-mysql.sql:

SELECT product_id, MAX (date_reported) AS latest
GROUP_CONCAT(bug_id) AS bug_id_list,

should be:

SELECT product_id, MAX (date_reported) AS latest,
GROUP_CONCAT(bug_id) AS bug_id_list

In code file group-concat-pgsql.sql

SELECT product_id, MAX (date_reported) AS latest
ARRAY_TO_STRING(GROUP_ARRAY(bug_id), ‘,’ ) AS bug_id_list,

should be:

SELECT product_id, MAX (date_reported) AS latest,
ARRAY_TO_STRING(GROUP_ARRAY(bug_id), ‘,’ ) AS bug_id_list

Incorrect article in the last paragraph:

…as an quick reference…

Should change to:

…as a quick reference…

In code file Jaywalking/soln/create.sql:

INSERT INTO Contacts (product_id, accont_id)

Should change to:

INSERT INTO Contacts (product_id, account_id)

In
SELECT status FROM BugStatus ORDER by status;
the word “by” is formatted in the wrong color and font.

In the text after the heading “Object-relational mapping:”, first sentence, you have used the word “an” when you should have used “a”. It currently says:

… as part of an layer of …

It should say:

… as part of a layer of …

Creator metadata is invalid in EPUB version. File content.opf, element /package/metadata/dc:creator looks like this:

Bill Karwin

The namespace is not valid here. It should be …purl.org/dc/elements/1.1/. E-book readers that expect valid namespace will not show author information.

(urls were modified to bypass spam filter)

In the PHP source code on the bottom of the page (“Magic-Beans/anti/crud.php”), I think the second line

$bugTable->find(1234);

should probably read

$bug = $bugTable->find(1234);

instead.

The first line of the source code on this page is

$bug->description = $_POST[“summary”];

but should probably be

$bug->description = $_POST[“description”];

instead?

Sometimes you need to make the result of a query against multiple parent tables appear is if you had stored the parents in a single table

=> “is if” should be “as if”

Appendix A: Fifth Normal Form

The BugsAssigned table, under Normalization/5NF-normal.sql, there is a lingering foreign key reference.

I think the last FK reference should not be in that table?
FOREIGN KEY (product_id) REFERENCES Products(product_id)
[which would also remove the comma before that line too]

12 should be 11 as mentioned on page 206

FEAR OF UNKNOWN - INTRO- CHAPTER 14 (ITALIAN VERSION OF P1 ? ) : AT LEAST IN ORACLE IT IS NOT TRUE THAT , AS YOU STATED, IN THE QUERY “SELECT first_name || ’ ’ || middle_initial || ’ ’ || last_name AS full_name
FROM Accounts” SOME RECORDS ARE NOT RETURNED IF ONE OF THE VALUE IS NULL.

There’s an error in the SQL statement for the “Issues” view:

CREATE VIEW Issues AS
SELECT b.*, ‘bug’ AS issue_type
FROM Bugs AS b
UNION ALL
SELECT f.*, ‘feature’ AS issue_type
FROM FeatureRequests AS f;

results in an error, since the tables have different columns; the query should select only the common columns instead.

There’s an error in the DDL statement for the “Bugs” table:

CREATE TABLE Bugs (
bug_id SERIAL PRIMARY KEY,
description VARCHAR (1000),
tag1 VARCHAR (20),
tag2 VARCHAR (20),);
tag3 VARCHAR (20)

This should rather be …
tag2 VARCHAR (20),
tag3 VARCHAR (20));

Please note that this error appears only in the ePub version, NOT in the PDF version.

The book recommends to use separate queries for achieving the goal (“Follow the Single-Value Rule to avoid ambiguous query result”).

Suggestion: Please mention that you can also use windowing functions for this kind of problems (e.g. in Oracle
… row_number() … as rn) where rn = 1) (at least in RDBMS’ that support it).

There’s a typo in the statement for the BugsText index:

CREATE INDEX BugsText ON Bugs(summary) INDEXTYPE IS
CTSSYS.CONTEXT;

The name of the schema is CTXSYS, not CTSSYS (the error appears also in the other statements in this section).

The book states (in the section on Oracle fulltext indices):

“The index doesn’t stay consistent with changes to data, so you have to rebuild the index manually or on a schedule.”

If you create the fulltext index with “PARAMETERS (‘SYNC (ON
COMMIT)’)”, the index is automatically updated for you.

In sidebar “Are Integers a Non-Renewable Resource?” The math is wrong; a 32-bit integer would last 136 years at a rate of 1 insert per second, not 1000 per second.

Appendix 1.1; Figure 26 (Third Normal Form). Table Accounts has tuple {Moe, moe@example.com} repeated. A typo, but technically violates PK {account_id}; not a relation …

Column ‘author’ is FK with NOT NULL referencing Account table.
Column ‘comment_date’ is NOT NULL.
Reply author may not be ‘Kukla’ but ‘Ollie’?

In code file Trees/anti/insert.sql:

INSERT INTO Comments (bug_id, parent_id, author, comment) VALUES (1234, 7, 'Kukla', 'Thanks!');

may be:

INSERT INTO Comments (bug_id, parent_id, author, comment_date, comment) VALUES (1234, 7, 12, CURRENT_TIMESTAMP, 'Thanks!');

Ditto with p.27.
Column ‘author’ is FK with NOT NULL referencing Account table.
Column ‘comment_date’ is NOT NULL.

In code file Trees/soln/path-enum/insert.sql:

INSERT INTO Comments (author, comment) VALUES ('Ollie', 'Good job!');

may be:

INSERT INTO Comments (bug_id, author, comment_date, comment) VALUES (1234, 23, CURRENT_TIMESTAMP, 'Good Job!');

Column ‘author’ is FK with NOT NULL referencing Accounts table.
Column ‘bug_id’ is FK with NOT NULL referencing Bugs table.
Column ‘comment_date’ is NOT NULL.
TYPO in table name. Should be ‘Comments’.

In code file Trees/soln/nested-sets/insert.sql:

INSERT INTO Comment (nsleft, nsright, author, comment) VALUES (8, 9, 'Fran', 'Me too!');

may be:

INSERT INTO Comments (nsleft, nsright, bug_id, author, comment_date, comment) VALUES (8, 9, 1234, 12, CURRENT_TIMESTAMP, 'Me too!');

By default, MySQL does not allow ‘||’ as string concatenation operator but works like ‘Logical OR’ operator. So MySQL users should use CONCAT function or run “set @@session.sql_mode=‘PIPES_AS_CONCAT’;”

MySQL causes ERROR 1093 (HY000) on UPDATE when running Trees/soln/path-enum/insert.sql.

mysql> UPDATE Comments -> SET path = (SELECT path FROM Comments WHERE comment_id = 7) -> || LAST_INSERT_ID() || '/' -> WHERE comment_id = LAST_INSERT_ID(); ERROR 1093 (HY000): You can't specify target table 'Comments' for update in FROM clause

solution may be:

UPDATE Comments SET path = (SELECT x.path FROM (SELECT path FROM Comments WHERE comment_id = 7) AS x) || LAST_INSERT_ID() || '/' WHERE comment_id = LAST_INSERT_ID();

Is this solution suitable for this book?

TYPO in table name. Should be ‘Comments’.

TYPOs are in
p.33: Trees/soln/nested-sets/ancestors.sql
p.34: Trees/soln/nested-sets/depth.sql
p.35: Trees/soln/nested-sets/parent.sql
p.35: Trees/soln/nested-sets/insert.sql

Missing comma after “resolution VARCHAR (1000)”.

In code file Metadata-Tribbles/soln/separate-fixed-length.sql:

CREATE TABLE BugDescriptions ( bug_id BIGINT UNSIGNED PRIMARY KEY, description VARCHAR(1000), -- variable length data type resolution VARCHAR(1000) -- variable length data type FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id) );

should be:

CREATE TABLE BugDescriptions ( bug_id BIGINT UNSIGNED PRIMARY KEY, description VARCHAR(1000), -- variable length data type resolution VARCHAR(1000), -- variable length data type FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id) );

Missing comma after “account_id SERIAL PRIMARY KEY”.

In code file Phantom-Files/anti/create-accounts.sql:

CREATE TABLE Accounts ( account_id SERIAL PRIMARY KEY account_name VARCHAR(20), portrait_image BLOB );

should be:

CREATE TABLE Accounts ( account_id SERIAL PRIMARY KEY, account_name VARCHAR(20), portrait_image BLOB );

Wrong variable name ‘$dbh’

In code file See-No-Evil/anti/no-check.php:

... ② $stmt = $dbh->prepare($sql); ...

may be:

... ② $stmt = $pdo->prepare($sql); ...

First query reports depth = 4 and second query reports depth = 3
TYPO in table name. Should be ‘Comments’ (as I reported in #49750).

In code file Trees/soln/nested-sets/depth.sql:

==
— Reports depth = 3
SELECT c1.comment_id, COUNT (c2.comment_id) AS depth
FROM Comment AS c1
JOIN Comment AS c2
ON c1.nsleft BETWEEN c2.nsleft AND c2.nsright
WHERE c1.comment_id = 7
GROUP BY c1.comment_id;

DELETE FROM Comment WHERE comment_id = 6;

— Reports depth = 2
SELECT c1.comment_id, COUNT (c2.comment_id) AS depth
FROM Comment AS c1
JOIN Comment AS c2
ON c1.nsleft BETWEEN c2.nsleft AND c2.nsright
WHERE c1.comment_id = 7
GROUP BY c1.comment_id;
==

may be:

==
— Reports depth = 4
SELECT c1.comment_id, COUNT (c2.comment_id) AS depth
FROM Comments AS c1
JOIN Comments AS c2
ON c1.nsleft BETWEEN c2.nsleft AND c2.nsright
WHERE c1.comment_id = 7
GROUP BY c1.comment_id;

DELETE FROM Comments WHERE comment_id = 6;

— Reports depth = 3
SELECT c1.comment_id, COUNT (c2.comment_id) AS depth
FROM Comments AS c1
JOIN Comments AS c2
ON c1.nsleft BETWEEN c2.nsleft AND c2.nsright
WHERE c1.comment_id = 7
GROUP BY c1.comment_id;
==

For immediate parent search, parent.sql in p.35 returns empty set.
Should use > and < instead of BETWEEN since > and < are exclusive.
TYPO in table name. Should be ‘Comments’ (as I reported in #49750).
(also related to #49755 ?)

In code file Trees/soln/nested-sets/parent.sql:

SELECT parent.* FROM Comment AS c JOIN Comment AS parent ON c.nsleft BETWEEN parent.nsleft AND parent.nsright LEFT OUTER JOIN Comment AS in_between ON c.nsleft BETWEEN in_between.nsleft AND in_between.nsright AND in_between.nsleft BETWEEN parent.nsleft AND parent.nsright WHERE c.comment_id = 6 AND in_between.comment_id IS NULL;

may be:

SELECT parent.* FROM Comments AS c JOIN Comments AS parent ON parent.nsleft < c.nsleft AND c.nsleft < parent.nsright LEFT OUTER JOIN Comments AS in_between ON in_between.nsleft < c.nsleft AND c.nsleft < in_between.nsright AND parent.nsleft < in_between.nsleft AND in_between.nsleft < parent.nsright WHERE c.comment_id = 6 AND in_between.comment_id IS NULL;

Jaywalking/soln/group.sql uses c.contacts_per_product in the outer query, but the inner query names accounts_per_product.

Tree/soln/nested-sets/insert.sql shows a UPDATE statement to insert a new leaf node below comment #5 which results in NS values 8 and 9 being spaced into the series of NS values. So everything above 8 would have +2 added to it. The where clause in this statement is WHERE nsright >= 7, which seems incorrect to me. I believe it should be WHERE nsright >=8

This would not cause any issue if it were executed at this node of the tree, but would cause an issue if the leaf is being added to a non-leaf node.

For example, if the update was run to add a leaf to comment #6, it would create an error. Comment #6 has nsleft=9 and nsright=12, and it’s child is nsleft=10, nsright=11. If you added a new node, the new node would have values nsleft=12, nsright=13. If you followed the example, and used WHERE nsright >= 11, then you would update the other child’s nsright from 11 to 13, and the new node would also have an nsright=13 which is a duplicate number.

The sentence

“Each of the twelve rows for FIXED bugs is paired with all seven rows for OPEN bugs.”

in the chapter “Antipattern: Solve a Complex Problem in One Step” should be

“Each of the eleven rows for FIXED bugs is paired with all seven rows for OPEN bugs.”

The author refers to Lisp and Haskell as “declarative languages”. That isn’t actually true. In some situations, they can be more declarative than (say) C or Python, but they are nowhere near as declarative as SQL. A declarative language is one in which you state what you want the program to compute but not how to compute it. In Lisp and Haskell, as in most programming languages, you must do both.

1. In the phrase: “Enita non sunt multiplicanda praeter necessitatem” it should be “Entia”, not the “Enita”
2. Wikipedia says these words don’t belong to William of Ockham :)