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

WHERE Clause not applied on View - Empty result set returned

    XMLWordPrintable

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

          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.