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

(NULLIF(count(table.col)), 0) gives wrong result on 10.1.x

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.1.8, 10.1(EOL)
    • 10.1.11
    • Optimizer
    • openbsd, windows
    • 10.1.10, 10.1.11

    Description

      I have 4 rows in col1, yet maria 10.1 says on

      select nullif(count(`lame`.`col1`),0) from lame

      that I have 7.
      maria 10.0 and mysql gives 4 here.

      changing to ifnull instead of nullif works fine.

      To recreate this simple bug just use that line on anything you have or to recreate my testdb see below

      CREATE TABLE IF NOT EXISTS `lame` (
      		 
        `col1` varchar(50) DEFAULT NULL
      		 
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
      		 
       
      /*!40000 ALTER TABLE `lame` DISABLE KEYS */;
      		 
      INSERT INTO `lame` (`col1`) VALUES
      		 
              ('hello'),
      		 
              ('hello\r\n'),
      		 
              ('hello'),
      		 
              ('hello');

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          View 6 older comments
          bar Alexander Barkov added a comment -

          Behaviour does not depend on the engine.
          The same problem is demonstrated with ENGINE=MyISAM.

          bar Alexander Barkov added a comment - Behaviour does not depend on the engine. The same problem is demonstrated with ENGINE=MyISAM.
          serg Sergei Golubchik added a comment -

          ok to push, thanks!

          serg Sergei Golubchik added a comment - ok to push, thanks!
          javamarket MT added a comment -

          This appears to have resurfaced with mysql Ver 15.1 Distrib 10.1.12-MariaDB

          javamarket MT added a comment - This appears to have resurfaced with mysql Ver 15.1 Distrib 10.1.12-MariaDB
          elenst Elena Stepanova added a comment -

          javamarket,
          I've just tried the test case from the description, and it seems to work okay, so it's not exactly resurfaced.
          We do have a problem in 10.1.12, MDEV-9637, it's a bit different from this one; and if you have another test case, please paste it as well.

          elenst Elena Stepanova added a comment - javamarket , I've just tried the test case from the description, and it seems to work okay, so it's not exactly resurfaced. We do have a problem in 10.1.12, MDEV-9637 , it's a bit different from this one; and if you have another test case, please paste it as well.
          javamarket MT added a comment -

          @Elena apologies, you are indeed correct. After a bit more investigating it is 9637 already on the radar.

          javamarket MT added a comment - @Elena apologies, you are indeed correct. After a bit more investigating it is 9637 already on the radar.

          People

            bar Alexander Barkov
            sopparus sippanson bernt
            Votes:
            6 Vote for this issue
            Watchers:
            12 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.