Errata for SQL Antipatterns

#49745: 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!');
==========
--Takuto Wada

#49746: 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!');
==========
--Takuto Wada

#49749: 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?
--Takuto Wada

#49755: 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;
==========
--Takuto Wada

#49747: 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!');
==========
--Takuto Wada

#49756: 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;
==========
--Takuto Wada

#49707: 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 ...--Damir Sudarevic

#49565: gotchas in this book. I fwololed the examples step by step without any problem. I think some people have used the book's online forum to ask about which packages to import, but when I used Eclipse it was done automatically for me. (By the way, all the files are also online.)Even after having many months of Android development under my belt, I find that I still refer back to this book from time to time. That's saying something for a book that sets out to be an introduction.Remember, this book is a great introduction. If you already know Android and are looking for a deep-dive, look elsewhere. But if you are curious about all the excitement around Android and have a few hours to spare, spend them with this book and find out what developing in Android is all about.--bNXIerhABJktQbLs