Bug #83934 | Invalid select from view result | ||
---|---|---|---|
Submitted: | 23 Nov 2016 13:38 | Modified: | 23 Nov 2016 15:01 |
Reporter: | Evgeny Rogozhnikov | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
Version: | 5.5/5.6 | OS: | Windows |
Assigned to: | CPU Architecture: | Any |
[23 Nov 2016 13:38]
Evgeny Rogozhnikov
[23 Nov 2016 13:41]
Evgeny Rogozhnikov
On 5.7.12 it is all right On 5.5.51 it is the same problem
[23 Nov 2016 14:55]
MySQL Verification Team
c:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 -p --prompt="mysql 5.7 > " Enter password: ****** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.18 Source distribution PULL: 2016-NOV-20 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.7 > USE test Database changed mysql 5.7 > DELIMITER GO mysql 5.7 > CREATE TABLE `tag_lists` ( -> `nId` INT NOT NULL , -> `wstrListName` VARCHAR(256) UNICODE NOT NULL , -> CONSTRAINT `PK_tag_lists__nId` PRIMARY KEY (`nId`) , -> CONSTRAINT `UK_tag_lists__wstrListName` UNIQUE (`wstrListName`) -> )ENGINE=InnoDB DEFAULT CHARACTER SET `ascii` COLLATE `ascii_general_ci` -> GO Query OK, 0 rows affected (0.36 sec) mysql 5.7 > mysql 5.7 > CREATE TABLE `tag_values` ( -> `nId` BIGINT NOT NULL , -> `nListId` INT NOT NULL , -> `wstrValue` VARCHAR(256) UNICODE NOT NULL , -> CONSTRAINT `PK_tag_values__nId` PRIMARY KEY (`nId`) , -> CONSTRAINT `UQ_tag_values` UNIQUE (`nListId`, `wstrValue`) , -> CONSTRAINT `FK_tag_values__nListId` FOREIGN KEY (`nListId`) REFERENCES `tag_lists` (`nId`) ON UPDATE CASCADE ON DELETE CASCADE -> )ENGINE=InnoDB DEFAULT CHARACTER SET `ascii` COLLATE `ascii_general_ci` -> GO Query OK, 0 rows affected (0.27 sec) mysql 5.7 > CREATE INDEX `IX_tag_values_nListId_nId_wstrValue` ON `tag_values` (`nListId`, `nId`, `wstrValue`) -> GO Query OK, 0 rows affected (0.39 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql 5.7 > mysql 5.7 > CREATE TABLE `tag_list_tags` ( -> `nListItemId` BIGINT NOT NULL , -> `nTag` BIGINT NOT NULL , -> CONSTRAINT `UQ_tag_list_tags` UNIQUE (`nListItemId`, `nTag`) -> )ENGINE=InnoDB DEFAULT CHARACTER SET `ascii` COLLATE `ascii_general_ci` -> GO Query OK, 0 rows affected (0.25 sec) mysql 5.7 > CREATE TABLE `tag_test_list` ( -> `nId` BIGINT NOT NULL , -> CONSTRAINT `UK_tag_test_list__nId` UNIQUE (`nId`) -> )ENGINE=InnoDB DEFAULT CHARACTER SET `ascii` COLLATE `ascii_general_ci` -> GO Query OK, 0 rows affected (0.25 sec) mysql 5.7 > CREATE VIEW v_testlist_tags -> AS -> SELECT -> `tag_test_list`.`nId` AS `nTestId` , -> `tag_values`.`wstrValue` AS `wstrTagValue` -> FROM `tag_test_list` -> INNER JOIN `tag_list_tags` ON `tag_list_tags`.`nListItemId` = `tag_test_list`.`nId` -> INNER JOIN `tag_values` ON `tag_list_tags`.`nTag` = `tag_values`.`nId` -> INNER JOIN `tag_lists` ON `tag_values`.`nListId` = `tag_lists`.`nId` -> WHERE `tag_lists`.`wstrListName` = _utf8'TestListName' -> GO Query OK, 0 rows affected (0.11 sec) mysql 5.7 > insert into `tag_lists` (nId, wstrListName) values (1, _utf8'TestListName') -> GO Query OK, 1 row affected (0.02 sec) mysql 5.7 > mysql 5.7 > insert into `tag_values` -> (nId, nListId, wstrValue) values (1, 1, _utf8'TestTagValue') -> GO Query OK, 1 row affected (0.03 sec) mysql 5.7 > mysql 5.7 > insert into `tag_test_list` (nId) values (1) -> GO Query OK, 1 row affected (0.03 sec) mysql 5.7 > mysql 5.7 > insert into `tag_list_tags` -> ( nListItemId, nTag) values ( 1, 1) -> GO Query OK, 1 row affected (0.11 sec) mysql 5.7 > select * from v_testlist_tags GO +---------+--------------+ | nTestId | wstrTagValue | +---------+--------------+ | 1 | TestTagValue | +---------+--------------+ 1 row in set (0.03 sec) mysql 5.7 >
[23 Nov 2016 15:01]
MySQL Verification Team
Thank you for the bug report. c:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > " Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.36 Source distribution PULL: 2016-NOV-20 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.6 > USE test Database changed mysql 5.6 > DELIMITER GO mysql 5.6 > CREATE TABLE `tag_lists` ( -> `nId` INT NOT NULL , -> `wstrListName` VARCHAR(256) UNICODE NOT NULL , -> CONSTRAINT `PK_tag_lists__nId` PRIMARY KEY (`nId`) , -> CONSTRAINT `UK_tag_lists__wstrListName` UNIQUE (`wstrListName`) -> )ENGINE=InnoDB DEFAULT CHARACTER SET `ascii` COLLATE `ascii_general_ci` -> GO Query OK, 0 rows affected (0.42 sec) mysql 5.6 > mysql 5.6 > CREATE TABLE `tag_values` ( -> `nId` BIGINT NOT NULL , -> `nListId` INT NOT NULL , -> `wstrValue` VARCHAR(256) UNICODE NOT NULL , -> CONSTRAINT `PK_tag_values__nId` PRIMARY KEY (`nId`) , -> CONSTRAINT `UQ_tag_values` UNIQUE (`nListId`, `wstrValue`) , -> CONSTRAINT `FK_tag_values__nListId` FOREIGN KEY (`nListId`) REFERENCES `tag_lists` (`nId`) ON UPDATE CASCADE ON DELETE CASCADE -> )ENGINE=InnoDB DEFAULT CHARACTER SET `ascii` COLLATE `ascii_general_ci` -> GO Query OK, 0 rows affected (0.34 sec) mysql 5.6 > mysql 5.6 > CREATE INDEX `IX_tag_values_nListId_nId_wstrValue` ON `tag_values` (`nListId`, `nId`, `wstrValue`) -> GO Query OK, 0 rows affected (0.23 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql 5.6 > mysql 5.6 > CREATE TABLE `tag_list_tags` ( -> `nListItemId` BIGINT NOT NULL , -> `nTag` BIGINT NOT NULL , -> CONSTRAINT `UQ_tag_list_tags` UNIQUE (`nListItemId`, `nTag`) -> )ENGINE=InnoDB DEFAULT CHARACTER SET `ascii` COLLATE `ascii_general_ci` -> GO Query OK, 0 rows affected (0.27 sec) mysql 5.6 > mysql 5.6 > CREATE TABLE `tag_test_list` ( -> `nId` BIGINT NOT NULL , -> CONSTRAINT `UK_tag_test_list__nId` UNIQUE (`nId`) -> )ENGINE=InnoDB DEFAULT CHARACTER SET `ascii` COLLATE `ascii_general_ci` -> GO Query OK, 0 rows affected (0.50 sec) mysql 5.6 > CREATE VIEW v_testlist_tags -> AS -> SELECT -> `tag_test_list`.`nId` AS `nTestId` , -> `tag_values`.`wstrValue` AS `wstrTagValue` -> FROM `tag_test_list` -> INNER JOIN `tag_list_tags` ON `tag_list_tags`.`nListItemId` = `tag_test_list`.`nId` -> INNER JOIN `tag_values` ON `tag_list_tags`.`nTag` = `tag_values`.`nId` -> INNER JOIN `tag_lists` ON `tag_values`.`nListId` = `tag_lists`.`nId` -> WHERE `tag_lists`.`wstrListName` = _utf8'TestListName' -> GO Query OK, 0 rows affected (0.05 sec) mysql 5.6 > insert into `tag_lists` (nId, wstrListName) values (1, _utf8'TestListName') -> GO Query OK, 1 row affected (0.03 sec) mysql 5.6 > mysql 5.6 > insert into `tag_values` -> (nId, nListId, wstrValue) values (1, 1, _utf8'TestTagValue') -> GO Query OK, 1 row affected (0.03 sec) mysql 5.6 > mysql 5.6 > insert into `tag_test_list` (nId) values (1) -> GO Query OK, 1 row affected (0.03 sec) mysql 5.6 > mysql 5.6 > insert into `tag_list_tags` -> ( nListItemId, nTag) values ( 1, 1) -> GO Query OK, 1 row affected (0.02 sec) mysql 5.6 > select * from v_testlist_tags -> GO Empty set (0.01 sec) mysql 5.6 >
[23 Nov 2016 15:11]
MySQL Verification Team
mysql 5.5 > insert into `tag_list_tags` -> ( nListItemId, nTag) values ( 1, 1) -> GO Query OK, 1 row affected (0.03 sec) mysql 5.5 > select * from v_testlist_tags GO Empty set (0.00 sec) mysql 5.5 > SHOW VARIABLES LIKE "%version%"; -> GO +-------------------------+---------------------------------------+ | Variable_name | Value | +-------------------------+---------------------------------------+ | innodb_version | 5.5.55 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.5.55 | | version_comment | Source distribution PULL: 2016-NOV-20 | | version_compile_machine | AMD64 | | version_compile_os | Win64 | +-------------------------+---------------------------------------+ 7 rows in set (0.02 sec)