Consider these three queries, the first fails with an error, the second
succeeds and third also succeeds. The only difference the set of records
available in the database to match the query.
In the case that fails, the picture record exists but the batchflow
record does not exist. In fact, NO batch flow records exist.
In the case the succeeds, the picture record doesn't exist.
In the second case that succeeds, a NON MATCHING batch flow record
exists.
Isn't (or shouldn't it be) a bug for an update to fail in this way,
caused only by the content of the database?
F:\temp>mysql --version
mysql Ver 12.22 Distrib 4.0.20a, for Win95/Win98 (i32)
# fails.
mysql> UPDATE picture LEFT JOIN batchflow
-> ON batchflow.batch=picture.batch AND batchflow.number=picture.number
-> SET batchflow.needs_reflow=1,picture.batch='foo'
-> WHERE uid='1000024514';
ERROR 1032: Can't find record in 'batchflow'
# specify a nonmatching picture record, all is ok
mysql> UPDATE picture LEFT JOIN batchflow
-> ON batchflow.batch=picture.batch AND batchflow.number=picture.number
-> SET batchflow.needs_reflow=1,picture.batch='foo'
-> WHERE uid='1000001080';
Query OK, 0 rows affected (0.02 sec)
Rows matched: 0 Changed: 0 Warnings: 0
# add a nonmatching batchflow record, all is ok
mysql> insert into batchflow set number='30950-2005',batch='none';
Query OK, 1 row affected (0.00 sec)
mysql> UPDATE picture LEFT JOIN batchflow
-> ON batchflow.batch=picture.batch AND batchflow.number=picture.number
-> SET batchflow.needs_reflow=1,picture.batch='foo'
-> WHERE uid='1000024514';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
# verify that removing the added batchflow record reinstates the error
mysql> delete from batchflow;
Query OK, 1 row affected (0.00 sec)
mysql> UPDATE picture LEFT JOIN batchflow
-> ON batchflow.batch=picture.batch AND batchflow.number=picture.number
-> SET batchflow.needs_reflow=1,picture.batch='foo'
-> WHERE uid='1000024514';
ERROR 1032: Can't find record in 'batchflow'
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
