Bug #10773 Incorrect message is displayed while updating a view
Submitted: 20 May 2005 18:34 Modified: 23 May 2006 10:47
Reporter: Disha Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Views Severity:S3 (Non-critical)
Version:5.0.5-beta-nt OS:Linux (Redhat Linux 9.0)
Assigned to: Georgi Kodinov CPU Architecture:Any

[20 May 2005 18:34] Disha
Description:
When we create a table 'res_t033' and create a view on table 'res_t033' with multiple select statement.And update the view it displays incorrect error message.

How to repeat:
1. Set the delimiter ie execute the SQL statement:
        delimiter //

2. Change the database to 'test1' ie execute the SQL statement:
        Use test1//

3. Create a table 'res_t033' ie execute the SQL statement:
         Create table res_t033 (f59 int, f60 int, f61 int) //

4. Insert values in table 'res_t033' ie execute the SQL statement:
         Insert into res_t033 values (19,41,32)  //

5. Create view 'v_4_402034' ie execute the SQL statement:
         create view v_4_402034 as select f59, f60 from res_t033 where f59 in  
         (select f59 from res_t033)//

6. Update the view 'v_4_402034' ie execute the SQL statement:
          update v_4_402034 set f60=2345//

Observed : Step 6 gives an error message as "ERROR 1093 (HY000) : You can't specify target table 'v_4_402034' for update in FORM clause".

Expected Result: It should give an error message as " ERROR 1288 (HY000) : The target table 'v_4_402033 of the Update is not updatable'.

Actual Result: It gives an error message "ERROR 1093 (HY000) : You can't specify target table 'v_4_402034' for update in FORM clause".
[24 May 2005 6:26] MySQL Verification Team
From our manual:

http://dev.mysql.com/doc/mysql/en/update.html

Currently, you cannot update a table and select from the same table in a subquery.

Then looks to me a correct error message and more precise because
shows the cause.
[24 Jun 2005 18:21] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26416
[22 Jul 2005 18:28] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27491
[2 Aug 2005 19:55] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27811
[23 Aug 2005 21:27] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/28722
[23 May 2006 10:47] Georgi Kodinov
Thank you for taking the time to report a problem.  Unfortunately
you are not using a current version of the product your reported a
problem with -- the problem might already be fixed. Please download
a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions,
please change the version on this bug report to the version you
tested and change the status back to "Open".  Again, thank you for
your continued support of MySQL.

Additional info:

This bug appears fixed in 5.0.22