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.
Attachments
Activity
presence of use_case_id alone of with 'and' spoil everything:
SELECT * FROM V_OVERVIEW;
|
use_case_id InitialDeadline
|
10 2015-12-18
|
20 2015-10-18
|
SELECT * FROM V_OVERVIEW where use_case_id = 10;
|
use_case_id InitialDeadline
|
SELECT * FROM V_OVERVIEW where use_case_id <> 20;
|
use_case_id InitialDeadline
|
SELECT * FROM V_OVERVIEW where InitialDeadline <> "222";
|
use_case_id InitialDeadline
|
10 2015-12-18
|
20 2015-10-18
|
Warnings:
|
Warning 1292 Incorrect datetime value: '222'
|
SELECT * FROM V_OVERVIEW where InitialDeadline <> "222" and use_case_id <> 20;
|
use_case_id InitialDeadline
|
SELECT * FROM V_OVERVIEW where InitialDeadline <> "222" or use_case_id <> 20;
|
use_case_id InitialDeadline
|
10 2015-12-18
|
20 2015-10-18
|
1) repeatable with derived tables
2) use_case_id report NULL
(easy to catch with changing condition to <=)
table info:
(gdb) p field->table->s->path
$3 =
(gdb) p field->table->alias
$4 =
The problem is that "use_case_id = 10" got into outer_ref_condition (which evaluated first when tables are not yet setup).
It got there because OUTER_REF_TABLE_BIT was set.
The bit was set because we have Item_derect_view ref which points inside view (or derived table) on the field which really was dependent:
(gdb) p ((Item_field *)(*((Item_direct_view_ref *)((Item_func_le *)join->outer_ref_cond)->args[0])->ref))->get_depended_from()
|
$18 = (st_select_lex *) 0x2589d20
|
The real problem is that dependence reference somehow "escapes" from WHERE expression of subquery and show up in SELECT list.
revision-id: eebc42f18a7cae934252a1f241fc1cf80a383531 (mariadb-10.0.25-9-geebc42f)
parent(s): 0a50e43e9df2f1e005591e22388e6d20f82edf72
committer: Oleksandr Byelkin
timestamp: 2016-06-17 16:51:11 +0200
message:
MDEV-8642: WHERE Clause not applied on View - Empty result set returned
Do not mark reference UP as a dependent (because it is what the DOWN items dependent from)
—
Above do not create problems in 5.5 only because merging view in the select list is not allowed
revision-id: ea909f28407133364d98718c6650df9763e47ebd (mariadb-10.0.30-21-gea909f2)
parent(s): f2dc04abea172e4c5d701a749902c88f4a626c2c
committer: Oleksandr Byelkin
timestamp: 2017-04-05 13:35:07 +0200
message:
MDEV-8642: WHERE Clause not applied on View - Empty result set returned
An attempt to mark reference as dependent lead to transfering this quality to
original view field and through it to other references of this field which
can't be dependent.
Thanks for the report and the test case.