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

select nullif(count(col1),0) gives wrong result if in a view

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.1.12
    • 10.1.13
    • Optimizer, Views
    • None
    • openbsd, windows

    Description

      This is related to MDEV-9181 and MDEV-9576.

      select nullif(count(col1),0) from table1

      returns 3

      creating a view with the same query and it returns 5.
      to recreate ->

       
      CREATE DATABASE IF NOT EXISTS `testdb5` /*!40100 DEFAULT CHARACTER SET latin1 */;
      USE `testdb5`;
       
      CREATE TABLE IF NOT EXISTS `table1` (
        `col1` varchar(50) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
       
       
      /*!40000 ALTER TABLE `table1` DISABLE KEYS */;
      INSERT INTO `table1` (`col1`) VALUES
      	('hello'),
      	('hello'),
      	('hello');
      /*!40000 ALTER TABLE `table1` ENABLE KEYS */;
       
       
      CREATE TABLE `testview` (
      	`nullif(count(col1),0)` BIGINT(21) NULL
      ) ENGINE=MyISAM;
       
      DROP TABLE IF EXISTS `testview`;
      CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `testview` AS select nullif(count(col1),0) from table1 ;

      then

      select * from testview

      Attachments

        Activity

          People

            serg Sergei Golubchik
            sopparus sippanson bernt
            Votes:
            3 Vote for this issue
            Watchers:
            5 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.