MySQL Bugs: #9686: inconsistent update with selfreference via merge table

Bug #9686	inconsistent update with selfreference via merge table
Submitted:	6 Apr 2005 16:44	Modified:	3 Oct 2008 13:32
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Merge storage engine	Severity:	S3 (Non-critical)
Version:	4.1.15-BK	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
See the 2 examples given below.

In the first cache the subquery contains a rand(), wich is why I assume it is not cached. order by rand is not relevant for the result, as the qury return only one row.

The 2nd example is cached, the result of the update differs.

I believe the first result to be correct, as when each row gets updated, the maximum+1 has been increased by the previous row.

The flush table is needed to work around another bug, which I will report seperatly.

How to repeat:
CREATE TABLE t1 (a int(11));
CREATE TABLE t2 (a int(11)) ENGINE=MRG_MyISAM UNION=(t1); 

insert into t1 values (1), (1), (1);
update t1 set a = (select max(a)+1 from tm order by rand());
select * from t1;
+------+
| a    |
+------+
|    2 |
|    3 |
|    4 |
+------+

drop table t2;
drop table t1;
flush tables;

CREATE TABLE t1 (a int(11));
CREATE TABLE t2 (a int(11)) ENGINE=MRG_MyISAM UNION=(`t1`);

insert into t1 values (1), (1), (1);
update t1 set a = (select max(a)+1 from tm order );
select * from t1;
+------+
| a    |
+------+
|    1 |
|    1 |
|    1 |
+------+

drop table t2;
drop table t1;

Suggested fix:
There are two options:

1) Currently the updated table is not allowed as a datasource to the query. The sample queries bypass thsi by using a merge table (which mysql treads as a different table). The query parser could determine the "real" tables behind the merge, and return the error.

2) (my preference) You would still need to determine the tables behind the merge. The "cachability" of the subquery could then be determined on its using tables that are updated in the outer query

actually the bug goes even further, the example without rand() order should at least return the value "2" for all rows. ( max(1)+1 = 2 )

So I removed this beeing a "cache" error

sorry, just seen after pasting and copying, I left an error in the 2nd set of sql'

instead of
update t1 set a = (select max(a)+1 from tm order );

it should be
update t1 set a = (select max(a)+1 from tm  );

setting all results to 2

However the behaviour is the same with 
set GLOBAL query_cache_limit=0

so it seems to be cached outside the query cache

Reverify, please.

The problem as originally reported is either a bug (I think) or a "feature" that should be described. Verified on latest 4.1.15-BK build:

mysql> create table t1 (a int(11)) engine=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t2 (a int(11)) engine=MRG_MyISAM UNION=(t1);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t1 values (1), (1), (1);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> update t1 set a = (select max(a)+1 from t2 order by rand());
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from t1;
+------+
| a    |
+------+
|    2 |
|    3 |
|    4 |
+------+
3 rows in set (0.00 sec)

So, we have inconsistem results really.

mysql> update t1 set a = 1;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from t1;
+------+
| a    |
+------+
|    1 |
|    1 |
|    1 |
+------+
3 rows in set (0.01 sec)

mysql> update t1 set a = (select max(a)+1 from t1 order by rand());
ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM clause

Because of MERGE table usage (and what about views in 5.0.x?)...

This query, from the later comment:

mysql> update t1 set a = (select max(a)+1 from t2);
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from t1;
+------+
| a    |
+------+
|    2 |
|    2 |
|    2 |
+------+
3 rows in set (0.00 sec)

give consistent result really. Yes, there was a cache:

mysql> show variables like 'query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
5 rows in set (0.01 sec)

But it does not metter:

mysql> set GLOBAL query_cache_limit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'query_cache%';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| query_cache_limit            | 0     |
| query_cache_min_res_unit     | 4096  |
| query_cache_size             | 0     |
| query_cache_type             | ON    |
| query_cache_wlock_invalidate | OFF   |
+------------------------------+-------+
5 rows in set (0.00 sec)

mysql> update t1 set a = 1;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> update t1 set a = (select max(a)+1 from t1 order by rand());
ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM clause
mysql> update t1 set a = (select max(a)+1 from t2 order by rand());
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from t1;
+------+
| a    |
+------+
|    2 |
|    3 |
|    4 |
+------+
3 rows in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 4.1.15-debug |
+--------------+
1 row in set (0.00 sec)

I guess this one will be fixed (in the sense of Martins proposal no. 1) when I fix Bug #5390.

Unfortunately, it's not. The bug still exists in 4.1.19. Bug#5390 was fixed in 4.1.17.

I think this is working as designed.

There is no way for mysqld to know the dependencies between the MyISAMMRG table and the MyISAM table - as far as mysqld is concerned, they are logically two independent tables.

The optimiser will examine subselects to see if they may be replaced with a fixed constant. In this case, the statement "SELECT max(a)+1 FROM t1" is seen as a deterministic statement which may be optimized by being replaced with a constant.

Appending to the subselect a "ORDER BY RAND()" is a simple/effective (and probably documented somewhere) method to prevent the optimiser form replacing the subselect with a constant.

Therefore, the two UPDATE statements will have a different result because of how one table is working as an alias for the other. I would imagine that this same behaviour is possible without having to use MyISAMMRG: Start mysqld with external locking enabled, create two MyISAM tables with the same row format, hard link one tables MYD file to the other.

I don't believe this is a kind of bug that is to be fixed for 5.1.
The fix would require introduction of new handler functions such as
get_first_underlying_table and get_net_underlying_table.
In other words the fix would require new architectural features.
At the same time I think the problem should be fixed in future versions
for sure.

We fixed a number of merge table issues in 5.1 and 6.0.
The bug needs to be re-verified.

Not repeatable any more with 5.0.66a, 5.1.28 and 6.0.6:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.28-rc-community MySQL Community Server (GPL)

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

mysql> drop table t1;
Query OK, 0 rows affected (0.03 sec)

mysql> drop table t2;
Query OK, 0 rows affected (0.03 sec)

mysql> create table t1 (a int(11)) engine=MyISAM;
Query OK, 0 rows affected (0.08 sec)

mysql> create table t2 (a int(11)) engine=MRG_MyISAM UNION=(t1);
Query OK, 0 rows affected (0.09 sec)

mysql> insert into t1 values (1), (1), (1);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> update t1 set a = (select max(a)+1 from t2 order by rand());
ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM claus
e
mysql>  update t1 set a = (select max(a)+1 from t1 order by rand());
ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM claus
e