MySQL Bugs: #17726: mysqld hangs at update of view with check option

Bug #17726	mysqld hangs at update of view with check option
Submitted:	26 Feb 2006 20:47	Modified:	14 Mar 2006 18:34
Reporter:	Ghica van Emde Boas
Status:	Closed
Category:	Server: Views	Severity:	S2 (Serious)
Version:	5.0.18/5.0.19 BK	OS:	Microsoft Windows (Win XP Prof/Linux Suse)
Assigned to:	Evgeny Potemkin	Target Version:

Description:
This bug may be related to 16813 but the symptoms are more serious: mysqld turns up the
cpu consumption to 100% and the only way to stop is to power-off the PC.

I have three tables and two views, the definitions of which I will attach as a file.
The first view is a join of the three tables, and it is perfectly updatable on all three
tables. The second view is something like: all employees with a salary > 25000 with check
option.
I am trying an UPDATE statement which takes a row outside the view and increases the
salary to be in the view. I am not sure whether the check option should fail or not, in
any case it should not hang!
 

How to repeat:
Import the three tables and two views, then do this update statement:

UPDATE Topverdiener 
 SET salaris = 35000
 WHERE id =5;

and mysqld will hang.

Suggested fix:
An OK result, or an error message, whatever is appropriate.

sample table and view defintions to recreate bug

Attachment: viewbug.sql (application/octet-stream, text), 3.93 KiB.

Thank you for the bug report. I was able to repeat on Linux/Windows.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.19-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+----------------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State | Info                           
                         |
+----+------+-----------+------+---------+------+-------+----------------------------------------------------------+
|  2 | root | localhost | db15 | Query   |  190 | init  | update topverdiener 
 set salaris = 35000
 where id =5 |
|  3 | root | localhost | NULL | Sleep   |   83 |       | NULL                           
                         |
<cut>

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/commits/3430

When the Item_cond::fix_fields() function reduces cond tree, it in loop
scans it's own list and when it founds Item_cond with same function (AND
or OR) it does next things: 1) replaces that item with item's list. 2)
empties item's list. Due to this operation is done twice - for update and
for view, at the update phase cond's list of lower view is already empty.
Empty list returns ref to itself, thus making endless loop by replacing
list with itself, emptying, replacing again and so on. This results in
server hung up.

Fixed in 5.0.20, cset 1.2053.40.1

Fixed in 5.1.8

Documented in 5.0.20 and 5.1.8 changelogs.