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 9:58]
Vladimír Jilemnický
[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