[MDEV-8642] WHERE Clause not applied on View - Empty result set returned Created: 2015-08-18  Updated: 2017-04-06  Resolved: 2017-04-05

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0.20, 10.0, 10.1
Fix Version/s: 10.1.23, 10.0.31, 10.2.6

Type: Bug Priority: Major
Reporter: Christian Schmid Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: merge_view
Environment:

Linux openSUSE

Sprint: 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.

 Comments   
Comment by Elena Stepanova [ 2015-08-18 ]

Thanks for the report and the test case.

Comment by Oleksandr Byelkin [ 2015-12-15 ]

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

Comment by Oleksandr Byelkin [ 2016-04-20 ]

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 =

{str = 0x7fffee461188 "./test/use_case_log", length = 19}

(gdb) p field->table->alias
$4 =

{Ptr = 0x7fffee7e1ef0 "use_case_log", str_length = 12, Alloced_length = 16, extra_alloc = 0, alloced = true, thread_specific = false, str_charset = 0x16a5c40 <my_charset_bin>}
Comment by Oleksandr Byelkin [ 2016-04-25 ]

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

Comment by Oleksandr Byelkin [ 2016-06-17 ]

The real problem is that dependence reference somehow "escapes" from WHERE expression of subquery and show up in SELECT list.

Comment by Oleksandr Byelkin [ 2016-06-17 ]

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)

—

Comment by Oleksandr Byelkin [ 2017-04-05 ]

Above do not create problems in 5.5 only because merging view in the select list is not allowed

Comment by Oleksandr Byelkin [ 2017-04-05 ]

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.

Generated at Thu Feb 08 07:28:41 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.