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

syntax error on view with nullif and count

Details

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

    Description

      A valid SQL query is

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

      now if I create this as a view with

      create view counts4 AS select nullif(count(distinct col1),0) from table1 ;

      and try to look at the view I get an error

      select * from counts4;
      		 
      /* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '<cache>(count(distinct `testdb5`.`table1`.`col1`)),0) AS `nullif(count(distinct ' at line 1 */

      this exact same procedure works fine on mariadb 10.0.23
      and this bug may have something to do with https://lists.launchpad.net/maria-developers/msg09111.html

      I can't test it on 10.1.10 since windows wont allow me to downgrade and I cant uninstall.
      This (again) affects all kodi users that can't create db's anymore

      to recreate db

      CREATE DATABASE IF NOT EXISTS `testdb5` 
      USE `testdb5`;
      		 
       
      		 
      CREATE TABLE IF NOT EXISTS `table1` (
      		 
        `col1` varchar(50) DEFAULT NULL
      		 
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

      Thanks!

      Attachments

        Activity

          elenst Elena Stepanova added a comment -

          Thanks for the report.

          create table table1 (col1 int);
          		 
          create view counts4 AS select nullif(count(distinct col1),0) from table1 ;
          		 
          show create view counts4;

          The behavior changed with this commit:

          commit ce40ccaf24af2fe395f541cb1079256de8727ccd
          		 
          Author: Alexander Barkov <bar@mariadb.org>
          		 
          Date:   Thu Jan 28 13:58:39 2016 +0400
          		 
           
          		 
              MDEV-9181 (NULLIF(count(table.col)), 0) gives wrong result on 10.1.x
          		 
              Wrapping args[0] and args[2] into an Item_cache for aggregate functions.

          Before the commit, SHOW CREATE VIEW would pass on a non-debug build; but on debug build, we wouldn't even get this far, because CREATE VIEW would cause an assertion failure. The patch made the assertion failure go away, but now SHOW CREATE fails as described.

          elenst Elena Stepanova added a comment - Thanks for the report. create table table1 (col1 int); create view counts4 AS select nullif(count(distinct col1),0) from table1 ; show create view counts4; The behavior changed with this commit: commit ce40ccaf24af2fe395f541cb1079256de8727ccd Author: Alexander Barkov <bar@mariadb.org> Date: Thu Jan 28 13:58:39 2016 +0400   MDEV-9181 (NULLIF(count(table.col)), 0) gives wrong result on 10.1.x Wrapping args[0] and args[2] into an Item_cache for aggregate functions. Before the commit, SHOW CREATE VIEW would pass on a non-debug build; but on debug build, we wouldn't even get this far, because CREATE VIEW would cause an assertion failure. The patch made the assertion failure go away, but now SHOW CREATE fails as described.

          People

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