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

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 10.1.12
    • Fix Version/s: 10.1.13
    • Component/s: Optimizer, Views
    • Labels:
      None
    • Environment:
      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

            Assignee:
            serg Sergei Golubchik
            Reporter:
            sopparus sippanson bernt
            Votes:
            3 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration