|
|
Posted by Erland Sommarskog on 06/18/05 20:46
Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
>>Not all people read the newsgroups or other SQL forums. In facr, I would
>>suspect most people don't.
>
> But since this started over what advice I should or should not give in
> the newsgroups, it's safe to assume that the people reading my advice do
> read the groups.
Person A reads your post on the net and follows your advice. A quits
his job to move elsewhere, and B comes in and looks at it says "What
the ****?"
> No. JOIN clauses might result in undesired results (but so could
> subqueries, or any other code), but not in unpredictable code. The
> UPDATE FROM syntax and TOP without ORDER BY are the only constructions
> where the result on the same starting data might change if the optimizer
> picks a different plan.
Any SELECT without an ORDER BY is unpredicatble. It may not be unpredicatble
in the relational realm, since it deals with unordered sets. However, in
real life order matter in many cases. For instance a programmer might
program his SELECT statement that is supposed to return one row incorrectly.
His client program reads that only row that is supposed to be there.
Sometimes that happens to be the row he is actually looking for. And
one day it is not.
>>Sure, the result is not predictable - we know that the entire table
>>will be thrashed.
>
> The result IS predictable. Wrong, perhaps (probably) - but predictable
> and repeatable.
Sorry, I did of course mean to say "is predictable". For once I had one
"not" too many.
And my point is that is irrelevant whether incorrect code is unpredictable
or not. An incorrect result is incorrect, no matter it is predictable
or not.
Now, since last it has occurred to me that I actually have access to
MySQL. So I ran this on MySQL 4.1:
CREATE TABLE tbl (a int NOT NULL,
b int NOT NULL,
total int NULL);
CREATE TABLE tbl2 (a int NOT NULL,
total int NULL);
INSERT tbl2(a) SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
INSERT tbl (a, b)
SELECT 1, 1 UNION ALL SELECT 1, 2 UNION ALL SELECT 1, 3 UNION ALL
SELECT 2, 11 UNION ALL SELECT 2, 21 UNION ALL SELECT 2, 23 UNION ALL
SELECT 3, 101 UNION ALL SELECT 3, 102 UNION ALL SELECT 3, 103;
UPDATE tbl
SET total = (SELECT SUM(b.b)
FROM tbl b
WHERE b.a = tbl.a);
UPDATE tbl2
SET total = (SELECT SUM(b.b)
FROM tbl b
WHERE b.a = tbl2.a);
SELECT * FROM tbl;
SELECT * FROM tbl2;
DROP TABLE tbl;
DROP TABLE tbl2;
(Each statement was a batch of its own it seems.)
The UPDATE of tbl2 worked fine. But the UPDATE of tbl, with the unreadable
syntax that started this thread, failed:
ERROR 1093 (HY000): You can't specify target table 'tbl' for update in
FROM clause
Now, on http://sql-info.de/mysql/gotchas.html you can find a quite a few
issues with MySQL that quite hilarious. So of course, you could disqualify
it as "unserious DBMS". Nveertheless, MySQL has gained quite some
popularity, and is definitely a competitor to SQL Server in the low-end
market, and to some extent in the middle-range as well.
How you actually would write that UPDATE in MySQL, I don't know. But
I have not tried to find out either.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|