| Bug #6054 | query fails or succeeds, dependent on data rather than syntax | ||
|---|---|---|---|
| Submitted: | 13 Oct 2004 0:25 | Modified: | 16 Dec 2004 16:23 |
| Reporter: | Dave Dyer | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S2 (Serious) |
| Version: | 4.0.20a, 4.1, 5.0 | OS: | Windows (win2k) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[13 Oct 2004 19:57]
MySQL Verification Team
Hi, Thank you for the report. I can't reproduce this error using my test data. Please, upload dump of your tables for testing.
[13 Oct 2004 20:08]
Dave Dyer
database for bug 6054
Attachment: winback.zip (application/x-zip-compressed, text), 126.25 KiB.
[13 Oct 2004 20:11]
Dave Dyer
I uploaded the database I used to generate the bug report.
[13 Oct 2004 21:28]
MySQL Verification Team
Verified with latest BK 4.0 tree. OS: Linux, Windows
[16 Dec 2004 15:32]
Timour Katchaounov
The bug was due to that there was a 0.001 factor added to the cost in the new greedy optimizer, but it was not added to the old optimizer.
[16 Dec 2004 16:23]
Timour Katchaounov
Please ignore the previous comment - was for another bug. This one is duplicate #5837, and which is fixed bu now.
Description: Consider these three queries, the first fails with an error, the second succeeds and third also succeeds. The only difference is the set of records available in the database to match the query. My guess is that the difference between success and failure is how the constants in the query interact with the indeces. 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 it a bug (or shouldn't it be) for an UPDATE query to fail in this way, depending on 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' How to repeat: I can supply the schema and content in a reasonably compact form. Suggested fix: update and select queries should only fail with errors when the syntax is incorrect.