| Bug #84257 | Update of a view made of joined MYISAM tables returns no rows | ||
|---|---|---|---|
| Submitted: | 19 Dec 2016 9:58 | Modified: | 19 Dec 2016 12:21 |
| Reporter: | Vladimír Jilemnický | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S2 (Serious) |
| Version: | 5.7.17 | OS: | Windows (w10 CZECH NATIONALISATION) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | join, myisam, UPDATE, updateable view, VIEW | ||
[19 Dec 2016 12:09]
MySQL Verification Team
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.17 |
+-----------+
1 row in set (0.00 sec)
mysql> delimiter ; ;
mysql> Create database if not exists Test;
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> use Test;
Database changed
mysql>
mysql> drop view if exists vTest02u;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> drop view if exists vTest021u;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql> drop table if exists Test02 ;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> drop table if exists Test04 ;
create index iNewRow on Test04 (NewRow) ;
create view vTest021u as select Test04.ID, PQuery OK, 0 rows affected, 1 warning (0.00 sec)
odni
k, COM,
CasStamp, sign(Errmysql> or) as
mysql>
mysql> create table Test02 (ID INTEGER UNSIGNED, Error TINYINT UNSIGNED, CasStamp bigint UNSIGNED, E0 TINYINT UNSIGNED, E1 TINYINT UNSIGNED, E2 TINYINT UNSIGNED)
-> ENGINE = MYISAM ;
update vTest021u set Error = Error | 2 where E1>0;
update vTest021u set Error = Error | 4 where E2>0;
#... though select finds 1, 2, 3 rows in 5.7.x version too
Select ID, CasStamp from vTest021u where E0>0;
Select ID, CasStamp from vTest021u where E1>0;
Select ID, CasStamp from vTest021u where E2>0;
#This way it works and ...
update Test04 inner join Test02 on Test04.ID=Test02.ID set Error = Error | 1 where NewRow>0 and E0>0;
update Test04 inner join Test02 on Test04.ID=Test02.ID set Error = Error | 2 where NewRow>0 and E1>0;
update Test04 inner join Test02 on Test04.ID=Test02.ID set Error = Error | 4 where NewRow>0 and E2>0;
#... update of a view with "where" clause on the table where the update is actualy made works too
update vTest02u set Error = Error | 1 where E0>0;
update vTest02u set Error = Error | 2 where E1>0;
update vTest02u set Error = Error | 4 where E2>0;Query OK, 0 rows affected (0.05 sec)
mysql> create UNIQUE index iIDCasStamp on Test02 (ID, CasStamp) ;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create index iID on Test02 (ID) ;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create index iError on Test02 (Error) ;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create table Test04 (ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, Podnik int unsigned, COM char(12), NewRow tinyint, PRIMARY KEY (ID) )
-> ENGINE = MYISAM ;
Query OK, 0 rows affected (0.03 sec)
mysql> create index iPodnikCOM on Test04 (Podnik, COM) ;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create index iNewRow on Test04 (NewRow) ;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> create view vTest021u as select Test04.ID, Podnik, COM,
-> CasStamp, sign(Error) as E, concat(repeat('0', 8-length(bin(Error))), bin(Error)) as Err,
-> NewRow, Error, E0, E1, E2
-> from Test04 inner join Test02
-> on Test04.ID=Test02.ID
-> where NewRow > 0;
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> create view vTest02u as select Test04.ID, Podnik, COM,
-> CasStamp, sign(Error) as E, concat(repeat('0', 8-length(bin(Error))), bin(Error)) as Err,
-> NewRow, Error, E0, E1, E2
-> from Test04 inner join Test02
-> on Test04.ID=Test02.ID
-> where CasStamp > 0;
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> insert into Test04 (Podnik, COM, NewRow) values (71, '111', 0), (71, '112', 0), (72, '222', 1), (72, '223', 1), (72, '224', 1);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> insert into Test02 (ID, Error, CasStamp, E0, E1, E2) values (1, 0, 2016010101, 0,0,0), (1, 0, 2016010102, 0,0,0), (1, 0, 2016010103, 4,0,0), (1, 0, 2016010104, 0, 0, 0);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into Test02 (ID, Error, CasStamp, E0, E1, E2) values (2, 0, 2016010104, 0,0,0), (2, 0, 2016010103, 0,0,16), (2, 0, 2016010102, 0,0,0), (2, 0, 2016010101, 0, 0, 0);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into Test02 (ID, Error, CasStamp, E0, E1, E2) values (3, 0, 2016010104, 0,0,1), (3, 0, 2016010103, 16,0,64), (3, 0, 2016010102, 0,0,0), (3, 0, 2016010101, 0, 0, 0);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into Test02 (ID, Error, CasStamp, E0, E1, E2) values (4, 0, 2016010101, 0,0,0), (4, 0, 2016010103, 0,32,1), (4, 0, 2016010102, 0,0,0), (4, 0, 2016010104, 0, 4, 0);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into Test02 (ID, Error, CasStamp, E0, E1, E2) values (5, 0, 2016010101, 0,0,0), (5, 0, 2016010102, 0,0,0), (5, 0, 2016010104, 0,0,0), (5, 0, 2016010103, 0, 0, 0);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql>
mysql> #This update works fine with InnoDB and MYISAM up to 5.6.35, but returns "Query OK, 0 rows affected (0.00 sec), Rows matched: 0 Changed: 0 Warnings: 0 in 5.7.x version combined with MYISAM table, ...
mysql> update vTest021u set Error = Error | 1 where E0>0;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> update vTest021u set Error = Error | 2 where E1>0;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> update vTest021u set Error = Error | 4 where E2>0;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql>
mysql> #... though select finds 1, 2, 3 rows in 5.7.x version too
mysql> Select ID, CasStamp from vTest021u where E0>0;
+----+------------+
| ID | CasStamp |
+----+------------+
| 3 | 2016010103 |
+----+------------+
1 row in set (0.00 sec)
mysql> Select ID, CasStamp from vTest021u where E1>0;
+----+------------+
| ID | CasStamp |
+----+------------+
| 4 | 2016010103 |
| 4 | 2016010104 |
+----+------------+
2 rows in set (0.00 sec)
mysql> Select ID, CasStamp from vTest021u where E2>0;
+----+------------+
| ID | CasStamp |
+----+------------+
| 3 | 2016010103 |
| 3 | 2016010104 |
| 4 | 2016010103 |
+----+------------+
3 rows in set (0.00 sec)
mysql>
mysql> #This way it works and ...
mysql> update Test04 inner join Test02 on Test04.ID=Test02.ID set Error = Error | 1 where NewRow>0 and E0>0;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update Test04 inner join Test02 on Test04.ID=Test02.ID set Error = Error | 2 where NewRow>0 and E1>0;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> update Test04 inner join Test02 on Test04.ID=Test02.ID set Error = Error | 4 where NewRow>0 and E2>0;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql>
mysql> #... update of a view with "where" clause on the table where the update is actualy made works too
mysql> update vTest02u set Error = Error | 1 where E0>0;
Query OK, 1 row affected (0.00 sec)
Rows matched: 2 Changed: 1 Warnings: 0
mysql> update vTest02u set Error = Error | 2 where E1>0;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2 Changed: 0 Warnings: 0
mysql> update vTest02u set Error = Error | 4 where E2>0;
Query OK, 1 row affected (0.00 sec)
Rows matched: 4 Changed: 1 Warnings: 0
[19 Dec 2016 12:21]
MySQL Verification Team
Hi, Thanks for the test case. Reproduced as described. Best regards Bogdan Kecman
Description: Update of a view made of joined MYISAM tables returns no rows, if the view definition incorporates "where" clause on the table in which the actual update is not made. There is no problem with it up to 5.6.35 version and on InnoDB tables in all versions. Select works fine, returns rows that meet the condition. Rescripting of the update works. Tested on 5.5.19, 5.5.54, 5.6.35, 5.7.15, 5.7.17 (except of 5.5.19 all 64-bit compilation) How to repeat: delimiter ; ; Create database if not exists Test; use Test; drop view if exists vTest02u; drop view if exists vTest021u; drop table if exists Test02 ; drop table if exists Test04 ; create table Test02 (ID INTEGER UNSIGNED, Error TINYINT UNSIGNED, CasStamp bigint UNSIGNED, E0 TINYINT UNSIGNED, E1 TINYINT UNSIGNED, E2 TINYINT UNSIGNED) ENGINE = MYISAM ; create UNIQUE index iIDCasStamp on Test02 (ID, CasStamp) ; create index iID on Test02 (ID) ; create index iError on Test02 (Error) ; create table Test04 (ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, Podnik int unsigned, COM char(12), NewRow tinyint, PRIMARY KEY (ID) ) ENGINE = MYISAM ; create index iPodnikCOM on Test04 (Podnik, COM) ; create index iNewRow on Test04 (NewRow) ; create view vTest021u as select Test04.ID, Podnik, COM, CasStamp, sign(Error) as E, concat(repeat('0', 8-length(bin(Error))), bin(Error)) as Err, NewRow, Error, E0, E1, E2 from Test04 inner join Test02 on Test04.ID=Test02.ID where NewRow > 0; create view vTest02u as select Test04.ID, Podnik, COM, CasStamp, sign(Error) as E, concat(repeat('0', 8-length(bin(Error))), bin(Error)) as Err, NewRow, Error, E0, E1, E2 from Test04 inner join Test02 on Test04.ID=Test02.ID where CasStamp > 0; insert into Test04 (Podnik, COM, NewRow) values (71, '111', 0), (71, '112', 0), (72, '222', 1), (72, '223', 1), (72, '224', 1); insert into Test02 (ID, Error, CasStamp, E0, E1, E2) values (1, 0, 2016010101, 0,0,0), (1, 0, 2016010102, 0,0,0), (1, 0, 2016010103, 4,0,0), (1, 0, 2016010104, 0, 0, 0); insert into Test02 (ID, Error, CasStamp, E0, E1, E2) values (2, 0, 2016010104, 0,0,0), (2, 0, 2016010103, 0,0,16), (2, 0, 2016010102, 0,0,0), (2, 0, 2016010101, 0, 0, 0); insert into Test02 (ID, Error, CasStamp, E0, E1, E2) values (3, 0, 2016010104, 0,0,1), (3, 0, 2016010103, 16,0,64), (3, 0, 2016010102, 0,0,0), (3, 0, 2016010101, 0, 0, 0); insert into Test02 (ID, Error, CasStamp, E0, E1, E2) values (4, 0, 2016010101, 0,0,0), (4, 0, 2016010103, 0,32,1), (4, 0, 2016010102, 0,0,0), (4, 0, 2016010104, 0, 4, 0); insert into Test02 (ID, Error, CasStamp, E0, E1, E2) values (5, 0, 2016010101, 0,0,0), (5, 0, 2016010102, 0,0,0), (5, 0, 2016010104, 0,0,0), (5, 0, 2016010103, 0, 0, 0); #This update works fine with InnoDB and MYISAM up to 5.6.35, but returns "Query OK, 0 rows affected (0.00 sec), Rows matched: 0 Changed: 0 Warnings: 0 in 5.7.x version combined with MYISAM table, ... update vTest021u set Error = Error | 1 where E0>0; update vTest021u set Error = Error | 2 where E1>0; update vTest021u set Error = Error | 4 where E2>0; #... though select finds 1, 2, 3 rows in 5.7.x version too Select ID, CasStamp from vTest021u where E0>0; Select ID, CasStamp from vTest021u where E1>0; Select ID, CasStamp from vTest021u where E2>0; #This way it works and ... update Test04 inner join Test02 on Test04.ID=Test02.ID set Error = Error | 1 where NewRow>0 and E0>0; update Test04 inner join Test02 on Test04.ID=Test02.ID set Error = Error | 2 where NewRow>0 and E1>0; update Test04 inner join Test02 on Test04.ID=Test02.ID set Error = Error | 4 where NewRow>0 and E2>0; #... update of a view with "where" clause on the table where the update is actualy made works too update vTest02u set Error = Error | 1 where E0>0; update vTest02u set Error = Error | 2 where E1>0; update vTest02u set Error = Error | 4 where E2>0;