Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.20, 10.0(EOL), 10.1(EOL)
-
Linux openSUSE
-
10.0.25, 10.0.26, 10.0.30
Description
When using a WHERE Clause on a View under some circumstances (View with subqueries) results in an empty result set:
Steps to reproduce:
Create dummy table
CREATE TABLE `use_case_log` (
|
`id` int(20) NOT NULL AUTO_INCREMENT,
|
`use_case` int(11) DEFAULT NULL,
|
`current_deadline` date DEFAULT NULL,
|
`ts_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `id_UNIQUE` (`id`)
|
) ENGINE=MyISAM AUTO_INCREMENT=13976 DEFAULT CHARSET=latin1;
|
Insert dummy data:
INSERT INTO `use_case_log` VALUES (1,10,'2015-12-18','2015-08-18 08:38:16');
|
INSERT INTO `use_case_log` VALUES (2,20,'2015-10-18','2015-08-18 08:43:30');
|
Create a view:
CREATE VIEW V_OVERVIEW AS SELECT
|
use_case as use_case_id,
|
(
|
SELECT
|
deadline_sub.current_deadline
|
FROM
|
use_case_log deadline_sub
|
WHERE
|
deadline_sub.use_case = use_case_id
|
AND ts_create = (SELECT
|
MIN(ts_create)
|
FROM
|
use_case_log startdate_sub
|
WHERE
|
startdate_sub.use_case = use_case_id
|
)
|
) AS InitialDeadline
|
FROM
|
use_case_log
|
Query the view with a WHERE clause:
|
SELECT * FROM V_OVERVIEW where use_case_id = 10;
|
Actual Result
use_case_id | InitialDeadline |
---|
Expected Result
use_case_id | InitialDeadline |
---|---|
10 | 2015-12-18 |
Hints
- Using no WHERE Clause at all in the Query works:
SELECT * FROM V_OVERVIEW;
use_case_id InitialDeadline 10 2015-12-18 20 2015-10-18 - When setting the VIEW Algorithm to "TEMPTABLE" it works too
- In MySQL 5.5.43 the correct result is returned.