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

WHERE Clause not applied on View - Empty result set returned

        Details

        • Type: Bug
        • Status: Confirmed
        • Priority: Major
        • Resolution: Unresolved
        • Affects Version/s: 10.0.20, 10.1, 10.0
        • Fix Version/s: 10.1, 10.0
        • Component/s: Optimizer
        • Labels:
        • Environment:
          Linux openSUSE

          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.

            Gliffy Diagrams

              Attachments

                Activity

                Hide
                Permalink
                elenst Elena Stepanova added a comment -

                Thanks for the report and the test case.

                Show
                elenst Elena Stepanova added a comment - Thanks for the report and the test case.

                  People

                  • Assignee:
                    sanja Oleksandr Byelkin
                    Reporter:
                    Christian.Schmid Christian Schmid
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    2 Start watching this issue

                    Dates

                    • Created:
                      Updated: