MySQL Bugs: #83934: Invalid select from view result

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:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.5/5.6	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
I have some view - v_testlist_tags

The query "select * from v_testlist_tags" return empty result.
It is wrong result. The command "describe select * from v_testlist_tags" returned error "Impossible WHERE noticed after reading const tables"

But if replace in query name of the view into it definition then it is worked correctly.

How to repeat:
I create minimal script where problem is reproduced

1) Create tables

	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

	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

	CREATE INDEX `IX_tag_values_nListId_nId_wstrValue` ON `tag_values` (`nListId`, `nId`, `wstrValue`)
	GO

	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

	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

2) Create problem view 	

	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
	
3) Filling tables

		insert into `tag_lists` (nId, wstrListName) values (1, _utf8'TestListName')
		GO

		insert into `tag_values`
		(nId, nListId, wstrValue) values (1, 1, _utf8'TestTagValue')
		GO

		insert into `tag_test_list` (nId) values (1)
		GO

		insert into `tag_list_tags`
		( nListItemId, nTag) values ( 1, 1)
		GO
		
4) Now try to execute select

			select * from v_testlist_tags
			
			It is return empty result. It is wrong.
			
5) If replace view name into view definition then it is all right

	select *  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' 
	
	it is returned:
	1	1	1	1	1	1	TestTagValue	1	TestListName

On 5.7.12 it is all right

On 5.5.51 it is the same problem

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 >

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 >

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)