Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8642

WHERE Clause not applied on View - Empty result set returned

Details

    • 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

          Ascending order - Click to sort in descending order
          elenst Elena Stepanova added a comment -

          Thanks for the report and the test case.

          elenst Elena Stepanova added a comment - Thanks for the report and the test case.
          sanja Oleksandr Byelkin added a comment -

          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

          sanja Oleksandr Byelkin added a comment - 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
          sanja Oleksandr Byelkin added a comment -

          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>}
          sanja Oleksandr Byelkin added a comment - 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>}
          sanja Oleksandr Byelkin added a comment - - edited

          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

          sanja Oleksandr Byelkin added a comment - - edited 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
          sanja Oleksandr Byelkin added a comment -

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

          sanja Oleksandr Byelkin added a comment - The real problem is that dependence reference somehow "escapes" from WHERE expression of subquery and show up in SELECT list.
          sanja Oleksandr Byelkin added a comment -

          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)

          —

          sanja Oleksandr Byelkin added a comment - 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) —
          sanja Oleksandr Byelkin added a comment -

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

          sanja Oleksandr Byelkin added a comment - Above do not create problems in 5.5 only because merging view in the select list is not allowed
          sanja Oleksandr Byelkin added a comment -

          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.

          sanja Oleksandr Byelkin added a comment - 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.

          People

            sanja Oleksandr Byelkin
            Christian.Schmid Christian Schmid
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.